Drop-Down Boxes and Multi-Field Searches

[Cal S.]

Hello all,

I am designing a search query that will allow the user to look up a record in a database to view it. I have everything already set up, and most of it working properly.

The user can recall a database entry using 7 different criteria--Type, Customer, PartNo, JobNo, Warehouse, Bin, and Shelf. The Type and Warehouse entries on the database are drop-down values, the other 5 values are text entries.

So far, I have been able to get the look up query to pull up the desired records on the Customer, PartNo, JobNo, Bin, and Shelf criteria using

Like "*" & [Forms]![Search Form]![Customer] & "*"

.



However, with Type and Warehouse criterion, the two that use drop-down boxes in the database, I have been unsuccessful in being able to call up any records using either the above partial or the more exact:

=[Forms]![Search Form]![Type]

. I did try to change Type to a textbox on the look up query, but that was similarly unsuccessful. On a side note, I must use drop-downs on the Warehouse field since I have another query that concatenates that value with a couple others.

How can I, without delving into VB coding unless absolutely necessary, format the lookup query so that it will read the values of the drop downs?

Thanks!

[rpeare]

Are your combo boxes based on a value list or a table?
If they are based on tables, do the tables contain a primary key (autonumber) or other identifier that is *NOT* based on the description?
Are you attempting to perform the match based on the description or the PK (if it exists).

If your combo box contains a PK as the BOUND COLUMN but you are searching based on the description you will never find a match.

[Cal S.]

The combo box is based on a table. Ok, let me back up for a second.

The combo box should have three values for 'Type'--Raw, Finished Goods, Sub Assembly. On the Warehouse, it's the same but only two values. I want to work it where the user may choose a desired value from the drop box and be able to pull all the records that have that status of completion. Or, for the warehouse cb, then all the records that are in that warehouse.

For your specific questions:
No, the table does not have a primary key. I'm trying to create a match from the values of that column in the table.

I found a source for doing what I want, but only halfway. http://datapigtechnologies.com/flash...tomfilter.html. This shows exactly how to do what I want it to, and it works partially. For some reason, it still pulls all the records no matter which one is chosen, and also displays all values in that column despite repeating values.

[June7]

Would have to review data structure, combobox properties, and relevant code.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Cal S.]

Ventory1.zip
Ok. It's attached. It's far from finished, so there are some other rough edges around it. All entries are fictitious and for testing purposes only. The Warehouse combo box isn't even set up yet, but I'm sure it's just copy+paste what I do for the Type combo box.

Thank you in advance.

[June7]

The expression in Type combobox won't work. Cannot reference table directly in ControlSource property. Why would you do that anyway?

The form doesn't even have a RecordSource.

Review the DataPig tutorial again. It does not open a query (although DataPig does have an example for this). It is using the dynamic parameterized query as the form RecordSource with textboxes bound to fields and VBA code to requery the form.

[Cal S.]

Unlike the DataPig tutorial, I want to pull multiple records, not just one. There may be hundreds of 'Raw' or 'Finished Goods' on hand that could be pulled up. That's why I have several criteria for the user to choose from and then to run a query and pull up a report based off of the results. So it doesn't make much sense for me to tell the whole form to read off of the combo box, or does it?

Is there something more I could say to help you understand what I'm trying to do? I do appreciate your assistance so far!

[June7]

Set the form for Continuous view and that will display multiple records. Put the UNBOUND filter input controls in form header section.

And/Or open report that uses the dynamic parameterized query as RecordSource (DataPig also has example for that).

In either case, there is no need to open the query object first.

All DataPig examples of dynamic parameterized query use text data type fields. Will not work for number, date, Yes/No types.

I never use dynamic parameterized queries. Review: http://www.allenbrowne.com/ser-62code.html