W
WAstarita
I have a customer account form thats bound to the customers table.
There are 3 unbound combo boxes that are set to value lists which store
a value that gets applied to its corresponding data entry txt boxes
controlsource.
Strange thing is, whenever I refer to any of the unbound combos in VBA,
it sets them all to Null. As you can see, I intially tried to set the
combo to a variable, use the variable to make the changes to the other
controls and then set the combo value back to the variable. The
problem is when the On_Current Event fires, the sequence nocks out
combo's again, and brings an error because one of the AfterUpdate
events are trying to set a ControlSource to Null.
Heres the code, I've actually tried this on older forms without a
problem, this one might be a little different although its a lot less
complicated.
Private Sub AddressCity_AfterUpdate()
If IsNull(Me.AddressPostalCode) Then Exit Sub
Me.AddressState = DLookup("State", "ZipEX", "Zip = '" &
Me.AddressPostalCode & "' and City = '" & Me.AddressCity & "'")
End Sub
Private Sub AddressPostalCode_AfterUpdate()
Me.AddressCity.Requery
If IsNull(Me.AddressCity) Then Exit Sub
Me.AddressState = DLookup("State", "ZipEX", "Zip = '" &
Me.AddressPostalCode & "' and City
= '" & Me.AddressCity & "'")
End Sub
Private Sub AddressTypeSelect_AfterUpdate()
ATS = AddressTypeSelect.Value
Me.Address1.ControlSource = ATS & "Address1"
Me.Address2.ControlSource = ATS & "Address2"
Me.AddressCity.ControlSource = ATS & "AddressCity"
Me.AddressPostalCode.ControlSource = ATS & "AddressPostalCode"
Me.AddressState.ControlSource = ATS & "AddressState"
AddressTypeSelect = ATS
End Sub
Private Sub Form_Current()
Me.AddressPostalCode.Requery
Me.AddressCity.Requery
Me.AddressTypeSelect = "ShipTo"
Me.FaxType = "Primary"
Me.PhoneType = "Primary"
Call AddressTypeSelect_AfterUpdate
Call PhoneType_AfterUpdate
Call FaxType_AfterUpdate
End Sub
Private Sub PhoneType_AfterUpdate()
PT = PhoneType.Value
Phone.ControlSource = PT
PhoneType = PT
End Sub
Private Sub FaxType_AfterUpdate()
FT = FaxType.Value
Fax.ControlSource = FT
FaxType = FT
End Sub
There are 3 unbound combo boxes that are set to value lists which store
a value that gets applied to its corresponding data entry txt boxes
controlsource.
Strange thing is, whenever I refer to any of the unbound combos in VBA,
it sets them all to Null. As you can see, I intially tried to set the
combo to a variable, use the variable to make the changes to the other
controls and then set the combo value back to the variable. The
problem is when the On_Current Event fires, the sequence nocks out
combo's again, and brings an error because one of the AfterUpdate
events are trying to set a ControlSource to Null.
Heres the code, I've actually tried this on older forms without a
problem, this one might be a little different although its a lot less
complicated.
Private Sub AddressCity_AfterUpdate()
If IsNull(Me.AddressPostalCode) Then Exit Sub
Me.AddressState = DLookup("State", "ZipEX", "Zip = '" &
Me.AddressPostalCode & "' and City = '" & Me.AddressCity & "'")
End Sub
Private Sub AddressPostalCode_AfterUpdate()
Me.AddressCity.Requery
If IsNull(Me.AddressCity) Then Exit Sub
Me.AddressState = DLookup("State", "ZipEX", "Zip = '" &
Me.AddressPostalCode & "' and City
= '" & Me.AddressCity & "'")
End Sub
Private Sub AddressTypeSelect_AfterUpdate()
ATS = AddressTypeSelect.Value
Me.Address1.ControlSource = ATS & "Address1"
Me.Address2.ControlSource = ATS & "Address2"
Me.AddressCity.ControlSource = ATS & "AddressCity"
Me.AddressPostalCode.ControlSource = ATS & "AddressPostalCode"
Me.AddressState.ControlSource = ATS & "AddressState"
AddressTypeSelect = ATS
End Sub
Private Sub Form_Current()
Me.AddressPostalCode.Requery
Me.AddressCity.Requery
Me.AddressTypeSelect = "ShipTo"
Me.FaxType = "Primary"
Me.PhoneType = "Primary"
Call AddressTypeSelect_AfterUpdate
Call PhoneType_AfterUpdate
Call FaxType_AfterUpdate
End Sub
Private Sub PhoneType_AfterUpdate()
PT = PhoneType.Value
Phone.ControlSource = PT
PhoneType = PT
End Sub
Private Sub FaxType_AfterUpdate()
FT = FaxType.Value
Fax.ControlSource = FT
FaxType = FT
End Sub