Access 2000 Search in VBA 1

[dar149]

I have two search criteria in VBA. strCriteria is a value I am grabbing from a Word document. strCriteria reads "lngCoIDNo = " & strHold, where strHold is the value brought in from Word. strCriteria2 reads "dtmYearQuota = '2002'". I can get the code to find the first occurrence in the table where lngCoIDNo equals the value in strHold. But once it finds that record I need to remain positioned on that record to then continue the search of field dtmYearQuota to find the year 2002. The primary key consists of lngCoIDNo and dtmYearQuota. So there are multiples of lngCoIDNo. What it is doing is moving back to record 1 when it starts strCriteria2 search.

I'd greatly appreciate any ideas or suggestions...

Debbie

 

[jhall156]

If these are criteria (WHERE Clause) in a query then the criteria will be applied to the entire table. If you want to move a record pointer about and perform those kinds of cursor operations you will need to use a recordset object (either DAO or ADO). JHall

 

[dar149]

This code is in VBA. It is not part of the Where Criteria in a Query. I am posting part of the code:

'Open Word and document
Set appWord = GetObject(, "Word.Application&quot
Set doc = appWord.Documents.Open(strDocName)

'Open Database and table needed for import
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=M:\Access Issues\CMJ\" & _
"VAR and PSC 2000 version.mdb;"

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblQuota", dbOpenDynaset)

'Retrieve variable value from word FormField
strHold = doc.FormFields("Text5&quot.Result

'lngCoIDNo and dtmYearQuota are fields on the tblQuota
strCriteria = "lngCoIDNo = " & strHold
strCriteria2 = "dtmYearQuota = '2002'"

rst.FindFirst strCriteria And strCriteria2

With rst

'Find first record satisfying search string.
'Message if no record found

If .NoMatch Then
MsgBox "No Company ID found!"
Else
rst.FindFirst strCriteria2
.Edit
!curQuotas = doc.FormFields("fldQuotafor2002&quot.Result
.Update
.Close
MsgBox "Contract Imported!"
End If
End With

doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
Cleanup:

 

[jhall156]

I think you need to create the criteria string all at once with the AND contained within it.

strCriteria = "lngCoIDNo = " & strHold & " AND dtmYearQuota = '2002'"

worth a shot.

JHall

 

[dar149]

Thank you so much!!!
It worked like a charm.

Debbie