Format of date in my code

[Tony Williams]

I have a subform with a date called txtmontha with a format mmmm/yyyy There
is also a command button that when clicked should open another subform which
has a date txtmonthlabela, also format as mmm/yyyy. The command button should
open the sub form for all records when the value of txtmontha and
txtmonthlabela are the same. However when I click on the command button
although the form opens I get a prompt box asking me for the value of
txtmonthlabela. If I cancel the prompt box the form opens with a number of
records shown in the record selector box at the foot of the form but no data
is shown in the controls on the form.
Here is my code:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click


Dim strtxtdate As Date
strtxtdate = Me.txtmontha.Value
Forms!frmMain!SubForm1.SourceObject = "frmFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabela] = #" & Format(strtxtdate, "mmmm/yyyy") & "#;"

Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

Can anyone suggest what the problem may be?
Thanks
Tony

 

[Marshall Barton]

I have a subform with a date called txtmontha with a format mmmm/yyyy There
is also a command button that when clicked should open another subform which
has a date txtmonthlabela, also format as mmm/yyyy. The command button should
open the sub form for all records when the value of txtmontha and
txtmonthlabela are the same. However when I click on the command button
although the form opens I get a prompt box asking me for the value of
txtmonthlabela. If I cancel the prompt box the form opens with a number of
records shown in the record selector box at the foot of the form but no data
is shown in the controls on the form.
Here is my code:
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click


Dim strtxtdate As Date
strtxtdate = Me.txtmontha.Value
Forms!frmMain!SubForm1.SourceObject = "frmFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabela] = #" & Format(strtxtdate, "mmmm/yyyy") & "#;"

txtmonthlabela needs to be replaced with the name of the
corresponding field name in table tblmaintabs.

 

[Douglas J. Steele]

mmmm/yyyy isn't a date. Dates must be complete dates: a full day, month and
year.

You cannot use the # delimiter unless you're dealing with dates.

 

[Tony Williams]

Thanks guys. I had got the incorrect field name in tblmaintabs (dyslectic
fingers!) and once I corrected it the code worked fine.
I know the field is called txtmonthlabel but it is a date field and
formatted as mmmm/yyyy This code now works (I've changed the name of my
destination form to)
Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click


Dim strtxtdate As Date
strtxtdate = Me.txtmontha.Value
Forms!frmMain!SubForm1.SourceObject = "subformFDA"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "#;"

Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub
Thanks
Tony