Any help would be great.
I have 20+ tables in a database all linked in some way or another.
What i would like some help and advice on concerns just 3.
Asset Table
Customer Table
Contact table
Asset table links to the customer table via a customer_id key in both tables. Meaning the Asset belongs to a particular customer. I have an asset form and grab the customer entry from a combo box linked to the customer table.
Contact table links to the customer table via a customer_id key in both tables. Meaning the contact belongs to one customer. Using a contact form i grab the customer from combo box drop down list.
Now on the Asset form i have also added a combo box drop down list that looks up the contacts. I have built a SQL where clause to the selection of the contacts to be restricted to those that have the customer_id of the customer selected from the customer combo drop down box.....or so i thought. The list of contacts is restricted to the correct customer on the first instance of selecting one. If i move records and drop the contacts list down for an asset that has a different customer, the list contents are that of the first selection. The contents of the combo box does not refresh based upon the customer combo box changing. I have to close the form, re-open it and then go to the next record and then indeed the correct list is there, again only for the first selection. This applies to whether i use the [Forms]![asset]![combo116].[value] type statement or whether i use the actual field value instead. There is no need to add more than one table into the lookup query as i see it as the asset table records the customer_id field therefore i am looking up the contacts in the contact table using the customer_id field as there is also a customer_id field in the contacts table
I have tried requery, but that requeries the whole record set and i end up at the first record again, that isnt desireable and doesnt refresh the lists anyway.
So anyone got any ideas how you refresh the contents of 1 combo box based upon the selection in another realtime upon the first changing without breaking the record source or row source??
Im relatively new to Access but have a SQL background but am failing badly on this one, it might be something simple but i cant see the obvious so any help would be appreciated from you Access lords out there.
Regards
Simon
I have 20+ tables in a database all linked in some way or another.
What i would like some help and advice on concerns just 3.
Asset Table
Customer Table
Contact table
Asset table links to the customer table via a customer_id key in both tables. Meaning the Asset belongs to a particular customer. I have an asset form and grab the customer entry from a combo box linked to the customer table.
Contact table links to the customer table via a customer_id key in both tables. Meaning the contact belongs to one customer. Using a contact form i grab the customer from combo box drop down list.
Now on the Asset form i have also added a combo box drop down list that looks up the contacts. I have built a SQL where clause to the selection of the contacts to be restricted to those that have the customer_id of the customer selected from the customer combo drop down box.....or so i thought. The list of contacts is restricted to the correct customer on the first instance of selecting one. If i move records and drop the contacts list down for an asset that has a different customer, the list contents are that of the first selection. The contents of the combo box does not refresh based upon the customer combo box changing. I have to close the form, re-open it and then go to the next record and then indeed the correct list is there, again only for the first selection. This applies to whether i use the [Forms]![asset]![combo116].[value] type statement or whether i use the actual field value instead. There is no need to add more than one table into the lookup query as i see it as the asset table records the customer_id field therefore i am looking up the contacts in the contact table using the customer_id field as there is also a customer_id field in the contacts table
I have tried requery, but that requeries the whole record set and i end up at the first record again, that isnt desireable and doesnt refresh the lists anyway.
So anyone got any ideas how you refresh the contents of 1 combo box based upon the selection in another realtime upon the first changing without breaking the record source or row source??
Im relatively new to Access but have a SQL background but am failing badly on this one, it might be something simple but i cant see the obvious so any help would be appreciated from you Access lords out there.
Regards
Simon
Last edited:
