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

    Inputbox on form (2003)

    Hello,

    I am trying to creade a VBA procedure that basically will required staff to enter their password and filter based on "managerLastName" and "managerapproved" set to no, however, if they press the cancel button, it will display all invoices. I want to cancel the event, but I don't know how. Below is a sample of the code. Any help would be great.

    Private Sub Form_Load()



    If [ManagerLastName] = "" Then
    DoCmd.Quit
    Else
    DoCmd.OpenForm "frmManagerMain", acNormal, "", "[ManagerLastName]" = InputBox("Enter your password") And "[ManagerApproved] =""no", , acWindowNormal
    End If
    End Sub

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

    Re: Inputbox on form (2003)

    I'm not sure I quite understand, but you could define a string variable and set it to the result of the InputBox. You can then check its value:

    Dim strManagerLastName As String
    strManagerLastName = InputBox("Enter your password")
    If strManagerLastName = "" Then
    Application.Quit
    Else
    DoCmd.OpenForm "frmManagerMain", , , "[ManagerLastName] = " & Chr(34) & strManagerLastName & Chr(34) & " And [ManagerApproved] = No"
    End If

    I assumed that ManagerApproved is a Yes/No field. If it is a text field, change

    " And [ManagerApproved] = No"

    to

    " And [ManagerApproved] = " & Chr(34) & "No" & Chr(34)

    Chr(34) is the double quote character ".

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

    Re: Inputbox on form (2003)

    Thanks Hans. Your assumption is correct.

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

    Re: Inputbox on form (2003)

    Hans can you be so kind to explain do docmd line of coding? I see the syntax as I am writing it, but I don't know what the & chr(34) does or most of what is in this line of coding. Thanks.

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

    Re: Inputbox on form (2003)

    See Chr() function and quotes within strings (all) for an explanation of why Chr(34) is used.

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

    Re: Inputbox on form (2003)

    Hans,

    What do I add to the Docmd.open command above if I need to add one more "and" statement? For example is the statement below correct?

    and [Invoicetype]=" & chr (34) & "January, February, March, April, May, June, July, August"

    Any help would be great. Thanks

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

    Re: Inputbox on form (2003)

    Is "January, February, March, April, May, June, July, August" a single invoice type, or are "January", "February" etc. separate types and should the invoice type be one of those listed?

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

    Re: Inputbox on form (2003)

    They are separate types and as long as the invoice type is one of the months above, I want them to be display. Basically, I also have Quarterly invoices and don't want to display them too. I hope this does not make it more confusing.

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

    Re: Inputbox on form (2003)

    Try
    <code>
    ... AND [Invoicetype] In ('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August')"
    </code>
    Note the use of single quotes around the month names. Using Chr(34) would have been a hassle here. The use of In(...) is easier than repeated ORs, that would have been
    <code>
    ... AND ([Invoicetype]='January' OR [Invoicetype]='February' OR ... OR [InvoiceType]='August')"
    </code>
    You'd have to write out all the conditions explicitly.

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

    Re: Inputbox on form (2003)

    Hans,

    When I put the statement above, I get a compile error message "Expected: end of statement". I think I am doing something wrong. Below is the statement.

    Private Sub Form_Load()

    Dim strManagerLastName As String
    strManagerLastName = InputBox("Enter your Password")
    If strManagerLastName = "" Then
    Aplication.DoCmd.Close

    Else
    DoCmd.OpenForm "frmManagerMain", , , "[managerlastname]=" & Chr(34) & strManagerLastName _
    & Chr(34) & " and [managerapproved] =" & Chr(34) & "No" & Chr(34) _
    And [InvoiceType] In ('January','February','March','April','May','June' ,'July','August','September','October','November', 'December')"

    End If



    End Sub

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

    Re: Inputbox on form (2003)

    You must use

    DoCmd.OpenForm "frmManagerMain", , , "[managerlastname]=" & Chr(34) & strManagerLastName _
    & Chr(34) & " and [managerapproved] =" & Chr(34) & "No" & Chr(34) _
    & " And [InvoiceType] In ('January','February','March','April','May','June' ,'July','August','September','October','November', 'December')"

    Note the ampersand & and the quotes " at the beginning of the third line.

Posting Permissions

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