My question is how can you make a combo box based on a Table/Query on a form change with the different records on that form?
I created a combo box that looks up information (shipper) on a query. This combo box contains three fields (Shipper)(ShipperID) and (ShipperAddress). When you input a Shipper Name into the combo box it populates an unbound text box directly below it with the Shipper Address, Phone,... The text box populates directly from a column on the combo box. This was done by adding a couple lines of code:
Private Sub cboShipper_AfterUpdate()
Me.txtShipAddress = Me.cboShipper.Column(2)
End Sub
The query is based upon a table tblShipper and concatenates address,zip,phone,state,contact fields to
populate the column of the combo box that goes to the text box below it.
I added a not in list event procedure found on your website, a link to microsoft Support, that brings you to tblShipper to input new shipper info if not in list:
Private Sub cboShipper_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr(13)
'Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
'ask the user if he or she wishes to add the new customer.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add Shipper?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
'If the user chose yes, start the consignee form in data entry
'mode as a dialog form, passing the company name in
'NewData to the openForm method's OpenArgs argument. The
'OpenArags argument is used in Consignee form's From_Load event procedure
DoCmd.OpenForm "frmShipper", , , , acAdd, acDialog, NewData
End If
'Look for the consignee the user created in the Consignee Form
Result = DLookup("[Shipper]", "tblShipper", _
"[Shipper]='" & NewData & "'")
If IsNull(Result) Then
'If the customer was not created, set the response argument
'to suppress an error message and undo changes.
Response = acDataErrContinue
'Display a customized message
MsgBox "Please try again!"
Else
'If the customer was created , set the Response argument to
'indicate that new data is being added
Response = acDataErrAdded
End If
End Sub
Everything works great but the form that the combo box is on does not store any of the information for that record. When you scroll through the different records the combo box does not change. I want the form that the combo box is on(frmGeneral) to store the shipper information along with other information BillNo, CustomerName...on the text box below combo box for that record.
I changed the control source of the combo box to Shipper, a field I created in tblShipper, so that it would be bound to the form. This works but then the text box below (txtShipAddress) does not change for each record.
So If I get rid of the txtShipAddress I think it works but is there any way that I could keep txtShipAddress so it changes along with the combo box. Please help I've put a lot of time into this
I created a combo box that looks up information (shipper) on a query. This combo box contains three fields (Shipper)(ShipperID) and (ShipperAddress). When you input a Shipper Name into the combo box it populates an unbound text box directly below it with the Shipper Address, Phone,... The text box populates directly from a column on the combo box. This was done by adding a couple lines of code:
Private Sub cboShipper_AfterUpdate()
Me.txtShipAddress = Me.cboShipper.Column(2)
End Sub
The query is based upon a table tblShipper and concatenates address,zip,phone,state,contact fields to
populate the column of the combo box that goes to the text box below it.
I added a not in list event procedure found on your website, a link to microsoft Support, that brings you to tblShipper to input new shipper info if not in list:
Private Sub cboShipper_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr(13)
'Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
'ask the user if he or she wishes to add the new customer.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add Shipper?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
'If the user chose yes, start the consignee form in data entry
'mode as a dialog form, passing the company name in
'NewData to the openForm method's OpenArgs argument. The
'OpenArags argument is used in Consignee form's From_Load event procedure
DoCmd.OpenForm "frmShipper", , , , acAdd, acDialog, NewData
End If
'Look for the consignee the user created in the Consignee Form
Result = DLookup("[Shipper]", "tblShipper", _
"[Shipper]='" & NewData & "'")
If IsNull(Result) Then
'If the customer was not created, set the response argument
'to suppress an error message and undo changes.
Response = acDataErrContinue
'Display a customized message
MsgBox "Please try again!"
Else
'If the customer was created , set the Response argument to
'indicate that new data is being added
Response = acDataErrAdded
End If
End Sub
Everything works great but the form that the combo box is on does not store any of the information for that record. When you scroll through the different records the combo box does not change. I want the form that the combo box is on(frmGeneral) to store the shipper information along with other information BillNo, CustomerName...on the text box below combo box for that record.
I changed the control source of the combo box to Shipper, a field I created in tblShipper, so that it would be bound to the form. This works but then the text box below (txtShipAddress) does not change for each record.
So If I get rid of the txtShipAddress I think it works but is there any way that I could keep txtShipAddress so it changes along with the combo box. Please help I've put a lot of time into this