Jumping to another form

I have a question... is it possible to click on a field on a form and have it jump to another form based on the contents of the clicked-on field? For example, I have Form A. One of the fields is UserName. I'd like to be able to click on the name and have it open the UserData form for that person. I can put the correct code in the On Click property to open the UserData form, but how do I use the information in the UserName field as criteria for the opening the UserData form? In other words, how do I get the UserData form to open with Joe Doe's data (the name on Form A I clicked on) rather than Jane Doe or Kenny Doe's data (the names above and below - or any other name on Form A)? I can't tell the UserData form to get the name from the UserName field, because that field is repeated a number of times (once for each record). Can I somehow store the name in code as a variable and use it to open the UserData form?

Any help?... anyone?.... anyone?

There is an easy way to do this via the wizard. Place a command button on your form and step through the wizard.

- Choose to open another form
- Find Specific Data on form
- Choose the matching IDs

So in your case you are looking to match the ID of the User. If you have any problems implementing this I have a sample which I am more than happy to send onto you.

Hay

Unfortunately, a command button won't do. Form A displays Users and their total assigned hours for each week. The form's Recordset Type is set to continuous (it has to be so we show all users on a single page). So... imagine the form with columns for Name, Week 1, Week 2, etc. I need to be able to click on one of the names and have another form open with the UserData for the name that is in the field that was clicked on. Let's say I have twenty records returned into FormA. I have twenty rows of data, which means twenty names. Can't tell the form to go to Forms![FormA].[Name] (or whatever the format is...) because there are twenty different entries in that field; one for each record. So how do I get the form UserData to look to the correct record for the name it needs to get the User's data?

I'm thinking Imight have the user click on the name they want and somehow use the Got Focus property... but not sure what I need to do with it.

I still don't see why you cannot do as I suggested above. I currently use this in a database and it is more or less set up as you have described above.

What I have in my first form is a list of suppliers just as you have with your users set up in a continuous form. Highlighting the record selector for say supplier1 or in your case user1 then takes you to form2 with user1's details. Every record will have a unique identifier so you will be able to retrieve the correct information.

If you would like me to export the tables, forms concerned from my database and send onto you It's really no trouble.

My work email should you need it: Hayley.Baxter@bskyb.com

Hay

bpalesch,

HEY! That's my avatar!!


LOL!

Thought I'd post the example I sent to Brian incase anyone else was following the thread. Similar look to the email example I posted recently only this time the button opens corresponding data in form2 from the record selected in the subfrm.

This example is in A2000

Here it is in A97.

Brian, I know you have a new issue with this. I received your email - just to let you know I haven't forgotten about you. I'll amend the sample and send to you so it works the way you want.

Hay

Hayley,

Your example uses a subform, but the command button is also on the subform. Is it possible to get the command button onto the main form and still have it open the active record's(which is on the subform) details?

By the way, I am using your solution on another form - works great and really impressed the client - my boss (which is the whole idea, isn't it!)

Hi Brian

I haven't tested this but you should be able to reference the subform. The syntax would be something like

stLinkCriteria = "[Order No] = [Forms]![SubfrmSearchResults]![Order No]" & Me![Order No]

The above may be slightly out as I've not tested it. There is also another option of using a macro and entering a Where condition to open the relevant records. Both would do the same job.

Hay