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

close form don't go to first record on related form?

AccessIdiot
100+
P: 493
Hello all,

I have a form (frm_Entrainment) with a button that opens a 2nd form (frm_Specimen_Entrainment). The 2nd form shares an ID field with the first form (Entrainment_ID - its like opening the Orders form for a particular Customer). I have a button on the 2nd form that says "return to the first form" which really is just sitting beneath the 2nd form. When the button is clicked it simply closes the 2nd form so you can see the 1st form that is just sitting there. However, when the 2nd form is closed the first form automatically goes back to the first record. I would prefer if it just stayed on the record it was at, where the 2nd from was launched from, in case the user wasn't done entering information.

I have lots of code on the 2nd form including an after update event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Forms!frm_Entrainment.Requery
  3. End Sub
Could this be what is causing the form to go back to the 1st record?

This is the code on the button that closes the 2nd form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCloseForm_Click()                                                    'Close form return to Entraiment form
  2. On Error GoTo Err_btnCloseForm_Click
  3.  
  4.  
  5.     DoCmd.Close
  6.  
  7. Exit_btnCloseForm_Click:
  8.     Exit Sub
  9.  
  10. Err_btnCloseForm_Click:
  11.     MsgBox Err.Description
  12.     Resume Exit_btnCloseForm_Click
  13.  
  14. End Sub
thanks for any help

:) melissa
May 9 '07 #1
Share this Question
Share on Google+
22 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hello all,

I have a form (frm_Entrainment) with a button that opens a 2nd form (frm_Specimen_Entrainment). The 2nd form shares an ID field with the first form (Entrainment_ID - its like opening the Orders form for a particular Customer). I have a button on the 2nd form that says "return to the first form" which really is just sitting beneath the 2nd form. When the button is clicked it simply closes the 2nd form so you can see the 1st form that is just sitting there. However, when the 2nd form is closed the first form automatically goes back to the first record. I would prefer if it just stayed on the record it was at, where the 2nd from was launched from, in case the user wasn't done entering information.

I have lots of code on the 2nd form including an after update event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Forms!frm_Entrainment.Requery
  3. End Sub
Could this be what is causing the form to go back to the 1st record?

This is the code on the button that closes the 2nd form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCloseForm_Click()                                                    'Close form return to Entraiment form
  2. On Error GoTo Err_btnCloseForm_Click
  3.  
  4.  
  5.     DoCmd.Close
  6.  
  7. Exit_btnCloseForm_Click:
  8.     Exit Sub
  9.  
  10. Err_btnCloseForm_Click:
  11.     MsgBox Err.Description
  12.     Resume Exit_btnCloseForm_Click
  13.  
  14. End Sub
thanks for any help

:) melissa
Melissa the requery will send you back to first record. To go back to the record you were on pass the id to a variable before the requery and use it to return to the record afterwards.
May 10 '07 #2

AccessIdiot
100+
P: 493
Melissa the requery will send you back to first record. To go back to the record you were on pass the id to a variable before the requery and use it to return to the record afterwards.
Thanks Mary, I was afraid of that. So do I put some kind of DoCmd to return to the last record on some kind of form event? I do have some code to go to a new record when the form is first launched so I hope not to interfere with that.
May 10 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks Mary, I was afraid of that. So do I put some kind of DoCmd to return to the last record on some kind of form event? I do have some code to go to a new record when the form is first launched so I hope not to interfere with that.
Try something like this ...
Expand|Select|Wrap|Line Numbers
  1. Dim curRec
  2.  
  3.     curRec = Me.CurrentRecord
  4.     Me.Requery
  5.     DoCmd.GoToRecord , , acGoTo, curRec
  6.  
Mary
May 10 '07 #4

AccessIdiot
100+
P: 493
Hi Mary,

I'm sorry but where do I put this? I have a Me.Refresh on the form_current event. Do I replace that with the above code?
May 11 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mary,

I'm sorry but where do I put this? I have a Me.Refresh on the form_current event. Do I replace that with the above code?
Try it there and see if it works.

Change requery with refresh if you wish.
May 11 '07 #6

AccessIdiot
100+
P: 493
When I try it with Refresh it does not work. When I try it with Requery it gets stuck in an endless loop?
May 11 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
When I try it with Refresh it does not work. When I try it with Requery it gets stuck in an endless loop?
OK when you had Me.Refresh in Form Current event it returned you to the first record on the form. Right?

Why are you triggering this in Form Current. Just realised the endless loop is being caused becuase the event keeps triggering.
May 11 '07 #8

AccessIdiot
100+
P: 493
OK when you had Me.Refresh in Form Current event it returned you to the first record on the form. Right?

Why are you triggering this in Form Current. Just realised the endless loop is being caused becuase the event keeps triggering.
Don't know if you remember a couple of weeks ago you helped with a sum function. I have a textbox on frm_Specimen_Entrainment where the user can type in the number of Specimens. But a particular Entrainment can have many specimens so one specimen there may be 4 and the next specimen there will be 3 bringing the total number of specimens for that particular entrainment to 7.

On frm_Entrainment there is a textbox that reports the total number of specimens fro that particular entrainment_id: 7 specimens. If you go into the specimen record and change the 3 to say, 5, then when you close the specimen form the entrainment form should now say 9 instead of 7. So I have the refresh on there to refresh the textbox with the sum function on it that counts the number of specimes. I do it on the current event because before when you closed the specimen form the entrainment form didn't change or go back to the first record. Now all of a sudden it is.

