Where do I find a "statement" definition?

[Robin Chapple]

I have a piece if code giving an error message:

"Compile error. Expected end of statement"

I would like to debug the problem myself, ( I will return here if I
fail <G>). Where do I find a definition so that I can work out what is
missing?

 

[Ken Snell [MVP]]

Statement is referring to a line of code. This error often occurs when you
have mismatched ' or " characters in the code step. Or if you have
mismatched ( and ) characters.

Essentially, the compiler is telling you that it could not parse the code
step to find the beginning and end.

 

[Al Camp]

Robin,
This error message is a "general" response to an error in your code...
usually a "syntax" type problem.
In Tools/Startup/Advanced turn on the Allow Viewing Code After Error. If
you get an error during form run, you should (but not always) see a button
on the error message that says "Debug." Clicking that will take you to the
line of code that is causing the problem.
Or, compiling your code while in a module will also hilight the problem.
Review that line of code vs. the syntax for the code your using in Help,
and you should be able to determine the cause.
hth
Al Camp

 

[Robin Chapple]

Al,

I do not have an "Advanced" option under Tools > Startup.

I am using Access 2002 SP3. I have placed a screen capture here:

http://www.rotary9790.org.au/test/alumni2.htm

Thanks,

Robin Chapple

 

[Robin Chapple]

Ken,

The line of code does not have anything like you describe:

CurrentDb.Execute "Update tblContents Set tblContents.Sponsor
="intPageNumber +1;", dbFailOnError

It was suggested in the "Reports" newsgroup.

The highlight is over "intPageNumber" when the line is red.

The reason for the line of code is to extract the last page number
from a report and put it into field "Sponsor" in table "tblContents".

Thanks,

Robin

 

[tina]

well, if you pasted the code into the post EXACTLY as it shows in your
module (including being on two lines) then the problem is that you need a
line continuation character in the code and an ampersand, as

CurrentDb.Execute "Update tblContents Set tblContents.Sponsor = " _
& intPageNumber +1, dbFailOnError

the above assumes that the Sponsor field in tblContents is a Number data
type. my guess is that the code was posted in the Reports thread all on one
line, but your newsreader broke it up into two lines.

hth

 

[Robin Chapple]

The code is on one line and gives the error message.

 

[RuralGuy]

Hi Robin,

I believe you left out the concantenation characters (&). See in line.

Ken,

The line of code does not have anything like you describe:

CurrentDb.Execute "Update tblContents Set tblContents.Sponsor
="intPageNumber +1;", dbFailOnError

s/b
CurrentDb.Execute "Update tblContents Set tblContents.Sponsor
=" & intPageNumber +1 & ";", dbFailOnError

All on one line of course.

It was suggested in the "Reports" newsgroup.

The highlight is over "intPageNumber" when the line is red.

The reason for the line of code is to extract the last page number
from a report and put it into field "Sponsor" in table "tblContents".

Thanks,

Robin

hth

 

[fredg]

Ken,

The line of code does not have anything like you describe:

CurrentDb.Execute "Update tblContents Set tblContents.Sponsor
="intPageNumber +1;", dbFailOnError

It was suggested in the "Reports" newsgroup.

The highlight is over "intPageNumber" when the line is red.

The reason for the line of code is to extract the last page number
from a report and put it into field "Sponsor" in table "tblContents".

Thanks,

Robin

This is the line of code I posted to you:
CurrentDb.Execute "Update tblContents Set tblContents.Sponsor =
intPageNumber +1;", dbFailOnError

This is the line of code you posted in this message:
CurrentDb.Execute "Update tblContents Set tblContents.Sponsor
="intPageNumber +1;", dbFailOnError

Note the extra quote in
="intPageNumber +1

The line I sent was to have been all on one line. Remove that extra
quote.

In addition, Robin, please do NOT send questions, follow ups, thank
you's, etc. to my personal email address. My newsgroup signature is
quite clear that I don't wish to take any newsgroup business out of
the newsgroup. 3 personal emails is 3 too many.

 

