Originally Posted by
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.