event procedure

[Jerrry D]

Hi
When I select a category in the category combobox from the table named
categories I also want to select the corresponding cost and place the cost in
the cost textbox. It works fine to a point that only the first item in the
cost field is selected regardless of which item I select in the category
field. The event procedure in the combobox is

Private Sub CategoryName_AfterUpdate()

Dim strFilter As String

' Look up product's Cost and assign it to Cost control.
Me!Cost = DLookup("Cost", "Categories", strFilter)

Exit_CategoryName_AfterUpdate:
Exit Sub

End Sub

The row source in the combobox is SELECT Categories.CategoryName FROM
Categories;

How do I retrieve the cost for the categoryname that I select?

Thanks

 

[MikeJohnB]

For a start, you have dimensioned the variable strFilter but not asigned any
value to it so your Dlookup will only return the first record irresspective
of the Category record selected. I suggest you consider the criterial for the
Dlookup.

Place a break in your code and see what strFilter contains when you get to
the Dlookup statement???

HTH and goes towards you finding a solution

Mike B


--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all,
you have shouted at and blamed the innocent,
and asked everyone you know,

READ THE INSTRUCTION MANUAL.

 

[Jerrry D]

Thanks Mike. However, I do not have sufficient knowledge of the program to
fully understand your statement. Is it possible you could give an example of
code on how I can correct the filter?

 

[MikeJohnB]

I will set up a trial database based on your field names and let you know the
code. Please keep an eye on this thread for my reply.
--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all,
you have shouted at and blamed the innocent,
and asked everyone you know,

READ THE INSTRUCTION MANUAL.

 

[Jerrry D]

Thanks Mike, I will do that.

Jerry

I will set up a trial database based on your field names and let you know the
code. Please keep an eye on this thread for my reply.
--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all,
you have shouted at and blamed the innocent,
and asked everyone you know,

READ THE INSTRUCTION MANUAL.

 

[MikeJohnB]

Hi Jerry

Right here we go

I have a form which is unbound with two controls on it.
Control Combo0
And a unbound textbox named Costs

I made the Combo0 using the wizard selecting the option, I want the Combo
box to look up value in a table or query and pointed to the table Categories
on the next option.

I have a table called Categories with the following fields

Category
Cost

I Populated the fields with test data so That I could prove the VBA

In the combo0 box on change event (Right click the combo box with the form
in design mode and select properties) I have typed the following

Private Sub Combo0_Change()
Dim StrFilt As String
Dim MyVar As Variant

StrFilt = Me.Combo0

MyVar = DLookup("[Cost]", "[Categories]", "[Category]='" & [StrFilt] & "'")

Me.Costs = MyVar

End Sub

I have split the above with a line divider because of word wrapping and for
your clarity.

Now,

Either by selecting the combo box list in the drop down list or by typing in
the combo box the category, the unbound text box is now populated with the
cost associated with the category.

One or two questions though.

In the table Categories are there the fileds Category and Cost?

If so I don't know why you are going this way round to solve your problem?
If they are in the same table. Make a form which is bound to the table
Categoies.
Use the form Wizard if you need to and select the table Catecories as the
source table.

Now with the form in design mode, select a combo box from the tool box and
drag it to your form. Now you have more options. A third option for the combo
box wizard which says, Find a record on my form based on the value I selected
in my combo box.

Drop the cost control onto the form (Select View from the top menu bar in
Access as View\field list will show you all the controls available for the
form, there should be a cost one. Drag this onto your form again in design
mode. Now run the form and you have a combo box in which you can select
category and the control Cost will show the associated cost without any
coding.

Please let me know if your are struggling and perhaps we can get together
again for more assistance. Perhaps even shoot me your DB and I will take a
look.

Hope this helps, let me know?

--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all,
you have shouted at and blamed the innocent,
and asked everyone you know,

READ THE INSTRUCTION MANUAL.

 

[Jerrry D]

Thanks Mike It's now working the way I want it.

Hi Jerry

Right here we go

I have a form which is unbound with two controls on it.
Control Combo0
And a unbound textbox named Costs

I made the Combo0 using the wizard selecting the option, I want the Combo
box to look up value in a table or query and pointed to the table Categories
on the next option.

I have a table called Categories with the following fields

Category
Cost

I Populated the fields with test data so That I could prove the VBA

In the combo0 box on change event (Right click the combo box with the form
in design mode and select properties) I have typed the following

Private Sub Combo0_Change()
Dim StrFilt As String
Dim MyVar As Variant

StrFilt = Me.Combo0

MyVar = DLookup("[Cost]", "[Categories]", "[Category]='" & [StrFilt] & "'")

Me.Costs = MyVar

End Sub

I have split the above with a line divider because of word wrapping and for
your clarity.

Now,

Either by selecting the combo box list in the drop down list or by typing in
the combo box the category, the unbound text box is now populated with the
cost associated with the category.

One or two questions though.

In the table Categories are there the fileds Category and Cost?

If so I don't know why you are going this way round to solve your problem?
If they are in the same table. Make a form which is bound to the table
Categoies.
Use the form Wizard if you need to and select the table Catecories as the
source table.

Now with the form in design mode, select a combo box from the tool box and
drag it to your form. Now you have more options. A third option for the combo
box wizard which says, Find a record on my form based on the value I selected
in my combo box.

Drop the cost control onto the form (Select View from the top menu bar in
Access as View\field list will show you all the controls available for the
form, there should be a cost one. Drag this onto your form again in design
mode. Now run the form and you have a combo box in which you can select
category and the control Cost will show the associated cost without any
coding.

Please let me know if your are struggling and perhaps we can get together
again for more assistance. Perhaps even shoot me your DB and I will take a
look.

Hope this helps, let me know?

--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all,
you have shouted at and blamed the innocent,
and asked everyone you know,

READ THE INSTRUCTION MANUAL.

Thanks Mike, I will do that.

Jerry