combo box on form

pizaccess

New member
Local time
Today, 14:00
Joined
Dec 24, 2001
Messages
8
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
 
Here's what I did. The combo box created has to have a control source to be bound to the form. You have to create an entry in your table where the combo box is located to link them. To illustrate my example: the combo box looked up a shipper name. There were three fields in my box: Shipper and ShipperID(Primary key 'auto number') and ShipperAddress. These came from my Shipper tbl. So on my General tbl I created an entry: ShipperID and made this my control source for the box. Then for the text box below I typed in the control source properties

=[cboShipper].[column](2)

cboShipper is my combo box and column2 is the third column in my combo box (since it is zero based) that populates the text box.
Don't forget delete the afterUpdate code not necessary anymore.
You also need to change column widths property of combo box to
1";0";0"
so only the Shipper is visible and make this the bound column.
 

Users who are viewing this thread

Back
Top Bottom