By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,828 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,828 IT Pros & Developers. It's quick & easy.

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

LeighW
P: 73
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SOMEBUTTON_Click()
  2.  
  3. Dim MyVar
  4.  
  5. MyVar = "[SOMEFIELD] = " & SOMECOMBOBOX.Value & "" 'This is the filter
  6.  
  7. DoCmd.OpenForm "SOMEFORM", WhereCondition:=MyVar 'This opens the form with the filter above.
  8.  
  9. 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
Aug 21 '12 #1
Share this Question
Share on Google+
32 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,283
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdSearch_Click()
  5. On Error GoTo EH
  6.     Dim strFilter as String
  7.     strFilter = "fld_Permit1 = " & Me.chkPermit1 & _
  8.         "fld_Permit2 = " & Me.chkPermit2 & _
  9.         "fld_Permit3 = " & Me.chkPermit3 & _
  10.         "fld_Permit4 = " & Me.chkPermit4 & _
  11.         "fld_Permit5 = " & Me.chkPermit5 & _
  12.         "fld_Permit6 = " & Me.chkPermit6
  13.     DoCmd.OpenForm "frm_Form1", , , strFilter
  14.     Exit Sub
  15. EH:
  16.     MsgBox Err.Number & " " & Err.Description
  17.     Exit Sub
  18. End Sub
  19.  
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.
Aug 21 '12 #2

LeighW
P: 73
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
Aug 21 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
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
Aug 21 '12 #4

twinnyfo
Expert Mod 2.5K+
P: 3,283
My mistake.... Line 7 should read:

Expand|Select|Wrap|Line Numbers
  1. strFilter = "WHERE fld_Permit1 = " & Me.chkPermit1 & _
  2.  
Aug 21 '12 #5

LeighW
P: 73
No worries but still saying the same thing.

Expand|Select|Wrap|Line Numbers
  1. ?strFilter
  2. 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
Aug 21 '12 #6

zmbd
Expert Mod 5K+
P: 5,397
Try changing the code to this...
you need spaces inbetween each fld_permit*

Expand|Select|Wrap|Line Numbers
  1. strFilter = "WHERE ((fld_Permit1 = " & Me.chkPermit1 & _ 
  2.         ") OR (fld_Permit2 = " & Me.chkPermit2 & _ 
  3.         ") OR (fld_Permit3 = " & Me.chkPermit3 & _ 
  4.         ") OR (fld_Permit4 = " & Me.chkPermit4 & _ 
  5.         ") OR (fld_Permit5 = " & Me.chkPermit5 & _ 
  6.         ") OR (fld_Permit6 = " & Me.chkPermit6 & "))"
  7.  
Once again... if this errors please do the <ctrl><g> thing so we can see how this evaluated.
-z
Aug 21 '12 #7

twinnyfo
Expert Mod 2.5K+
P: 3,283
Z- Good catch! I wasn't looking at the details!

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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database 
  2. Option Explicit 
  3.  
  4. Private Sub cmdSearch_Click() 
  5. On Error GoTo EH 
  6.     Dim strFilter as String 
  7.     strFilter = "fld_Permit1 = " & Me.chkPermit1 & _ 
  8.         " AND fld_Permit2 = " & Me.chkPermit2 & _ 
  9.         " AND fld_Permit3 = " & Me.chkPermit3 & _ 
  10.         " AND fld_Permit4 = " & Me.chkPermit4 & _ 
  11.         " AND fld_Permit5 = " & Me.chkPermit5 & _ 
  12.         " AND fld_Permit6 = " & Me.chkPermit6 
  13.     DoCmd.OpenForm "frm_Form1", , , strFilter 
  14.     Exit Sub 
  15. EH: 
  16.     MsgBox Err.Number & " " & Err.Description 
  17.     Exit Sub 
  18. End Sub
  19.  
Aug 21 '12 #8

zmbd
Expert Mod 5K+
P: 5,397
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
Aug 21 '12 #9

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

Expand|Select|Wrap|Line Numbers
  1. ?strFilter
  2. 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.
Aug 21 '12 #10

LeighW
P: 73
Nay set all the default values to =False but still but working
Aug 21 '12 #11

twinnyfo
Expert Mod 2.5K+
P: 3,283
Depends on if you want records that meet all criteria or those that meet at least one of the criteria.
Aug 21 '12 #12

LeighW
P: 73
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"
Aug 21 '12 #13

LeighW
P: 73
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
Aug 21 '12 #14

zmbd
Expert Mod 5K+
P: 5,397
Leigh,
Let's go back to what this form is trying to accomplish and the undrlying table structure.
-z
Aug 21 '12 #15

LeighW
P: 73
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.
Aug 21 '12 #16

