FindNext Problem

[Mark Jones]

I have a table that contains a field called PurchaseOrderNumber. In this field the data is type String, and looks like this;

NM001-018
NM001-018-001
NM001-018-002
NM001-018-003
NM001-019
NM001-019-001 etc...

I need to set up a criteria string for FindNext which will match a sub-string match like "NM001-018" so that it will match all of the NM001-018xxxx strings. I know this can be done in a query but I need the match to be delivered back to VB code. Maybe FindNext is not the right method, but I can't seem to find a method which will work short of MoveNext / extracting the field / and then using the Mid function to see if the sub-string matches. Very inefficient over lots of records.

Thanks for any ideas in advance!


Mark

 

[Pat Hartman]

The following is a function that opens a parameter query and loops through the recordset to update it.

Public Function RenumberSeq()
Dim WgtDB As Database
Dim QD1 As QueryDef
Dim TempSet1 As Recordset
Set WgtDB = CodeDb

Set QD1 = WgtDB.QueryDefs!QRenumberSeq
QD1.Parameters![UserId] = gUserID
QD1.Parameters![Formula] = gFormula
QD1.Parameters![BegWgtRange] = gBegWgtRange
QD1.Parameters![EndWgtRange] = gEndWgtRange
QD1.Parameters![Plant] = gPlant
gSeqNum = 0
Set TempSet1 = QD1.OpenRecordset
Do Until TempSet1.EOF 'there is no weightmaster in process
TempSet1.Edit
gSeqNum = gSeqNum + 10
TempSet1!BCR_SEQ = gSeqNum
TempSet1!REC_TYP = "X" ' change rec typ temporarily to avoid seq # dups
TempSet1.Update
TempSet1.MoveNext ' Locate next record
Loop
Set QD1 = WgtDB.QueryDefs!QRenumberSeqUpdate ' reset rec typ to U
QD1.Parameters![UserId] = gUserID
QD1.Parameters![Formula] = gFormula
QD1.Parameters![BegWgtRange] = gBegWgtRange
QD1.Parameters![EndWgtRange] = gEndWgtRange
QD1.Parameters![Plant] = gPlant
QD1.Execute
End Function

 

[Mark Jones]

I didn't know you could do this!

Thanks!

Mark Jones