Using a Combo Box to update more than 1 field

[stinej]

Hi, any help is appreciated.

I have a form that won't update properly no matter what I try.

I'm using a combobox to update one field called Service on my Clients form (that pulls information from a Services table) this works fine as it selects the service then updates the Clients record.

The problem I'm having is that I have another field on my Clients form (Rate) that I want updated as well when I select the appropriate service. Rate is also stored on my Services table and I want it to update the Rate field on my Clients form once the Service has been selected.

I've tried to update the Rate fields control source on my Clients form with a query and code and for some reason nothing seems to work.

 

[WayneRyan]

j,

Since it is stored in another table (linked by your service), they you really
should not replicate it. It's always available by joining that table in you
query.

That said, the combo can return two columns.

Select Service, Rate
From   YourTable
Order by Service

You can use the AfterUpdate event of your combobox to:

Me.Rate = Me.cboService.Column(1)

You don't even have to display the Rate on the combo, it's width can
be 0 (1";0").

Wayne

 

[stinej]

Thanks Wayne.

I have the combo box working properly, but the after update won't work?

I keep getting an error.

My code is as follows

Me.ClientRate = Me.Service.Column(2)

The field I want to update is ClientRate, the Combo box is named Service, and there are 3 columns in my lookup (ID, Service, RatePerUse)

Any ideas? Thanks.

 

[WayneRyan]

j,

That is the line of code? What error do you get?

Is the combo's name "service"? It looks like you had the wizard build it
for you and it's name is probably something else.

Wayne

 

[stinej]

Wayne,

I did use the wizard to create the combo box, but it's name under the "Other" tab in the properties box is "Service"

The error is "The expression After Update you entered as the event property setting produced the following error: Invalid outside procedure"

Thanks,
J

 

[Pat Hartman]

You don't just place the expression in the properties box next to the event name, you need to press the build button to build a sub procedure. Place the line of code in the sub procedure.

 

[stinej]

Tnanks Pat, but it is in my subpredure for AfterUpdate on the Service object, which is th combobox... Still not working.

 

[Pat Hartman]

Please post the entire sub.

 

[stinej]

Private Sub Service_AfterUpdate()
Me.ClientRate = Me.Service.Column(2)
End Sub

Thanks!