when close a Pop-up form, keep main form on same record

[banderson]

Hello,
I know I've seen an answer to this question before, but I cannot seem to find it again. After searching for a few hours, I've decided to re-post in hopes someone can give advice and/or send me to an existing thread...

I have a main form with a number of subforms. On each subform there is a button that opens a Pop-up form where users can enter data. When I close the Pop-up form, however, the mainform/subform jump to the first record rather than staying at the same record. I know I should bookmark a recordset clone, but I am having trouble with the code because I'm going between two different forms. I want to save the ID from the Pop-up form and then find the record with that ID in the main form. I have a button that closes the Pop-up and figure I should add this code to the On_Click event, no?

I am trying to learn VBA, but am very slow, so if someone can recommend appropriate code, I would be very appreciative!

Thanks so much,
Bridget

[Delerna]

I have a button that closes the Pop-up and figure I should add this code to the On_Click event, no?

Yes
One thing you could try is a public variable on your main form and then as you close the popup form you could set that variable to equal the ID from the popup.
Then you arrange so that when the mainform gets refreshed it uses the contents of the public variable to move to the correct record.

or another, probably better, possibility would be to add a public parameterised subroutine on your mainform. That way, in the on click event of the button you call that public sub passing the id in the parameter.

Does that help?

[banderson]

Hello Delerna,
Thank you for your reply!
Yes these options make sense. I am not very good with code however. (relatively new to Access). Would be able to suggest some code to implement this public variable / sub?

Bridget

Yes
One thing you could try is a public variable on your main form and then as you close the popup form you could set that variable to equal the ID from the popup.
Then you arrange so that when the mainform gets refreshed it uses the contents of the public variable to move to the correct record.

or another, probably better, possibility would be to add a public parameterised subroutine on your mainform. That way, in the on click event of the button you call that public sub passing the id in the parameter.

Does that help?

[Delerna]

yes here is a question very similar to yours.
Although you are going to need to use GotoRecord or something like that.

Have a go and if you have trouble post back here with what you have tried and what problem you are having and either I or someone else will be glad to assist.

PS Don't forget the help menu, theres some great stuff in there for learning the basics

[banderson]

yes here is a question very similar to yours.
Although you are going to need to use GotoRecord or something like that.

Have a go and if you have trouble post back here with what you have tried and what problem you are having and either I or someone else will be glad to assist.

PS Don't forget the help menu, theres some great stuff in there for learning the basics

Thank you Delerna! I will check that post. And thank you for the reminder about using Help!
Best,
Bridget

[banderson]

Thank you Delerna! I will check that post. And thank you for the reminder about using Help!
Best,
Bridget

Okay, I have made some progress. :)
However I'm still not quite able to do what I want... :(

I am able to open the main form and filter on the record currently displayed in teh pop up form. However, rather than filter, I would like to just go to that record.
Below (first) is the code that works to filter on the correct record and (Second)my attempt at using a recordset clone that did not work. I'm sure I'm doing something simple wrong. Any advice would be greatly appreciated!
Bridget

This code is in the "Done" button of the pop-up form.
In both forms, the objects are called txtBldgID and the control source is BldgID.

FIRST

Private Sub btnDone_Click()
On Error GoTo Err_btnDone_Click
 
'opens main form and filters on record displayed in pop-up form
DoCmd.OpenForm "frmTabs", , , "[BldgID] = " & Me.txtBldgID
 
'closes pop-up form
DoCmd.Close acForm, "frmAddBldgNm", acSaveYes
 
Exit_btnDone_Click:
    Exit Sub
 
Err_btnDone_Click:
    MsgBox Err.Description
    Resume Exit_btnDone_Click
End Sub
 

SECOND

Private Sub btnDone_Click()
On Error GoTo Err_btnDone_Click
 
'opens main form and filters on record displayed in pop-up form
' DoCmd.OpenForm "frmTabs", , , "[BldgID] = " & Me.txtBldgID
 
'closes pop-up form
' DoCmd.Close acForm, "frmAddBldgNm", acSaveYes
 
   Dim rs As Object
   Dim intBldgID As Integer
 
   intBldgID = Me.txtBldgID
 
   DoCmd.OpenForm "frmTabs"
 
   Set rs = Me.Recordset.Clone
   rs.FindFirst "[BldgID] = " & intBldgID
 
   DoCmd.Close acForm, "frmAddBldgNm", acSaveYes
 
Exit_btnDone_Click:
    Exit Sub
 
Err_btnDone_Click:
    MsgBox Err.Description
    Resume Exit_btnDone_Click
End Sub
 

[Stewart Ross]

...Below (first) is the code that works to filter on the correct record and (Second)my attempt at using a recordset clone that did not work. I'm sure I'm doing something simple wrong.

Hi. There are only a few changes to your code needed to make the bookmark property work for you:

Dim rs As DAO.Recordset
Dim intBldgID As Integer
Dim FrmName as String
intBldgID = Me.txtBldgID
FrmName = "frmTabs" 
DoCmd.OpenForm FrmName
Set rs = forms(FrmName).Recordset.Clone
rs.FindFirst "[BldgID] = " & intBldgID
Forms(FrmName).Bookmark = rs.bookmark
 

