Using ADO Find

[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
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

"Frank" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>