Determing the Number of Records in an Append Query

[Guest]

I have a database with two tables - a Work Order table and a Preventive
Maintenance (PM) schedule table. I am also using the Access Switchboard when
the database is opened.

In the normal Database view, I created an update query (PMCreateOrders) that
will check for due dates in the PM table, and then Append these records to
the Work Order table so a Work Order can be generated.

In the Switchboard form's Open command, I tried to use the OpenRecordset
method on the Update query to determine if ant records need to be created and
appended to the Work Order table. It fails. So, I thought perhaps a Recordset
is not generated during an Update query. I created a Select query
(PMPendingOrders) on the PM table and can create a recordset via
OpenRecordset. Then, I check if the number of records in the recordset is
greater than zero. If so, I then the Update query and a third query not
involved in this question.

My code is below. Is there a way to determine if PM records need to be
appended to the Work Order table without first running the Select query? And,
should I change the DoCmd.OpenQuery commands to Execute commands?

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.
Dim dbs As Database
Dim rst As DAO.Recordset


' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("PMPendingOrders", dbOpenDynaset, dbReadOnly)
If rst.RecordCount > 0 Then
DoCmd.OpenQuery "PMCreateOrder", , acReadOnly
DoCmd.OpenQuery "PMUpdateTable", , acReadOnly
End If
End Sub

 

[Dirk Goldgar]

In

I have a database with two tables - a Work Order table and a
Preventive Maintenance (PM) schedule table. I am also using the
Access Switchboard when the database is opened.

In the normal Database view, I created an update query
(PMCreateOrders) that will check for due dates in the PM table, and
then Append these records to the Work Order table so a Work Order can
be generated.

In the Switchboard form's Open command, I tried to use the
OpenRecordset method on the Update query to determine if ant records
need to be created and appended to the Work Order table. It fails.
So, I thought perhaps a Recordset is not generated during an Update
query. I created a Select query (PMPendingOrders) on the PM table and
can create a recordset via OpenRecordset. Then, I check if the number
of records in the recordset is greater than zero. If so, I then the
Update query and a third query not involved in this question.

My code is below. Is there a way to determine if PM records need to be
appended to the Work Order table without first running the Select
query? And, should I change the DoCmd.OpenQuery commands to Execute
commands?

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.
Dim dbs As Database
Dim rst As DAO.Recordset


' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("PMPendingOrders", dbOpenDynaset,
dbReadOnly)
If rst.RecordCount > 0 Then
DoCmd.OpenQuery "PMCreateOrder", , acReadOnly
DoCmd.OpenQuery "PMUpdateTable", , acReadOnly
End If
End Sub

Don't forget to close your recordset.

I'm not sure what the point is of specifying acReadOnly for your
OpenQuery calls for action queries.

One way or another, you're going to have to query the table to find out
whether there are any orders to create. You can do this with the query
you're running, or you could conceivably use DCount or DLookup to find
out -- but those functions would also be building a query and opening a
recordset behind the scenes, so there's no special advantage to doing
so. You could open your recordset on a SQL statement instead of a
stored query, if you just want to avoid having that extra object saved
in your database.

I prefer to use the DAO Execute method to run action queries. It
doesn't give me warning prompts ("You are about to update n records ...
Are you sure?"), and I can determine in code how many records were
updated or appended. I might reqwrite your code like this:

'----- start of revised code -----
Set dbs = CurrentDb

' ** You'd need to fix the SQL statement below **
Set rst = dbs.OpenRecordset( _
"SELECT Count(*) FROM [PMTable] WHERE DueDate ...", _
dbOpenSnapshot)

If rst.Fields(0) > 0 Then
dbs.Execute "PMCreateOrder", dbFailOnError
dbs.Execute "PMUpdateTable", dbFailOnError
End If

rst.Close
Set rst = Nothing
'----- end of revised code -----

If you want to know and display how many records were appended by
PMCreateOrder, you could either look at the value of rst.Fields(0) --
assuming that is the number -- or you could interrogate
dbs.RecordsAffected after executing the action query.

 

[Guest]

Dirk,

Thanks very much. I'm new to VBA in Access so your thoughts are quite
helpful. After I posted, I realized I will have to print any PM records that
meet the criteria, so maybe the Select query is a good way to trap those
records for printing.

I have to think about the concept of opening the query in SQL instead of a
stored query. I don't know exactly how the acreadOnly parameter got set. I
may, due to my inexperience, have accepted a default somewhere.

Thanks again, very much.
Lowell

In

I have a database with two tables - a Work Order table and a
Preventive Maintenance (PM) schedule table. I am also using the
Access Switchboard when the database is opened.

In the normal Database view, I created an update query
(PMCreateOrders) that will check for due dates in the PM table, and
then Append these records to the Work Order table so a Work Order can
be generated.

In the Switchboard form's Open command, I tried to use the
OpenRecordset method on the Update query to determine if ant records
need to be created and appended to the Work Order table. It fails.
So, I thought perhaps a Recordset is not generated during an Update
query. I created a Select query (PMPendingOrders) on the PM table and
can create a recordset via OpenRecordset. Then, I check if the number
of records in the recordset is greater than zero. If so, I then the
Update query and a third query not involved in this question.

My code is below. Is there a way to determine if PM records need to be
appended to the Work Order table without first running the Select
query? And, should I change the DoCmd.OpenQuery commands to Execute
commands?

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.
Dim dbs As Database
Dim rst As DAO.Recordset


' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("PMPendingOrders", dbOpenDynaset,
dbReadOnly)
If rst.RecordCount > 0 Then
DoCmd.OpenQuery "PMCreateOrder", , acReadOnly
DoCmd.OpenQuery "PMUpdateTable", , acReadOnly
End If
End Sub

Don't forget to close your recordset.

I'm not sure what the point is of specifying acReadOnly for your
OpenQuery calls for action queries.

One way or another, you're going to have to query the table to find out
whether there are any orders to create. You can do this with the query
you're running, or you could conceivably use DCount or DLookup to find
out -- but those functions would also be building a query and opening a
recordset behind the scenes, so there's no special advantage to doing
so. You could open your recordset on a SQL statement instead of a
stored query, if you just want to avoid having that extra object saved
in your database.

I prefer to use the DAO Execute method to run action queries. It
doesn't give me warning prompts ("You are about to update n records ...
Are you sure?"), and I can determine in code how many records were
updated or appended. I might reqwrite your code like this:

'----- start of revised code -----
Set dbs = CurrentDb

' ** You'd need to fix the SQL statement below **
Set rst = dbs.OpenRecordset( _
"SELECT Count(*) FROM [PMTable] WHERE DueDate ...", _
dbOpenSnapshot)

If rst.Fields(0) > 0 Then
dbs.Execute "PMCreateOrder", dbFailOnError
dbs.Execute "PMUpdateTable", dbFailOnError
End If

rst.Close
Set rst = Nothing
'----- end of revised code -----

If you want to know and display how many records were appended by
PMCreateOrder, you could either look at the value of rst.Fields(0) --
assuming that is the number -- or you could interrogate
dbs.RecordsAffected after executing the action query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

In

Dirk,

Thanks very much. I'm new to VBA in Access so your thoughts are quite
helpful. After I posted, I realized I will have to print any PM
records that meet the criteria, so maybe the Select query is a good
way to trap those records for printing.

I have to think about the concept of opening the query in SQL instead
of a stored query. I don't know exactly how the acreadOnly parameter
got set. I may, due to my inexperience, have accepted a default
somewhere.

You're welcome. I'll keep an eye on this thread -- post back if you
have any more questions.