Basic Recordset Question

[Abbey Normal]

Hi. I'm trying to learn VBA by the seat of my pants - like any good
programmer would - and need some help. I'm trying to run a query/report based
on a week ending date that the user enters from a form [which is a combo box
from a select distinct WeekEnding date from table] I am assuming that I need
to create a recordset in order to get a subset of the table. I copied the
code from another working module - like any good programmer would - and
modified it to suit my needs. But the recordset does not return any records,
even though they are definately in the table, and I have debugged the
criteria (txtWeekEnding). It stops at the line rst.MoveLast and displays the
message box from error 3021. Am I correct in assuming that this code is all I
need to get the subset of records? Here is the code:

Public Sub RecordsetUpdate()
On Error GoTo ErrorHandler
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblTaggingProduction WHERE
tblTaggingProduction.WeekEnding = " & txtWeekEnding)
rst.MoveLast
rst.MoveFirst
txtResult = "Criteria returns " & rst.RecordCount & " records"
Exit Sub

ErrorHandler:
If Err.Number = 3021 Then
MsgBox "No items match the specified criteria. Data fields " _
& "will now be reset. Please change your criteria and " _
& "and try again.", vbOKOnly, "No data"
Call FormSetup
Else
MsgBox "Error in the Recordset Update Module having number " _
& Err.Number & " and the description: " & Err.Description _
, vbOKOnly, "Unexpected Error"
End If
End Sub

Thank you for any help offered...

 

[Ken Snell [MVP]]

Assuming that WeekEnding is a date/time field, you need to delimit the value
that is in the textbox txtWeekEnding with # characters:

Set rst = db.OpenRecordset("SELECT * FROM tblTaggingProduction WHERE
tblTaggingProduction.WeekEnding = #" & txtWeekEnding & "#")

 

[Abbey Normal]

Thank you - the delimiter was the ticket. I now am returning the correct
number records from my select criteria. However, my next step should be to
either run a query or a report [from a command button], and using only that
recordset. How do I tell the report to only use that recordset?

Assuming that WeekEnding is a date/time field, you need to delimit the value
that is in the textbox txtWeekEnding with # characters:

Set rst = db.OpenRecordset("SELECT * FROM tblTaggingProduction WHERE
tblTaggingProduction.WeekEnding = #" & txtWeekEnding & "#")


--

Ken Snell
<MS ACCESS MVP>

Hi. I'm trying to learn VBA by the seat of my pants - like any good
programmer would - and need some help. I'm trying to run a query/report
based
on a week ending date that the user enters from a form [which is a combo
box
from a select distinct WeekEnding date from table] I am assuming that I
need
to create a recordset in order to get a subset of the table. I copied the
code from another working module - like any good programmer would - and
modified it to suit my needs. But the recordset does not return any
records,
even though they are definately in the table, and I have debugged the
criteria (txtWeekEnding). It stops at the line rst.MoveLast and displays
the
message box from error 3021. Am I correct in assuming that this code is
all I
need to get the subset of records? Here is the code:

Public Sub RecordsetUpdate()
On Error GoTo ErrorHandler
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblTaggingProduction WHERE
tblTaggingProduction.WeekEnding = " & txtWeekEnding)
rst.MoveLast
rst.MoveFirst
txtResult = "Criteria returns " & rst.RecordCount & " records"
Exit Sub

ErrorHandler:
If Err.Number = 3021 Then
MsgBox "No items match the specified criteria. Data fields " _
& "will now be reset. Please change your criteria and " _
& "and try again.", vbOKOnly, "No data"
Call FormSetup
Else
MsgBox "Error in the Recordset Update Module having number " _
& Err.Number & " and the description: " & Err.Description _
, vbOKOnly, "Unexpected Error"
End If
End Sub

Thank you for any help offered...

 

[Ken Snell [MVP]]

Easiest way to to do this is to create and save a query that uses this SQL
statement:

SELECT * FROM tblTaggingProduction

Then bind the report to that query. Click a button on your form to open the
report using code similar to this:


Private Sub CommandButtonName_Click()
DoCmd.OpenReport "ReportName", , , _
"tblTaggingProduction.WeekEnding = #" & _
Me.txtWeekEnding & "#"
End Sub

This will filter the report using the value in the textbox and the report
will show just the desired record.

--

Ken Snell
<MS ACCESS MVP>

Thank you - the delimiter was the ticket. I now am returning the correct
number records from my select criteria. However, my next step should be to
either run a query or a report [from a command button], and using only
that
recordset. How do I tell the report to only use that recordset?

Assuming that WeekEnding is a date/time field, you need to delimit the
value
that is in the textbox txtWeekEnding with # characters:

Set rst = db.OpenRecordset("SELECT * FROM tblTaggingProduction WHERE
tblTaggingProduction.WeekEnding = #" & txtWeekEnding & "#")


--

Ken Snell
<MS ACCESS MVP>

Hi. I'm trying to learn VBA by the seat of my pants - like any good
programmer would - and need some help. I'm trying to run a query/report
based
on a week ending date that the user enters from a form [which is a
combo
box
from a select distinct WeekEnding date from table] I am assuming that I
need
to create a recordset in order to get a subset of the table. I copied
the
code from another working module - like any good programmer would - and
modified it to suit my needs. But the recordset does not return any
records,
even though they are definately in the table, and I have debugged the
criteria (txtWeekEnding). It stops at the line rst.MoveLast and
displays
the
message box from error 3021. Am I correct in assuming that this code is
all I
need to get the subset of records? Here is the code:

Public Sub RecordsetUpdate()
On Error GoTo ErrorHandler
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblTaggingProduction WHERE
tblTaggingProduction.WeekEnding = " & txtWeekEnding)
rst.MoveLast
rst.MoveFirst
txtResult = "Criteria returns " & rst.RecordCount & " records"
Exit Sub

ErrorHandler:
If Err.Number = 3021 Then
MsgBox "No items match the specified criteria. Data fields " _
& "will now be reset. Please change your criteria and " _
& "and try again.", vbOKOnly, "No data"
Call FormSetup
Else
MsgBox "Error in the Recordset Update Module having number " _
& Err.Number & " and the description: " & Err.Description _
, vbOKOnly, "Unexpected Error"
End If
End Sub

Thank you for any help offered...

 

[Abbey Normal]

Success! Thank you. I just want to confirm that when you said "bind the
report to that query" it meant to use that query as a record source within
the report.

 

[Ken Snell [MVP]]

Success! Thank you. I just want to confirm that when you said "bind the
report to that query" it meant to use that query as a record source within
the report.

You are correct. Congratulations on your success; good luck!