DLookup value in table based on value in combo box

[Jallen182]

I'm relatively new to using VB in access and i have came across an issue using the DLookup function to retrieve a value in a table based on a value in a combo box.
I am struggling to write my third argument correctly to retrieve the value in my combo box.

The closest i can get to writing this Function correctly is as seen below:

txtStaff_Member_Price.Value = DLookup("Staff_Skill_Level", "tblStaff_Skill_Level_Bonus", "Staff_Skill_Level_Bonus=" & Forms!frmCustomer_Receipt!Combo39.Column(3))

Can anyone Help?

 

[Minty]

What value do you get if you
debug.print Forms!frmCustomer_Receipt!Combo39.Column(3))

And if Combo39 is on the form you are running this from try using Me.Combo39.Column(3)

 

[Jallen182]

Hi Minty
The value returned if i run debug.Print Forms!frmCustomer_Receipt!Combo39.Column(3) is 1 as expected.
I have tried using both Me.Combo39.Column(3) and Forms!frmCustomer_Receipt!Combo39.Column(3).
Both methods result in the same run-time error '2471':
The expression you have entered as a query parameter produced this error: 'Staff_Skill_Level_Bonus'

Could the combo box be doing something to the format of the value stored, preventing the correct Staff_Skill_Level_Bonus from finding the value due to being incompatible?

 

[Minty]

Is it a number or text? If it's text use
"Staff_Skill_Level_Bonus= '" & Forms!frmCustomer_Receipt!Combo39.Column(3)) & "' "

 

[Jallen182]

It's a number

 

[Minty]

Are you certain you have the field names correct?

 

[Jallen182]

The field Names are correct as they have been copied and pasted.

I can retrieve the Value by other means, by adding another combo box to lookup the Staff_Skill_level and the Staff_Skill_Level_Bonus in tblStaff_Skill_Level_Bonus based on the value stored in Column 3 of Combo39, and then write a small lookup to retrieve the value and put it in txtStaff_Member_Price.
It may be unconventional, though it essential does the job if i am still unable run the dlookup function.

ps. hope that made sense

 

[arnelgp]

columns are zero based. meaning column(1), in code you refer it as column(0).
therefore, you refer column(3) as column(2).

xtStaff_Member_Price.Value = DLookup("Staff_Skill_Level", "tblStaff_Skill_Level_Bonus", "Staff_Skill_Level_Bonus=" & Forms!frmCustomer_Receipt!Combo39.Column(2))

 

[Jallen182]

Hi arnelgp

Its the 4th column i'm interested in retrieving the value from, but thank you all the same.

 

[Minty]

I've just noticed this
txtStaff_Member_Price.Value

If this is a text box on your form you probably need to use
Me.txtStaff_Member_Price = ...
And make sure you haven't got that txtbox bound to any control source.

Finally if you write a query that performs your lookup, using hardcoded values do you get the result you are expecting?

 

[Jallen182]

Thanks Minti, i can't believe i didn't notice that for myself, it works perfectly now.