Why would a refresh on current go back to the first record? Shouldn't it just refresh the current record?

thanks for the help
May 11 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Don't know if you remember a couple of weeks ago you helped with a sum function. I have a textbox on frm_Specimen_Entrainment where the user can type in the number of Specimens. But a particular Entrainment can have many specimens so one specimen there may be 4 and the next specimen there will be 3 bringing the total number of specimens for that particular entrainment to 7.

On frm_Entrainment there is a textbox that reports the total number of specimens fro that particular entrainment_id: 7 specimens. If you go into the specimen record and change the 3 to say, 5, then when you close the specimen form the entrainment form should now say 9 instead of 7. So I have the refresh on there to refresh the textbox with the sum function on it that counts the number of specimes. I do it on the current event because before when you closed the specimen form the entrainment form didn't change or go back to the first record. Now all of a sudden it is.

Why would a refresh on current go back to the first record? Shouldn't it just refresh the current record?

thanks for the help
Just refresh the textbox rather than the form.

Expand|Select|Wrap|Line Numbers
  1. Me!TextboxName.Refresh
Mary
May 11 '07 #10

Denburt
Expert 100+
P: 1,356
May I cut in? If I remember correctly we went over the openArgs before haven't we AccessI?

I would use the openArgs to pass my current record over to the second form then back or just use a global variable as a place holder.

What do you think Mary?
May 11 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
May I cut in? If I remember correctly we went over the openArgs before haven't we AccessI?

I would use the openArgs to pass my current record over to the second form then back or just use a global variable as a place holder.

What do you think Mary?
Problem with whats happening I think is having the event in On Current means it's triggering every time the form returns to the record. I'm happy for any other approach to be tried.
May 11 '07 #12

AccessIdiot
100+
P: 493
Unfortunately I get an error that Refresh isn't a supported method and when I try Requery it does the same thing of going back to the first record.
May 11 '07 #13

AccessIdiot
100+
P: 493
Just saw Denburts message.

I do have the OpenArg going from frm_Entrainment to frm_Specimen_Entrainment. Previously when I closed the frm_Specimen_Entrainment the frm_Entrainment just sat where it was. Now all of a sudden it is going back to the first record. Do I have to pass the ID field they share BACK to frm_Entrainment on the close event of frm_Specimen_Entrainment and then go to that record where OpenArgs yadda yadda yadda?
May 11 '07 #14

Denburt
Expert 100+
P: 1,356
From what I see you use the forms after update event to re query the first form thus on closing the second form the first is re queried and goes back to the first record.

Set a variable to the current record, in the top of the first forms module.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim lngCur as Long
  4.  

Expand|Select|Wrap|Line Numbers
  1. Docmd openSecondForm
  2. lngCur = Me.CurrentRecord
  3.  
May 11 '07 #15

Denburt
Expert 100+
P: 1,356
Sorry super busy atr the moment, only gave you the first part. Then using the On Activate event use Docmd.gotorecord as Mary suggested. Make sure you check to see if the variable is set first though.
May 11 '07 #16

AccessIdiot
100+
P: 493
Hi sorry, where do I put this? In the after update of the 2nd form? What is openSecondForm? Is it a custom function? Or is it shorthand for DoCmd.Open "frm_Entrainment"?

Expand|Select|Wrap|Line Numbers
  1. Docmd openSecondForm
  2. lngCur = Me.CurrentRecord
  3.  
May 11 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi sorry, where do I put this? In the after update of the 2nd form? What is openSecondForm? Is it a custom function? Or is it shorthand for DoCmd.Open "frm_Entrainment"?

Expand|Select|Wrap|Line Numbers
  1. Docmd openSecondForm
  2. lngCur = Me.CurrentRecord
  3.  
It's just psedocode Melissa. Replace it with your normal open statement.
May 11 '07 #18

AccessIdiot
100+
P: 493
That's what I thought, but I'm still unsure where to put it.

The screwy thing is that I've got the same code on two different sets of forms. I have a frm_Entrainment and a frm_Replicate. I have a frm_Specimen_Entrainment and a frm_Specimen_Replicate. I've got the same code and count functionality on both sets. The frm_Replicate stays on the current working record when the frm_Specimen_Replicate is closed. The frm_Entrainment does not. It's enough to drive me batty!
May 11 '07 #19

AccessIdiot
100+
P: 493
Wow I actually fixed it with something simple. :)

Instead of a Requery I did a Refresh on the after update event of the 2nd form (Specimen form) and it works just fine. Go figure!!
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Forms!frm_Entrainment.Refresh
  3. End Sub
I guess I'm still learning the ins and outs of Requery vs Refresh.

Thanks Mary and Denburt for your time and patience!
May 11 '07 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
Wow I actually fixed it with something simple. :)

Instead of a Requery I did a Refresh on the after update event of the 2nd form (Specimen form) and it works just fine. Go figure!!
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Forms!frm_Entrainment.Refresh
  3. End Sub
I guess I'm still learning the ins and outs of Requery vs Refresh.

Thanks Mary and Denburt for your time and patience!
No problem. Because of the way events are triggered this kind of thing can be problematic and is often a case of trial and error.

Congratulations on solving it yourself :)

Mary
May 11 '07 #21

AccessIdiot
100+
P: 493
lol, I'm lazy that way and am always looking for the simple solutions. ;)
May 11 '07 #22

MMcCarthy
Expert Mod 10K+
P: 14,534
lol, I'm lazy that way and am always looking for the simple solutions. ;)
Don't we all.
May 11 '07 #23

Post your reply

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