set bookmarks on main and subform

[banderson]

I have one additional question, please humor me if you have the time! :)

I'm not sure if this is even possible, but for part of this project I would like to set two bookmarks - one to show a specific record on my main form, and another to show a specific record on a subform.

The main form shows Building information and the subform shows Contact information for that building (a one to many relationship). I click on an "edit" button on the main form to open a pop-up form for data entry about the building. When I click on the "done" button on this pop-up form, the code above is used in the On-Click event to go back to the specific record on the main form. To edit the contact information I click on an "edit" button on the contacts subform to open a pop-up form for data entry about the contact. When closing this pop-up, I would like to return to the specific building record on the main form, and also go to the specific contact on the subform for which I was entering data in the pop-up form.


I tried to adjust the code above to add a second bookmark, but am getting the error "Object variable or With block variable not set". I've posted the code below. I think the problem might be in in second "FindFirst" line of code, but I'm not sure. I tried to look at the explanation for this bug on support.microso ft.com/kb/316478, but have to admit not understanding it :(

The main form is "frmTabs", the subform is "frmTabContacts ", the pop-up form in this case is "frmEditContact Nm".

Any assistance would be greatly appreciated!!

Bridget

'Called from the "Done" button of the pop-up form "frmEditContactNm", 
'which was opened using the "edit" button on the main form's ("frmTabs")
'subform called "frmTabContacts".

Private Sub btnDone_Click()

'start error check.
On Error GoTo Err_btnDone_Click

'list variables
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Dim intBldgID As Integer
Dim intContactID As Integer
Dim PopUpFrm As String
Dim MainFrm As String

'store IDs from Pop-up form as variables.
'(The control txtBldgID is in a subform on the pop-up form sfrmContBldg and
' the control txtContactID is on the main pop-up form frmEditContactNm)
intBldgID = Forms![frmEditContactNm]![sfrmContBldg].Form![txtBldgID]
intContactID = Me.txtContactID

'define pop-up form variable
PopUpFrm = "frmEditContactNm"

'close pop-up form
DoCmd.Close acForm, PopUpFrm

'define main form variable.
MainFrm = "frmTabs"

'open main form
DoCmd.OpenForm MainFrm

'set recordset clone on main form
Set rs = Forms(MainFrm).Recordset.Clone

'find record that matches stored ID
rs.FindFirst "[BldgID] = " & intBldgID

'set bookmark on main form to the record that matches the stored record from the pop-up form.
Forms(MainFrm).Bookmark = rs.Bookmark

'set focus to frmTabContacts
Forms!frmTabs.frmTabContacts.SetFocus

'find record that matches stored ID
rst.FindFirst "[ContactID] = " & intContactID

'set bookmark on main form to the record that matches the stored record from the pop-up form.
Forms!frmTabs.frmTabContacts.Bookmark = rs.Bookmark

'Exit the sub
Exit_btnDone_Click:
    Exit Sub
    
'Exit the error check.
Err_btnDone_Click:
    MsgBox Err.Description
    Resume Exit_btnDone_Click

End Sub

[Stewart Ross]

Hi Bridget. You have not set a value for your second Recordset - rst (a confusing choice of name when the other one is called rs) - hence the object not set error. Because of the missing Set statement you have not actually cloned the subform's recordset at all so far.

I'm not sure that when you fix all this it will do what you want it to do, as subforms are normally bound to a value in the main form, which I think will work against trying to go to a specific record in the subform using recordsetclone, but feel free to experiment here!

-Stewart

[banderson]

Hi Bridget. You have not set a value for your second Recordset - rst (a confusing choice of name when the other one is called rs) - hence the object not set error. Because of the missing Set statement you have not actually cloned the subform's recordset at all so far.

I'm not sure that when you fix all this it will do what you want it to do, as subforms are normally bound to a value in the main form, which I think will work against trying to go to a specific record in the subform using recordsetclone, but feel free to experiment here!

-Stewart

Thank you for your response, Stewart. I realized the error in not having set a value for the second recordset after posting this. Duh! (and yes, rs vs rst is not very clear - I switched it to "rsm" and "rss" for rs main and rs sub respectively - not much better, but...)

Unfortunately, I still get an error. I'm wondering the same thing as you, whether or not its possible to do a double bookmark - one for a form and another for the subform... I will keep experimenting and post if I come up with a solution. In the mean time, if anyone has other ideas of how I can go to a specific record on a subform of a specific record on a main form, let me know!!
thx
Bridget

[ADezii]

Thank you for your response, Stewart. I realized the error in not having set a value for the second recordset after posting this. Duh! (and yes, rs vs rst is not very clear - I switched it to "rsm" and "rss" for rs main and rs sub respectively - not much better, but...)

Unfortunately, I still get an error. I'm wondering the same thing as you, whether or not its possible to do a double bookmark - one for a form and another for the subform... I will keep experimenting and post if I come up with a solution. In the mean time, if anyone has other ideas of how I can go to a specific record on a subform of a specific record on a main form, let me know!!
thx
Bridget

You could always use Public Variables initialized in the btnDone_Click() Event to store relevant values in both the Main and Sub-Forms, then use a pair of DoCmd.FindRecor ds to navigate to the correct Record in first the Main, and then the Sub_form.

[banderson]

You could always use Public Variables initialized in the btnDone_Click() Event to store relevant values in both the Main and Sub-Forms, then use a pair of DoCmd.FindRecor ds to navigate to the correct Record in first the Main, and then the Sub_form.

Thanks for the idea, ADezii! I will give this a try.

Bridget

[banderson]

Thanks for the idea, ADezii! I will give this a try.

Bridget

Hello all,
Before trying ADezii's idea, I gave the bookmark idea one more try and... I got it to work! (amazing - I almost feel like I'm starting to get this. At least until the next hiccup!)

