Assistance with Open Recordset Or

[Dalan]

I presume that using an open recordset method is the preferred method
of accomplishing what I'm trying to do. Of course, if there are other
options that would work, feel free to share them.

I need to provide a means of scanning one field for all records and to
trigger a message upon opening the form for alerting a user that
maintenance activities need to be promptly performed. The recordset
does not necessarily have to be used for any edits/updates as there is
another form that can be used to speed that task. Although indicating
the total number of records requiring maintenance would be a nice
enhancement.

Any assistance would be welcomed and appreciated. Thanks, Dalan
Private Sub Form_Activate()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblItems", dbOpenTable)
Stock = rst!Stock
With rst
.MoveLast
.MoveFirst
If !Stock = "RS" Then
MsgBox "There are stock codes that require prompt changing."
End If
End With
rst.Close

Set rst = Nothing
Set dbs = Nothing

End Sub

[Wayne Morgan]

Try a DCount function.

Private Sub Form_Activate()
If DCount("*", "tblItems", "[Stock]='RS'") > 0 Then
MsgBox "There are stock codes that require prompt changing."
End If
End Sub

This will count the number of records in the table tblItems where
[Stock]='RS'. If there are more than zero, then that means there are some so
pop-up your message. This will be faster than trying to step through the
recordset.

Also, a problem with the recordset code as you have it. You open the
recordset, move last, then move first. Next, you check the value of [Stock].
This checks the value of [Stock] for the current record, in this case the
first record since you just did a move first. You're not checking all of the
records. To do that you would have to set up a loop, moving forward one
record at a time and checking the field for each record. The DCount function
will be much faster.

The Activate event runs each time you return to your form, causing it to
receive the focus. If the form is left open and just covered by another
form, then when you return to the form, the Activate event will run again.
If this is your intent, that's fine. If not, you may want to check the Open
and Load events of the form.

--
Wayne Morgan
MS Access MVP
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...

I presume that using an open recordset method is the preferred method
of accomplishing what I'm trying to do. Of course, if there are other
options that would work, feel free to share them.

I need to provide a means of scanning one field for all records and to
trigger a message upon opening the form for alerting a user that
maintenance activities need to be promptly performed. The recordset
does not necessarily have to be used for any edits/updates as there is
another form that can be used to speed that task. Although indicating
the total number of records requiring maintenance would be a nice
enhancement.

Any assistance would be welcomed and appreciated. Thanks, Dalan
Private Sub Form_Activate()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblItems", dbOpenTable)
Stock = rst!Stock
With rst
.MoveLast
.MoveFirst
If !Stock = "RS" Then
MsgBox "There are stock codes that require prompt changing."
End If
End With
rst.Close

Set rst = Nothing
Set dbs = Nothing

End Sub

[Tom van Stiphout]

On 31 Jan 2004 03:38:47 -0800, ot***@safe-mail.net (Dalan) wrote:

Alternative: create a Totals query to get the count of rows with that
value in that field. MUCH faster, especially for larger sets.
select count(*) as RsCount from tblItems where Stock="RS"
Then open a recordset on that query, inspect the count, and if > 0
show your messagebox:
set rst=dbs.OpenRecordset("MyQuery", dbopensnapshot)
if rst!RsCount>0 then
Msgbox "There are stock..."
end if

-Tom.

I presume that using an open recordset method is the preferred method
of accomplishing what I'm trying to do. Of course, if there are other
options that would work, feel free to share them.

I need to provide a means of scanning one field for all records and to
trigger a message upon opening the form for alerting a user that
maintenance activities need to be promptly performed. The recordset
does not necessarily have to be used for any edits/updates as there is
another form that can be used to speed that task. Although indicating
the total number of records requiring maintenance would be a nice
enhancement.

Any assistance would be welcomed and appreciated. Thanks, Dalan
Private Sub Form_Activate()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblItems", dbOpenTable)
Stock = rst!Stock
With rst
.MoveLast
.MoveFirst
If !Stock = "RS" Then
MsgBox "There are stock codes that require prompt changing."
End If
End With
rst.Close

Set rst = Nothing
Set dbs = Nothing

End Sub

[Pieter Linden]

> I need to provide a means of scanning one field for all records and to

trigger a message upon opening the form for alerting a user that
maintenance activities need to be promptly performed. The recordset
does not necessarily have to be used for any edits/updates as there is
another form that can be used to speed that task. Although indicating
the total number of records requiring maintenance would be a nice
enhancement.

If you're doing this based on a count, you could use DCOUNT or open a
recordset based on SQL statement with a WHERE clause. Looping through
all the records in a table is overkill.

[Dalan]

Thanks all for your suggestions and advice. I will give it a go today.
I would also like to say thanks to everyone who has provided helpful
assistance over the last few months. It has been genuinely appreciated
- keep up the good work. Dalan
Try a DCount function.

Private Sub Form_Activate()
If DCount("*", "tblItems", "[Stock]='RS'") > 0 Then
MsgBox "There are stock codes that require prompt changing."
End If
End Sub

This will count the number of records in the table tblItems where
[Stock]='RS'. If there are more than zero, then that means there are
some so
pop-up your message. This will be faster than trying to step through
the
recordset.

Also, a problem with the recordset code as you have it. You open the
recordset, move last, then move first. Next, you check the value of
[Stock].
This checks the value of [Stock] for the current record, in this case
the
first record since you just did a move first. You're not checking all
of the
records. To do that you would have to set up a loop, moving forward
one
record at a time and checking the field for each record. The DCount
function
will be much faster.

The Activate event runs each time you return to your form, causing it
to
receive the focus. If the form is left open and just covered by
another
form, then when you return to the form, the Activate event will run
again.
If this is your intent, that's fine. If not, you may want to check the
Open
and Load events of the form.

--
Wayne Morgan
MS Access MVP

Alternative: create a Totals query to get the count of rows with that
value in that field. MUCH faster, especially for larger sets.
select count(*) as RsCount from tblItems where Stock="RS"
Then open a recordset on that query, inspect the count, and if > 0
show your messagebox:
set rst=dbs.OpenRecordset("MyQuery", dbopensnapshot)
if rst!RsCount>0 then
Msgbox "There are stock..."
end if

-Tom van Stiphout

If you're doing this based on a count, you could use DCOUNT or open a
recordset based on SQL statement with a WHERE clause. Looping through
all the records in a table is overkill.

Pieter Linden

I presume that using an open recordset method is the preferred method
of accomplishing what I'm trying to do. Of course, if there are other
options that would work, feel free to share them.

I need to provide a means of scanning one field for all records and to
trigger a message upon opening the form for alerting a user that
maintenance activities need to be promptly performed. The recordset
does not necessarily have to be used for any edits/updates as there is
another form that can be used to speed that task. Although indicating
the total number of records requiring maintenance would be a nice
enhancement.

Any assistance would be welcomed and appreciated. Thanks, Dalan
Private Sub Form_Activate()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblItems", dbOpenTable)
Stock = rst!Stock
With rst
.MoveLast
.MoveFirst
If !Stock = "RS" Then
MsgBox "There are stock codes that require prompt changing."
End If
End With
rst.Close

Set rst = Nothing
Set dbs = Nothing

End Sub