Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two List Boxes to Open a Form (2002)

    I have a database that collects financial information from Units (floors or departments) in a hospital.

    I have one form set up to have the manager click on their Unit then click a command button that opens the data entry form and subform that displays the information for that particular Unit. This works fine with one List Box.

    What I need is for them to be able to click on "Unit" AND "Fiscal Year" list boxes on the first form. Then have the data entry form opent to the appropriate Unit's data.

    Here are the two seperate command button codes that I've been working with to combine - but I don't know VB.

    Private Sub Command7_Click()
    On Error GoTo Err_Command7_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmYearlyEntry"

    stLinkCriteria = "[Unit]=" & Me![Unit]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Command7_Click:
    Exit Sub

    Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click

    End Sub
    Private Sub Command11_Click()
    On Error GoTo Err_Command11_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmYearlyEntry"

    stLinkCriteria = "[FiscalYear]=" & Me![FiscalYear]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Command11_Click:
    Exit Sub

    Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click

    End Sub

    Is there a way to combine these functions to include the Unit and Fiscal Year list box choices to open the data entry form?
    Thanks so much!

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two List Boxes to Open a Form (2002)

    Just combine the two statements (with "and" logic) that specify the "where" criteria for your OpenForm command:

    <font face="Georgia">stLinkCriteria = "([Unit]=" & Me![Unit] & ") And ([FiscalYear]=" & Me![FiscalYear] & ")"</font face=georgia>

    This should replace the "<font face="Georgia">stLinkCriteria = ...</font face=georgia>" in the routine for whichever button you want to use (now you need only one button, right?).

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two List Boxes to Open a Form (2002)

    Yes, one button.
    Thank you so much Tom, I tried "&" and "," and lots of other combinations but not "And"..
    It works great!

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two List Boxes to Open a Form (2002)

    In case you're interested -- this might come in handy as you develop your application further -- all you're doing here is building a string that matches, syntax-wise, the "WHERE" clause of a query that is used to filter the record source for the form you are opening. If you are familiar with building queries (e.g., with Access's design grid), you can look at the SQL equivalent by selecting the "SQL View" option when looking at your query. I often do this and the cut and paste the WHERE clause text into my code, just to make sure I get the syntax right. Then I go through and substitute constant phrases (e.g., "[FiscalYear] = 2003") with variables -- values pulled from my form -- (e.g., "[FiscalYear] = " & Me![FiscalYear]). The "&" here is, of course, the VBA string concatenation operator whereas the "And" (in your code) is the SQL boolean operator for combining the two criteria (sub-clauses).

    Good luck.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two List Boxes to Open a Form (2002)

    I'm very interested.
    Thanks for the explanation and extra time.. This helps me understand what I did - and I know I'll be using it much more in the future.
    I am familiar with the SQL view and have written a little.
    It seems every application I develop has some little new thing that I can't find in books. Woody's has been the best resource I've ever encountered.
    Visual Basic is required in the CIS program I'm currently in - can't wait for that one!

Posting Permissions

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