I'm posting the code for people's reference. (A huge thank you to Allen Browne - I was able to use a suggestion made by him on another website and adjust it to make it work for me)

The code below is to set two bookmarks, one on a main form and the second on a subform on the main form. The code is called from the OnClick event of a Pop up form (opened by clicking on a button on the main form). The code first captures the current record (BldgID) from the main form and bookmarks it. Then it captures the current record (ContactIID) from the popup form and bookmarks this record on the main form's subform.
I hope it is of use to others.
Bridget

Private Sub btnDone_Click()

'start error check.
On Error GoTo Err_btnDone_Click

'list variables
Dim rs As DAO.Recordset
Dim strWhere As String
Dim strMain As String
Dim strPopUp As String

'FIRST FIND RECORDSET IN MAIN FORM:

'capture the BldgID from the main form.
strWhere = "BldgID = " & Forms!frmTabs.txtBldgID

'define the main form variable.
strMain = "frmTabs"

're-open the main form.
DoCmd.OpenForm strMain

'set recordset clone on the main form.
Set rs = Forms(strMain).Recordset.Clone

'find record that matches stored ID.
rs.FindFirst strWhere

'set bookmark on main form to the record that matches the stored ID.
Forms(MainFrm).Bookmark = rs.Bookmark

'clear the recordset variable.
Set rs = Nothing

'SECOND FIND RECORDSET IN SUBFORM:


'capture the ContactID from the sub form.
strWhere = "ContactID = " & Me.txtContactID

'set recordset clone on the main form's subform.
With Forms!frmTabs.frmTabContacts.Form
  Set rs = .RecordsetClone

''find record that matches stored ID.
  rs.FindFirst strWhere
     .Bookmark = rs.Bookmark
End With

'define the pop-up form variable.
strPopUp = "frmEditContactNm"

'close the pop-up form.
DoCmd.Close acForm, strPopUp

'Exit the sub
Exit_btnDone_Click:
    Exit Sub
    
'Exit the error check.
Err_btnDone_Click:
    MsgBox Err.Description
    Resume Exit_btnDone_Click

End Sub

[ADezii]

Hello all,
Before trying ADezii's idea, I gave the bookmark idea one more try and... I got it to work! (amazing - I almost feel like I'm starting to get this. At least until the next hiccup!)

I'm posting the code for people's reference. (A huge thank you to Allen Browne - I was able to use a suggestion made by him on another website and adjust it to make it work for me)

The code below is to set two bookmarks, one on a main form and the second on a subform on the main form. The code is called from the OnClick event of a Pop up form (opened by clicking on a button on the main form). The code first captures the current record (BldgID) from the main form and bookmarks it. Then it captures the current record (ContactIID) from the popup form and bookmarks this record on the main form's subform.
I hope it is of use to others.
Bridget

Private Sub btnDone_Click()

'start error check.
On Error GoTo Err_btnDone_Click

'list variables
Dim rs As DAO.Recordset
Dim strWhere As String
Dim strMain As String
Dim strPopUp As String

'FIRST FIND RECORDSET IN MAIN FORM:

'capture the BldgID from the main form.
strWhere = "BldgID = " & Forms!frmTabs.txtBldgID

'define the main form variable.
strMain = "frmTabs"

're-open the main form.
DoCmd.OpenForm strMain

'set recordset clone on the main form.
Set rs = Forms(strMain).Recordset.Clone

'find record that matches stored ID.
rs.FindFirst strWhere

'set bookmark on main form to the record that matches the stored ID.
Forms(MainFrm).Bookmark = rs.Bookmark

'clear the recordset variable.
Set rs = Nothing

'SECOND FIND RECORDSET IN SUBFORM:


'capture the ContactID from the sub form.
strWhere = "ContactID = " & Me.txtContactID

'set recordset clone on the main form's subform.
With Forms!frmTabs.frmTabContacts.Form
  Set rs = .RecordsetClone

''find record that matches stored ID.
  rs.FindFirst strWhere
     .Bookmark = rs.Bookmark
End With

'define the pop-up form variable.
strPopUp = "frmEditContactNm"

'close the pop-up form.
DoCmd.Close acForm, strPopUp

'Exit the sub
Exit_btnDone_Click:
    Exit Sub
    
'Exit the error check.
Err_btnDone_Click:
    MsgBox Err.Description
    Resume Exit_btnDone_Click

End Sub

Nice job, and thanks for posting the solution.