Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error with VBA (2003)

    Hello,

    I am trying to run the following VBA command on an "on-open" event for form.

    Private Sub Form_Open(Cancel As Integer)
    Dim strPassword As String
    strPassword = InputBox("Please enter your Password")
    If strPassword = "" Then
    Application.DoCmd.Close
    Else
    DoCmd.OpenForm "frmManagerApprovalMain", , , "[Password]=" & Chr(34) _
    & strPassword & Chr(34) & " and [ManagerApproved]<>" & Chr(34) _
    & "Yes"
    End If

    End Sub

    When I run it, I get the following error:

    Run-Time error '3075'
    Syntax error in string in query expression .....

    What am I doing wrong? I want to get all invoices where manager approval is not equals to "yes". Any help would be great.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error with VBA (2003)

    What kind of field is ManagerApproved? If it is a text field that contains "Yes" or "No" or nothing, you're missing a closing quote:
    <code>
    DoCmd.OpenForm "frmManagerApprovalMain", , , "[Password]=" & Chr(34) _
    & strPassword & Chr(34) & " and [ManagerApproved]<>" & Chr(34) _
    & "Yes" & Chr(34)
    </code>
    But if ManagerApproved is a Yes/No field, you don't need quotes around the value:
    <code>
    DoCmd.OpenForm "frmManagerApprovalMain", , , "[Password]=" & Chr(34) _
    & strPassword & Chr(34) & " and [ManagerApproved]=False"
    </code>
    (If a Yes/No field is not True/Yes, it is False/No)

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error with VBA (2003)

    Hans,

    The ManagerApproved field is a formatted to text. I tried the code below, but it ask me to enter a password and then proceeds to ask me for a parameter value. What could this be?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error with VBA (2003)

    What parameter does it ask for?

  5. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error with VBA (2003)

    Password
    This is the same field from my post <post:=676,396>post 676,396</post:> .

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error with VBA (2003)

    If it asks for Password as a parameter, it means that Password is not recognized as a field in the Record Source of the form frmManagerApprovalMain.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error with VBA (2003)

    How can I make it so I don't have to add this field to the table where the form is coming from? These fields are the same in the post <post:=676,402>post 676,402</post:>. I have this field, but is on a different table. What do you recommend I do?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error with VBA (2003)

    I'd create a query based on the "main" table and the one that contains the password. Join them on the appropriate field, then double click the join line and select the option to include all records from the "main" table.
    Use the query as record source for the form. You can bind the password text box directly to the password field then, instead of to a column of the combo box.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •