Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Move to record remotely

Posted on 2012-12-29
7
Medium Priority
?
271 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 61

Accepted Solution

by:
mbizup earned 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

730 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