Openrecord set problem!

[Swagener]

Hi Guys,

I,m openrecordset to look up a value in a table and it seems to be
picking up the first value in the field only.

Thanks in advance

 

[Brendan Reynolds]

Hard to say what the problem might be without seeing the code. Are you using
the MoveNext property of the recordset to move through the records?
Something like ...

Do Until rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop

If that doesn't help, please provide more detail.

 

[Swagener]

no the code looks like this which looks up a field value in a form and
then checks it in the table.

Dim db As DAO.Database
Dim snp As DAO.Recordset
Dim Dmbarcodecheck As String
Dim ScanBarcodeSearch As String

ScanBarcodeSearch = Me.TXTSearch
Set db = CurrentDb
Set snp = db.OpenRecordset("SO Cust Master", dbOpenSnapshot)
Dmbarcodecheck = snp!DMBarcode
snp.Close
db.Close

ScanBarcodeSearch = Me.TXTSearch

If ScanBarcodeSearch = Dmbarcodecheck Then
MsgBox "Match Found For: " & ScanBarcodeSearch, , "Customer
Found!"
DoCmd.OpenForm "search", acNormal, "", "[SO Cust
Master].DMBarcode=" & "'" & Me.TXTSearch & "'", acEdit, acNormal
DoCmd.Close acForm, "paratest"
'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & ScanBarcodeSearch & " -
Please Try Again.", _
, "Invalid Search Criterion!"
TXTSearch.SetFocus
End If
End Sub

 

[Brendan Reynolds]

You could loop through the recordset looking for a match using .MoveNext as
in my previous example, or you could use .FindFirst, but a more efficient
method would be to use a SQL statement when opening the recordset to return
only matching records, if any. Here's an example using the Categories form
from the Northwind database ...

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim boolFound As Boolean

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Count(*) AS TheCount " & _
"FROM Categories " & _
"WHERE CategoryName = '" & Me.CategoryName & "' " & _
"AND CategoryID <> " & Me.CategoryID)
boolFound = rst.Fields("TheCount") <> 0
rst.Close
If boolFound Then
MsgBox "There is alreasy a record with this category name." & _
vbCrLf & vbCrLf & _
"Please enter a unique category name."
Cancel = True
End If

End Sub