Help updating a control in a subform automatically

[froglog]

I have created a MS access DB to track transport costs for tax purposes.

I enter data into a main form [F_car] that tracks miles traveled by date with a subform [F_tolls subform]. The subform has fields for the date traveled [date], toll location [toll site] and the cost of the toll [cost]. The date on the subform is copied from the main form. [toll site] is a combo box that draws its values from [toll name] in the [key- Toll codes] table, which also has the fields -Toll ID, Toll text, and toll cost).

My question is regarding the [cost] control on the subform. I would like the cost to be filled in automatically from the [key- Toll codes] table [toll cost] field whenever a toll location is chosen from the combo box.

Being a beginner user of Access, I looked to the Internet for help and have been unsuccessful in trying the following:

-using the following Dlookup function as the control source for the cost text box

=DLookUp("[Toll cost]","Key-Toll codes","[Toll name] = ' " & [Tolls site] & " ' ")

- using the following after update event procedure with the [toll site] combo box

Private Sub Tolls_site_Afte rUpdate()
Me![Cost] = DLookup("[Toll cost]", "Key-Toll codes", "[Toll name] = ' " & [Tolls site] & " ' ")
End Sub

Can someone suggest the right way to do this?

[PEB]

Hi,

Use

Me![Tolls site]
or
Forms![Your form]![Tolls site]

And a question?

What is the name of your combo box? [Tolls site] ? Or other?

:)

[froglog]

Hi,

Use

Me![Tolls site]
or
Forms![Your form]![Tolls site]

And a question?

What is the name of your combo box? [Tolls site] ? Or other?

:)

HI,

Thanks for the reply.

[Tolls site] is the name of my combo box

I used the following event procedure code with the [toll site] combo box after substituting
Me![Tolls site] in place of [Tolls site] in criteria portion of the Dlookup function:


Private Sub Tolls_site_Afte rUpdate()
Me![Cost] = DLookup("[Toll cost]", "Key-Toll codes", "[Toll name] = ' " & Me![Tolls site] & " ' ")
End Sub

When I created a new record as a test and selected a toll site, the [cost] text box went from $0.00 to a blank box.

However, if I modify the criteria to reflect a specific toll site (2m), the cost value for that toll site pops into [cost] each time I choose a site from the combo box.

Private Sub Tolls_site_Afte rUpdate()
Me![Cost] = DLookup("[Toll cost]", "Key-Toll codes", "[Toll name] = '2m ' ")
End Sub


The row source for the [toll site] combo box is SELECT [Key-Toll codes].[Toll ID], [Key-Toll codes].[Toll name] FROM [Key-Toll codes] ORDER BY [Key-Toll codes].[Toll name]; and column 1 is bound.

Why does the Dlookup function recognise a specifc toll site when I use it in the criteria, but not the value that is chosen from the combo box? Is there something in the properties of the combo box or underlying [Key- Toll codes] table that I may need to check/change?

[PEB]

Why do not change the columns in your combo?

Do the first column this that you need and second the id!

What is the text in your combo that is shown?

:)

[froglog]

Why do not change the columns in your combo?

Do the first column this that you need and second the id!

What is the text in your combo that is shown?

:)

HI,

Switching order of the columns made no difference, but I solved the problem anyway.

For future reference, my combo box contained more than one column and only when I changed the criteria in the Dlookup function to refer to the column that was actually bound (which was different from the one I saw displayed in the combo box) did my code function as expected.