[tina]

okay. in the original code, just remove the semicolon ( and the double
quote mark directly after it.

or, just copy the code i posted, and paste it into your procedure, replacing
the one-line code that's erring out. my code will give you the same result
as the original code intended to.

in both cases above, i am assuming that "intPageNumber" is a variable in the
VBA module (or in another, public, module), and also that the field Sponsor
is a Number data type.

btw, the SQL statement will set the value of Sponsor to "intPageNumber + 1"
in *every record* in tblContents - the same single value. hopefully that's
what you want.

hth

 

[Robin Chapple]

This is the line of code I posted to you:
CurrentDb.Execute "Update tblContents Set tblContents.Sponsor =
intPageNumber +1;", dbFailOnError

This is the line of code you posted in this message:
CurrentDb.Execute "Update tblContents Set tblContents.Sponsor
="intPageNumber +1;", dbFailOnError

Note the extra quote in
="intPageNumber +1

The line I sent was to have been all on one line. Remove that extra
quote.

Fred,

All the code is in one line. I have removed the extra quote.

CurrentDb.Execute "Update tblContents Set tblContents.Sponsor
=intPageNumber +1;", dbFailOnError

I now get the error message:

"Runtime error '3061'. Too few parameters. Expected 1.

I am using Access 2002 SP3 and I have DAO 3.6 Library listed in
References

Thanks,

Robin

 

[fredg]

Fred,

All the code is in one line. I have removed the extra quote.

CurrentDb.Execute "Update tblContents Set tblContents.Sponsor
=intPageNumber +1;", dbFailOnError

I now get the error message:

"Runtime error '3061'. Too few parameters. Expected 1.

I am using Access 2002 SP3 and I have DAO 3.6 Library listed in
References

Thanks,

Robin

1) Do you have a table named "tblContents"?
2) Do you have a field in the table named "Sponsor"?
3) What happens if you change the above statement (all on one line)
to:

CurrentDb.Execute "Update tblContents Set tblContents.Sponsor
= " & intPageNumber +1 & ";", dbFailOnError

 

[Robin Chapple]

1) Do you have a table named "tblContents"?
2) Do you have a field in the table named "Sponsor"?
3) What happens if you change the above statement (all on one line)
to:

CurrentDb.Execute "Update tblContents Set tblContents.Sponsor
= " & intPageNumber +1 & ";", dbFailOnError

I confirm that I have a "tblContents" with a filed "sponsor".

I have pasted the code into one line and I get a "compile Error" with
the selection on the semi colon in & ";",

Thanks,

Robin

 

[fredg]

I confirm that I have a "tblContents" with a filed "sponsor".

I have pasted the code into one line and I get a "compile Error" with
the selection on the semi colon in & ";",

Thanks,

Robin

Please always copy and paste any code that doesn't work into your
post. Sometimes there is a difference between what we think we wrote
and what is actually written. This is a human fact of life, and we all
do it.

1) Did you declare
intPageCount as Integer
in the Declaration's section of the code?

2) Do you give intPageCount a value in the Report Footer Format event?
inpPageCount = [Page]

3) Is Sponsor a Number datatype, Integer field size?
It should be.

4) Below is code copied exactly from a sample report I used, and it
works. The CurrentDb.Execute statement should all be on one line.

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As
Integer)
intPageCount = [Page]
CurrentDb.Execute "Update tblcontents Set tblContents.Sponsor = " &
[intPageCount] + 1 & ";", dbFailOnError
End Sub

Sometimes email readers split a line incorrectly and add > symbols.
Here is how the ending of the statement should appear:
tblContents.Sponsor = " & [intPageCount] + 1 & ";"

 

[Robin Chapple]

Fred,

Thanks for your patience.

i) I had not made the declaration;
ii) I had not given intPageCount a value in the Report Footer Format
event

Now all is well.

Very many thanks.

Robin