Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.