Recordset - Value not found

A

AJ

I have a dumb question that I cannot seem to find the answer to. I have a
recordset that uses a field.value in the where clause. Can anyone tell me how
to set up the recordset so if nothing is returned from my sql the vba does
not fail.

Example:

recset = "select * from stores where store = form.store"

If ???? NO STORE FOUND?????
msgbox "store does not exist"
else
msgbox "store found, continue processing"
end if

Thanks!!
 
J

John W. Vinson

I have a dumb question that I cannot seem to find the answer to. I have a
recordset that uses a field.value in the where clause. Can anyone tell me how
to set up the recordset so if nothing is returned from my sql the vba does
not fail.

Example:

recset = "select * from stores where store = form.store"

If ???? NO STORE FOUND?????
msgbox "store does not exist"
else
msgbox "store found, continue processing"
end if

Thanks!!
Click to expand...

Well, you have a problem here. To actually retrieve records you need to *open*
the recordset, not just set it to a string!

Dim db As DAO.Database
Dim recset As DAO.Recordset
Set db = CurrentDb
Set recset = db.OpenRecordset("Select * from stores where store = '" _
& Me!Store & "'"
recset.MoveLast ' to find all the records
Select Case recset.RecordCount
Case 0 ' no records returned
MsgBox "store does not exist"
Case 1 ' just one store found
MsgBox "One store found, continue processing"
Case else
MsgBox "Found a bunch of stores, now what?"
End Select

However - why go to all the trouble?

If IsNull(DLookUp("[Store]", "[Stores]", "[Store] = '" & Me!Store & "'") Then
MsgBox "Store not found"
Else
MsgBox "continue..."
End If


John W. Vinson [MVP]
 
A

AJ

Thanks Rob and John, really appreciate your help.

John W. Vinson said:
I have a dumb question that I cannot seem to find the answer to. I have a
recordset that uses a field.value in the where clause. Can anyone tell me how
to set up the recordset so if nothing is returned from my sql the vba does
not fail.

Example:

recset = "select * from stores where store = form.store"

If ???? NO STORE FOUND?????
msgbox "store does not exist"
else
msgbox "store found, continue processing"
end if

Thanks!!
Click to expand...

Well, you have a problem here. To actually retrieve records you need to *open*
the recordset, not just set it to a string!

Dim db As DAO.Database
Dim recset As DAO.Recordset
Set db = CurrentDb
Set recset = db.OpenRecordset("Select * from stores where store = '" _
& Me!Store & "'"
recset.MoveLast ' to find all the records
Select Case recset.RecordCount
Case 0 ' no records returned
MsgBox "store does not exist"
Case 1 ' just one store found
MsgBox "One store found, continue processing"
Case else
MsgBox "Found a bunch of stores, now what?"
End Select

However - why go to all the trouble?

If IsNull(DLookUp("[Store]", "[Stores]", "[Store] = '" & Me!Store & "'") Then
MsgBox "Store not found"
Else
MsgBox "continue..."
End If


John W. Vinson [MVP]
Click to expand...
 
D

Dirk Goldgar

John W. Vinson said:
Well, you have a problem here. To actually retrieve records you need to
*open*
the recordset, not just set it to a string!

Dim db As DAO.Database
Dim recset As DAO.Recordset
Set db = CurrentDb
Set recset = db.OpenRecordset("Select * from stores where store = '" _
& Me!Store & "'"
recset.MoveLast ' to find all the records
Select Case recset.RecordCount
Case 0 ' no records returned
MsgBox "store does not exist"
Case 1 ' just one store found
MsgBox "One store found, continue processing"
Case else
MsgBox "Found a bunch of stores, now what?"
End Select

However - why go to all the trouble?

If IsNull(DLookUp("[Store]", "[Stores]", "[Store] = '" & Me!Store & "'")
Then
MsgBox "Store not found"
Else
MsgBox "continue..."
End If
Click to expand...


There's a minor oversight there: if there are no matching stores, the
statement ...
recset.MoveLast ' to find all the records
Click to expand...

.... will raise an error. I would suggest checking first to see if you have
any records, and only doing the MoveLast if you have at least one record
(and only then if you really need to know whether there are more than one).
 
A

AJ

I am still getting an error. I have an ADO recordset. I put the display in
below, it seems that regardless of whether the sql is successful, the
recordset is "-1".
Help???

msgbox rs5.recordcount
If rs5.RecordCount = 0 Then
MsgBox "Store Does Not Exist, Please Try Again!"
Me.STORE1 = Null
STORE1.SetFocus
Else
[Forms]![FORM1]![store1_address].Visible = True
End if

Dirk Goldgar said:
John W. Vinson said:
Well, you have a problem here. To actually retrieve records you need to
*open*
the recordset, not just set it to a string!

Dim db As DAO.Database
Dim recset As DAO.Recordset
Set db = CurrentDb
Set recset = db.OpenRecordset("Select * from stores where store = '" _
& Me!Store & "'"
recset.MoveLast ' to find all the records
Select Case recset.RecordCount
Case 0 ' no records returned
MsgBox "store does not exist"
Case 1 ' just one store found
MsgBox "One store found, continue processing"
Case else
MsgBox "Found a bunch of stores, now what?"
End Select

However - why go to all the trouble?

If IsNull(DLookUp("[Store]", "[Stores]", "[Store] = '" & Me!Store & "'")
Then
MsgBox "Store not found"
Else
MsgBox "continue..."
End If
Click to expand...


There's a minor oversight there: if there are no matching stores, the
statement ...
recset.MoveLast ' to find all the records
Click to expand...

... will raise an error. I would suggest checking first to see if you have
any records, and only doing the MoveLast if you have at least one record
(and only then if you really need to know whether there are more than one).

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

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

AJ said:
I am still getting an error. I have an ADO recordset. I put the display in
below, it seems that regardless of whether the sql is successful, the
recordset is "-1".
Help???

msgbox rs5.recordcount
If rs5.RecordCount = 0 Then
MsgBox "Store Does Not Exist, Please Try Again!"
Me.STORE1 = Null
STORE1.SetFocus
Else
[Forms]![FORM1]![store1_address].Visible = True
End if
Click to expand...


In an ADO recordset, the RecordCount property will have a value of -1 if the
number of records can't be determined. This depends on the data provider
and the type of cursor. This is different from the behavior of a DAO
recordset, so the fact that it's an ADO recordset is an important piece of
information.

Since your recordset apparently is of the type that doesn't know the number
of records yet, Instead of testing the RecordCount property, test the EOF
property:

If rs5.EOF Then
MsgBox "Store Does Not Exist, Please Try Again!"
Me.STORE1 = Null
STORE1.SetFocus
Else
[Forms]![FORM1]![store1_address].Visible = True
End if

If you make this test immediately after opening the recordset, testing EOF
alone should be all you need. If you don't know for sure whether the
current-record pointer in the recordset may have been moved since the
recordset was opened, test for both EOF and BOF:

If rs5.EOF and rs5.BOF Then

The only way both those conditions can be true is if the recordset contains
no records.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Another Easy Access Questions 5
ADO Recordset 3
A theoretical question: how best to manage perpetual recordset needs? 25
How to refer to a VBA module variable within a report select state 3
Record Find Problem 24
Can't Create Recordset 1
sql stored procedure results in a recordset questions 2
SQL not importing records from ACCDB 2

Top