Bookmark not working

[Ashleyjp1985]

Hi,

i have a form with a nested subform that are opened from a mainform.

When the user selects a specific record on the main form i want the form to open and the subform to display the selected record. I am using the below code on the load event of the form:

when i first run it it works! the nested sub from displays the correct record and did so for the next few that i tried. There were 10 related records in this example and after number 4 it stopped working. When the form opened the subform very briefly went to the correct record then jumped to add a new record. Any help????

Dim rs As Object
Dim lngBookmark As Long

lngBookmark = Me.SIID

Set rs = Me.SubformPopformQCustomerSI.Form.RecordsetClone
rs.FindFirst "SIID = " & lngBookmark

Me.SubformPopformQCustomerSI.Form.Bookmark = rs.Bookmark

Set rs = Nothing

 

[Beetle]

Is there some reason you're doing this instead of using the Master/Child link? What am I missing here?

 

[Ashleyjp1985]

Hi, thanks for the response. If I use the master child link it does work but it doesn't allow the user to cycle through the related records. They only get record 1 of 1 rather than e.g 1 of 20.

 

[Beetle]

Just to clarify, you're saying that when you have the main form/sub form set up with a Master/Child link, and you select a given record on the main form, the sub form only shows one related record even though multiple related records exist?

 

[Ashleyjp1985]

Hi,

Correct. If the user selects related record 20 of 30 then the subform will only show record 20 with master/link selected. The user can not cycle through the other 29 records.

 

[Beetle]

OK, if I understand correctly, you're saying that when a record is selected in the main form you want the sub form to move to the related record, but you still want the user to be able to view and cycle through all the other non-related records as well?

Is there only one related record in the sub form for each record in the main form (which would make one wonder why the need for a sub form)?

 

[Ashleyjp1985]

Hi thank for baring with em on this,

my database has 3 tables: customer details, customer business and customer business interactions

a customer can have many businesses and a business can have many interactions. when the user clicks on the record id of a business interaction they want to open on the main form a pop up form opens. the parent part of this pop up form is based on customer details and customer business and showsthe customer name and business ID. the subform part is based on business interactions showing all the interaction details for the selected record id.

the parent form restricts the subform to specific a customer and business by master/child link but i want the subform to open at the selected record id but with the option of cycling through the other related interactions for that specific customers business.

please see first post for how far i have got with this and the error i am getting.

 

[Beetle]

Well, I'm still not 100% percent sure exactly what you need, but based on what I think, you might try this.

First, when you open the pop up form, pass the ID of the current record via OpenArgs;

DoCmd.OpenForm "YourPopUpForm", , , , , , Me.RecordID

Then, in the Open event of the pop up form, grab the value from OpenArgs and use it to determine the bookmark for the sub form;

Private Sub Form_Open (Cancel As Integer)

Dim lngRecordID As Long

If Not IsNull(Me.OpenArgs) Then
    lngRecordID = Me.OpenArgs
    With Me.SubformPopformQCustomerSI.Form.RecordsetClone
        .FindFirst "SSID=" & lngRecordID
        If Not .NoMatch Then
            Me.SubformPopformQCustomerSI.Form.Boomark = .Bookmark
        End If
    End With
End If

End Sub

You'll need to adjust some of the above for the correct object naming, etc.

 

[Ashleyjp1985]

Hi,

I'm already using openargs on the form. I pass two variables as one (bpid and ssid) and then split it. The bpid works fine, I use it as part of the master/child link to restrict the subform to the particular business. I just can't get the subform to go to the correct ssid record. Well as per my first port it does for a split second then jumps to the add new record page.

 

[Ashleyjp1985]

Hi All,

have tried above suggestion but still no joy. Please see my complete code for the open event of the parent form. it nearly works but for some reason once its found the selected record it jumps to the end of the recordset to the add new record.

Dim bpidopar As Long
Dim siidopar As Long
Dim opars() As String


If Len(Me.OpenArgs & "") > 0 Then
opars = Split(Me.OpenArgs, "|")
bpidopar = opars(0)
siidopar = opars(1)
Me.BPID = bpidopar
Me.SIID = siidopar
End If

Dim rs As DAO.Recordset
Dim lngBookmark As Long

'set a variable to the current record
lngBookmark = Me.SIID


Set rs = Me.SubformPopformQCustomerSI.Form.RecordsetClone
rs.FindFirst "[SIID] = " & lngBookmark

Me.SubformPopformQCustomerSI.Form.Bookmark = rs.Bookmark
rs.Close

Set rs = Nothing

 

[Beetle]

Can you post a copy of the app that can duplicate the problem with some dummy data?