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

[LeighW]

frm_Form1 is bound to tbl_Table1

Fields within tbl_Table1 include PK_ID (Autonumber), Title(Text), Description(Mem o), 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]

build on the fly

The following code when placed in the clickevent of the command button "cmd_build_filt er" 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]

normalize

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]

[I][U]Field[/U]           [U]Type[/U]      [U]IndexInfo[/U][/I]
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]

build checkbox filters from scratch

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.For m.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