retrieval of data not from record source 1

[GShen]

Hi,
Running ACCESS 2000, SQL 2000.
Have an input form for adding items to an item file.
The record source is the actual table.
One of the pieces of info. is selecting the vendor number on a combo box. Here is the problem.
When I select the vendor I want to get information from the vendor file for the new item being added and display it.
I am using the event after update. Not sure how to get this data on the form. I can call a stored procedure to get the actual data (happens to be only 1 field) but how do I get this info. on the form because the record source is not the vendor file (eg. it the item file).
Is there a way of doing this?
I feel like I am going in circles.
Thanks for any help.

 

[TonyJollans]

Hi GShen,

Can you not use a DLookup for the controlsource of your field from the vendor file? Shouldn't need code at all.

Enjoy,
Tony

 

[GShen]

Tony,
I read up on the DLookUp. This is great but still have 2 problems.
1)the key to my table is 2 fields. Can't seem to get the correct syntax for multiple fields in the criteria.

2)More importantly. I cannot use this in the control source because I have to select a vendor in a combo box in order to get the information from the vendor table(the non control source table). The vendor is part of the key. The facility is the other part.
(we could have the same vendor # with different facilites, hence the need for #1 above). In any case, I put the code in VB, on the after update event for the combo box. I was able to execute it but I was not able to send it back to the form.
I created an unbound field on the form and tried setting the results to it but no can do.

Am I missing something yet?

If I cannot do this at least you gave me another piece of food for thought on other applications.
Thanks.

 

[TonyJollans]

Hi GShen,

The criteria on a DLookup are like an SQL WHERE clause but without the "WHERE", for exampe "ID=1 AND ID2=2". You can also use values from controls on the form in building the criteria so that updates are immediate, for example:

=DLookUp("VendorName","Vendor","Facility = """ & [Text30] & """ AND VendorID = " & [combo26])

Please post back if that is not enough for you.

Enjoy,
Tony

 

[GShen]

Tony,
I had a double quote missing on my parameter list. Problem 1 solved.
This may have been causing me my other problem becuase life is just dandy now. I created an unbound field on the form and when the user selects a vendor number from the combo box, I initiate the After Update event which performs the DLOOKUP in VBA and sets the unbound field equal to my NEW DATA, all in one statement.

Life is grand again.

Thanks a million. That is worth a STAR.