Syntax error on Dlookup Function

[Pamela]

I've found in an Access help book some sample code for the DLookup function
and now I'm trying it out but getting a syntax error. The exact error is:
Run-time error '3075': Syntax error in string expression '[InspLocID]='2'
The is an unbound field on my form. The "answer" that the error is showing
of "2" is the correct InspLocID that I'm looking up but why is there an error
on it? Here is my exact code:
Private Sub TestInspLoc_Enter()
Dim varX As Variant
If [cboInspLoc] = "Body Shop" Then
varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]='" &
Me.InspLocID)
End If
End Sub

One other note, is that ultimately I would like to then (somehow) display
(in another field if necessary) the RepairerName - can I build a Dlookup on
another Dlookup? It would probably be easier but there is a Many-to-Many
relationship between my InspLoc table and my Repairer table so I have a
junction table joining them... Thanks so much, in advance, for any help on
this!!

 

[fredg]

I've found in an Access help book some sample code for the DLookup function
and now I'm trying it out but getting a syntax error. The exact error is:
Run-time error '3075': Syntax error in string expression '[InspLocID]='2'
The is an unbound field on my form. The "answer" that the error is showing
of "2" is the correct InspLocID that I'm looking up but why is there an error
on it? Here is my exact code:
Private Sub TestInspLoc_Enter()
Dim varX As Variant
If [cboInspLoc] = "Body Shop" Then
varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]='" &
Me.InspLocID)
End If
End Sub

Your single quote in the Where Clause is incorrect.
Since [InspLocID] appears to be a Number datatype, use:

varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]=" &
Me.InspLocID)

If [InspLocID] was Text datatype then you would use:

varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]='" &
Me.InspLocID & "'")

 

[Pamela]

Thanks so much for that, Fred, the error has stopped but the Dlookup doesn't
seem to be returning any value - my field stays empty...any other suggestions?

I've found in an Access help book some sample code for the DLookup function
and now I'm trying it out but getting a syntax error. The exact error is:
Run-time error '3075': Syntax error in string expression '[InspLocID]='2'
The is an unbound field on my form. The "answer" that the error is showing
of "2" is the correct InspLocID that I'm looking up but why is there an error
on it? Here is my exact code:
Private Sub TestInspLoc_Enter()
Dim varX As Variant
If [cboInspLoc] = "Body Shop" Then
varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]='" &
Me.InspLocID)
End If
End Sub

Your single quote in the Where Clause is incorrect.
Since [InspLocID] appears to be a Number datatype, use:

varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]=" &
Me.InspLocID)

If [InspLocID] was Text datatype then you would use:

varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]='" &
Me.InspLocID & "'")

One other note, is that ultimately I would like to then (somehow) display
(in another field if necessary) the RepairerName - can I build a Dlookup on
another Dlookup? It would probably be easier but there is a Many-to-Many
relationship between my InspLoc table and my Repairer table so I have a
junction table joining them... Thanks so much, in advance, for any help on
this!!

 

[Linq Adams via AccessMonster.com]

You need to check the spelling on all of your objects. For instance, is the
table name actually

jtblInspRepairer

with a "j" at the beginning?

 

[Pamela]

You know, I double checked them all...and they all match up -- yes, it is
jtblinspRepairer which I have so named because it is a junction table and it
reminds me so when I look at the name... If DLookup isn't going to be the
right method for doing this, can you give me any other alternatives? I like
your idea of "more than one way to skin a cat" Typically, I've also
found that you get an error if one of the objects is unknown...perhaps not in
this kind of function?? Thanks so much for your continued assistance!!!

 

[Steve Sanford]

You could verify a record exists....

Create a new query, add the table "jtblinspRepairer", add the two fields
[RepairerID] and [InspLocID] . In the criteria row for the field
[InspLocID],enter a 2.

Execute the query. Are the results what you expected??

I don't understand how the PK for the location "Body Shop" gets into the
control "Me.InspLocID".

For [cboInspLoc], I would have a row source something like "SELECT
InspLocID, InspLocDesc FROM tblInspLoc ORDER By InspLocDesc" . The bound
field would be 1 and the column count would be 2. Column widths set to 0", 1"

Then your sub would look something like:

'-------
Private Sub TestInspLoc_Enter()
Dim varX As Variant
varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]='" &
Me.cboInspLoc)

End Sub
'-------

If the data for tblInspLoc looked like

InspLocID (PK) InspLocDesc
1 Paint Shop
2 Body Shop


selecting either desc would give you the PK.....no extra code. If you then
added

3 Tire Shop

you wouldn't have to edit your code.


Another way (to skin the cat) might be to use a recordset


(BTW, not to be picky, but it is important to understand that tables have
fields, forms have controls.)

HTH

 

[Pamela]

Hi Steve!

Thanks so much for your response. I took your advise and created the query
and it did give me the correct results so I don't understand why the Dlookup
isn't matching it.

