goto next record

[exaccess]

Hello to All,
I execute a query, results are in a multirecord continuous form. User clicks a button in a row to open a form with all the details of the record in the row. Here is code:
'stLinkCriteria = "[NOM]=" & "'" & Me![NOM] & "'" & "And" & "[PRENOM]=" & "'" & Me![PRENOM] & "'"
'DoCmd.OpenForm stDocName, , , stLinkCriteria
New form opens up displaying the detailed record. All fine up to here. Now I wish to have a NEXT button to go to the next detail record without going back to the continuous form. How can I do that? Please advise. Thanks.

 

[Catalina]

I use the bookmark method to open a detail form when clicking on a row
in a continuous form:

    Dim rs As Object
    Dim lngBookmark As Long

    'set a variable to the current record
    lngBookmark = Me.PEID 'Unique Record ID
    'open the new form
    DoCmd.OpenForm "frmPeopleEdit"

    'take it to the selected record
    Set rs = Forms!frmPeopleEdit.RecordsetClone
    rs.FindFirst "PE_ID = " & lngBookmark
    Forms!frmPeopleEdit.Bookmark = rs.Bookmark

    Set rs = Nothing
    Forms!frmPeopleEdit!fldHidden.SetFocus

In the detail form I can use navigation buttons like:

        DoCmd.GoToRecord , , acNext

Catalina

 

[exaccess]

Thanks for the response. Firstly some clarification. Do I have to insert only the last statement in the detail form? Secondly I do not have a unique record ID. Instead I use the concatenation of NOM and PRENOM to uniquely identify records. This combined key is text. I can not use lngBookmark. Can I have some clarification please?

 

[Catalina]

The easiest way to identify an unique record is to include an autonumber field in the table.

Be sure to include that field in the row in the continuous form, you can make it invisible if
you want to.

Then in the on click event of every field call a public sub. Like:

Call SelectRecord

Then place the code I gave you in a public sub (edited to match the form and fields in your db of course).

Public Sub SelectRecord()

'Open the Contact form and go to the record selected here

    Dim rs As Object
    Dim lngBookmark As Long

    'set a variable to the current record
    lngBookmark = Me.PEID
    'open the new form
    DoCmd.OpenForm "frmPeopleEdit"

    'take it to the selected record
    Set rs = Forms!frmPeopleEdit.RecordsetClone
    rs.FindFirst "PE_ID = " & lngBookmark
    Forms!frmPeopleEdit.Bookmark = rs.Bookmark

    Set rs = Nothing
    Forms!frmPeopleEdit!fldHidden.SetFocus

End Sub

 

[exaccess]

Here is an improved version of how I sorted out the problem. This is the code in the continuous form displaying the results:

Private Sub Sbox_BeforeUpdate(Cancel As Integer)
    Dim ForName As String
    Dim ParName As String
    ParName = Me.Form.Name
    Select Case Me.Sbox.Value
        Case "Address Data"
            ForName = "AddressFm"
            Call SboxSelectForm(ForName, ParName)
        Case "Financial Data"
            ForName = "FinancialFm"
            Call SboxSelectForm(ForName, ParName)
        Case "Details Data"
            ForName = "Members_DetailsFm"
            Call SboxSelectForm(ForName, ParName)
        Case "Personal Data"
            ForName = "PersonalFm"
            Call SboxSelectForm(ForName, ParName)
    End Select
End Sub

This is the code in a module:

Public Sub SboxSelectForm(ForName As String, ParName As String)
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim rsc As Recordset
    'open form
    DoCmd.OpenForm ForName
    'take it to the selected record
    
    Set rsc = Forms(ForName).RecordsetClone
    rsc.FindFirst ("NOM" & "=" & "'" & Forms(ParName).NOM.Value & "'" & " And " & _
                   "PRENOM" & "=" & "'" & Forms(ParName).PRENOM.Value & "'")
    Forms(ForName).Bookmark = rsc.Bookmark
    Set rsc = Nothing
    Forms(ForName).SetFocus
End Sub

This is the code that I placed in the 4 detailed forms:

DoCmd.GoToRecord , , acNext

Basically it works. However there are some problems that need to be ironed out.
1. If the user reaches the first record of the list the program continues displaying records that are alphabetically beyond the scope of the query.
2. In some cases the sort order is not respected.
I am trying to solve these points. Any help will be highly appreciated.

 

[exaccess]

I have the public sub below called by various continuous forms displaying results of queries one row per record. This sub finds the corrosponding record and displays the details in a form ForName. ParName is the calling sub. Record is found and displayed and next and previous commands work. But when the form ForName reaches the end of the query it continues with other records of the table. I checked the number of records in recordsetclones of ParName and ForName. They are different. How can I have the ForName recordset have the same recordset property as ParName. Please advise.

Public Sub SboxSelectForm(Forname As String, ParName As String)
    Dim Frsc As Recordset
    DoCmd.OpenForm Forname
    Set Frsc = Forms(Forname).RecordsetClone
    Frsc.FindFirst ("NOM" & "=" & "'" & Forms(ParName).NOM.Value & "'" & " And " & _
                   "PRENOM" & "=" & "'" & Forms(ParName).PRENOM.Value & "'")
    
    Forms(Forname).Bookmark = Frsc.Bookmark
    Set Frsc = Nothing
    Forms(Forname).SetFocus
    G_For = Forname
    G_Par = ParName

End Sub

 

[exaccess]

No advice from anyone? In fact I am just trying to display details of a query result one by one. If I force the user to close the detail form, go back to the result form and select the next row the problem is solved, but it is cumbersome for the user. There must be an easy way to implement this. Please help.

 

[boblarson]

First off, your code seems a little convoluted as well as your process. A series of screenshots would be helpful to see what you actually have and how you intend to interact. Just trying to read through it makes my head hurt.

But one thing that I can spot right away in your code is that you are trying to set a bookmark of one form to the bookmark of another. The problem is that bookmarks are only relative positions of a record within A SPECIFIC recordset. Once the form is closed, that bookmark may no longer be applicable for the same record when the form is reopened. And so trying to use the bookmark from another form to set one in a completely different form is not the way they were designed to work. You would need to do a FindFirst ON EACH FORM and you use the PK ID as the value to search for, not the bookmark.

 

[exaccess]

Thanks. At least someone has read the post. Unfortunately I can not publish any screenshots here, but let me be more precise:
1. User runs a query such as list all employees whose names start with F.
2. 35 employees satisfy the query.
3. A continuous form named PARNAME displays results. One row per employee. Employee's name, first name, birthdate are displayed.
4. User wants to see address of an employee. On the row there is a combobox with available detail form names. One of the forms is called ADDRESS. User clicks this combobox entry.
5. Form ADDRESS opens up. Displays name, first name, address,etc of the employee.
6. User hits NEXT button on ADDRESS form and sees the address of the following employee. So he keeps hitting NEXT button.
7. When user reaches end of the list the program should stop. BUT NO IT GOES ON DISPLAYING OTHER EMPLOYEES. This is the problem.
As to bookmarks I took them out of the code, because as you say they do not serve the purpose. I do not have a unique id for the employees. Also I have changed previous posts by making code clearer. Help is welcome.