Access Form - Auto Populate w/ current ID

kwarden13
kwarden13 used Ask the Experts™
on
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?
sample_3.241.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
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
Most Valuable Expert 2012
Top Expert 2014
Commented:
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.

Author

Commented:
Yes it does stay open. I will try that Scott. Thanks
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Distinguished Expert 2017

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Distinguished Expert 2017

Commented:
@Scott,
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
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).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial