By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,623 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,623 IT Pros & Developers. It's quick & easy.

set bookmarks on main and subform

P: 59
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.microsoft.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 "frmEditContactNm".

Any assistance would be greatly appreciated!!

Bridget

Expand|Select|Wrap|Line Numbers
  1. 'Called from the "Done" button of the pop-up form "frmEditContactNm", 
  2. 'which was opened using the "edit" button on the main form's ("frmTabs")
  3. 'subform called "frmTabContacts".
  4.  
  5. Private Sub btnDone_Click()
  6.  
  7. 'start error check.
  8. On Error GoTo Err_btnDone_Click
  9.  
  10. 'list variables
  11. Dim rs As DAO.Recordset
  12. Dim rst As DAO.Recordset
  13. Dim intBldgID As Integer
  14. Dim intContactID As Integer
  15. Dim PopUpFrm As String
  16. Dim MainFrm As String
  17.  
  18. 'store IDs from Pop-up form as variables.
  19. '(The control txtBldgID is in a subform on the pop-up form sfrmContBldg and
  20. ' the control txtContactID is on the main pop-up form frmEditContactNm)
  21. intBldgID = Forms![frmEditContactNm]![sfrmContBldg].Form![txtBldgID]
  22. intContactID = Me.txtContactID
  23.  
  24. 'define pop-up form variable
  25. PopUpFrm = "frmEditContactNm"
  26.  
  27. 'close pop-up form
  28. DoCmd.Close acForm, PopUpFrm
  29.  
  30. 'define main form variable.
  31. MainFrm = "frmTabs"
  32.  
  33. 'open main form
  34. DoCmd.OpenForm MainFrm
  35.  
  36. 'set recordset clone on main form
  37. Set rs = Forms(MainFrm).Recordset.Clone
  38.  
  39. 'find record that matches stored ID
  40. rs.FindFirst "[BldgID] = " & intBldgID
  41.  
  42. 'set bookmark on main form to the record that matches the stored record from the pop-up form.
  43. Forms(MainFrm).Bookmark = rs.Bookmark
  44.  
  45. 'set focus to frmTabContacts
  46. Forms!frmTabs.frmTabContacts.SetFocus
  47.  
  48. 'find record that matches stored ID
  49. rst.FindFirst "[ContactID] = " & intContactID
  50.  
  51. 'set bookmark on main form to the record that matches the stored record from the pop-up form.
  52. Forms!frmTabs.frmTabContacts.Bookmark = rs.Bookmark
  53.  
  54. 'Exit the sub
  55. Exit_btnDone_Click:
  56.     Exit Sub
  57.  
  58. 'Exit the error check.
  59. Err_btnDone_Click:
  60.     MsgBox Err.Description
  61.     Resume Exit_btnDone_Click
  62.  
  63. End Sub
  64.  
Feb 27 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
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
Feb 27 '08 #2

P: 59
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
Feb 27 '08 #3

ADezii
Expert 5K+
P: 8,703
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.FindRecords to navigate to the correct Record in first the Main, and then the Sub_form.
Feb 27 '08 #4

P: 59
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.FindRecords 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
Feb 28 '08 #5

P: 59
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnDone_Click()
  2.  
  3. 'start error check.
  4. On Error GoTo Err_btnDone_Click
  5.  
  6. 'list variables
  7. Dim rs As DAO.Recordset
  8. Dim strWhere As String
  9. Dim strMain As String
  10. Dim strPopUp As String
  11.  
  12. 'FIRST FIND RECORDSET IN MAIN FORM:
  13.  
  14. 'capture the BldgID from the main form.
  15. strWhere = "BldgID = " & Forms!frmTabs.txtBldgID
  16.  
  17. 'define the main form variable.
  18. strMain = "frmTabs"
  19.  
  20. 're-open the main form.
  21. DoCmd.OpenForm strMain
  22.  
  23. 'set recordset clone on the main form.
  24. Set rs = Forms(strMain).Recordset.Clone
  25.  
  26. 'find record that matches stored ID.
  27. rs.FindFirst strWhere
  28.  
  29. 'set bookmark on main form to the record that matches the stored ID.
  30. Forms(MainFrm).Bookmark = rs.Bookmark
  31.  
  32. 'clear the recordset variable.
  33. Set rs = Nothing
  34.  
  35. 'SECOND FIND RECORDSET IN SUBFORM:
  36.  
  37.  
  38. 'capture the ContactID from the sub form.
  39. strWhere = "ContactID = " & Me.txtContactID
  40.  
  41. 'set recordset clone on the main form's subform.
  42. With Forms!frmTabs.frmTabContacts.Form
  43.   Set rs = .RecordsetClone
  44.  
  45. ''find record that matches stored ID.
  46.   rs.FindFirst strWhere
  47.      .Bookmark = rs.Bookmark
  48. End With
  49.  
  50. 'define the pop-up form variable.
  51. strPopUp = "frmEditContactNm"
  52.  
  53. 'close the pop-up form.
  54. DoCmd.Close acForm, strPopUp
  55.  
  56. 'Exit the sub
  57. Exit_btnDone_Click:
  58.     Exit Sub
  59.  
  60. 'Exit the error check.
  61. Err_btnDone_Click:
  62.     MsgBox Err.Description
  63.     Resume Exit_btnDone_Click
  64.  
  65. End Sub
  66.  
Feb 29 '08 #6

ADezii
Expert 5K+
P: 8,703
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnDone_Click()
  2.  
  3. 'start error check.
  4. On Error GoTo Err_btnDone_Click
  5.  
  6. 'list variables
  7. Dim rs As DAO.Recordset
  8. Dim strWhere As String
  9. Dim strMain As String
  10. Dim strPopUp As String
  11.  
  12. 'FIRST FIND RECORDSET IN MAIN FORM:
  13.  
  14. 'capture the BldgID from the main form.
  15. strWhere = "BldgID = " & Forms!frmTabs.txtBldgID
  16.  
  17. 'define the main form variable.
  18. strMain = "frmTabs"
  19.  
  20. 're-open the main form.
  21. DoCmd.OpenForm strMain
  22.  
  23. 'set recordset clone on the main form.
  24. Set rs = Forms(strMain).Recordset.Clone
  25.  
  26. 'find record that matches stored ID.
  27. rs.FindFirst strWhere
  28.  
  29. 'set bookmark on main form to the record that matches the stored ID.
  30. Forms(MainFrm).Bookmark = rs.Bookmark
  31.  
  32. 'clear the recordset variable.
  33. Set rs = Nothing
  34.  
  35. 'SECOND FIND RECORDSET IN SUBFORM:
  36.  
  37.  
  38. 'capture the ContactID from the sub form.
  39. strWhere = "ContactID = " & Me.txtContactID
  40.  
  41. 'set recordset clone on the main form's subform.
  42. With Forms!frmTabs.frmTabContacts.Form
  43.   Set rs = .RecordsetClone
  44.  
  45. ''find record that matches stored ID.
  46.   rs.FindFirst strWhere
  47.      .Bookmark = rs.Bookmark
  48. End With
  49.  
  50. 'define the pop-up form variable.
  51. strPopUp = "frmEditContactNm"
  52.  
  53. 'close the pop-up form.
  54. DoCmd.Close acForm, strPopUp
  55.  
  56. 'Exit the sub
  57. Exit_btnDone_Click:
  58.     Exit Sub
  59.  
  60. 'Exit the error check.
  61. Err_btnDone_Click:
  62.     MsgBox Err.Description
  63.     Resume Exit_btnDone_Click
  64.  
  65. End Sub
  66.  
Nice job, and thanks for posting the solution.
Feb 29 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.