zmbd
Expert Mod 5K+
P: 5,397
The following code when placed in the clickevent of the command button "cmd_build_filter" will build the filter.
I placed six unbound contols

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Option Explicit
  4.  
  5. Private Sub cmd_build_filter_Click()
  6. '
  7. 'small code to build where statment on the fly
  8. '
  9. Dim z_str_where As String, z_str_addfield As String
  10. Dim z_ctl As Control, z_ctlg As Controls
  11. Dim z_bln_atleastone As Boolean
  12. '
  13. 'get the control group for the fomr
  14. Set z_ctlg = Me.Controls
  15. '
  16. 'prebuild the intro
  17. z_str_where = "WHERE ("
  18. '
  19. 'I don't assume that booleans will 
  20. 'always be defaulted to false!
  21. z_bln_atleastone = False
  22. '
  23. 'now loop thru every control on the form
  24. For Each z_ctl In z_ctlg
  25. '
  26. 'If the control is a check box type then lets take a look
  27.     If z_ctl.ControlType = 106 Then
  28.         '
  29.         'Add some other checks here to make sure that
  30.         'you have the correct controls...
  31.         '
  32.         'if the control is true then add it to the filter
  33.         'however, just incase it is null return a false
  34.         If Nz(z_ctl, False) Then
  35.             '
  36.             'set the flag to true for the rest of the build
  37.             z_bln_atleastone = True
  38.             '
  39.             'build my field string... like to take a look as I build these things...
  40.             'so using the control's name we're 
  41.             'going to add that name to the 
  42.             'filter... for example use the TAG property
  43.             'to store the field name.
  44.             z_str_addfield = "(" & z_ctl.Name & " = -1) " & "OR "
  45.             '
  46.             'append the string to the filter
  47.             z_str_where = z_str_where & z_str_addfield
  48.         End If
  49.     End If
  50. Next z_ctl
  51. '
  52. If z_bln_atleastone Then
  53.     '
  54.     'get rid of the final OR and space
  55.     'if you used the AND then you would change the -3 to -4
  56.     z_str_where = Left(z_str_where, Len(z_str_where) - 3)
  57.     '
  58.     'add closeing  parenthese 
  59.     z_str_where = z_str_where & ")"
  60.     '
  61.     'For now lets just print to the debug
  62.     'however, this would be the sting you should use.
  63.     debug.print z_str_where
  64. Else
  65.     MsgBox "You must select at least one check box to make this code work!", _
  66.         vbCritical, "Really?"
  67. End If
  68. End Sub
  69.  
  70.  
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
Aug 21 '12 #17

zmbd
Expert Mod 5K+
P: 5,397
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
Aug 21 '12 #18

LeighW
P: 73
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
Aug 23 '12 #19

NeoPa
Expert Mod 15k+
P: 31,489
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]
Expand|Select|Wrap|Line Numbers
  1. Field           Type      IndexInfo
  2. PK_ID           AutoNumber    PK
  3. Title           Text
  4. Description     Memo
  5. fld_Permit1     Yes/No
  6. fld_Permit2     Yes/No
  7. fld_Permit3     Yes/No
  8. fld_Permit4     Yes/No
  9. fld_Permit5     Yes/No
  10. 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?
Aug 23 '12 #20

zmbd
Expert Mod 5K+
P: 5,397
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)
Aug 23 '12 #21

LeighW
P: 73
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:
Expand|Select|Wrap|Line Numbers
  1. 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!
Aug 28 '12 #22

LeighW
P: 73
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
Aug 28 '12 #23

LeighW
P: 73
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.
Aug 28 '12 #24

NeoPa
Expert Mod 15k+
P: 31,489
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?
Aug 28 '12 #25

zmbd
Expert Mod 5K+
P: 5,397
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
Aug 28 '12 #26

NeoPa
Expert Mod 15k+
P: 31,489
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).
Aug 28 '12 #27

LeighW
P: 73
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
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Z_str_where
with:
Expand|Select|Wrap|Line Numbers
  1. 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!
Aug 29 '12 #28

LeighW
P: 73
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:

Expand|Select|Wrap|Line Numbers
  1. z_str_where = "WHERE ((chk_Permit1 = -1))"
Aug 29 '12 #29

zmbd
Expert Mod 5K+
P: 5,397
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
Aug 29 '12 #30

LeighW
P: 73
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.
Aug 29 '12 #31

LeighW
P: 73
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.
Aug 29 '12 #32

NeoPa
Expert Mod 15k+
P: 31,489
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 :
Expand|Select|Wrap|Line Numbers
  1. ([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.
Aug 30 '12 #33

Post your reply

Sign in to post your reply or Sign up for a free account.