Opening a 2nd form with form button using data displayed in first form

[Zork182]

Hi,

I have an access database and created two forms. The first form looks up Assets by inventory number and shows the student the assets are assigned to. The second form shows the student and all assets that are assigned to them. Sometimes I only have the asset info and want to be able to lookup all of the equipment the student has on the second form. Both forms currently use a Combo box for selecting the asset record or the student record depending on which form you are in. The problem I am having is that the student form requires the Combo Box to be filled in before it will show a record so I cannot just create a form button to call a specific record as it just gives me a blank form which I can then manually fill in the Combo Box value. I want to automate that part of the selection. The combo box contains the student name in LAST, FIRST format as well as the STUDENT NUMBER to ease selecting the correct student when they have the same name.
I am using MS Access for Office 365 v16 64-bit on Windows 10.

[NeoPa]

I do like it when new members post questions properly with care and that fundamentally make good sense.

To answer your question I would say that there are a number of possibilities. Sorry, but there you are.

1. If the form is already open then you can either apply a filter or navigate to the correct record using code.
2. If the form is opened only when you select the item you want from the other Form then you can either open it with a filter (WhereCondition parameter) or navigate to the required record once it's been opened.
   Some people prefer to pass the info in the OpenArgs property when opening the Form and letting the Form sort out navigating to the required record, but others prefer to handle this in the same code that opens the Form.
3. It may be that you have particular reasons for using two separate Forms for this, but if not then you may choose to manage it all with Cascaded Form Filtering instead. It seems more straightforward to me, but I don't know your project so there may be good reasons for doing it differently.

Let us know if you need further help with any of these suggestions.

[Zork182]

Thanks NeoPa.
I looked through many of the options that you have indicated and was being stumped due to the second form requiring an entry into the ComboBox. I realized shortly after posting that I was populating a text field based on the selection and then the form was populating based on the text field value. By skipping the ComboBox I was able to resolve my problem by;
Setting a TempVar in the original form and then call the 2nd form from the first.
I then set my text box on the second form to my temp value in the 'On Load' Event. I would then automatically receive the correct record data.

I will flag your answer as the best one. Thanks again.

[NeoPa]

You may want to consider using the OpenArgs parameter instead. This is an option available to you when opening a Form that allows you to pass information from the caller to the callee. It's preferable to the use of a TempVar as the scope is more appropriate (It doesn't hang around after it's needed cluttering up the database).

My preferred solution, unless you actually need to navigate between different records from within the second Form, is to filter it to that one record when opening. It involves the least code and makes it very clear what your code is doing.

All that said, it's obviously up to you how you play it. I wish you the best of course, but my main desire is to ensure the possible solutions are up and visible to all who seek.

[Zork182]

Thanks for the follow up. I have added a Remove TempVar after I open the 2nd form for the moment until I can get a change to learn more about OpenArgs and implement it that way. Being a Noob I usually land on the first thing that works even if it is not the most elegant but I do like to learn how to improve. Luckily my Databases are for my own consumption at the moment. I am moving towards making them available to others so am always striving to improve upon them.

[NeoPa]

Good for you. I started that way (I guess most do). I was just using Access for data manipulation then started playing with Forms etc and realised how fabulous it could be. My first couple of years on here caused me to find out about a whole bunch of things because people had questions about them that I felt I wanted to answer. Once I'd been doing that for a while I found I had become 'the expert' and was very well equipped to take on serious projects.

I've certainly enjoyed the ride :-)

[zmbd]

Good Morning Zork182,
Just another approach to cascading forms, combo-boxes, etc...
Parent/Sub-form

I've attached a little example that I whipped up...
It's not a polished application; however, I hope that you are presently surprised with what can be done with some simple default objects, some minor normalization, very basic queries, a few subforms, and no vba. (Hmmm... certain expert (Rabbit ;-) ) here got me on the hook to look for non-VBA solutions)

There's a ton of functionality that I'd put in place; however, this is just a jumping point.

A couple if minor notes:
+ The database opens to the main navigation form
-- This is a special Access Object, called amazingly enough, a Navigation Form
-- This is NOT a tabbed form in anyway shape or form

+The navigation pane is minimized on open, press [F11] to see the underlying tables and forms

+ If you look at the tables if you see in the description
"Which Student is the widget assigned to (1/2 S2WKey)"
"Which widget in inventory is assigned to the student (2/2 S2WKey)"
This indicates that the fields (in this case) are part of a compound key named [S2WKey].
I do this to prevent duplicating records (in this case S2WKey is such that it prevents assigning the same inventory item to the same student multiple times). So it's worth taking a look at the table index properties. I've also set it up so that the same inventory item cannot be assigned to more than one student... yet, I've built in the ability to do so should there be a need in the workflow.

Play with it... just please keep in mind I bodged this together in under hour with three children floating in and around my desk :)