Using a combobox for searching multiple fields in "option group"

[LeighW]

Hi,

I'm still having a couple problems with searches.
I have a search form, frm_Search.
The form I am trying to filter, frm_Form1
An unbound combobox on frm_Search, Cbo_Permit
6 different fields bound to tbl_Table1 within frm_Form1, fld_Permit1, fld_Permit2...

The 6 bound fields are within a custom made "option group" so that users can select the related permit(s) via check boxes. I understand this is a poor design because of multiple fields in the table but it looks good on the form and each record can have multiple permits.

What I would like, is to use an unbound combobox within frm_Search, Cbo_Permit, using the names of the fields I shown above. The user then selects a permit within the combobox and presses a command button to open frm_Form1 filtered to that particular permit.

I need to know if this is possible as it will be one of the main searches users will make.

With other searches I've used the code:

Private Sub SOMEBUTTON_Click()
 
Dim MyVar
 
MyVar = "[SOMEFIELD] = " & SOMECOMBOBOX.Value & "" 'This is the filter
 
DoCmd.OpenForm "SOMEFORM", WhereCondition:=MyVar 'This opens the form with the filter above.
 
End Sub

The problem is, this code only filters by one field and I need to filter by multiple fields.

Any help would be great,

Leigh

[twinnyfo]

Leigh,

If I understand you correctly, you want the frm_Search to filter records in frm_Form1 based on the value of the combo box in which if someone selects Permit1, then the form will only show records in which Permit1 is used, if the user selects Permit2, then only Permit2 records, and so on.... Is this correct?

