HELP!! Creating queries to pull info from multiple tables

[mgillespie21234]

I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.

What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.

So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records. Once i learn the way to do it i'm sure i can tweak it to what i need.

Please bear with me i'm new and will be asking many questions. Patience is appreciated!!

 

[Brianwarnock]

If memory serves me correctly creating the combo is prompt driven so create your form and combo, then to address it in a query switch to SQL view and write the where clause

Where (myfield = Forms!Myformname!Mycomboname or Forms!Myformname!Mycomboname Is Null)

The purpose of the Is Null part is that if nothing is selected from the combo all records match, this will be important when you add other criteria and want to select by just one.

Brian

 

[mgillespie21234]

Thanks this is a project for work that i do in my spare time i will try this today and let you know how it goes. Please stand by for questions

 

[mgillespie21234]

This what i came up with and the errors i got:

WHERE OfficialName=Forms!Location_SRCH!Loc!Location_SRCH!LOC Is Null

myfield= OfficialName, myformname=Location_SRCH, Mycomboname=loc

Official name is the field that i want to be selected in the combo box. So for example a official name for one our locations is MPP @ 19th Street and there are many more located in the Fields column. From the combo i want a drop down that has all our possible Location names (official names).

When i entered this SQL Statement and ran the query i get a enter parameter box that says official name. after i type in the official name i get another enter parameters box that has the rest of the statement. ( Forms!Location_SRCH!Loc!Location_SRCH!LOC )

So i want my form to look like this the combo box where i can select the locations and then a button next to it that says search so when you click it.. it will display the certain information for that location (pc type, Name, model and so on)

I'm pretty sure i am typing it wrong or i used the wrong statement. Any help is appreciated.

 

[Brianwarnock]

Where OfficialName=Forms!Location_SRCH!loc or Forms!Location_SRCH!Loc Is Null

This assumes official name is the field name in the query to which the criteria is being applied and
Location_SRCH is the form name and Loc is the combobox name

Brian

 

[mgillespie21234]

Official Name is the name of a column in my table. what should my query field name be im not sure what that is??

I spoke with a SQL DBA yesterday and she mentioned something about the combo box needs to pass the parameters threw. For example when i run the query now i get back all my location and all the details for each location. I need the combo box to tell the query that i only want the details for the location entered in the combo box. Im assuming this would be in The VB for the combo box. she mentioned something about setting a distinct value.

 

[mgillespie21234]

ALso i read that thi might work with my combo and forms names plugged in..

So, in the query builder, in the criteria section just type in

[forms]![form1]![Combo4]

But what criteria field do i enter it in when i have multiple fields and tables?

 

[mgillespie21234]

SO i got it work yae!!! with the last post i made using

[forms]![form1]![Combo4]

I am creating a new post with next question about displaying the results in a sub form from my Locations Search Form

 

[Brianwarnock]

How does that differ from what I have been telling you?

Oh I know , it does not allow for the selection of all values as I explained!!

I don't know why I bothered

Brian

 

[mgillespie21234]

I got it working guys by setting the the critera in my queary to filter using the values entered and also using me!.subform.requery in the vba code. Thanks everybody