Limiting a datasheet subform based on a date/box?

[simpleton via AccessMonster.com]

I have a form that shows a datasheet subform, but i need to have a little
"date" box up top where I can have the data limited by the date.

Nothing is working.

What direction should I take?

 

[Damon Heron]

Private Sub Text1_AfterUpdate() 'this is your textbox on main form
Dim strsql As String
Dim strWhere As String
strsql = "Select * from tblTransactions " 'replace with your tablename
If Not IsNull(Text1) Then
strWhere = "Where tblTransactions.Transactiondate = #" & [Text1] & "#"
strsql = strsql & strWhere
End If
Me![subform1].Form.RecordSource = strsql 'replace subform1 with your
'subform name
End Sub

The null test is for returning all results. If you leave the textbox blank
then the entire recordset is shown - else your date records are shown.

Damon

 

[simpleton via AccessMonster.com]

That looks really great!
I'm trying it out right now, but it keeps erroring....

Run-time error '3131':
Syntax error in FROM clause.
(The "Me!" line at the bottom is highlighted.)




Private Sub txtDate_AfterUpdate()
Dim strsql As String
Dim strWhere As String
strsql = "Select * from tblProdSheet"
If Not IsNull(txtDate) Then
strWhere = "Where tblProdSheet.date = #" & [txtDate] & "#"
strsql = strsql & strWhere
End If
Me![tblProdSheet_Admin2_subform].Form.RecordSource = strsql
End Sub

 

[John W. Vinson]

That looks really great!
I'm trying it out right now, but it keeps erroring....

Run-time error '3131':
Syntax error in FROM clause.
(The "Me!" line at the bottom is highlighted.)




Private Sub txtDate_AfterUpdate()
Dim strsql As String
Dim strWhere As String
strsql = "Select * from tblProdSheet"
If Not IsNull(txtDate) Then
strWhere = "Where tblProdSheet.date = #" & [txtDate] & "#"
strsql = strsql & strWhere
End If
Me![tblProdSheet_Admin2_subform].Form.RecordSource = strsql
End Sub

Subtle error: you need a blank either after tblProdSheet or before WHERE. What
you're getting now is

SELECT * from tblProdSheetWhere tblProdSheet.date...

On a side note, I'd suggest changing the name of the field date. That's a
reserved word (for the built in Date() function); Access can and will get
confused.

 

[simpleton via AccessMonster.com]

Beautiful.
That worked like a charm!!