how do i use a quersy to populate a form

[Guest]

I have a query that i want to use to look up parts by part number, every
part has 20 related field in this query, i want to use a textbox to type
the part number and use a command button to execute the search that will then
populate the remaining fields, how should this be done

 

[Al Campagna]

Mike,
On my website (below) I have a sample file (A97 or A2003) called QuickFindCombo, that
uses a combo box on the form to quickly find any particular record in a recordset. It
works right from your normal recordset form.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Hey Al, the QuickFindCombo worked perfectly, another question....i noticed
when you use the scroll wheel to look through records the related data
changes but the part number doesnt, I looked at you example in the
"ComboPopulatesMultipleFields" database and its excatly what i need, how can
i getti to work with the following fields in the Products Table and i need to
display all the fields in the form:

ProductID
ProductName (which is the actual part number)
ProductDescription
CategoryID
SupplierID
Discontinued
SellPrice
SellDate
PartDeliveryTime
Vendor1Name
Vendor1Address
Vendor1City
Vendor1Region
Vendor1ZipCode
Vendor1Phone
Vendor1Cost
Vendor1QouteDate
Vendor2Name
Vendor2Cost
Vendor2QouteDate
Vendor2Phone
Vendor3Name
Vendor3Cost
Vendor3QouteDate
Vendor3Phone
Vendor4Name
Vendor4Cost
Vendor4QouteDate
Vendor5Name
Vendor5Cost
Vendor5QouteDate
Vendor5Phone
OEM
OEMSellPrice
OEMDate
OEMPhone
Remarks
DatePartLastSold

also how could i program a macro to clear the form after the search? I
hope i not being a burden with these question, am just new to Access.....

 

[Al Campagna]

Mike,
If by PartNumber, you mean the Find PartNo Combo, that's because it's an unbound field,
so whatever happens to be entered displays on each record, until you change it. This
should not be a problem, but... you could always just null out the The PartNo after
"finding" the correct record.

Re ComboPopulatesMultipleFields, those values only "display" as a help to the user.
In your field list below, I'll assume that ProductID is the key field. If customers
order a ProductID, all you really need to save in the Order table would be the ProductID.
In any subsequent form, query, or report you would use that ProductID to rerelate it to
all it's ancillary information(CatrgoryID SupplierID, etc.. etc..)

Using the method in ComboPopulatesMultipleFields, you might want to "display only" the
ProductName and Description as an aid to the user, but not all the others. All that data
is stored in the original Parts table, and does not need to be captured every time someone
orders that Part.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

oh i didnt look at that one yet but will, the reason why i said the
"ComboPopulatesMultipleFields" one works because the user needs ll that info
to determine what they actually qoute a part for, the y wantto see what was
the previous information and be able ot call the vendor to see if the
information is still valid so they dont either "misqoute" and lose money
by selling a part tooo low and track how often a part is bought/sold to and
from vendors and customers

 

[Al Campagna]

Mike,
Then instead of using Combo Populates Multiple Values, you should be using a subform
to display all those fields, where the parent would be the PartNo in the combo, and the
child... the PartNo in the subform.
Select a PartNo, and use the subform to see all the ancillary info you need for your
OrderPrice, and whatever other fields you need (on thge Main) to complete the original
record.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."