Using field list as criteria to select records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I don't know if this subject makes sense but I have a combo box that uses a
field list for selection. The fields are in a table and the rows are
activities. So each row has an "x" placed under the specfic field where this
particular activity should be selected. I would like to build a query that
basically finds the field that I select in the combo box and pulls only the
records that have an "x" in the row under the selected field. The table
looks something like this:

Activity ACMM AMM ASM CD-ROM APP
Verify cover and/or title pages X X X X
Verify Export Control Notice X X X X
Verify ATA number matches. X X
Verify title matches PWMIS X X X X


So I want to select from my combo box a field (ASM) and only select the
records that have an "X" in that row.

Can someone help!!
 
Yes, you can do that. You will have to construct your SQL programmatically:

strSQL = "SELECT " & Me.MyCombo & " FROM SomeTable WHERE " & Me.MyCombo & "
= 'X';"

So, if you selected ASM in the comb box, it will translate to

SELECT ASM FROM SomeTable WHERE ASM = 'X';
 
Klatuu:

Thanks for your help. I can't seem to get this statement written correctly.
If you could create the SQL exactly as I should write it, I would appreciate
it. The table name is "tblQualChecklist"; the combo box is using the field
list from the tblQualChecklist; the combo box is "CmbDocType" and the two
fields that I want to select are "Sequence" and "ChecklistItem" where there
is an X in the field. One more thing, the table consists of the the field
names, i.e. ASM, ACMM, etc. but my combo box also shows the field name
"Sequence" and "ChecklistItem". Is there a way to not show those two and
only show the others in the combo box?
 
I'm not sure what you are asking regarding the field names. Are you saying
the field names are ASM and ACCM and you are wanting to show Sequence and
ChecklistItem instead of the actual field names?

Also, here is a trick you can use to write the SQL. Use the query designer
to build the query. The switch to SQL view and copy/paste the code into your
VBA.
 
Thanks again. No, the field names are "Sequence" which is the number of the
record for each "ChecklistItem" (also a field name) which is the actual
activity. The other field names are ACMM, ASM, CD-ROM, etc. I only want to
show the other field names in the combo box, not "Sequence" or
"ChecklistItem".

With my SQL, I believe I am not quite clear. See, the combo box lists the
field names in the tblQualChecklist. The table has an activity number
(Sequence) and the description of the activity "ChecklistItem". So when a
user selects the field "ASM" from the combo box, the results should be each
sequence and checklistitem that has an "X" next to that particular record in
the field that has been selected from the combo box. Make more sense?
 
The way to filter a field list type row source type is rather than to use the
table as the row source, create a stored query that has only the fields you
want to select from and use the query name as the row source.
 
Back
Top