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

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

"Frank" wrote in message
...
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

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

"Robin Hammond" wrote:

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

"Frank" wrote in message
...
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