Problem with VBA code on command button

T

Tony Williams

I have a form that has a combo box to select a month. The name of the combo
box is txtmontha (I realise it would have been better to name it cmb???) The
is a command button that when clicked should open a form where the date
selected from the combo box is the same value as the date on the form. Here
is the code behind the command button:

Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click
Dim strqtr As String

strqtr = Me.txtmontha.Value

Forms!frmMain!SubForm1.SourceObject = "frmFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearch]
WHERE [txtmonthlabel] = strqtr"
Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

Both the combox and the control on the form are formatted as mmmm yyyy.
However when I click the command button the form opens but I get a message
box that asks for the value of strqtr.

Can someone help me with this problem please?
Thanks
Tony
 
D

Daniel Pineault

You can't place a vba variable inside your sql statement quote... Try this
instead,

Dim strqtr As String

strqtr = Me.txtmontha.Value

Forms!frmMain!SubForm1.SourceObject = "frmFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearch]
WHERE [txtmonthlabel] = '" & strqtr & "'"

Also note the ' surrounding the strqtr. Since strqtr is a string value they
are required.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
T

Tony Williams

Thanks Daniel I now get an message box that says "Data type mismatch in
criteria expression"
can you help?
Thanks
Tony
PS As I mentioned previously both the fields are mmmm yyyy formatted.

Daniel Pineault said:
You can't place a vba variable inside your sql statement quote... Try this
instead,

Dim strqtr As String

strqtr = Me.txtmontha.Value

Forms!frmMain!SubForm1.SourceObject = "frmFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearch]
WHERE [txtmonthlabel] = '" & strqtr & "'"

Also note the ' surrounding the strqtr. Since strqtr is a string value they
are required.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



Tony Williams said:
I have a form that has a combo box to select a month. The name of the combo
box is txtmontha (I realise it would have been better to name it cmb???) The
is a command button that when clicked should open a form where the date
selected from the combo box is the same value as the date on the form. Here
is the code behind the command button:

Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click
Dim strqtr As String

strqtr = Me.txtmontha.Value

Forms!frmMain!SubForm1.SourceObject = "frmFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearch]
WHERE [txtmonthlabel] = strqtr"
Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

Both the combox and the control on the form are formatted as mmmm yyyy.
However when I click the command button the form opens but I get a message
box that asks for the value of strqtr.

Can someone help me with this problem please?
Thanks
Tony
Click to expand...
Click to expand...
 
T

Tony Williams

Thanks Roger but I got the same message as I mentioned in my reply yo Daniel
- Type mismatch
Thanks
Tony

Roger Carlson said:
Try this:

Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearch] WHERE
[txtmonthlabel] = '" & strqtr & "'"

In case you can't read it I'll add extra spaces to show the delimiters: = '
" & strqtr & " ' "

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Tony Williams said:
I have a form that has a combo box to select a month. The name of the combo
box is txtmontha (I realise it would have been better to name it cmb???)
The
is a command button that when clicked should open a form where the date
selected from the combo box is the same value as the date on the form.
Here
is the code behind the command button:

Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click
Dim strqtr As String

strqtr = Me.txtmontha.Value

Forms!frmMain!SubForm1.SourceObject = "frmFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [qrysearch]
WHERE [txtmonthlabel] = strqtr"
Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

Both the combox and the control on the form are formatted as mmmm yyyy.
However when I click the command button the form opens but I get a message
box that asks for the value of strqtr.

Can someone help me with this problem please?
Thanks
Tony
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

Format of date in my code 3
Code doesn't open my form? 2
Opening a form and unable to edit it? 2
How can I make my select statement include two statements 3
Command button code not opening my form 1
Open form problem 2
Command button code problem 4
bound column in combo box 3

Top