You were cloning the wrong recordset - the one from your pop-up instead of frmTabs - and you weren't setting the bookmark property in frmTabs to match the record found.

I assume there will always be a match between the pop-up form and the main form. If there is the possibility that there won't be a match this can be tested using

If rs.nomatch then...

-Stewart

[banderson]

Hi. There are only a few changes to your code needed to make the bookmark property work for you:

Dim rs As DAO.Recordset
Dim intBldgID As Integer
Dim FrmName as String
intBldgID = Me.txtBldgID
FrmName = "frmTabs" 
DoCmd.OpenForm FrmName
Set rs = forms(FrmName).Recordset.Clone
rs.FindFirst "[BldgID] = " & intBldgID
Forms(FrmName).Bookmark = rs.bookmark
 

You were cloning the wrong recordset - the one from your pop-up instead of frmTabs - and you weren't setting the bookmark property in frmTabs to match the record found.

I assume there will always be a match between the pop-up form and the main form. If there is the possibility that there won't be a match this can be tested using

If rs.nomatch then...

-Stewart

It works beautifully. A thousand thank-you's! I had come up with a more clunky set of code that also worked, but this processes much faster and requires less code. For the forum's reference, I am posting the complete OnClick event code for the "Close" button of the Pop-Up form (FIRST) - based on Stewart's suggestion above - along with the alternate code that I had come up with (SECOND).

Again, thank you!!
Bridget

FIRST

Private Sub btnDone_Click()
'Start error handling code.
On Error GoTo Err_btnDone_Click
 
'Define variables.
Dim rs As DAO.Recordset
Dim intBldgID As Integer
Dim PopUpFrm As String
Dim MainFrm As String
 
'Store ID from pop-up form as variable.
intBldgID = Me.txtBldgID
 
'Define PopUpFrm name and close Pop-up form.
PopUpFrm = "frmAddBldgNm"
DoCmd.Close acForm, PopUpFrm
 
'Define MainFrm name and open main form.
MainFrm = "frmTabs"
DoCmd.OpenForm MainFrm
 
'Set recordset clone.
Set rs = Forms(MainFrm).Recordset.Clone
 
'Locate stored ID in the recordset.
rs.FindFirst "[BldgID] = " & intBldgID
 
'Move the bookmark on the main form to the stored ID bookmark.
Forms(MainFrm).Bookmark = rs.Bookmark
 
'Exit the sub
Exit_btnDone_Click:
    Exit Sub
 
'Exit the error handling.
Err_btnDone_Click:
    MsgBox Err.Description
    Resume Exit_btnDone_Click
 
End Sub
 

SECOND

'ALTERNATE CODE THAT WORKS, BUT IS NOT AS SLICK AS THE CODE ABOVE BASED ON THE POST BY STEWART.
Private Sub btnDone_Click()
'Start the error check.
On Error GoTo Err_btnDone_Click
 
'Store the main form name as a string variable.
Dim stDocName As String
stDocName = "frmTabs"
 
'Store the pop-up form's ID as a variable.
strBldgID = Me!txtBldgID
 
'Close the pop-up Form
DoCmd.Close acForm, "frmAddBldgNm", acSaveYes
 
'Open the main form and set the OpenArgs property to the ID variable "strBldgID"
'that stores the ID from the pop-up form that I want to locate.
 
DoCmd.OpenForm stDocName, , , , acFormPropertySettings, , strBldgID
 
'Go to the ID control on the main form and set focus to it.
Forms!frmTabs!txtBldgID.SetFocus
 
'Find the first record on the main form that matches the stored ID from the pop-up form.
DoCmd.FindRecord strBldgID, , True, , True, , True
 
'Exit the sub
Exit_btnDone_Click:
    Exit Sub
 
'Exit the error check.
Err_btnDone_Click:
    MsgBox Err.Description
    Resume Exit_btnDone_Click
End Sub
 

[RedSon]

This post has been reported to moderator staff, the request has been to split it into to separate threads. However, I am not understanding why it needs to be split. I am happy to do it, if someone could provide some justification as to why I would be thankful.

Unless another moderator already split it and I didn't notice.

[Stewart Ross]

This post has been reported to moderator staff, the request has been to split it into to separate threads. However, I am not understanding why it needs to be split. I am happy to do it, if someone could provide some justification as to why I would be thankful.

Unless another moderator already split it and I didn't notice.

Post has been split at some time following my request - see thread http://www.thescripts.com/forum/thread776091.html. Thanks for trying anyway.
-Stewart

[banderson]

Post has been split at some time following my request - see thread http://www.thescripts.com/forum/thread776091.html. Thanks for trying anyway.
-Stewart

Thanks for notifying me of the split.
Best,
Bridget

[RedSon]

Post has been split at some time following my request - see thread http://www.thescripts.com/forum/thread776091.html. Thanks for trying anyway.
-Stewart

Boy, those mods are fast!