Recordset loop ..Help plz!!

[Anonymous]

i want to make a loop to check all records in an Access Database to find the id of this record so i wrote the following syntax but error take place so any one can help me in this??

Function PLID(DBPathX As String, STRPLName As String)
Dim PLNAME As String
Dim oAccess As New ADODB.Connection
Dim oRecordset As New ADODB.Recordset
oAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBPathX & ";"
oRecordset.Open "Select * From Floor", oAccess, adOpenKeyset, adLockOptimistic
'Start searching in the Access DB in table Floor
Do Until oRecordset.EOF
'If oRecordset![FLAT NAME] = STRPLName Then
'PLID = oRecordset!ID
'Else
'oRecordset.MoveNext
'End If
'Loop
If PLID = "" Then
With oRecordset
.AddNew
![FLAT NAME] = STRPLName
.Update
End With
PLID = oRecordset!ID
End If
End Function

[Anonymous]

If I understand you task completely
try this instead
I don't know what is the value type of ID fields
in your table
See my poor explanation inside the code

Private Sub PLID(DBPathX As String, STRPLName As String)
' request reference to: Microsoft ActiveX Data Objects 2.X Library
' (the very newest one in list of these libraries)
Dim oAccess As ADODB.Connection
Dim oRecordset As ADODB.Recordset
'create connection
Set oAccess = New ADODB.Connection
'open connection
oAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBPathX & ";"
'create recordset
Set oRecordset = New ADODB.Recordset
'open recordset
oRecordset.Open "Floor", oAccess, adOpenStatic, adLockPessimistic
'build search string
Dim findStr As String
' if value type of ID field is numeric then
findStr = "ID=Null"
'
' if value type of ID field is string then
'findStr = "ID="""""
'
'Start searching in the Access DB in table Floor
oRecordset.MoveFirst
' find value in desired field
oRecordset.Find findStr
'loop trough all table
Do Until oRecordset.EOF
oRecordset![FLAT NAME] = STRPLName
oRecordset.Find findStr, 1
Loop
'clean up
oRecordset.Close
Set oRecordset = Nothing
oAccess.Close
Set oAccess = Nothing

End Sub

Sub test()
Dim p As String
Dim n As String
p = "C:\Temp\Test.mdb"
n = "Bla-bla-bla"
Call PLID(p, n)
End Sub

~'J'~