Solved

Move to record remotely

Posted on 2012-12-29
7
254 Views
Last Modified: 2012-12-30
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
0
Comment
Question by:DatabaseDek
  • 3
  • 3
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38729655
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38729681
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
 

Author Comment

by:DatabaseDek
ID: 38730730
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38730770
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
 

Author Closing Comment

by:DatabaseDek
ID: 38731200
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38731279
>> 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
 

Author Comment

by:DatabaseDek
ID: 38731320
Thank you!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now