After reading your post I think that perhaps I didn't explain it well enough
and I apologize for that. Control cboInspLoc has just a value list that will
not need to be updated. Again, I am very new to Access and DLookup but the
InspLocID is supposed to be the WHERE clause (I think that's what they call
it). When the user enters my unbound control, I want Access to see if "Body
Shop" is selected from the value list of cboInspLoc, and if it is, return the
value of the RepairerID field in jtblInspRepairer where the InspLocID field
in that same table = the InspLocID control in my form. So the value of
InspLocID will not change - it is just the reference for looking up the
RepairerID.

Thanks so much, in advance, for any other help you can give me...*smile*

You could verify a record exists....

Create a new query, add the table "jtblinspRepairer", add the two fields
[RepairerID] and [InspLocID] . In the criteria row for the field
[InspLocID],enter a 2.

Execute the query. Are the results what you expected??

I don't understand how the PK for the location "Body Shop" gets into the
control "Me.InspLocID".

For [cboInspLoc], I would have a row source something like "SELECT
InspLocID, InspLocDesc FROM tblInspLoc ORDER By InspLocDesc" . The bound
field would be 1 and the column count would be 2. Column widths set to 0", 1"

Then your sub would look something like:

'-------
Private Sub TestInspLoc_Enter()
Dim varX As Variant
varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]='" &
Me.cboInspLoc)

End Sub
'-------

If the data for tblInspLoc looked like

InspLocID (PK) InspLocDesc
1 Paint Shop
2 Body Shop


selecting either desc would give you the PK.....no extra code. If you then
added

3 Tire Shop

you wouldn't have to edit your code.


Another way (to skin the cat) might be to use a recordset


(BTW, not to be picky, but it is important to understand that tables have
fields, forms have controls.)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

You know, I double checked them all...and they all match up -- yes, it is
jtblinspRepairer which I have so named because it is a junction table and it
reminds me so when I look at the name... If DLookup isn't going to be the
right method for doing this, can you give me any other alternatives? I like
your idea of "more than one way to skin a cat" Typically, I've also
found that you get an error if one of the objects is unknown...perhaps not in
this kind of function?? Thanks so much for your continued assistance!!!

 

[Steve Sanford]

OK, I need to slow down and leard how to read <g>. I was focused on the name
of the control instead of *reading* the code. I use a suffix of 'ID' in a
field name to indicate it is a long integer (usually a PK). So I didn't read
as closely as I should have.

The reason the DLookup() wasn't returning a value is that you are missing a
closing single quote in the WHERE clause.

Try this:
Private Sub TestInspLoc_Enter()
Dim varX As Variant
varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]='" &
Me.cboInspLoc & "'")
End Sub


Expanded, it is Me.cboInspLoc & " ' " )


Also, be aware that if there is more than 1 record, DLookup() returns the
first record it finds. That means you cannot rely on the same record being
returned every time you call the function.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Steve!

Thanks so much for your response. I took your advise and created the query
and it did give me the correct results so I don't understand why the Dlookup
isn't matching it.

After reading your post I think that perhaps I didn't explain it well enough
and I apologize for that. Control cboInspLoc has just a value list that will
not need to be updated. Again, I am very new to Access and DLookup but the
InspLocID is supposed to be the WHERE clause (I think that's what they call
it). When the user enters my unbound control, I want Access to see if "Body
Shop" is selected from the value list of cboInspLoc, and if it is, return the
value of the RepairerID field in jtblInspRepairer where the InspLocID field
in that same table = the InspLocID control in my form. So the value of
InspLocID will not change - it is just the reference for looking up the
RepairerID.

Thanks so much, in advance, for any other help you can give me...*smile*

You could verify a record exists....

Create a new query, add the table "jtblinspRepairer", add the two fields
[RepairerID] and [InspLocID] . In the criteria row for the field
[InspLocID],enter a 2.

Execute the query. Are the results what you expected??

I don't understand how the PK for the location "Body Shop" gets into the
control "Me.InspLocID".

For [cboInspLoc], I would have a row source something like "SELECT
InspLocID, InspLocDesc FROM tblInspLoc ORDER By InspLocDesc" . The bound
field would be 1 and the column count would be 2. Column widths set to 0", 1"

Then your sub would look something like:

'-------
Private Sub TestInspLoc_Enter()
Dim varX As Variant
varX = DLookup("[RepairerID]", "jtblInspRepairer", "[InspLocID]='" &
Me.cboInspLoc)

End Sub
'-------

If the data for tblInspLoc looked like

InspLocID (PK) InspLocDesc
1 Paint Shop
2 Body Shop


selecting either desc would give you the PK.....no extra code. If you then
added

3 Tire Shop

you wouldn't have to edit your code.


Another way (to skin the cat) might be to use a recordset


(BTW, not to be picky, but it is important to understand that tables have
fields, forms have controls.)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

You know, I double checked them all...and they all match up -- yes, it is
jtblinspRepairer which I have so named because it is a junction table and it
reminds me so when I look at the name... If DLookup isn't going to be the
right method for doing this, can you give me any other alternatives? I like
your idea of "more than one way to skin a cat" Typically, I've also
found that you get an error if one of the objects is unknown...perhaps not in
this kind of function?? Thanks so much for your continued assistance!!!

:

You need to check the spelling on all of your objects. For instance, is the
table name actually

jtblInspRepairer

with a "j" at the beginning?