ukdane
11-20-2009, 12:42 AM
Access 2003.
I have a form, which contains two comboboxes.
The first combobox is populated with a list of suppliers names.
The second combobox is populated with the supplier's customers names.
The relationship is one supplier to many customers.
I have managed to set it up so that when I select a certain supplier, only the relevant customer names are shown in the respective combobox.
This was done using the following vba attached to the Exit event of the supplier combobox (actually I've applied it to a number of events).
Private Sub supplierid_Exit(Cancel As Integer)
Me.consigneeid.Requery
Me.Repaint
End Sub
The row source for the customer is as follows (note consignee is the same as customer):
SELECT QryConsigneeFullName.consigneeid, QryConsigneeFullName.ConsigneeFullName FROM QryConsigneeFullName GROUP BY QryConsigneeFullName.consigneeid, QryConsigneeFullName.ConsigneeFullName, QryConsigneeFullName.supplierid HAVING (((QryConsigneeFullName.supplierid)=Forms!FrmSaleOrder!supplierid));
The problem is, that if I change the customer combobox, on an existing record, and then move to a record with a different supplier than that I've just adjusted, the customer dropdown is blank (if I then click/activate the supplier combobox, it will reshow the correct data).
How do I get the database to "refresh" the supplier and customer comboboxes each time the user changes the record?
Thanks for your help.
I have a form, which contains two comboboxes.
The first combobox is populated with a list of suppliers names.
The second combobox is populated with the supplier's customers names.
The relationship is one supplier to many customers.
I have managed to set it up so that when I select a certain supplier, only the relevant customer names are shown in the respective combobox.
This was done using the following vba attached to the Exit event of the supplier combobox (actually I've applied it to a number of events).
Private Sub supplierid_Exit(Cancel As Integer)
Me.consigneeid.Requery
Me.Repaint
End Sub
The row source for the customer is as follows (note consignee is the same as customer):
SELECT QryConsigneeFullName.consigneeid, QryConsigneeFullName.ConsigneeFullName FROM QryConsigneeFullName GROUP BY QryConsigneeFullName.consigneeid, QryConsigneeFullName.ConsigneeFullName, QryConsigneeFullName.supplierid HAVING (((QryConsigneeFullName.supplierid)=Forms!FrmSaleOrder!supplierid));
The problem is, that if I change the customer combobox, on an existing record, and then move to a record with a different supplier than that I've just adjusted, the customer dropdown is blank (if I then click/activate the supplier combobox, it will reshow the correct data).
How do I get the database to "refresh" the supplier and customer comboboxes each time the user changes the record?
Thanks for your help.