First, it would be nice to know if the permit fields are text fields or Yes/No check boxes. I would recommend making them Yes/No fields, because then this code is incredibly easy. Assuming this is the case, I would remove the Option Group (because I'm not sure it works well covering multiple fields anyway, and replace it with just your six Check Boxes (chkPermit1, chkPermit2, etc.).

Then, when someone selects any of those check boxes, after they click a command button (we'll call it cmdSearch) the following code fires:

Option Compare Database
Option Explicit
 
Private Sub cmdSearch_Click()
On Error GoTo EH
    Dim strFilter as String
    strFilter = "fld_Permit1 = " & Me.chkPermit1 & _
        "fld_Permit2 = " & Me.chkPermit2 & _
        "fld_Permit3 = " & Me.chkPermit3 & _
        "fld_Permit4 = " & Me.chkPermit4 & _
        "fld_Permit5 = " & Me.chkPermit5 & _
        "fld_Permit6 = " & Me.chkPermit6
    DoCmd.OpenForm "frm_Form1", , , strFilter
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub
 

Again, this is my recommendation, that the Permit fields are Yes/No. If not, then you can modify the code above slightly so that you would check to see whether or not there is a value in fld_Permit1, fld_Permit2 etc., based on whther or not the check boxes have been clicked.

Hope this helps, or at least gets you pointed in the right direction.

[LeighW]

Thank you again twinnyfo.

The permit fields are yes/no check boxes like you expected. I've changed the combobox in the search form to a set of checkboxes with a command button.

The only problem is when I click the command button with the code above it comes up with Run-Time Error 3075. Syntax error (missing operator) in query expression. It highlights the Cmd.OpenForm line but I believe it means strFilter.

Trying to edit some of the code but not working as of yet

[zmbd]

Leigh,
run your code again... when it errors, select the debug option...
Now with the VBA editor open we can get at that string:
<ctrl><g> to open the immediate window
type ?strFilter and press enter
this will show what the string has evaluated to and you can cut and paste it back here... or it may be obvious what the issue is with it...
-z

[twinnyfo]

My mistake.... Line 7 should read:

strFilter = "WHERE fld_Permit1 = " & Me.chkPermit1 & _
 

[LeighW]

No worries but still saying the same thing.

?strFilter
WHERE fld_Permit1 = -1fld_Permit2 = fld_Permit3 = 0fld_Permit4 = 0fld_Permit5 = fld_Permit6 = 

That is with chkPermit1 checked when pressing the command button

Thanks for the help so far though guys

[zmbd]

Try changing the code to this...
you need spaces inbetween each fld_permit*

strFilter = "WHERE ((fld_Permit1 = " & Me.chkPermit1 & _ 
        ") OR (fld_Permit2 = " & Me.chkPermit2 & _ 
        ") OR (fld_Permit3 = " & Me.chkPermit3 & _ 
        ") OR (fld_Permit4 = " & Me.chkPermit4 & _ 
        ") OR (fld_Permit5 = " & Me.chkPermit5 & _ 
        ") OR (fld_Permit6 = " & Me.chkPermit6 & "))"
 

Once again... if this errors please do the <ctrl><g> thing so we can see how this evaluated.
-z

[twinnyfo]

Z- Good catch! I wasn't looking at the details!

Also, you will need the operator " AND " between all ... Like this:

Option Compare Database 
Option Explicit 
 
Private Sub cmdSearch_Click() 
On Error GoTo EH 
    Dim strFilter as String 
    strFilter = "fld_Permit1 = " & Me.chkPermit1 & _ 
        " AND fld_Permit2 = " & Me.chkPermit2 & _ 
        " AND fld_Permit3 = " & Me.chkPermit3 & _ 
        " AND fld_Permit4 = " & Me.chkPermit4 & _ 
        " AND fld_Permit5 = " & Me.chkPermit5 & _ 
        " AND fld_Permit6 = " & Me.chkPermit6 
    DoCmd.OpenForm "frm_Form1", , , strFilter 
    Exit Sub 
EH: 
    MsgBox Err.Number & " " & Err.Description 
    Exit Sub 
End Sub
 

[zmbd]

I'm also thinking that you may need to default the checkboxes (me.chkpermit#) to false... depends on how the table is setup, are the fields null, true, or false, that we're looking at... this might be a better build to use the afterupdate event in each checkbox to help build the string...

-z

[LeighW]

After trying Z's method I get the same error just more syntax:

?strFilter
WHERE ((fld_Permit1 = -1) OR (fld_Permit2 = ) OR (fld_Permit3 = ) OR (fld_Permit4 = ) OR (fld_Permit5 = ) OR (fld_Permit6 = ))

Twinnyfo: I'm not sure what you mean by the AND when the OR has been placed there using Z's method? Do mean place the words AND OR together?

Z: The bound fields are checked either true or false on frm_Form1 and on the search form I have an unbound set of checkboxes which should also be true or false but I guess they could also be "null"
Aha that may be the problem.

[LeighW]

Nay set all the default values to =False but still but working

[twinnyfo]

Depends on if you want records that meet all criteria or those that meet at least one of the criteria.

[LeighW]

The user should only selects one value when using that search method although I guess if the user really wants to filter it by more than one permit they have the option to with "AND"

[LeighW]

OK I've set the defaults to "False" on Form1 too and now it is coming up with a different error message: Run-Time Error 3085. Undefined function 'WHERE' in expression

[zmbd]

Leigh,
Let's go back to what this form is trying to accomplish and the undrlying table structure.
-z

[LeighW]

frm_Form1 is bound to tbl_Table1

Fields within tbl_Table1 include PK_ID (Autonumber), Title(Text), Description(Memo), fld_Pemrit1(Yes/No), fld_Permit2(Yes/No), fld_Permit3(Yes/No), fld_Permit4(Yes/No), fld_Permit5(Yes/No), fld_Permit6(Yes/No), etc...

On Form1 the fld_Permit# are displayed as checkboxes (true or false) with labels which a user can see and edit if needs be.

[zmbd]

The following code when placed in the clickevent of the command button "cmd_build_filter" will build the filter.
I placed six unbound contols

 
Option Compare Database
Option Explicit
 
Private Sub cmd_build_filter_Click()
'
'small code to build where statment on the fly
'
Dim z_str_where As String, z_str_addfield As String
Dim z_ctl As Control, z_ctlg As Controls
Dim z_bln_atleastone As Boolean
'
'get the control group for the fomr
Set z_ctlg = Me.Controls
'
'prebuild the intro
z_str_where = "WHERE ("
'
'I don't assume that booleans will 
'always be defaulted to false!
z_bln_atleastone = False
'
'now loop thru every control on the form
For Each z_ctl In z_ctlg
'
'If the control is a check box type then lets take a look
    If z_ctl.ControlType = 106 Then
        '
        'Add some other checks here to make sure that
        'you have the correct controls...
        '
        'if the control is true then add it to the filter
        'however, just incase it is null return a false
        If Nz(z_ctl, False) Then
            '
            'set the flag to true for the rest of the build
            z_bln_atleastone = True
            '
            'build my field string... like to take a look as I build these things...
            'so using the control's name we're 
            'going to add that name to the 
            'filter... for example use the TAG property
            'to store the field name.
            z_str_addfield = "(" & z_ctl.Name & " = -1) " & "OR "
            '
            'append the string to the filter
            z_str_where = z_str_where & z_str_addfield
        End If
    End If
Next z_ctl
'
If z_bln_atleastone Then
    '
    'get rid of the final OR and space
    'if you used the AND then you would change the -3 to -4
    z_str_where = Left(z_str_where, Len(z_str_where) - 3)
    '
    'add closeing  parenthese 
    z_str_where = z_str_where & ")"
    '
    'For now lets just print to the debug
    'however, this would be the sting you should use.
    debug.print z_str_where
Else
    MsgBox "You must select at least one check box to make this code work!", _
        vbCritical, "Really?"
End If
End Sub
 
 

So if you had six unbound check box type controls on the form named: ckbx_permit1; ckbx_permit2; ckbx_permit3; etc...
and you check marked 1,2, and 4 then clicked the button you should get: WHERE ((ckbx_permit1 = -1) OR (ckbx_permit2 = -1) OR (ckbx_permit4 = -1) )

printed in the debug window...

-z

[zmbd]

Well... if you'll never ever ever add another permit to the system you might be just fine; however, I would more than likely normalize this db a tad. It would make finding the related permits to a given record in tbl_table1 a lot easier. You'd query on the join table...

-z

[LeighW]

Sorry Z for not understanding you correctly but do you mean to add that code with the code mentioned before. As with just that code the button does nothing though I can see what it would do if it knew what controls (checkboxes) to use and how they relate to fields on the form to be opened.

The permits should be the same and if not I have an "Other" field which the user can click and enter text into an unbound textbox to what that "other" is.

Clicking buttons in a subform would mess up the coding I have on Form1 so normalizing the permit fields is only the last resort

[NeoPa]

Let's get some clarification here, as I, for one, am royally confused about what you even want.

You have a table :
Table = [tbl_Table1]

Field           Type      IndexInfo
PK_ID           AutoNumber    PK
Title           Text
Description     Memo
fld_Permit1     Yes/No
fld_Permit2     Yes/No
fld_Permit3     Yes/No
fld_Permit4     Yes/No
fld_Permit5     Yes/No
fld_Permit6     Yes/No

You also have a search form [frm_Search] which has controls which match the fld_PermitX fields somehow. What exactly are you hoping to provide though, in the way of selecting records?

[zmbd]

I can assure you that the code posted in #17 works. You should have at least received a msgbox telling you that you needed to select something.

You would not out-right combine the code in #17 with anything else posted in this thread. The code is building the Where string from scratch based upon the control name property of checkbox controls with a value equal to true. You would then modify the code to use that string. For example line 13 in post 2

That you saw nothing printed/popup etc is not surprising as the code I posted prints the result (finalized in line 59) to the immediate (ctrl-g) window in the vba editor (line 63...) I figured that you could modify the code from that point for further use.

Now if you received the message box then you don't have checkbox controls on the same form as the code is looking at or you didn't select any of them... I can only guess that you placed the command button on the master/parent form, built the on-click event using the code posted in #17, and the check boxes are on the subform? If, Yes, then look at Set z_ctlg = Me.Controls on line 14... you will have to change the "Me" to a proper reference to the subform such as:
Set z_ctlg = Me!Subform1.Form.Controls wherein subform1 is the name of your subform.

-z
(I also had a typo on line 63... "pring" instead of "print" don't know how that happened as I did a copy and paste from the VBA editor... maybe while I was stepping the code)

[LeighW]

Hi guys,

Sorry for the late reply.

Z - I can see what you mean now with that code. However Me.Controls doesn't seem to work or at least this line is highlighted:

If Nz(z_ctl, False) Then

It spurs from a run-time error with code -2147352567 (80020009) 'The expression you entered has a field, control, or property name that ms access can't find.'

This error occurs whether a box is checked or not.
Do unbound checkboxes count as controls?

I have not modified the code and I am clicking the command button within the search form with the unbound checkboxes also within the search form which I'm hoping will open the main form filtered by the permit fields on the main form.

NeoPa - You are right with that and also a form, "frm_Form1" which is bound to Table1 with the fld_PermitX checkboxes.

I'd like the user to select an unbound checkbox (or a value in a combobox if easier) on frm_Search which will filter by the permits, fld_PermitX, on frm_Form1.

So if the user selects Permit1 in frm_Search then frm_Form1 will open filtered to the records which have fld_Permit1 checked.

Each record within frm_Form1 can have one or more permits checked but the user when searching is likely to only care about one particular permit.

I hope that clears the air a little. I'm very sorry for the mis-understanding, I know you guys aren't paid for this so I don't want you to go too out your way. But still, thank you for helping and attempting to understand it!

[LeighW]

OK scratch that Z it is now working saying chk_PermitX = -1 within the immediate box when the command button is clicked.

I forgot I had bound controls left on the form from messing about the other day trying to get it to work

[LeighW]

I'm still not sure how to link the filter now performed on frm_Search with the permit fields on frm_Form1 however. It's obviously not as simple as just adding Line 13 from Post#2 (I'm guessing) as it's still a group of fields i'm trying to filter and not just one.

[NeoPa]

That's much clearer Leigh, but doesn't quite answer the question as to what you are hoping to achieve. Let me put it a different way :
If you have the unbound controls set so that Permit #3 and Permit #5 are checked, would that mean you are looking for all records where both #3 and #5 are checked, or might you be looking for all records where Permit #3 is checked as well as all records where #5 is set?

[zmbd]

Leigh:
yes... it is basically that simple....
DoCmd.OpenForm "frm_Form1", , ,z_str_where
NeoPa still asks a very good question... what is it exactly that you need. I think I understand; however, it wouldn't hurt to really detail the goal.

NeoPa:
If Leigh uses the string that z_str_where builds then it will return form with any of the permits selected so in your question it would return all forms with either #3 or #5 or both checked.
-z

[NeoPa]

I suppose the answer determines if line #44 ends with an "OR " string or an "AND " string. Simple enough really, but often confused so I wanted the OP to give it some consideration.

My suggestion would have been on the lines of the code/logic found in Example Filtering on a Form. I suspect the code there, though somewhat different from that which you suggested, would produce very similar results (Obviously modified for this particular situation).

[LeighW]

To be honest what I'd prefer is to have EITHER just the one box checked and a messagebox popping up saying something along the lines of "you can only search using one permit" if more than one is checked OR all of the permit related records are filtered if more than one permit is checked that being just Permit1 records, just Permit2 records and records with both Permit1 and Permit2 related (If Permit1 and Permit2 have been checked of course).

Which ever is easiest basically but I do not want just Permit1 AND Permit2 related records to pop up if Permit 1 and 2 have been checked as that would be pointless for this application.

Z - I've tried using the method and swapping the line

Debug.Print Z_str_where

with:

DoCmd.OpenForm "frm_Form1", , ,z_str_where

However, it's coming up with Run-time error '3085'
"Undefined function 'WHERE' in expression"

When Debugging it highlights the line of code above.

It's annoying that this is one of the most important searches else I'd certainly leave it be by now!

[LeighW]

Just to clarify after selecting Permit1 and receiving the error message, if I hover over the string in the code log, z_str_where, it shows the code:

z_str_where = "WHERE ((chk_Permit1 = -1))"

[zmbd]

sorry,
I took this code from a form that pulls values for a SQL statement...
line 17: z_str_where = "WHERE ("
change to z_str_where = "("
Check here for proper syntax: http://msdn.microsoft.com/en-us/libr.../ff820845.aspx

As for what this will filter see my reply to NeoPa post #26

-z

[LeighW]

No worries Z, thank you very much for the help anyway.

I did what you asked but now when I click the button it prompts you to enter a parameter value.

[LeighW]

I've managed to do it.

Thanks so much for the help guys it's very much appreciated!

To get it to work I used the Debug.Print bar on a search that worked to see how the syntax was laid out. I realised that these were using [fields] on the other form rather than the check boxes such as "chk_Permit1". So I changed each of the checkbox names to the names of the fields on the other form i.e. "Permit1" and then made sure the syntax was the same.

The format ended up being [Permit1] = -1.

It now opens the form to that specific filter.

[NeoPa]

As a simple qualification, checking for Permit3 and/or Permit5 (IE find all records that have either or both of them) can be as simple as :

([Permit3]) OR ([Permit5])

It's never necessary to compare Boolean values (Yes/No, True/False, CheckBoxes, ToggleBoxes, etc values are all Boolean).

Filtering always works on fields rather than controls. You've done well to spot that. Filtering is a SQL process and SQL always works with the data and not the form as such. I hope that helps to clarify things.