Solved Find next sequential number (1 Viewer)

[Eljefegeneo]

I have the following code to search for a record based on its record number. What I want to do is modify the code so that if a certain number is not found I want to open the next sequential record. I.e. if record number 80 is not found, then open the next record in sequence which may be 85. (I am using late binding)

Dim lngPK As Long
lngPK = Me.NumberID
 Me.Requery
   Me.FilterOn = False
With Me.RecordsetClone
   .FindFirst "ID = " & lngPK
      If .NoMatch Then
         MsgBox "Record not found!", vbCritical
Else
  Me.Bookmark = .Bookmark
     End If
         End With

 

[Gasman]

Set up a loop for .FindFirst until NOT .Nomatch or NumberID = DMAX(NumberID) ?
Increment NumberID within the loop.

HTH

 

[theDBguy]

Hi. Are you saying you want to create a new record if what you're looking for does not exist? If so, you might be able to just set the default value for the next sequential number.

 

[Pat Hartman]

What you are doing doesn't make sense in the world of relational databases. Tables and queries are unordered sets of data. The only time they have an order is if you specifically order them in a query.

So for starters, if your form is bound to a table, this search will ultimately fail. Your form MUST be bound to a query and the query MUST have an order by clause if you ever expect to do sequential processing on its recordset.

If you can explain the business logic of what you are trying to do, we can probably offer a "database" solution rather than the "flat file" solution you are trying to use.

 

[Eljefegeneo]

I think I almost have it except for one glaring error. The following will open the next sequential record that exists but unfortunately doesn't stop there. It gets hung up and seems to be in an endless loop. (Both the table and the query result in the same thing – either one will give me the next sequential record). Record 80 does not exist but 84 does so I want it to open 84 if I select 80 as the first reference number.

How do I stop the code when it opens the next sequential record?
This code is on the frmNewNames so the form is already open.

Dim dbs As Object
Dim rst As Object
Dim lngPK As Long
lngPK = Me.NumberID
Set dbs = CurrentDb
'Set rst = dbs.OpenRecordset("qryNewNamesSequential")
Set rst = dbs.OpenRecordset("tblNewNames")
With rst
  Do While Not .EOF
     .FindFirst "ID = " & lngPK
        DoCmd.OpenForm "frmNewNames", , , "ID = " & lngPK

If .NoMatch Then
   lngPK = lngPK + 1
     End If
       Loop
         End With
rst.Close
Set rst = Nothing
Set dbs = Nothing

 

[Eljefegeneo]

Got it! Alt least if does what I want it to do.

Dim dbs As Object
Dim rst As Object
Dim lngPK As Long
Dim booValueFound As Boolean
'booValueFound = False
lngPK = Me.NumberID
Set dbs = CurrentDb
'Set rst = dbs.OpenRecordset("qryNewNamesSequential")
Set rst = dbs.OpenRecordset("tblNewNames")
With rst
Do While Not .EOF
.FindFirst "ID = " & lngPK
If Not IsNull(DLookup("ID", "tblNewNames", "ID = " & lngPK)) Then
booValueFound = True
Else: booValueFound = False
End If

If .NoMatch Then
lngPK = lngPK + 1
Else
DoCmd.OpenForm "frmNewNames", , , "ID = " & lngPK
End If

If booValueFound = True Then Exit Do

Loop

End With
rst.Close
Set rst = Nothing
Set dbs = Nothing

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out. Cheers!

 

[Solo712]

Got it! Alt least if does what I want it to do.

FYI, you could have used the DMin domain aggregate function and save yourself unnecessary coding:

Nz(DMin("ID", "tblNewNames", "ID >= " & Me.NumberID))

would give you the requisitioned or the next record number in sequence.

Best,
Jiri