Reference a Table for Dlookup???

[Don V]

What the hell am I doing wrong in this Dlookup.
I have a very basic understanding of this stuff and I'm stumped on this.
I'm guessing I have some stupid syntax error.
Would greatly appeciate any help. I'm dizzy after a week of puzzling at
this.

Me![txtUnitPrice1].Value = DLookup("[Price]",
"Application.CurrentData.Alltables[tblWork]", "[Type]= Me![cboWork1].Text")

I'm getting an error saying the that Jet Data Engine can not find the
table.
I'll try to explain what I'm attempting and case anyone can give me a more
efficient way.


I'm trying to access a table(Work) with 2 fields Type and Price.
I'm trying to reach it from within a form that is not linked to the the
Work table(Could be the problem)
When I select a value from a Combo box(cboWork1)which is populated with a
value list pulled from the Type field in the Work table.
I want the Price field value looked up for the record that matches the Type
field selected from cboWork1 and entered into a text box(txtUnitPrice1).

Don

 

[Joe Fallon]

Me![txtUnitPrice1] = DLookup("Price","tblWork", "Type= '" & Me![cboWork1] &
"'")

If you hard code a value for cboWork it would look like this: (note the
single quotes around Text values)
(Numeric values do not need them.)
Me![txtUnitPrice1] = DLookup("Price","tblWork", "Type= 'SomeWork'")

 

[John Vinson]

Me![txtUnitPrice1].Value = DLookup("[Price]",
"Application.CurrentData.Alltables[tblWork]", "[Type]= Me![cboWork1].Text")

I'm getting an error saying the that Jet Data Engine can not find the
table.
I'll try to explain what I'm attempting and case anyone can give me a more
efficient way.


I'm trying to access a table(Work) with 2 fields Type and Price.

The second argument to DLookUp should just be the Name of the table.
Is it named tblWork or Work? Is Type a numeric field or a text field?

I'd suggest that if the table is named Work and type is a number, just
use

DLookUp("[Price]", "[Work]", "[Type] = " & Me!cboWork1)

You're making it much harder than it needs to be, but after a week of
struggling I can understand why!

 

[Don V]

Me![txtUnitPrice1] = DLookup("Price","tblWork", "Type= '" & Me![cboWork1] &
"'")

If you hard code a value for cboWork it would look like this: (note the
single quotes around Text values)
(Numeric values do not need them.)
Me![txtUnitPrice1] = DLookup("Price","tblWork", "Type= 'SomeWork'")

That did the trick.
Thanks for the quick and accurate reply.
Eternally grateful.
BTW
Could you point me to some references for syntax in VBA.
I don't have a clue what internal "" or the & do.
I have enough basic understanding to know what I want to do but usually get
hung up on some syntax error. The help file seems useless.
Thanks again.
Don

 

[Joe Fallon]

Glad to hear I got it right!
(Public typos can be embarrassing.)

"The help file seems useless."
Well known problem.
I learned Access from the A2.0 and A97 Help files.
Most developers think A97 was the last good version.
A2003 should be better than 2000 and 2002 which weren't very good.


Could you point me to some references for syntax in VBA.
I don't have a clue what internal "" or the & do.

They are just basic tricks used when doing string concatenation.
You start with a goal and then work backwards to iunsert your variables.
String variables must be enclosed in single quotes and numeric variables do
not.

e.g.
Goal: (note it is a single string.)
"Type= 'xyz' And ID=123"

Variables come from a form.
Me![txtType] and Me![txtID]

So you want to replace the hard coded values with the variables and still
end up with the Goal string when they are evaluated at run time.

Result:
"Type= '" & Me![txtType] & "' And ID=" & Me![txtID]

The & character joins strings together.
So I have a String, joined to a Variable, joined to another string, joined
to another variable.

Hope that makes sense now.
--
Joe Fallon
Access MVP

Me![txtUnitPrice1] = DLookup("Price","tblWork", "Type= '" & Me![cboWork1] &
"'")

If you hard code a value for cboWork it would look like this: (note the
single quotes around Text values)
(Numeric values do not need them.)
Me![txtUnitPrice1] = DLookup("Price","tblWork", "Type= 'SomeWork'")

That did the trick.
Thanks for the quick and accurate reply.
Eternally grateful.
BTW
Could you point me to some references for syntax in VBA.
I don't have a clue what internal "" or the & do.
I have enough basic understanding to know what I want to do but usually get
hung up on some syntax error. The help file seems useless.
Thanks again.
Don