Access Form - Auto Populate w/ current ID

I have a pop up form that a user can click on the supplier id from my main form. They can use this to add categories. How can I make it so the Supplier Name and ID will auto populate and all they have to do is select another category?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pass in the ID for the FK using the OpenArgs.  Then in the BeforeInsert event of the popup form, populate the FK

Me.SupplierID = Me.OpenArgs

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Does the "calling form" stay open? If so, then your popup could look at that form to get the values - for example, in the Load or Open event of that popup form, you could do this:

Dim SupplierID As Integer
SupplierID = Forms("YourCallingForm").SupplierID

You can then use that SupplierID to populate controls on your form, filter records, etc.
kwarden13Author Commented:
Yes it does stay open. I will try that Scott. Thanks
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Scott, If you use the Open event for this purpose, you have two problems
1. It only works for a single record.
2. You dirty the form with code which will confuse the user if he tries to exit without saving.

Using the BeforeInsert works regardless of how many records you add in the popup and it does not dirty the form since this event would only run if the user had dirtied the form so presumably, he is intending to save or he will at least not be confused if he elects to exit without saving since he knows he typed something.

If you want the field to populate earlier, you could use the form's Dirty event but in that case, you would need to determine if this was a NewRecord or not since you would only want to do this for new records.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You dirty the form with code which will confuse the user if he tries to exit without saving.
My suggestion was just a way to retrieve the values from the calling form, not necessarily how to use them once you retrieved them. Once you did, you could certainly use those in the BeforeInsert event (just as you would use the values from your OpenArgs suggestion).

Just two different ways to get the values, I suppose.
If you need more than one field, the OpenArgs becomes difficult to use so referring to an open form is fine.  I just would never use the Open event because of the two things I mentioned.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see what you mean - in my suggestion, you would ONLY have those variables during the Open event. A better idea would be to declare your variables like SupplierID at the Form level, and they'd be available for the duration of the form session, and you could use them anywhere.
I'm not sure why you are making work arounds to avoid using the BeforeInsert event.    When I have one argument to pass, I use OpenArgs.  When I have multiple, I refer to the calling form fields.  But in ALL cases, I use the BeforeInsert event because that event runs ONLY for new records and it runs for EVERY new record.  The BeforeInsert event runs once for every record you add.  The Open event runs only once, period - when the form opens.  Using the correct event eliminates problems rather than creating them.

If I wanted to pass in multiple values using the OpenArgs, I would decode the string in the Open event and save the individual arguments as TempVars.  But, I would still not apply the values there.  I would still use the BeforeInsert event and from there I would reference the TempVars.  Using the OpenArgs would be necessary if the "popup" form could be called from multiple main forms so that you couldn't directly reference form fields without knowing what form caused the "popup" to open.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not avoiding the use of BeforeInsert.

I said nothing about avoiding the use of BeforeInsert.

I fully understand what and how BeforeInsert works.

My comment (as poorly worded as it is) involved RETRIEVING the values in the form's Open event, not RETRIEVING and USING them in the Open event. You could RETRIEVE them in the Open event, and then store them in a Form-level var, a Public var, TempVars, or anywhere else you choose - and you could then use those in the BeforeInsert event (or anywhere else your little heart desires).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.