Form to search on a foreign id, then update the calling record.

[izoprene]

Hello there,
I've been stuck on an access 2007 problem, which to me seems like a common thing to solve but I cannot
find any information on how to solve it.

This example shows what I mean. Take 2 tables;

orders_table
------------
order_id int primary key
customer_id int (foreign key of customer_table customer_id field)
order_ref text

customer_table
--------------
customer_id int primary key
company_name text
contact_person text


So I made an orders form like this to display and update the data;
---------------------------------
Order ID: [order_id]

Customer ID: [customer_id] {change_customer_button}
Customer Name: [customer_name]


Contact Person: [contact_person]

order Reference: [order_ref]
----------------------------------

I would like a button on the orders form like the one next to the Customer ID above which when pressed would open a modal search box, where you can search the customer_name field of the customer_table to drill down to the desired customer. It would show all the data about that customer. Once the desired customer is found, is there some way to close the modal box and return to the orders form (Via a 'Apply changes' to record type button?) which would now contain the NEW customer ID of the desired customer.

I'm sure this has been done before, ie a search on an a foreign id field, which then updates the record with the new foreign id.

Thanks in advance. :-)

[NTC]

a. you can make the CustomerID field in the Orders table be a LookUp field that looks at the customer table. This will change this field into a combobox/dropdown type field. (there is some resistance to this feature by professional developers - but it is a feature of the product).

b. you can set up, at the form level, a combobox that looks at the customer list, and then put in some VBA in the AfterUpdate event that puts the information of this combobox into the field on the form.

c. as variation of b. would be to use a popup form rather than a combobox - the concept is identical.

both b & c presume your form is in single form mode and not continuous form mode.

hope this helps.

[izoprene]

Hi HTC,
Thanks for the quick reply. I think I'll go with the combobox idea, sounds like the neatest solution. Many thanks! :-)