Multi Field Selection (1 Viewer)

JMZ

New member
Local time
Today, 14:26
Joined
Nov 20, 2020
Messages
12
I have a huge problem going on for 2 weeks, I am trying to get my ComboBox selection to look at 3 different fields for a match and fill in the TextBox controls with those records in a SubForm.
Can someone explain the VBA needed to get a ComboBox to look at multiple fields for a match?
Example:
Main Form - ComboBox selection is "Bat"
Table:
Field1 = Glove
Field2 = Bat (matched)
Field3 = Ball
The CB selection could be in any one of the 4 fields.

Thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:26
Joined
Sep 21, 2011
Messages
14,046
Your structure is all wrong.
Each of those should be a record. Then no issues.
 

plog

Banishment Pending
Local time
Today, 14:26
Joined
May 11, 2011
Messages
11,611
Agree with Gasman and would like to expand.

Now, I'm sure those aren't your field names, but whenever you feel the need to numerate your field names (Equipment1, Equipment2, Equipment3, etc.) or suffix your field name with a type (Equipment_Football, Equipment_Golf, Equipment_Soccer, etc) its time for a new table. Instead of a field for each number or type you add a record to that new table:

tblEquipment
eq_ID, autonumber, primary key
ID_MainID, number, foreign key back to the table where all these fields currently are
eq_Name, text, will hold all those values currently have in your fields (Glove, Bat, Ball, etc.)
eq_Number, number, this will hold the number suffixed to your fields currently--if that number is important to you

That's it. Now, instead of 4 fields with every record, you simply put a record into this table for each piece of equipment. Then you can turn your combo box to it and use it for your source.
 

June7

AWF VIP
Local time
Today, 11:26
Joined
Mar 9, 2014
Messages
5,423
Why do you ask for VBA? A query would use OR operator in criteria to apply filter on all 3 fields.

However, I agree with others - db structure is not optimized.
 

JMZ

New member
Local time
Today, 14:26
Joined
Nov 20, 2020
Messages
12
I asked for VBA because I tried the OR in a Query SQL, but I could not get it to reference the combobox to, as I am not that proficient in SQL.
 

JMZ

New member
Local time
Today, 14:26
Joined
Nov 20, 2020
Messages
12
How can I get a ComboBox selection to match one of three fields and fill in a subform? I tried the OR operator but could not get it to work.
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:26
Joined
Sep 21, 2011
Messages
14,046
Use the query gui and the build button to get the form control reference correct.
Each criteria would be on a separate row.
 

Users who are viewing this thread

Top Bottom