Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Location
    Netherlands
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA variables in queries (2000)

    I want to use a VBA-variable as a criteria in a query.

    Now I read a field from a form (form!form1.field1), and set the value of the field from VBA (me!field1=12 e.g.), but that's slow, I guess, and a bit clumsy I think.

    Can anyone help?

    rettnuc.

  2. #2
    New Lounger
    Join Date
    Feb 2002
    Location
    Netherlands
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA variables in queries (2000)

    Ofcourse I can use:

    Function GetVariable() as variant
    GetVariable=(fill in name of variable)
    End Function

    But isn't there an easier way to do this?

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

    Re: VBA variables in queries (2000)

    Why not just use a parameter query?

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

    Re: VBA variables in queries (2000)

    I don't think there is an easier way. Standard SQL doesn't know about VBA, forms etc. Microsoft extended SQL in Access to recognize VBA functions and references to controls on forms or reports. Variables - not.

    Maybe, if you explain why you want to refer to a variable (and not to a control or a function), somebody will come up with a bright idea.

  5. #5
    New Lounger
    Join Date
    Feb 2002
    Location
    Netherlands
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA variables in queries (2000)

    Parameterquery is not an option because of the reason I want to use that variable in a query:

    I use a combobox on a form to select a writer. On that form I have different buttons which open different forms which show information about that writer. Using subforms has come into my mind, but is not what I want.
    Therefore I use different queries, having one item in common: writerID. Catching that ID from the main-form is slow, so I want to set a public variable and use that as a criterium in the different queries for the forms.

    Q: IS there anyone having bright ideas? Otherwise I'll stick with the GetVariable function HansV and me mentioned before.

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

    Re: VBA variables in queries (2000)

    Have you considered using the WhereCondition argument of the OpenForm method?

    It would work more or less like this:

    Base your information forms on queries without criteria. The queries should contain WriterID, however.

    The OnClick handler of the command buttons on your main form could look like this:

    Private Sub cmdMyButton_Click()
    DoCmd.OpenForm FormName:="frmDetailInfo", WhereCondition:="WriterID=" & [cboWriter]
    End Sub

    where cboWriter is the name of the combo box used to select the writer. I assumed that WriterID is numeric; if it's text, replace the last part of the instruction by
    <pre>WhereCondition:="WriterID='" & [cboWriter] & "'"</pre>


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

    Re: VBA variables in queries (2000)

    You could also open the forms using the OpenArgs argument to pass the writerID. There is NO way to directly reference variables in a query. You can't even use built-in variables in a query, only their numeric equivalents. So either use Hans's functional approach or make it easy on yourself and use either the WhereCondition or OpenArgs to open the forms in the first place.
    Charlotte

Posting Permissions

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