Form wont show records

I have an unbound form which has a unbound combox box called cmbmonth where
the user chooses a value from the list for which the record source is based
on this SQL statement

SELECT tblMonth.txtmonthlabela
FROM tblMonth
ORDER BY tblMonth.txtmonthlabela DESC;

txtmonthlabela is a date field - (I realise that the name is totally
misleading but that's how I it was when I got it!)

There is then a command button which should open a form which has a control
called txtmonth, a date field. However when I click on the form it doesn't
show any records. Here is the code behind the button.

Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click


Dim strtxtdate As Date
strtxtdate = Me.cmbmonth.Value

Forms!frmMain!SubForm1.SourceObject = "frmhighvaluedeals"


Forms!frmMain!SubForm1.Form.RecordSource = _
"SELECT * FROM [tblhvcomp] " & _
"WHERE [txtmonth] = #" & Format(strtxtdate, "mmmm/yyyy") & "#"


Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

Can anyone point me in the right direction as to why no records are being
shown?
Thanks
Tony

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

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

I suspect that this line is the problem.

"WHERE [txtmonth] = #" & Format(strtxtdate, "mmmm/yyyy") & "#"

If txtmonth is a date field and you are trying to compare it with
a formatted string, which is what is returned by;

Format(strtxtdate, "mmmm/yyyy")

then it will never find any matches and no records will be returned.

Try comparing the field directly to the combo box;

"WHERE [txtmonth] = #" & Me.cmbmonth & "#"

--
_________

Sean Bailey


"TonyWilliams via AccessMonster.com" wrote:

I have an unbound form which has a unbound combox box called cmbmonth where
the user chooses a value from the list for which the record source is based
on this SQL statement

SELECT tblMonth.txtmonthlabela
FROM tblMonth
ORDER BY tblMonth.txtmonthlabela DESC;

txtmonthlabela is a date field - (I realise that the name is totally
misleading but that's how I it was when I got it!)

There is then a command button which should open a form which has a control
called txtmonth, a date field. However when I click on the form it doesn't
show any records. Here is the code behind the button.

Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click


Dim strtxtdate As Date
strtxtdate = Me.cmbmonth.Value

Forms!frmMain!SubForm1.SourceObject = "frmhighvaluedeals"


Forms!frmMain!SubForm1.Form.RecordSource = _
"SELECT * FROM [tblhvcomp] " & _
"WHERE [txtmonth] = #" & Format(strtxtdate, "mmmm/yyyy") & "#"


Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

Can anyone point me in the right direction as to why no records are being
shown?
Thanks
Tony

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

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

.

Actually, you probably don't need the date delimiters in there either.
--
_________

Sean Bailey


"Beetle" wrote:

I suspect that this line is the problem.

"WHERE [txtmonth] = #" & Format(strtxtdate, "mmmm/yyyy") & "#"

If txtmonth is a date field and you are trying to compare it with
a formatted string, which is what is returned by;

Format(strtxtdate, "mmmm/yyyy")

then it will never find any matches and no records will be returned.

Try comparing the field directly to the combo box;

"WHERE [txtmonth] = #" & Me.cmbmonth & "#"

--
_________

Sean Bailey


"TonyWilliams via AccessMonster.com" wrote:

I have an unbound form which has a unbound combox box called cmbmonth where
the user chooses a value from the list for which the record source is based
on this SQL statement

SELECT tblMonth.txtmonthlabela
FROM tblMonth
ORDER BY tblMonth.txtmonthlabela DESC;

txtmonthlabela is a date field - (I realise that the name is totally
misleading but that's how I it was when I got it!)

There is then a command button which should open a form which has a control
called txtmonth, a date field. However when I click on the form it doesn't
show any records. Here is the code behind the button.

Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click


Dim strtxtdate As Date
strtxtdate = Me.cmbmonth.Value

Forms!frmMain!SubForm1.SourceObject = "frmhighvaluedeals"


Forms!frmMain!SubForm1.Form.RecordSource = _
"SELECT * FROM [tblhvcomp] " & _
"WHERE [txtmonth] = #" & Format(strtxtdate, "mmmm/yyyy") & "#"


Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

End Sub

Can anyone point me in the right direction as to why no records are being
shown?
Thanks
Tony

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

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

.

Hi Beetle, I've tried both expressions and when the delimiters are in I get a
message that says expression refers to an object that is closed or doesn't
exist and then the form opens but shows all the records not just the ones for
the month.
Any ideas?
Thanks
Tony

Beetle wrote:
Actually, you probably don't need the date delimiters in there either.
I suspect that this line is the problem.

[quoted text clipped - 53 lines]
Thanks
Tony

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

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

This seems to work but I'm not sure why?

Private Sub cmdopenrecord_Click()
On Error GoTo Err_cmdopenrecord_Click


Dim strtxtdate As Date
strtxtdate = Format(Me.cmbmonth.Value, "MM/DD/YYYY")

Forms!frmMain!SubForm1.SourceObject = "frmhighvaluedeals"

Forms!frmMain!SubForm1.Form.RecordSource = _
"SELECT * FROM [tblhvdealspt1] " & _
"WHERE [txtmonth] = #" & strtxtdate & "#"

Exit_cmdopenrecord_Click:
Exit Sub

Err_cmdopenrecord_Click:
MsgBox Err.Description
Resume Exit_cmdopenrecord_Click

Thanks again
Tony

End Sub
TonyWilliams wrote:
Hi Beetle, I've tried both expressions and when the delimiters are in I get a
message that says expression refers to an object that is closed or doesn't
exist and then the form opens but shows all the records not just the ones for
the month.
Any ideas?
Thanks
Tony

Actually, you probably don't need the date delimiters in there either.
I suspect that this line is the problem.

[quoted text clipped - 53 lines]
Thanks
Tony


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

Message posted via http://www.accessmonster.com