Basic Recordset Question

[Guest]

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 & "#")

 

[Mark via AccessMonster.com]

When opening recordsets, I like to check to see if there are any records in there before trying to move to the last (or any) record. Otherwise, you'll always get the 3021 "No current record" error when you get to "rst.movelast".

Set rst=CurrentDB.OpenRecordset("qryMyData",dbOpenSnapshot)
If rst.EOF Then
MsgBox "No records found!"
'whatever you want to do when there are no records
Else
rst.MoveLast
MsgBox "We found " & rst.RecordCount & " record(s)."
End If
Set rst = Nothing

 

[Guest]

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...

 

[Guest]

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!