How do I move to the next record when searching for a specific record.

[CowCamp]

I have a main form (Cemetery) in this form I have a Map overlayed with a Grid, a ListBox (lstPeople) and a Button (btnEditPeople).
The user can click on a person in the Cemetery form List Box and a Location Box will show where the person is buried.


If the user clicks on the Edit Person button a form will come up showing all the people in the Table (tblPeople). The form shows one person at a time.
I am trying to get the Edit People form to show the person that the user clicked on in the Cemetery form.
I know how to pass the Key Field for the chosen record from the Cemetery form to the Edit People form. That works OK.
The key field in the table tblPeople is (autID). This is set automatically when a person is added to the table.
The first thing I did was to make a holding variable, (numTempID = OpenArgs).
Next I tested the variable (numTempID) to make sure that it is larger than “0”.
Then I tested to see if numTempID is equal to the key field of the current record Me.autID
NOW!! Here is my problem!! How do I “Move” to the “Next” record?
My code:

Private Sub Form_Load()
    Dim numTempID As Integer
    Dim txtPersonName As String
       
    If IsNull(OpenArgs) Then            'OpenArgs is the ID number for the record I want, ID is key field.
        strOpenArgument = ""
        numTempID = 0                         'If no OpenArgs then Temp ID is set to 0
    Else
        numTempID = Val(OpenArgs)       'Temp ID is set to OpenArgs
    End If
   
                                                                           'Person Name is set to current record to show the table is open (A message box that will be taken out when the code works)
                txtPersonName = Me.txtLastName & "," & Me.txtFirstName & " " & Me.txtMiddleName
                                                                                              
                'MsgBox "Open Arguments = " & Str(numTempID)        'msgbox displays the current records autID - Key Field
 
    If numTempID > 0 Then                                         'Test to see if Temp ID is larger than "0"
        Do While numTempID <> Me.autID                  'Test to see if current record is same as search record

----->  'Move to the next record                    ‘<-----  How do I move to the next record?

            MsgBox "Persons ID = " & Str(Me.autID)      'Show the current autID number
        Loop                                                              'Loop if Search numTempID is not equal to current record Me.autID
    End If

[Gicu]

You don't want to loop, check the code in this link, it is what the built in Access wizard uses for a "searching" combo box (using the FindFirst method of the form's recordsetclone).
https://learn.microsoft.com/en-us/of...recordsetclone
Cheers,

[CowCamp]

Gicu, that works thanks.

[Gicu]

Cheers,

[CowCamp]

Gicu; I do have a question!
- Are you saying that there is no command "to move to the next record" in my situation?
- or is your suggestion just the best choice.

Note- Nice dog!

[orange]

CowCamp,

See if this helps.

[Welshgasman]

Gicu; I do have a question!
- Are you saying that there is no command "to move to the next record" in my situation?
- or is your suggestion just the best choice.

Note- Nice dog!

Of course there is, acNext on gotorecord
https://learn.microsoft.com/en-us/of...cmd.gotorecord

[CowCamp]

CowCamp,

See if this helps.

Yes I tried it but couldn't make it work. Probably my fault.

CowCamp

[Gicu]

There are many ways to navigate to the desired record, the one I showed you first being one of the most used; you can also use Docmd.FindRecord after you set the focus on the control holding your unique id, Docmd.SearchForRecord, etc.

It looked like you wanted to loop through all the records and compare the ID to the one passed in the OpenArgs and stop when they are equal and I was just saying that, while that would work, would not be very efficient.

Cheers,

[CowCamp]

Gicu,
Thanks for the help.

CowCamp

[orange]

CowComp,

What is latest status?
Are you:
1- trying to read all records in a recordset and identify those with specific attributes, or
2- searching for records in a recordset that have some specific attribute.

If 1, then read , check for specific term and movenext and repeat.

If 2, then findfirst,then findnext and repeat findnext.


As vlad/gicu said, 2 is more efficient if search is your goal.

[CowCamp]

Orange,
I was trying to get to a specific record buy reading each record for a matching "ID", then to display it in a form.

The problem is solved. I used the "Recordsetclone" command and everything worked out well.

Thank you for your help.

CowCamp