simple look up question

[cgmckenzie]

I have a table with mc#,company,address, and etc. The mc# is made up of a 5 digit number, with no duplicates. I would like to make a query where i could type in the mc# and the query pulls up the rest of the information. I appreciate any help thankyou
Chris Mckenzie

 

[RV]

What you want is called a parameter query:

SELECT ....
FROM YourTable
WHERE mc# =[Enter mc];

RV

 

[cgmckenzie]

RV,
I am a little confused here is the SQL for the query. This pulls up every load. What i am actually planning to do is have a form with some control buttons on it to find specific loads. The control button will say find a load by MC#, are Find a load by Load#, etc., you click the button, it will prompt you for the mc# are the load#, then find all the loads with that MC#, are load# matching, and so forth. I will generalize your sulotion to the rest of the query.
Thank you for your help Chris




SELECT LOAD.[Load#], LOAD.Agent, LOAD.TruckMC, LOAD.TruckCom, LOAD.ContactName, LOAD.Driver, LOAD.[Driver Cell], LOAD.PuCompany, LOAD.PuDate, LOAD.PUTime, LOAD.[PU#], LOAD.PuAddress, LOAD.PuCity, LOAD.PuState, LOAD.PuPo, LOAD.PuPhone, LOAD.AddPu, LOAD.PuComments, LOAD.Equipment, LOAD.DelDate, LOAD.DelTime, LOAD.DelCompany, LOAD.DelAddress, LOAD.DelCity, LOAD.DelState, LOAD.DelPo, LOAD.DelPhone, LOAD.AddDel, LOAD.DelComments, LOAD.BrokerCom, LOAD.Milleage, LOAD.RatePerMile, LOAD.FreightRate, LOAD.FuelSurcharge, LOAD.TarpFee, LOAD.TotalFreight, LOAD.TruckPay, LOAD.Profit, LOAD.TruckEvaluation, LOAD.CustomerID
FROM LOAD;

 

[jfgambit]

Once you create the form, you need to have the query reference the textbox that you create for the mc#

In the criteria of the mc# of the query add the following:
[Forms]![FormName]![TextboxName]

of course you will need to change the above to the names of your form and textbox.

HTH

 

[cgmckenzie]

Thanks Jf,

I did it and got it working I attached Docmd.OpenQuery statement to the textbox in "On Enter" control it works good but when i open the form it opens the blanck query along with the form. I tried to put a subreport at the bottom of the form to show the query, but the query will not work. I get Run-time error '3009' You tried to lock table 'LoadMCQuery' while opening it, but the table cannot be locked because it is currently in use. Wait a moment, and then try again. thank you for your time and help
Chris

 

[jfgambit]

I wouldn't put the code on a command Buttons On Click Event. Then you can fill out the textboxes and click the button to ru the query.

HTH

 

[cgmckenzie]

right now i am not using any buttons just a control box. The query runs perfect when i have Docmd.openquery "LoadMcQuery" on the "on enter" control of the box. The problem is instead of opening the query seperately i would like to have a subreport at the botton of the form with the query information in it. I appreciate your help thankyou Chris