Question How to populate fields on a form using a combo box

[cbrace09]

Hello,

I have a form (Work Orders Home) with a record source (tblWorkOrders). This form also contains a subform (Work Order Details) with a record source (tblWorkOrderDetails).

On the top section of my form, the user selects the CustomerID from a combo box. I have various other fields containg information about the order.

My Question - I would like to populate several fields (listed below) on the form based on the CustomerID retrieved in the combo box. I would like to also store those values in their fields but if that is rather extensive I can do without since I already have the information in the Customers table.

FullName: which combines [Fname] & [Lname]
StreetAddress: which combines [Address1] & [Address2] from the customers table
ExtendedAddress: which combines [City] & [State] & [Postal]
Email
Title
etc....

I have tried various methods with combo boxes to no avail.

Any help would be greatly appreciated.

Thank you in advance!

 

[HiTechCoach]

Do you really need to store a snapshot of the Customer's info with the Work Order so that is the Customer's information changes in the future, you will know the original data? If no, then you really do not need to store it. I normally only store the customer/address for where the work was actually performed.

To do what you want, you can use colums in the combo box to load the data (works great with smaller amounts of data) or use a Dlookup().

To use the DLookup method, here is an example from the Northwind sample database's Orders Sub form

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

    Dim strFilter As String
    
    ' Evaluate filter before it's passed to DLookup function.
    strFilter = "ProductID = " & Me!ProductID
    
    ' Look up product's unit price and assign it to UnitPrice control.
    Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductID_AfterUpdate:
    Exit Sub

Err_ProductID_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_ProductID_AfterUpdate

End Sub

To push the data from the combo box into the text boxes using the combo box's after update event. First you will need to be sure that you have columns in the combo box for each piece of data you want to place in a combo box.

Here is the above example converted to use a column in the combo box

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

    
    '  product's unit price is in the third column (index = 2) of the combo box 
    ' and assign it to UnitPrice control. 
    ' Combo box columns are text so also convert back to currency
       Me!UnitPrice = CCur(IIF(Me.ProductID.Column(2) = "", "0.00", Me.ProductID.Column(2)))

Exit_ProductID_AfterUpdate:
    Exit Sub

Err_ProductID_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_ProductID_AfterUpdate

End Sub

Hope this helps ...

 

[RuralGuy]

Is it a bound form? Using a query? Just join the two tables on the CustomerID field in the query. Post back if that does not make any sense.

 

[cbrace09]

Thanks for the adise guys, I really do appreciate the free help!

I have it working right now using the following code:

'CustomerID After update - Populate related fields
Private Sub CustomerID_AfterUpdate()
CompanyID.Value = CustomerID.Column(1)
FullName.Value = CustomerID.Column(2)
Email.Value = CustomerID.Column(3)
Address.Value = CustomerID.Column(4)
AddressExt.Value = CustomerID.Column(5)
JoinMailingList.Value = CustomerID.Column(6)
Billable.Value = CustomerID.Column(7)
PreferedCustomer.Value = CustomerID.Column(8)
CreditLine.Value = CustomerID.Column(9)
CurrentAccountBallance.Value = CustomerID.Column(10)
OKToEmailDocuments.Value = CustomerID.Column(11)
Active.Value = CustomerID.Column(12)
End Sub
'End Section

What do you guys think of doing it this way?

 

[RuralGuy]

If you are duplicating the data in more than one table then it is not a good idea. Have you tried the join idea I suggested? It is easy to do and simplifies things greatly.

 

[HiTechCoach]

Thanks for the adise guys, I really do appreciate the free help!

I have it working right now using the following code:

'CustomerID After update - Populate related fields
Private Sub CustomerID_AfterUpdate()
CompanyID.Value = CustomerID.Column(1)
FullName.Value = CustomerID.Column(2)
Email.Value = CustomerID.Column(3)
Address.Value = CustomerID.Column(4)
AddressExt.Value = CustomerID.Column(5)
JoinMailingList.Value = CustomerID.Column(6)
Billable.Value = CustomerID.Column(7)
PreferedCustomer.Value = CustomerID.Column(8)
CreditLine.Value = CustomerID.Column(9)
CurrentAccountBallance.Value = CustomerID.Column(10)
OKToEmailDocuments.Value = CustomerID.Column(11)
Active.Value = CustomerID.Column(12)
End Sub
'End Section

What do you guys think of doing it this way?

I agree with the RuralGuy that you may be storing data that you should not store int he work order.


If you are really needing to store the Custom info with the Work Order sot hat it never changes then you could use your method. Some of the fields look like they should NOT be store again in the Work Order. This is what the RuralGuy is talking about not storing duplicate data..

I would think you would want something more like:

CustomerID After update - Populate related fields
Private Sub CustomerID_AfterUpdate()
CompanyID.Value = CustomerID.Column(1)
FullName.Value = CustomerID.Column(2)
Email.Value = CustomerID.Column(3)
Address.Value = CustomerID.Column(4)
AddressExt.Value = CustomerID.Column(5)
End Sub
'End Section

You may still what o just display the following, but not store int he Work Order table.

These probably should NOT be stored.

' remove these!  
JoinMailingList.Value = CustomerID.Column(6)
Billable.Value = CustomerID.Column(7)
PreferedCustomer.Value = CustomerID.Column(8)
CreditLine.Value = CustomerID.Column(9)
CurrentAccountBallance.Value = CustomerID.Column(10)
OKToEmailDocuments.Value = CustomerID.Column(11)
Active.Value = CustomerID.Column(12)

 

[cbrace09]

Thanks for all the advice guys, I really appreciate it!

I am currently using the code I posted originally but I have left the combo boxes unbound.

The purpose of displaying this information on the work order form is simply for a quick reference. This way when a user enteres a work order, they can verify they have selected the right customer from the customer list because they can see the name, address, etc.

The documents created from the work order form will acutally pull the customer information from the customers table.

Again, thanks for all the help!

 

[HiTechCoach]

Thanks for all the advice guys, I really appreciate it!

I am currently using the code I posted originally but I have left the combo boxes unbound.

The purpose of displaying this information on the work order form is simply for a quick reference. This way when a user enteres a work order, they can verify they have selected the right customer from the customer list because they can see the name, address, etc.

The documents created from the work order form will acutally pull the customer information from the customers table.

Again, thanks for all the help!

If all you wanted to do was display the data, then I would have used a sub form. This way you would not need any VBA code!