Move to record remotely

How do I write

Remember the [ItemNumber] from this form and when I close it (OnClose) Open the "ProjectForm" and in the "ProjectForm's" subform ("Details") go to the record where the [ItemNumber] matches the [ItemNumber] that I remembered from the form that was just closed
Derek BrownMDAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Nothing wrong with your approach - it's pretty commonly used to bring up popups from a mainform/subform combination and return the user to the same subform record they were editing.

The scenario you originally described is less common - people generally want to re-open a form to a specific record (and then goto a specific subform record if needed).  But I've seen that approach used when the mainform is unbound.

For getting to  a specific record on the subform, the bookmark code I posted should do the trick.  The reason I suggested a custom Close button is that the Close event of a form is too late to refer to any of the fields on that form.  You CAN use global or module level variables to 'remember' field values for use in late events such as the close event, but the code is not as clean (and if that field value can change while your popup is open, determining when to set your global variable can be a little tricky).

Did you try the suggestion I posted?
0
 
mbizupCommented:
I'd use a custom Close button for this, and disable the x button in the form's design.

The code for a custom close button would be:

Docmd.OpenForm "ProjectForm"
Dim rs as dao.recordset

set rs = Forms!projectForm.Details.Form.recordsetclone

rs.FindFirst "ItemNumber = " & me.ItemNumber
if rs.nomatch = true then exit sub
Forms!projectForm.Details.Form.Bookmark = rs.Bookmark

docmd.close acform, me.name

Open in new window



The above assumes that ItemNumber is numeric.  If it is text, your FindFirst would change to:

rs.FindFirst "ItemNumber = '" & me.ItemNumber & "'"

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
DatabaseDek,

Just curious,
...since the Main form ("ProjectForm") will typically control the subform records ("Details")

How are you determining what record the main form is set to?
(Since you are forcing a specific record in the subform, without regard to the main form, ...AFAICT)

    ("Tail wagging the dog"?)

Again, just curious...
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Derek BrownMDAuthor Commented:
The truth is that the Project form is already open. I chose to ask the question this way because my questions get so complicated. I would have had to explain that the form I want to close was opened by a button on the ProjectForm subform and is linked to that form by ItemNumber so the project is already selected and the new form has its underlying query filtered to the same project as the main already open form but not filtered to the subform. Then I would have to explain why I need to move to another record on the new form and why i need to have the same linked record when i go back to the ProjectForm and subform.

Life is never easy the way I create it.
0
 
Derek BrownMDAuthor Commented:
Hi

Works perfect. What needs to change in the code to allow for the fact that the form is already open? It works without fault so if the form is already open does it just ignore the Docmd.OpenForm

I had just written a long message about the fact that on many occasions I have had inconsistent results from code in the OnClose event and wanted to explain and thank you for clearing that up, but I was not logged in so when I pressed submit it went into log in mode and I lost the lot.

I know you are one of the main guys in this area. If you have any clout with the hierarchy please pass on my dismay at this ridiculous security nonsense. Why do we need a password now when we did not when I joined? What secrets are we passing that require security anyway. I just lost 15 minutes of my time! For what?

Do you find this as frustrating as me? ...............................Or is it just me?

Thanks again
0
 
mbizupCommented:
>> It works without fault so if the form is already open does it just ignore the Docmd.OpenForm...

Yep = that is redundant, and can be removed.  Since your form is already open in 'Normal View' the line is effectively ignored.  (If your form were open in Design View, an Open Form would switch it to normal view... just an FYI, but it does not apply here).

<<
Why do we need a password now when we did not when I joined
>>
Do you have the 'remember me' box checked when you log in?  That should keep you perpetually logged in, so that you can open EE pages with no password.  

It is a stored cookie, so things that will make the credentials box appear are:
- Using a different computer
- Using a different browser that you haven't used with EE before
- Deleting your cookies
- EE's cookie expiring (I believe it expires if you are inactive on the site for more than 14 days)
- Some of EE's site updates require logins after they are released.

(But in general, if you are using the site on the same computer, it keeps you logged in ).
0
 
Derek BrownMDAuthor Commented:
Thank you!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.