473,836 Members | 1,509 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

73 New Member

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()
  3. Dim MyVar
  5. MyVar = "[SOMEFIELD] = " & SOMECOMBOBOX.Value & "" 'This is the filter
  7. DoCmd.OpenForm "SOMEFORM", WhereCondition:=MyVar 'This opens the form with the filter above.
  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,

Aug 21 '12
32 6207
73 New Member
Nay set all the default values to =False but still but working
Aug 21 '12 #11
3,653 Recognized Expert Moderator Specialist
Depends on if you want records that meet all criteria or those that meet at least one of the criteria.
Aug 21 '12 #12
73 New Member
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
73 New Member
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
5,501 Recognized Expert Moderator Expert
Let's go back to what this form is trying to accomplish and the undrlying table structure.
Aug 21 '12 #15
73 New Member
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.
Aug 21 '12 #16
5,501 Recognized Expert Moderator Expert
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

Expand|Select|Wrap|Line Numbers
  2. Option Compare Database
  3. Option Explicit
  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
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...

Aug 21 '12 #17
5,501 Recognized Expert Moderator Expert
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...

Aug 21 '12 #18
73 New Member
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
32,584 Recognized Expert Moderator MVP
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

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

Similar topics

by: yf | last post by:
A KB article "http://support.microsoft.com/default.aspx?scid=kb;en-us;209599" tells that the maximum number of records that a table may hold if the PRIMARY key data type is set to AUTONUMBER is 4,294,967,295. Suppose the PRIMARY key data type is set to "RANDOM" AutoNumber. Suppose an application (a) successfully INSERTS "X" records, then (b) successfully DELETES "Y" records (X >= Y), then
by: Alpha | last post by:
It's handy to have the blue sqare mark on all the resutls of the Found lines but how do I remove it once I'm done with that? Thanks, Alpha
by: Phill W. | last post by:
Is there anything amiss with the newsgroup microsoft.public.dotnet.vb.general ?? From where I'm sitting, it's /completely/ dried up - only four posts in as many days. Did I miss the mass exodus, or is my news server simply messing me about?
by: ntsNews | last post by:
Hi, Using the built in command "Find Next" is there a way to have the Match Drop Down Menu to default to "Start of Field"... or can it be the only option? GCM
by: Greg Smith | last post by:
I am making the step from windows to web. The final destination is our production server running Server 2003 Web Edition. I am having problems using the BUILD | PUBLISH WEB SITE option. What are the basic steps to publish a simple web site? All examples I can find use the local server. I am looking for something like. 1. Create a "New web site" selecting the "File System" option.
by: wecka | last post by:
Does any one know to bound the choices of an option group to a table. Table structure is ID, Text which shall map to Text, Value for the radio buttons repectively. Appreciate advice. - Hany
by: Robert Kilroy | last post by:
Greetings, I've been working on this for a few hours now. It seems to be a pretty simple task but I keep running into " has no properties". I have a select box defined as follows: <SELECT MULTIPLE NAME="myOptions" SIZE=5> <OPTION VALUE="Blue">Blue</OPTION> <OPTION VALUE="Red">Red</OPTION>
by: cepera | last post by:
Hi guys! How I can retrieve "option value" info from select tag? I am using this code, but it is only let me get "name". I need to pass this value to second select tag. I found this code here: http://adamv.com/dev/javascript/http_request but they do not have any instructions how to retrieve option values. ----------JS------------
by: bonneylake | last post by:
Hey Everyone, Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place. what i am trying to display previously entered multiple fields. I am able to get my serial fields to display correctly, but i can not display my parts fields correctly. Currently this is what it does serial information 1 parts 1
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.