Syntax Error, no sense... Please Help

  • Thread starter Thread starter Gina Whipp
  • Start date Start date
G

Gina Whipp

Hi All,

Anyone can tell me why I am getting a 'Syntax" error on this line?
ContactID is a numeric field.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
 
Is cpContactID a numeric field or a text field? If it's text, you need to
use

"cpContactID='" & Me![txtContactID] & "'"

or

"cpContactID=" & Chr$(34) & Me![txtContactID] & Chr$(34)
 
No End IF. For readablity purposes I like to put my lines of code on
separate lines and indent a little so it is easier to match things up,
like IF with End IF. Just a suggestion.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then
Exit Sub
End IF

Hope that helps!
 
It is a numeric field.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II


Douglas J. Steele said:
Is cpContactID a numeric field or a text field? If it's text, you need to
use

"cpContactID='" & Me![txtContactID] & "'"

or

"cpContactID=" & Chr$(34) & Me![txtContactID] & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Gina Whipp said:
Hi All,

Anyone can tell me why I am getting a 'Syntax" error on this line?
ContactID is a numeric field.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
Click to expand...
Click to expand...
 
Here's my entire code, when I try to put End If, which I never did before
for this type of code, it won't compile saying the is no 'Block If'


If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub
'Author not me I forgot who
With Me.RecordsetClone
.MoveLast
Me.txtPage = Me.CurrentRecord & " of " & .RecordCount
End With


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II


Jeff L said:
No End IF. For readablity purposes I like to put my lines of code on
separate lines and indent a little so it is easier to match things up,
like IF with End IF. Just a suggestion.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then
Exit Sub
End IF

Hope that helps!


Gina said:
Hi All,

Anyone can tell me why I am getting a 'Syntax" error on this line?
ContactID is a numeric field.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II
Click to expand...
Click to expand...
 
Just so this makes more sense. I am trying to make it so the txtPage works
whether there is a contact or not but shortly about to try something else.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II


Jeff L said:
No End IF. For readablity purposes I like to put my lines of code on
separate lines and indent a little so it is easier to match things up,
like IF with End IF. Just a suggestion.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then
Exit Sub
End IF

Hope that helps!


Gina said:
Hi All,

Anyone can tell me why I am getting a 'Syntax" error on this line?
ContactID is a numeric field.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II
Click to expand...
Click to expand...
 
No End IF is necessary if Gina's code is a single line (which it appears to
be even though the post is wrapped).

Gina - This is in a sub and not a function, right? And the field and table
are spelled correctly?


--
Bill Mosca, MS Access MVP


Jeff L said:
No End IF. For readablity purposes I like to put my lines of code on
separate lines and indent a little so it is easier to match things up,
like IF with End IF. Just a suggestion.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then
Exit Sub
End IF

Hope that helps!


Gina said:
Hi All,

Anyone can tell me why I am getting a 'Syntax" error on this line?
ContactID is a numeric field.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II
Click to expand...
Click to expand...
 
Try running your domain aggregrate function in the Immediate Window (open
with Ctrl G) and substitute Me![txtContactID] with a valid ID number.
Something like this:

? DLookup("cpContactID", "tblContactProfile", "cpContactID=" & 4)

Does it work in this case? If not, check that you have used the exact field
and table names that you indicated.

Jeff: In answer to your statement, you do not need to use End If if the
entire IF statement is on one line of code. However, I like your style
better. It's more readable in my opinion.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Bill,

Thanks, my code is on one line! (Was wondering why it worked all these
years)

Anyway, found soultion, using:

DCount("cpContactID","tblContactProfile")>0

works like a charm!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II


Bill Mosca said:
No End IF is necessary if Gina's code is a single line (which it appears
to be even though the post is wrapped).

Gina - This is in a sub and not a function, right? And the field and table
are spelled correctly?


--
Bill Mosca, MS Access MVP


Jeff L said:
No End IF. For readablity purposes I like to put my lines of code on
separate lines and indent a little so it is easier to match things up,
like IF with End IF. Just a suggestion.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then
Exit Sub
End IF

