Expected end of statement

[TonyWilliams via AccessMonster.com]

Could someone tell me why I'm getting an Expected end of statement with this
code:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click
Dim dtmFirstDay As Date
Dim dtmLastDay As Date
Dim strtxtcompany As String
Dim strdeal As Integer
'set strtxtsurname equal to the selected value before closing the form,
otherwise it will give us an error
strtxtcompany = Me.txtcompany.Value
strdate = Me.txtmontha
strdeal = Me.txtdealnbr
dtmFirstDay = DateSerial(Year(Me.txtsearch2), Month(Me.txtsearch2), 1)
dtmLastDay = DateSerial(Year(Me.txtsearch2), Month(Me.txtsearch2) + 1, 0)
Forms!frmMain!SubForm1.SourceObject = "frmhighvaluedeals"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearchHV]
WHERE [txtcompany] = '" & strtxtcompany & "'" & _
"AND ([txtmonth] Between " & _
Format(dtmFirstDay, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(dtmLastDay, "\#yyyy\-mm\-dd\#") & ") & _
" AND [txtdealnbr]=strdeal"

Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

The line that is highlighted is this one
" AND [txtdealnbr]=strdeal"

Thanks in advance
Tony

 

[TonyWilliams via AccessMonster.com]

I've managed to get rid of the message using this amended code:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click
Dim dtmFirstDay As Date
Dim dtmLastDay As Date
Dim strtxtcompany As String
Dim strdeal As Integer
'set strtxtsurname equal to the selected value before closing the form,
otherwise it will give us an error
strtxtcompany = Me.txtcompany.Value
strdate = Me.txtmontha
strdeal = Me.txtdealnbr
dtmFirstDay = DateSerial(Year(Me.txtsearch2), Month(Me.txtsearch2), 1)
dtmLastDay = DateSerial(Year(Me.txtsearch2), Month(Me.txtsearch2) + 1, 0)
Forms!frmMain!SubForm1.SourceObject = "frmhighvaluedeals"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearchHV]
WHERE [txtcompany] = '" & strtxtcompany & "'" & _
"AND ([txtmonth] Between " & _
Format(dtmFirstDay, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(dtmLastDay, "\#yyyy\-mm\-dd\#") & ") AND [txtdealnbr]=
strdeal "

Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

However when I press the command button to run the code I get a prompt box
for strdeal.

Where am I going wrong?
Thanks
Tony

Could someone tell me why I'm getting an Expected end of statement with this
code:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click
Dim dtmFirstDay As Date
Dim dtmLastDay As Date
Dim strtxtcompany As String
Dim strdeal As Integer
'set strtxtsurname equal to the selected value before closing the form,
otherwise it will give us an error
strtxtcompany = Me.txtcompany.Value
strdate = Me.txtmontha
strdeal = Me.txtdealnbr
dtmFirstDay = DateSerial(Year(Me.txtsearch2), Month(Me.txtsearch2), 1)
dtmLastDay = DateSerial(Year(Me.txtsearch2), Month(Me.txtsearch2) + 1, 0)
Forms!frmMain!SubForm1.SourceObject = "frmhighvaluedeals"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearchHV]
WHERE [txtcompany] = '" & strtxtcompany & "'" & _
"AND ([txtmonth] Between " & _
Format(dtmFirstDay, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(dtmLastDay, "\#yyyy\-mm\-dd\#") & ") & _
" AND [txtdealnbr]=strdeal"

Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

The line that is highlighted is this one
" AND [txtdealnbr]=strdeal"

Thanks in advance
Tony

 

[Keith Wilby]

The line that is highlighted is this one
" AND [txtdealnbr]=strdeal"

Try

" AND [txtdealnbr] = '" & strdeal & "'"

Keith.
www.keithwilby.co.uk

 

[TonyWilliams via AccessMonster.com]

Keith this is what I've got now:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click
Dim dtmFirstDay As Date
Dim dtmLastDay As Date
Dim strtxtcompany As String
Dim strdeal As Integer
'set strtxtsurname equal to the selected value before closing the form,
otherwise it will give us an error
strtxtcompany = Me.txtcompany.Value
strdate = Me.txtmontha
strdeal = Me.txtdealnbr
dtmFirstDay = DateSerial(Year(Me.txtsearch2), Month(Me.txtsearch2), 1)
dtmLastDay = DateSerial(Year(Me.txtsearch2), Month(Me.txtsearch2) + 1, 0)
Forms!frmMain!SubForm1.SourceObject = "frmhighvaluedeals"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearchHV]
WHERE [txtcompany] = '" & strtxtcompany & "'" & _
"AND ([txtmonth] Between " & _
Format(dtmFirstDay, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(dtmLastDay, "\#yyyy\-mm\-dd\#") & ") AND [txtdealnbr] =
'" & strdeal & "'"


Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

But I get a message to say the statement is too complex etc
Any ideas?
Thanks
Tony

The line that is highlighted is this one
" AND [txtdealnbr]=strdeal"

Try

" AND [txtdealnbr] = '" & strdeal & "'"

Keith.
www.keithwilby.co.uk

 

[Keith Wilby]

WHERE [txtcompany] = '" & strtxtcompany & "'" & _
"AND ([txtmonth] Between " & _
Format(dtmFirstDay, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(dtmLastDay, "\#yyyy\-mm\-dd\#") & ") AND [txtdealnbr]
=
'" & strdeal & "'"

Try

WHERE [txtcompany] = '" & strtxtcompany & "' AND ([txtmonth] Between " & _
Format(dtmFirstDay, "mm/dd/yyyy") & _
" AND " & Format(dtmLastDay, "mm/dd/yyyy") & ") AND [txtdealnbr] = '" &
strdeal & "'"

 

[TonyWilliams via AccessMonster.com]

Still same message - too complex. Here's what I've got in case I have pasted
it correctlt:
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearchHV] WHERE
[txtcompany] = '" & strtxtcompany & "' AND ([txtmonth] Between " & _
Format(dtmFirstDay, "mm/dd/yyyy") & _
" AND " & Format(dtmLastDay, "mm/dd/yyyy") & ") AND [txtdealnbr] = '" &
strdeal & "'"

Thanks again for your help
Tony

WHERE [txtcompany] = '" & strtxtcompany & "'" & _
"AND ([txtmonth] Between " & _
Format(dtmFirstDay, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(dtmLastDay, "\#yyyy\-mm\-dd\#") & ") AND [txtdealnbr]
=
'" & strdeal & "'"

Try

WHERE [txtcompany] = '" & strtxtcompany & "' AND ([txtmonth] Between " & _
Format(dtmFirstDay, "mm/dd/yyyy") & _
" AND " & Format(dtmLastDay, "mm/dd/yyyy") & ") AND [txtdealnbr] = '" &
strdeal & "'"

 

[Keith Wilby]

Still same message - too complex. Here's what I've got in case I have
pasted
it correctlt:
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearchHV]
WHERE
[txtcompany] = '" & strtxtcompany & "' AND ([txtmonth] Between " & _
Format(dtmFirstDay, "mm/dd/yyyy") & _
" AND " & Format(dtmLastDay, "mm/dd/yyyy") & ") AND [txtdealnbr] = '" &
strdeal & "'"

Thanks again for your help
Tony

Not sure what else to suggest Tony, it looks OK to me. Debug.Print it to
the immediate window and check the actual sSQL tring. Try running the SQL
string as a query and see if that gives any clues.

Keith.

 

[TonyWilliams via AccessMonster.com]

Thanks Keith I will try that. txtdealnbr is a number field, I'm an Access
novice but I remember there being rules about how different fields are
seperated in VBA statements like dates have # signs etc. Could that be the
issue?
Tony

Still same message - too complex. Here's what I've got in case I have
pasted

[quoted text clipped - 8 lines]

Thanks again for your help
Tony

Not sure what else to suggest Tony, it looks OK to me. Debug.Print it to
the immediate window and check the actual sSQL tring. Try running the SQL
string as a query and see if that gives any clues.

Keith.

 

[Keith Wilby]

Thanks Keith I will try that. txtdealnbr is a number field, I'm an Access
novice but I remember there being rules about how different fields are
seperated in VBA statements like dates have # signs etc. Could that be the
issue?
Tony

Ah, I assumed it was all text given the prefixes. Having revisited your OP
I see that "strdeal" is an integer, so you don't need the single quotation
marks around it.

[txtdealnbr] = " & strdeal

It might be an idea to rename that variable to something like intDeal to
avoid confusion.

Keith.

 

[TonyWilliams via AccessMonster.com]

Thanks Keith, that got rid of the message. However it still didn't get me to
the right place. I think it may have something to do with the fact that I'm
using a proprietory UI from UIBuilder hence the reference to the form in the
source object statement. The deal number actually sits on a subform so I
think I need to work out how to open the form "frmhighvaluedeals" but the
code needs to reference the fact that the control is on the subform (I think?)

Anyway thatnks for your help so far at least it's given me a head start.
Cheers
Tony

Thanks Keith I will try that. txtdealnbr is a number field, I'm an Access
novice but I remember there being rules about how different fields are
seperated in VBA statements like dates have # signs etc. Could that be the
issue?
Tony

Ah, I assumed it was all text given the prefixes. Having revisited your OP
I see that "strdeal" is an integer, so you don't need the single quotation
marks around it.

[txtdealnbr] = " & strdeal

It might be an idea to rename that variable to something like intDeal to
avoid confusion.

Keith.

 

[BruceM via AccessMonster.com]

To reference a control on the subform, reference the Form property of the
subform control, then the control:

strdeal = Me.subformcontrolname.Form.txtdealnbr

You seem to have a general handle on that principle, as it appears elsewhere
in the code when you assign the Record Source, but there it is anyhow. Maybe
I missed something. By the way, if this code references controls and values
on the current form you don't need the full Forms!frmMain etc. syntax, as
demonstrated above. I am a little unclear on where this code resides, other
than that it is in a Click event.

Did the code compile? I ask because there seems to be a phantom variable
strdate. Also, although it doesn't matter because the line is commented out,
is there supposed to be strtxtsurname in the code?

Are you familiar with Debug.Print? For the RecordSource you could declare
strSQL as a string, then do something like this:

strSQL = "SELECT * FROM [qrysearchHV] WHERE = " & _
"[txtcompany] = '" & strtxtcompany & "' AND ([txtmonth] Between " & _
Format(dtmFirstDay, "mm/dd/yyyy") & _
" AND " & Format(dtmLastDay, "mm/dd/yyyy") & ") AND [txtdealnbr] = &
strdeal
Debug.Print strSQL
Me.RecordSource = strSQL

After you run the code, press Ctrl + G to open the immediate code window.
There you will see strSQL (the RecordSource SQL string) printed out. Errors
should be easier to spot that way than by reading the code.

You could also add a break point at the Me.RecordSource line. In the VBA
editor, click the vertical bar to the left of the code. This should place a
dot in the bar and highlight the code. When the code reaches that line it
will stop and show you the code window (including strSQL printed in the
immediate window). Press F8 to step through the code one line at a time.

If you were able to compile the code, go to the top of the code module and
add Option Explicit directly below Option Compare Database, then compile
again. Without requiring variable declaration, anything that cannot be
resolved as a control, function, variable, or whatever is treated as a
variable of Variant type. This means that if you make a typo Access will
treat the typo as null since it hasn't been assigned a value. The code may
run OK, but with unexpected results; or it may not run at all because null
doesn't work at that place in the code. Such errors can be very difficult to
track down.

Thanks Keith, that got rid of the message. However it still didn't get me to
the right place. I think it may have something to do with the fact that I'm
using a proprietory UI from UIBuilder hence the reference to the form in the
source object statement. The deal number actually sits on a subform so I
think I need to work out how to open the form "frmhighvaluedeals" but the
code needs to reference the fact that the control is on the subform (I think?)

Anyway thatnks for your help so far at least it's given me a head start.
Cheers
Tony
[quoted text clipped - 12 lines]

Keith.

 

[Daryl S]

Tony -

You can simplify your recordsource SQL dealing with the dates. If
[txtmonth] is a date field (and this is an Access database), then you can
change

"AND ([txtmonth] Between " & _
Format(dtmFirstDay, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(dtmLastDay, "\#yyyy\-mm\-dd\#") & ")

to

"AND ([txtmonth] Between #" & _
dtmFirstDay & _
"# AND "# & dtmLastDay & "#)

If [txtmonth] is a string field, or the back-end database needs the
formatting for the search, then change your dtmFirstDay and dtmLastDay
variables to string variables, and change the calculations to do the
formatting like this:

dtmFirstDay = Format(DateSerial(Year(Me.txtsearch2),
Month(Me.txtsearch2), 1), "\#yyyy\-mm\-dd\#")
dtmLastDay = Format(DateSerial(Year(Me.txtsearch2), Month(Me.txtsearch2)
+ 1, 0), "\#yyyy\-mm\-dd\#")

And then also simplify your SQL code above to this:

"AND ([txtmonth] Between '" & _
dtmFirstDay & _
"' AND '" & dtmLastDay & "')

--
Daryl S

Thanks Keith, that got rid of the message. However it still didn't get me to
the right place. I think it may have something to do with the fact that I'm
using a proprietory UI from UIBuilder hence the reference to the form in the
source object statement. The deal number actually sits on a subform so I
think I need to work out how to open the form "frmhighvaluedeals" but the
code needs to reference the fact that the control is on the subform (I think?)

Anyway thatnks for your help so far at least it's given me a head start.
Cheers
Tony

Thanks Keith I will try that. txtdealnbr is a number field, I'm an Access
novice but I remember there being rules about how different fields are
seperated in VBA statements like dates have # signs etc. Could that be the
issue?
Tony

Ah, I assumed it was all text given the prefixes. Having revisited your OP
I see that "strdeal" is an integer, so you don't need the single quotation
marks around it.

[txtdealnbr] = " & strdeal

It might be an idea to rename that variable to something like intDeal to
avoid confusion.

Keith.

--
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!




.

 

[TonyWilliams via AccessMonster.com]

Thanks for the suggestions Daryl and Bruce. I'll have a go and implementing
both your ideas over the weekend and come back if I run into trouble.
Thanks again
Tony

Tony -

You can simplify your recordsource SQL dealing with the dates. If
[txtmonth] is a date field (and this is an Access database), then you can
change

"AND ([txtmonth] Between " & _
Format(dtmFirstDay, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(dtmLastDay, "\#yyyy\-mm\-dd\#") & ")

to

"AND ([txtmonth] Between #" & _
dtmFirstDay & _
"# AND "# & dtmLastDay & "#)

If [txtmonth] is a string field, or the back-end database needs the
formatting for the search, then change your dtmFirstDay and dtmLastDay
variables to string variables, and change the calculations to do the
formatting like this:

dtmFirstDay = Format(DateSerial(Year(Me.txtsearch2),
Month(Me.txtsearch2), 1), "\#yyyy\-mm\-dd\#")
dtmLastDay = Format(DateSerial(Year(Me.txtsearch2), Month(Me.txtsearch2)
+ 1, 0), "\#yyyy\-mm\-dd\#")

And then also simplify your SQL code above to this:

"AND ([txtmonth] Between '" & _
dtmFirstDay & _
"' AND '" & dtmLastDay & "')

Thanks Keith, that got rid of the message. However it still didn't get me to
the right place. I think it may have something to do with the fact that I'm

[quoted text clipped - 23 lines]