Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing Variables from Form to Query (2000)

    I am trying to pass a variable that the user selects through a combo box into a query in Access.

    An example of my code is as follows:
    Private Sub cboDept3_Click()
    Department = cboDept3.ListIndex
    Select Case Department
    Case 0
    Dept_ID = 26
    Case 1
    Dept_ID = 2
    Case 2
    Dept_ID = 3
    Case 3
    Dept_ID = 4
    Case 4
    Dept_ID = 11
    Case 5
    Dept_ID = 27
    Case 6
    Dept_ID = 6
    Case 7
    Dept_ID = 7
    Case 8
    Dept_ID = 8
    Case 9
    Dept_ID = 9
    Case 10
    Dept_ID = 12
    Case 11
    Dept_ID = 13
    Case 12
    Dept_ID = 14
    Case 13
    Dept_ID = 37
    Case 14
    Dept_ID = 15
    Case Else
    MsgBox "Please Select a Department"
    End Select
    End Sub

    How do I get the Dept_ID variable from this form (frmDept) to my query in order to run a report?

    Thanks!
    Kindra

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Passing Variables from Form to Query (2000)

    I presume that DeptID has been defined on your form somewhere.
    When you reference this field in the criteria of a field in a query, just use:
    Forms![formname]!DeptID

    Don't forget to substitute your form name for formname above.

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

    Re: Passing Variables from Form to Query (2000)

    The easiest way would be to include Dept_ID as a column in the Row Source of the combo box (hidden if you like, by setting the column width to 0). Then, you can refer to Forms!frmMyForm!cboDept3.Column(n) where n is the column number (zero-based, the first column is 0).

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Variables from Form to Query (2000)

    Access 2000 doesn't like the Column(n) syntax and will complain seriously if you try to use it. It worked fine in Access 97, but the changes in the query engine between 97 and 2000 dislocated that syntax. Just refer to the combobox and that will return the bound column value. If you truly *need* to reference another column, you'll need to use the Eval() function to do it.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Variables from Form to Query (2000)

    A technique I used recently (not neat or elegant, but it works) is this. Place a text box on the form and set its visible property to no, if you are using check boxes then set-up the on click event to enter the relevant value in the text box, make your query refer to the text box. If you are using a combo box, use the on update property to set the text box value.

    Alternately use a the combo box to find a record set (the wizard offers this facility) and fill in the hidden text box that way.

    Like I said, its not a neat or elegant solution, but I've used both and they work for me.

    Ian

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

    Re: Passing Variables from Form to Query (2000)

    Hi Ian,

    There is nothing against your method - using a hidden control is a reliable and simple way to make information available. In the post that started this thread, only one instruction needs to be added near the end of the code. Let's say that the hidden text box is named txtDeptID, then txtDept_ID = Dept_ID will set the text box to the value of Dept_ID that has been determined in the Select Case ... End Select part. The query can refer to it as [Forms]![frmMyForm]![txtDept_ID] where frmMyForm is the name of the form.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Variables from Form to Query (2000)

    Hans

    That's pretty much how my forms work. I saw the suggestion to use hidden controls in a post on here, it wasn't my idea originally. Used to just 'shrink' the controls and hide them behind buttons.....

    Ian

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Variables from Form to Query (2000)

    You guys are great!!!! It works perfectly. I am so glad I found this site [img]/forums/images/smilies/smile.gif[/img] Hopefully I will be able to contribute on the other end soon [img]/forums/images/smilies/smile.gif[/img]

    blessings,
    Kindra

Posting Permissions

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