Using Find In ADO

[Frank]

Hi I have some VBA code building a query I run the code to add a new record
this works fine what I need to do is edit the a record in the same query, if
the record already exists I want to edit it if not add it.
I have been trying to use the find a bit like in DAO with findfirst but I
can get it to work can some help me please.
TIA
Frank

 

[Alex White MCDBA MCSE]

Hi Frank,

something like

dim adoTest as new adodb.recordset
dim SQL as string
SQL = "Select * from TblTest Where MyID=1"
with adoTest
.open SQL,currentproject.connection,adOpenKeyset, adLockOptimistic
if .recordcount > 0 then
... edit your existing record
else
.addnew
... edit your new record
end if
.update
.close
end with

Hope that answers your question.

 

[Frank]

Hi Thanks for your help I see what you are saying the code I am using below
you can see that the id is in side the rsado. this the reason I was trying to
use the find do you have any ideas.
TIA
Frank

strSQL = "SELECT * FROM tblAttendance"

cnADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
GetFromIniFile("dBPaths", "Database")
rsADO.Open strSQL, cnADO, adOpenKeyset, adLockOptimistic

With rsADO
For i = 0 To lstAssociate.ListCount - 1
If lstAssociate.Selected(i) = True Then
strAssoc = lstAssociate.Column(0, i)
.AddNew
!Assoc_No = lstAssociate.Column(0, i)
If WhichBnt = 1 Then
!Absent_Type = cboAbsent
End If
!Date_Stamp = Mid(Now(), 7, 4) & Mid(Now(), 4, 2) & Mid(Now(),
1, 2) & Mid(Now(), 12, 2) & Mid(Now(), 15, 2) & Mid(Now(), 18, 2)
!Creation_Date = Mid(Now(), 7, 4) & Mid(Now(), 4, 2) &
Mid(Now(), 1, 2)
!Zone = myZone(i)
End If
Next i
.UpdateBatch
.Close
End With
Set rsADO = Nothing
cnADO.Close