MS Access - Passing Data from a Subform to another Form

Question

I have a main form (Form1) which is used to search records and the subform to that form contains all records searched. Editing or adding data is not available in Form1.

I want to be able to click on a record from the subform and click a button, to copy that particular record, to a seperate form (Form2). I want to do this so that the user doesn't have to manually copy and paste the data from Form1-subform to Form2.

I have tried continuous form but the maximum width of it is not enough. I'm new to Access and trying to figure out a way to solve this problem. Can anyone suggest a way of doing this. TIA

Fairly common topic. What have you attempted? Why does complete record need to be copied? Sounds like data structure might not be normalized enough. — June7
I have three tables, to which data is entered at different times. The tables have relationships between each other (one to many relationships). The primary key of Table1 is the foreign key to table 2. Likewise the Primary key of Table 2 is the Foreign key to table 3. Since data is entered at different times, i want to be able to link the newly entered data. Ex: If im entering data to the table3 using a form, i want to be able to link it to the record in table2. Hence I wanted to copy just the primary key as described in the question, so that the user doesn't have to enter it manually — KaviA
Question says "to copy that particular record", not just pass the key value. Several ways to accomplish. One is to use OpenArgs of OpenForm method. Still, a common topic. But really, easiest approach is to use form/subform/subsubform arrangement for data entry/edit. — June7
Does this answer your question? Pass Variables From Access Form To Access Form — June7

Mathias Z Mathias Z · Accepted Answer · 2021-06-19T19:59:19

There are several ways to do this, i don't know the correct technical terms but i hope it can help;

Option 1 : Insert from subform to the relevant table that's conected to the main form

Example : Form Data : tblCustomer

Subform Data : tblPotentialCustomer

So to add the data from the subform to the form just try something like this :

    public sub Insert
    
    Dim strSQL as string
    Dim strPotentialCustomerName as string
    Dim strPotentialCustomerKey as string
    
strPotentialCustomerName  = me.subform.PotentialCustomerName
strPotentialCustomerKey = me.subform.PotentialCustomerKey

    strSQL = "Insert into tblCustomer (CustomerName,PotentialCustomerKey) Values ('"& strPotentialCustomerName &"','"& strPotentialCustomerKey  &"')"
    
    call Currentdb.execute(strSQL,dbseechanges)
    
    '--- BEWARE : In the mainform the new record is not automaticaly show, so you need an event to handle this.
        
    end sub

Option 2 : Insert it to the main form directly using parent

Try to reach the relevant object like:

From your subform:

me.parent!CustomerName = mySubform.PotentialCustomerName

Hope this sets you in the right direction. All the best !

MS Access - Passing Data from a Subform to another Form

1 Answers