Hope that helps!


Gina said:
Hi All,

Anyone can tell me why I am getting a 'Syntax" error on this line?
ContactID is a numeric field.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II
Click to expand...
Click to expand...
Click to expand...
 
Tom,

Found a solution...

DCount("cpContactID","tblContactProfile")>0

works like a charm. It wasn't that the field wasn't valid, it did not yet
exist in the table I was questioning! Now I'm just applying reverse logic.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II


Tom Wickerath said:
Try running your domain aggregrate function in the Immediate Window (open
with Ctrl G) and substitute Me![txtContactID] with a valid ID number.
Something like this:

? DLookup("cpContactID", "tblContactProfile", "cpContactID=" & 4)

Does it work in this case? If not, check that you have used the exact
field
and table names that you indicated.

Jeff: In answer to your statement, you do not need to use End If if the
entire IF statement is on one line of code. However, I like your style
better. It's more readable in my opinion.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Gina Whipp said:
Hi All,

Anyone can tell me why I am getting a 'Syntax" error on this line?
ContactID is a numeric field.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II
Click to expand...
Click to expand...
 
Do it this way. It is much easier to read and less likely to have a problem.
Notice the brackets around the field names. They may not be necessary, but
they do avoid any ambiguity. In case you are not familiary with the _ in this
context, it is line continuation character. It means "this line is contiuned
on the next line. One of the good practices in coding is you should never
have to scroll vertically to see all your code.

If IsNull(DLookup("[cpContactID]", "tblContactProfile", "[cpContactID] =
" & _
Me![txtContactID])) Then
Exit Sub
End If

With Me.RecordsetClone
.MoveLast
Me.txtPage = Me.CurrentRecord & " of " & .RecordCount
End With


Gina Whipp said:
Here's my entire code, when I try to put End If, which I never did before
for this type of code, it won't compile saying the is no 'Block If'


If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub
'Author not me I forgot who
With Me.RecordsetClone
.MoveLast
Me.txtPage = Me.CurrentRecord & " of " & .RecordCount
End With


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II


Jeff L said:
No End IF. For readablity purposes I like to put my lines of code on
separate lines and indent a little so it is easier to match things up,
like IF with End IF. Just a suggestion.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then
Exit Sub
End IF

Hope that helps!


Gina said:
Hi All,

Anyone can tell me why I am getting a 'Syntax" error on this line?
ContactID is a numeric field.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II
Click to expand...
Click to expand...
Click to expand...
 
Gina

I usually use DCount("*","MyTable","MyField=" & MyControl ) because it's
faster (I think) than DLookup , but now I see there is an even more valid
reason to use it.

--
Bill Mosca, MS Access MVP


Gina Whipp said:
Bill,

Thanks, my code is on one line! (Was wondering why it worked all these
years)

Anyway, found soultion, using:

DCount("cpContactID","tblContactProfile")>0

works like a charm!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II


Bill Mosca said:
No End IF is necessary if Gina's code is a single line (which it appears
to be even though the post is wrapped).

Gina - This is in a sub and not a function, right? And the field and
table are spelled correctly?


--
Bill Mosca, MS Access MVP


Jeff L said:
No End IF. For readablity purposes I like to put my lines of code on
separate lines and indent a little so it is easier to match things up,
like IF with End IF. Just a suggestion.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then
Exit Sub
End IF

Hope that helps!


Gina Whipp wrote:
Hi All,

Anyone can tell me why I am getting a 'Syntax" error on this line?
ContactID is a numeric field.

If IsNull(DLookup("cpContactID", "tblContactProfile", "cpContactID=" &
Me![txtContactID])) Then Exit Sub

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax Error in Union Query 3
Where Clause works by itself but not together? Please Help... 3
Too few parameters... Please Help! 3
New Problem... My Loop is only getting first eMail address... Please Help! 8
Stumped - Page Numbers in Header 14
Interesting problem, maybe... 2
Question for Duane Hookom Re: Concatenate 2
Multiplie criteria DLookUp not working, please help 5

Back
Top