Using ADO Find

[Guest]

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
PS Please see code below

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

 

[Robin Hammond]

Frank,

First off, Find is not reliable in Excel 97, so just in case, use the filter
command on the recordset. Not sure exactly what you are aiming for but I
have put some suggestions in your code (untested).

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)
.Filter = "Associate = '" & strAssoc & "'"
If .Recordcount = 0 then
.AddNew
.Fields("Associate")=strAssoc
End IF
'update your other fields in the recordset here
'you don't appear to be doing this at the moment
'unless those exclamation marks are a short cut I've never
heard of
'e.g. .Fields("TimeStamp") = Format(Now,"yyyy-mm-dd:hh-mm")

'!Assoc_No = lstAssociate.Column(0, i)
'the column number looks like it is wrong above

.Filter = adFilterNone
'some of your code deleted here
Next i
.UpdateBatch
.Close
End With
Set rsADO = Nothing
cnADO.Close

Robin Hammond
www.enhanceddatasystems.com

 

[Guest]

yes the exclamation marks are short cut as used in dao and works just as
well in ado I am just starting to switching over from dao, thanks for your
help I will try the filter and we are using office 200.
Frank