Copy a field from Access to use in another access object (1 Viewer)

Kuvasz

New member
Local time
Today, 03:51
Joined
Jun 17, 2020
Messages
6
I have a form that uses a query to open and display a specific customer info based on a customer ID that is manually entered by the user.

The form populates with the customer info from a table where the customer ID can only occur one time. A sub form opens below the customer info with a list of items owned by that customer from another table where the customer ID can occur many times but the items owned is limited by a unique Serial Number.

I have this form set to No Edits so someone does not hose up the original data.

I would like for the user to select a particular Owned Item / Serial Number, and have that SN copied and used to automatically populate another query to open a new work request on that SN.

I don't know if this is possible or not but if so some help with the code or macro would be most helpful.

I understand that using Ctrl C and Ctrl V are the simple answer but the people using this don't want the extra effort and in some cases it is beyond them to even get that right.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:51
Joined
Oct 29, 2018
Messages
21,357
Hi. One approach is to use a button next to the serial number, so you can open a new form filtered to the selected serial number.
Sent from phone...
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:51
Joined
Jan 20, 2009
Messages
12,849
Another way for the code shy is to use the LinkMasterFields and LinkChildFields. LinkMaster doesn't actually have to be a field. Controls work fine too and can even be a control in a completely different form. Of course you need the full reference through the Forms Collection. No VBA code required at all though.

You need a wrapper form the subformcontrol on the opening form but you can hide the Border, Selector etc so it all appears to be on the main form.

In this structure the Child doesn't automatically requery on the master change but it can work when you just want to open another form for a single record and close the dependent form before returning to the master. You can make it more configurable, allowing it to be called from different master forms, by passing the name of the Master to the opening form through OpenArgs and setting that as the MasterLinkFields of the subform.

If you want t make it continue to track the master records you can make the dependent form requery on the master change but you need more code then. There would be ways to code this better using an Event Listener but it would definitely be defeating the simplicity then.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:51
Joined
Feb 19, 2002
Messages
42,970
It is poor practice to open queries for users to interact with. The reason being that you have NO control. If the query is updateable, they can do anything they want including delete. Best practice is to always use forms for user interaction. That way, you have event code that can be used to validate and either ensure or prevent certain actions. To open another form, you can use the OpenForm method. You would use the OpenArgs to pass in the ID of the record you want to pass in. Then in the new form's BeforeInsert event, you would place the ID into the ID field:

Me.SN = Me.OpenArgs
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:51
Joined
Feb 19, 2013
Messages
16,553
another approach is to use the serial number control double click event to open another form
 

Users who are viewing this thread

Top Bottom