Results 1 to 10 of 10
  1. #1
    DavidMadge
    Guest

    Query value '*' from cbo value 'Null'

    I have a form with a number of combo boxes that are used to set parameter values for a query. What I want is that when the user double-clicks a combo, the combo clears and the value given to the query from that box is '*' (ie don't use that particular field to restrict the query. I have it working OK, but only by having a hidden text box associated with each combo box and setting the textbox value to the combo box value (on after update) or '*' (on double-click). The query then uses the textbox values.

    I assume I could do the same thing using variables instead of textboxes, but is there any way to get the query to determine the value directly from the combo boxes ? I'm thinking of something like an IIF statement in the criteria eg (without all the form references):
    IIf (isnull([cboState]),'*',[cboState])
    This works OK as long as cboState is not null. When it is null, no records are found. It is probably a simple syntax problem but I've tried numerous variations. Any suggestions ? PS I need to keep it simple as I have 12 parameters being set on the form.

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

    Re: Query value '*' from cbo value 'Null'

    I'm not sure this is what you're looking for, but the simplest way to pass an asterisk as a value from a combobox is to include it in the rowsource of the combobox. That means you have to base the combobox on a union query something like this:
    <pre>SELECT DISTINCTROW tblContractor.ContrID,
    tblContractor.Contractor, 2 AS SortOrder
    FROM tblContractor
    WHERE (((tblContractor.ContrID)<>0))
    UNION SELECT -1 AS ContrID, "*" As Contractor, 1 As SortOrder
    FROM tblContractor
    ORDER BY SortOrder, Contractor;</pre>


    In this case, since contrID is an autonumber field, I used a -1 for the unique ID of the "*" item for contractor. The sortorder expression causes the list to sort the asterisk to the top and everything else after that. Using this, you could simply test column(1) of the combobox to see if it was an asterisk and then proceed accordingly.
    Charlotte

  3. #3
    DavidMadge
    Guest

    Re: Query value '*' from cbo value 'Null'

    Thanks for your reply Charlotte - but - it is the autonum field I use in the query criteria, so what I need is not possible (ie an '*' in an autonum field). I'm thinking of using a different approach which will also make another aspect of the system easier to manage, but to do this I need to know how to refer to a global object variable in a query criteria. ie: if gcboCountry is a global object variable of type combobox, how do I include it in the criteria expression where I would have referred to a 'real' combobox as 'Like Forms![frmMyForm]![cboCountry]' ?

  4. #4
    DavidMadge
    Guest

    Re: Query value '*' from cbo value 'Null'

    Charlotte - I've just found the answer to my first question in a post you did on Feb 27. Thanks! I was missing was the 'Like' in front of my 'IIf' statement!

    I'd still like to change to using global cbo variables in the query criteria though...

    David

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

    Re: Query value '*' from cbo value 'Null'

    If you feel you absolutely must try this, you'll have to create a user-defined function that references the object and returns its value to the query. You <big>cannot</big> refer to variables in a query or anywhere else in the user interface, only in VBA. Furthermore, globals of all kinds break easily and suck up resources unnecessarily. They are no substitute for good program design, and I don't recommend wasting your time on them.
    Charlotte

  6. #6
    DavidMadge
    Guest

    Re: Query value '*' from cbo value 'Null'

    Point taken - globals are out!

    What I was hoping to do was have my 12 combobox values put into variables which the query would then use - I've got around that OK without variables.

    I was also hoping that if the variables could be array'd it would be easy to save/retrieve their values to/from a table, allowing users to define often-used queries and recall them easily. I would have tried this by looping through the array. I can obviously save combobox values to the table fields one at a time, but what is the easiest way (syntax-wise) to loop through ALL the comboboxes on a form to get their values ?

    David

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

    Re: Query value '*' from cbo value 'Null'

    This will work in either Access 97 or 2000 (as long as you have the DAO reference set, that is). You can't loop through the collection of comboboxes, unfortunately. You have to loop through the controls collection instead.

    <pre> Dim ctl As Control

    For Each ctl in Me.Controls
    If ctl.ControlType = acComboBox Then
    'do something here with the value
    End If
    Next ctl </pre>

    Charlotte

  8. #8
    DavidMadge
    Guest

    Re: Query value '*' from cbo value 'Null'

    Thanks Charlotte

    Now I need to compare the field names of the form (bound to a table) to the combo names and save the combo values to the appropriate field. Something like:

    Dim ctl As Control, fld as Field
    For Each ctl in Me.Controls
    If ctl.ControlType = acComboBox Then
    For Each fld in Me.Fields
    If ctl.Name = fld.Name then fld=ctl
    Next fld
    End If
    Next ctl

    'Me.Fields' is wrong so how do you refer to a form's fields collection ?

    The other way would be to generate the field name based on the ctl name. eg If my control is cboState and the associated field is FieldcboState, is there any way to refer to the field through its name made 'on the fly' by "Field" & ctl.Name ?

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

    Re: Query value '*' from cbo value 'Null'

    You don't. Recordsets have a fields collection, forms don't. If you need to associate a particular field name with a combobox, the easiest way is to put the field name into the combobox's tag property. Then you can refer to it in code like this:

    <pre> For Each ctl in Me.Controls
    If ctl.ControlType = acComboBox Then
    rst(ctl.tag) = ctl.value
    End If
    Next ctl </pre>


    ...where rst is a recordset object variable. I can't figure out from your description if there is only one record in the table, or what. If so, then just open a recordset based on the table, MoveFirst and use the above method to populate the fields. If there are multiple records, you'll have to figure out how to get to the correct one before you start populating its fields.
    Charlotte

  10. #10
    DavidMadge
    Guest

    Re: Query value '*' from cbo value 'Null'

    Thanks a lot Charlotte - its working just as I want it.

    Have a nice day.

    David

Posting Permissions

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