Data being limited in one field with entry from another

[rice1973]

I have a customer and an address field on a form. Once a customer name is inputted the Address field needs to be limited to the addresses for that customer. I am currently able to do this with the following code:



Dim Source As String
Dim CustomerLookup As Long

CustomerLookup = Me.CustomerLookup.Value
Source = "SELECT GrowCustAddr.AddrID, GrowCustAddr.Addr, GrowCustAddr.Addr2, GrowCustAddr.Addr3, GrowCustAddr.City, GrowCustAddr.StateProvince, CatState.StateAbbreviation, "
Source = Source & "GrowCustAddr.Zip , GrowCustAddr.Country, CatCountry.Country, GrowCustAddr.GrowCustID, GrowCustAddr.AddrType, CatAddrType.AddrType "
Source = Source & "FROM CatState Right Join (CatCountry Inner Join (GrowCustAddr Inner Join CatAddrType ON GrowCustAddr.AddrType = CatAddrType.AddrTypeID) ON CatCountry.CountryID = GrowCustAddr.Country) on CatState.StateID = GrowCustAddr.StateProvince "
Source = Source & "WHERE GrowCustAddr.GrowCustID = " & CustomerLookup & " "
Source = Source & "ORDER BY GrowCustAddr.[AddrType]"

Me.CustomerAddress.RowSource = Source

However, the problem I have is that once you select a customer for a new or old record then the address field shows blank when you scroll through the records. You can click on the address field and then it shows the correct address that was in it. When I do a compact and repair it fixes this issue until you add or change another record.

[June7]

On form in continuous or datasheet view, that is expected behavior with comboboxes using conditional RowSource with alias lookup. Compact & Repair has no impact - close and reopen the form should accomplish the same 'fix'. This is a common topic.

One approach is to include GrowCustAddr table in the form RecordSource, join type "Include all records from {primary data table} and only those from GrowCustAddr that match". Bind textboxes to GrowCustAddr fields and set them as Locked Yes, TabStop No.

[rice1973]

I am wanting to limit the address drop down to only those addresses that go with that customer. The control source for the form is a query and the growcustaddr table is already a part of it. I tried this and it does not limit the list to the customer on that rowset.

I have a customer and an address field on a form. Once a customer name is inputted the Address field needs to be limited to the addresses for that customer. I am currently able to do this with the following code:

Dim Source As String
Dim CustomerLookup As Long

CustomerLookup = Me.CustomerLookup.Value
Source = "SELECT GrowCustAddr.AddrID, GrowCustAddr.Addr, GrowCustAddr.Addr2, GrowCustAddr.Addr3, GrowCustAddr.City, GrowCustAddr.StateProvince, CatState.StateAbbreviation, "
Source = Source & "GrowCustAddr.Zip , GrowCustAddr.Country, CatCountry.Country, GrowCustAddr.GrowCustID, GrowCustAddr.AddrType, CatAddrType.AddrType "
Source = Source & "FROM CatState Right Join (CatCountry Inner Join (GrowCustAddr Inner Join CatAddrType ON GrowCustAddr.AddrType = CatAddrType.AddrTypeID) ON CatCountry.CountryID = GrowCustAddr.Country) on CatState.StateID = GrowCustAddr.StateProvince "
Source = Source & "WHERE GrowCustAddr.GrowCustID = " & CustomerLookup & " "
Source = Source & "ORDER BY GrowCustAddr.[AddrType]"

Me.CustomerAddress.RowSource = Source

However, the problem I have is that once you select a customer for a new or old record then the address field shows blank when you scroll through the records. You can click on the address field and then it shows the correct address that was in it. When I do a compact and repair it fixes this issue until you add or change another record.

[June7]

My suggestion should not have altered the combobox which you said was already working. So I don't understand the issue.

Why have StateProvince and StateAbbreviation? I would set the combobox RowSource property:

SELECT AddrID, Addr, Addr2, Addr3, City, StateProvince, GrowCustAddr.Country, Zip FROM CatState RIGHT JOIN (CatCountry INNER JOIN GrowCustAddr INNER JOIN CatAddrType ON GrowCustAddr.AddrType = CatAddrType.AddrTypeID) ON CatCountry.CountryID = GrowCustAddr.Country) ON CatState.StateID = GrowCustAddr.StateProvince WHERE GrowCustID = [CustomerID] ORDER BY GrowCustAddr.[AddrType];

Then code behind customer combobox:

Me.cbxAddress.Requery