DoCmd.OpenForm criteria not working.... help! - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

DoCmd.OpenForm criteria not working.... help!

lessthanmelessthanme Posts: 17Member
I'm new to VBA and need a wee bit of help on this code. in the linkcriteria, the finishedDate IS NULL part isnt working. I've tried changing the syntax but I either get an error saying that VB doesnt recognize a field/table, that the object is required, or it seems to work just fine but when the form opens, its displaying fields that have finishedDate. help!

---------------------------------------------------------------------

Private Sub openformTaskList_Click()
On Error GoTo Err_openformTaskList_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "taskList"
DoCmd.OpenForm stDocName, , , finishedDate Is Null

[Forms]![taskList]![approved].Visible = False
[Forms]![taskList]![approvedlabel].Visible = False
[Forms]![taskList]![refreshTaskListNext30Days].Visible = False
[Forms]![taskList]![refreshTaskListToBeApproved].Visible = False


Exit_openformTaskList_Click:
Exit Sub

Err_openformTaskList_Click:
MsgBox Err.description
Resume Exit_openformTaskList_Click

End Sub

Comments

  • RikRik Posts: 56Member
    : I'm new to VBA and need a wee bit of help on this code. in the linkcriteria, the finishedDate IS NULL part isnt working. I've tried changing the syntax but I either get an error saying that VB doesnt recognize a field/table, that the object is required, or it seems to work just fine but when the form opens, its displaying fields that have finishedDate. help!
    :
    : ---------------------------------------------------------------------
    :
    : Private Sub openformTaskList_Click()
    : On Error GoTo Err_openformTaskList_Click
    :
    : Dim stDocName As String
    : Dim stLinkCriteria As String
    :
    : stDocName = "taskList"
    : DoCmd.OpenForm stDocName, , , finishedDate Is Null
    :
    : [Forms]![taskList]![approved].Visible = False
    : [Forms]![taskList]![approvedlabel].Visible = False
    : [Forms]![taskList]![refreshTaskListNext30Days].Visible = False
    : [Forms]![taskList]![refreshTaskListToBeApproved].Visible = False
    :
    :
    : Exit_openformTaskList_Click:
    : Exit Sub
    :
    : Err_openformTaskList_Click:
    : MsgBox Err.description
    : Resume Exit_openformTaskList_Click
    :
    : End Sub
    :
    Access sees the expression [italic]finishedDate Is Null[/italic] (without quote marks) as a boolean expression, because Is Null is a boolean operator. Access will try to find a variable called [italic]finishedDate[/italic], to test it for 'nullness'. If there are no variables called [italic]finishedDate[/italic], access will complain. If there is a variable called [italic]finishedDate[/italic], access will evaluate the expression [italic]finishedDate Is Null[/italic] to be either True or False and pass that value to the OpenForm method.
    However, what the OpenForm method wants is a [b]string[/b], not a boolean.
    What you need is:
    [code]
    Private Sub openformTaskList_Click()
    On Error GoTo Err_openformTaskList_Click
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "taskList"

    ' Pass the criteria as a string, by using quotes
    DoCmd.OpenForm stDocName, , , "finishedDate Is Null"

    ' Or you could use the variable that the access wizard
    ' has provided (better):
    ' stLinkCriteria = "finishedDate Is Null"
    ' DoCmd.OpenForm stDocName, , , stLinkCriteria

    [Forms]![taskList]![approved].Visible = False
    [Forms]![taskList]![approvedlabel].Visible = False
    [Forms]![taskList]![refreshTaskListNext30Days].Visible = False
    [Forms]![taskList]![refreshTaskListToBeApproved].Visible = False

    Exit_openformTaskList_Click:
    Exit Sub
    Err_openformTaskList_Click:
    MsgBox Err.description
    Resume Exit_openformTaskList_Click
    End Sub
    [/code]

  • lessthanmelessthanme Posts: 17Member
    : : I'm new to VBA and need a wee bit of help on this code. in the linkcriteria, the finishedDate IS NULL part isnt working. I've tried changing the syntax but I either get an error saying that VB doesnt recognize a field/table, that the object is required, or it seems to work just fine but when the form opens, its displaying fields that have finishedDate. help!
    : :
    : : ---------------------------------------------------------------------
    : :
    : : Private Sub openformTaskList_Click()
    : : On Error GoTo Err_openformTaskList_Click
    : :
    : : Dim stDocName As String
    : : Dim stLinkCriteria As String
    : :
    : : stDocName = "taskList"
    : : DoCmd.OpenForm stDocName, , , finishedDate Is Null
    : :
    : : [Forms]![taskList]![approved].Visible = False
    : : [Forms]![taskList]![approvedlabel].Visible = False
    : : [Forms]![taskList]![refreshTaskListNext30Days].Visible = False
    : : [Forms]![taskList]![refreshTaskListToBeApproved].Visible = False
    : :
    : :
    : : Exit_openformTaskList_Click:
    : : Exit Sub
    : :
    : : Err_openformTaskList_Click:
    : : MsgBox Err.description
    : : Resume Exit_openformTaskList_Click
    : :
    : : End Sub
    : :
    : Access sees the expression [italic]finishedDate Is Null[/italic] (without quote marks) as a boolean expression, because Is Null is a boolean operator. Access will try to find a variable called [italic]finishedDate[/italic], to test it for 'nullness'. If there are no variables called [italic]finishedDate[/italic], access will complain. If there is a variable called [italic]finishedDate[/italic], access will evaluate the expression [italic]finishedDate Is Null[/italic] to be either True or False and pass that value to the OpenForm method.
    : However, what the OpenForm method wants is a [b]string[/b], not a boolean.
    : What you need is:
    : [code]
    : Private Sub openformTaskList_Click()
    : On Error GoTo Err_openformTaskList_Click
    : Dim stDocName As String
    : Dim stLinkCriteria As String
    :
    : stDocName = "taskList"
    :
    : ' Pass the criteria as a string, by using quotes
    : DoCmd.OpenForm stDocName, , , "finishedDate Is Null"
    :
    : ' Or you could use the variable that the access wizard
    : ' has provided (better):
    : ' stLinkCriteria = "finishedDate Is Null"
    : ' DoCmd.OpenForm stDocName, , , stLinkCriteria
    :
    : [Forms]![taskList]![approved].Visible = False
    : [Forms]![taskList]![approvedlabel].Visible = False
    : [Forms]![taskList]![refreshTaskListNext30Days].Visible = False
    : [Forms]![taskList]![refreshTaskListToBeApproved].Visible = False
    :
    : Exit_openformTaskList_Click:
    : Exit Sub
    : Err_openformTaskList_Click:
    : MsgBox Err.description
    : Resume Exit_openformTaskList_Click
    : End Sub
    : [/code]
    :
    :
    Rik,
    Thank you, it works beautifully now. I was still receiving an error that the object wasnt defined so I also added the following line....

    Dim finishedDate As String

    just wanted to double-check to make sure this was okay..
    thank you for your help!

    megan

  • RikRik Posts: 56Member
    : : : I'm new to VBA and need a wee bit of help on this code. in the linkcriteria, the finishedDate IS NULL part isnt working. I've tried changing the syntax but I either get an error saying that VB doesnt recognize a field/table, that the object is required, or it seems to work just fine but when the form opens, its displaying fields that have finishedDate. help!
    : : :
    : : : ---------------------------------------------------------------------
    : : :
    : : : Private Sub openformTaskList_Click()
    : : : On Error GoTo Err_openformTaskList_Click
    : : :
    : : : Dim stDocName As String
    : : : Dim stLinkCriteria As String
    : : :
    : : : stDocName = "taskList"
    : : : DoCmd.OpenForm stDocName, , , finishedDate Is Null
    : : :
    : : : [Forms]![taskList]![approved].Visible = False
    : : : [Forms]![taskList]![approvedlabel].Visible = False
    : : : [Forms]![taskList]![refreshTaskListNext30Days].Visible = False
    : : : [Forms]![taskList]![refreshTaskListToBeApproved].Visible = False
    : : :
    : : :
    : : : Exit_openformTaskList_Click:
    : : : Exit Sub
    : : :
    : : : Err_openformTaskList_Click:
    : : : MsgBox Err.description
    : : : Resume Exit_openformTaskList_Click
    : : :
    : : : End Sub
    : : :
    : : Access sees the expression [italic]finishedDate Is Null[/italic] (without quote marks) as a boolean expression, because Is Null is a boolean operator. Access will try to find a variable called [italic]finishedDate[/italic], to test it for 'nullness'. If there are no variables called [italic]finishedDate[/italic], access will complain. If there is a variable called [italic]finishedDate[/italic], access will evaluate the expression [italic]finishedDate Is Null[/italic] to be either True or False and pass that value to the OpenForm method.
    : : However, what the OpenForm method wants is a [b]string[/b], not a boolean.
    : : What you need is:
    : : [code]
    : : Private Sub openformTaskList_Click()
    : : On Error GoTo Err_openformTaskList_Click
    : : Dim stDocName As String
    : : Dim stLinkCriteria As String
    : :
    : : stDocName = "taskList"
    : :
    : : ' Pass the criteria as a string, by using quotes
    : : DoCmd.OpenForm stDocName, , , "finishedDate Is Null"
    : :
    : : ' Or you could use the variable that the access wizard
    : : ' has provided (better):
    : : ' stLinkCriteria = "finishedDate Is Null"
    : : ' DoCmd.OpenForm stDocName, , , stLinkCriteria
    : :
    : : [Forms]![taskList]![approved].Visible = False
    : : [Forms]![taskList]![approvedlabel].Visible = False
    : : [Forms]![taskList]![refreshTaskListNext30Days].Visible = False
    : : [Forms]![taskList]![refreshTaskListToBeApproved].Visible = False
    : :
    : : Exit_openformTaskList_Click:
    : : Exit Sub
    : : Err_openformTaskList_Click:
    : : MsgBox Err.description
    : : Resume Exit_openformTaskList_Click
    : : End Sub
    : : [/code]
    : :
    : :
    : Rik,
    : Thank you, it works beautifully now. I was still receiving an error that the object wasnt defined so I also added the following line....
    :
    : Dim finishedDate As String
    :
    : just wanted to double-check to make sure this was okay..
    : thank you for your help!
    :
    : megan
    :
    :
    Hi megan

    actually you shouldn't need the finishedDate variable at all! Can you post your exact solution?
  • lessthanmelessthanme Posts: 17Member
    : : : : I'm new to VBA and need a wee bit of help on this code. in the linkcriteria, the finishedDate IS NULL part isnt working. I've tried changing the syntax but I either get an error saying that VB doesnt recognize a field/table, that the object is required, or it seems to work just fine but when the form opens, its displaying fields that have finishedDate. help!
    : : : :
    : : : : ---------------------------------------------------------------------
    : : : :
    : : : : Private Sub openformTaskList_Click()
    : : : : On Error GoTo Err_openformTaskList_Click
    : : : :
    : : : : Dim stDocName As String
    : : : : Dim stLinkCriteria As String
    : : : :
    : : : : stDocName = "taskList"
    : : : : DoCmd.OpenForm stDocName, , , finishedDate Is Null
    : : : :
    : : : : [Forms]![taskList]![approved].Visible = False
    : : : : [Forms]![taskList]![approvedlabel].Visible = False
    : : : : [Forms]![taskList]![refreshTaskListNext30Days].Visible = False
    : : : : [Forms]![taskList]![refreshTaskListToBeApproved].Visible = False
    : : : :
    : : : :
    : : : : Exit_openformTaskList_Click:
    : : : : Exit Sub
    : : : :
    : : : : Err_openformTaskList_Click:
    : : : : MsgBox Err.description
    : : : : Resume Exit_openformTaskList_Click
    : : : :
    : : : : End Sub
    : : : :
    : : : Access sees the expression [italic]finishedDate Is Null[/italic] (without quote marks) as a boolean expression, because Is Null is a boolean operator. Access will try to find a variable called [italic]finishedDate[/italic], to test it for 'nullness'. If there are no variables called [italic]finishedDate[/italic], access will complain. If there is a variable called [italic]finishedDate[/italic], access will evaluate the expression [italic]finishedDate Is Null[/italic] to be either True or False and pass that value to the OpenForm method.
    : : : However, what the OpenForm method wants is a [b]string[/b], not a boolean.
    : : : What you need is:
    : : : [code]
    : : : Private Sub openformTaskList_Click()
    : : : On Error GoTo Err_openformTaskList_Click
    : : : Dim stDocName As String
    : : : Dim stLinkCriteria As String
    : : :
    : : : stDocName = "taskList"
    : : :
    : : : ' Pass the criteria as a string, by using quotes
    : : : DoCmd.OpenForm stDocName, , , "finishedDate Is Null"
    : : :
    : : : ' Or you could use the variable that the access wizard
    : : : ' has provided (better):
    : : : ' stLinkCriteria = "finishedDate Is Null"
    : : : ' DoCmd.OpenForm stDocName, , , stLinkCriteria
    : : :
    : : : [Forms]![taskList]![approved].Visible = False
    : : : [Forms]![taskList]![approvedlabel].Visible = False
    : : : [Forms]![taskList]![refreshTaskListNext30Days].Visible = False
    : : : [Forms]![taskList]![refreshTaskListToBeApproved].Visible = False
    : : :
    : : : Exit_openformTaskList_Click:
    : : : Exit Sub
    : : : Err_openformTaskList_Click:
    : : : MsgBox Err.description
    : : : Resume Exit_openformTaskList_Click
    : : : End Sub
    : : : [/code]
    : : :
    : : :
    : : Rik,
    : : Thank you, it works beautifully now. I was still receiving an error that the object wasnt defined so I also added the following line....
    : :
    : : Dim finishedDate As String
    : :
    : : just wanted to double-check to make sure this was okay..
    : : thank you for your help!
    : :
    : : megan
    : :
    : :
    : Hi megan
    :
    : actually you shouldn't need the finishedDate variable at all! Can you post your exact solution?
    :
    here's the exact solution. before I added 'Dim finishedDate As String' I was receiving an error which said "object required" so I figured I needed to define the variable or something of that nature. I also was thinking that perhaps its the wrong type (text, date, etc) in the table. Anyways... here it is...
    -------------------------------------------------
    Private Sub openformTaskList_Click()
    On Error GoTo Err_openformTaskList_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim finishedDate As String

    stDocName = "taskList"
    DoCmd.OpenForm stDocName, , , "finishedDate Is Null"

    [Forms]![taskList]![approved].Visible = False
    [Forms]![taskList]![approvedlabel].Visible = False
    [Forms]![taskList]![refreshTaskListNext30Days].Visible = False
    [Forms]![taskList]![refreshTaskListToBeApproved].Visible = False


    Exit_openformTaskList_Click:
    Exit Sub

    Err_openformTaskList_Click:
    MsgBox Err.description
    Resume Exit_openformTaskList_Click

    End Sub



  • RikRik Posts: 56Member
    :<stuff...
    :

    Nah, you can delete 'Dim finishedDate As String'; you aren't using finishedDate.
    You aren't using stLinkCriteria either. stLinkCriteria is pre-declared so can use it like this:
    [code]
    stLinkCriteria = "finishedDate Is Null"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    [/code]
    but for your example the criteria is (are) simple, so there's no benefit in using the variable. If you need a complicated criteria expression, the variable is useful for building it up bit by bit.


Sign In or Register to comment.