Copy value from field in subform to field in mainform during data entry (1 Viewer)

glawson

Registered User.
Local time
Today, 22:53
Joined
Jul 18, 2013
Messages
14
Hi all,

Firstly, I apologise in advance for being an Access 2010 novice, but there is something I really need help with.

How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?

The main form and sub form are linked using parent/child linking, and the sub form is in a tab.

I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.

In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.

So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.

Please fire back any questions - I hope the above is clear enough.

Your help will be much appreciated, but bear in mind I'm not that familiar with SQL or VBA.

See attachment for some screen shots.

Thanks
Gareth
 

Attachments

  • field on subfrom to update to field in main form when adding new record.zip
    147.5 KB · Views: 745

rzw0wr

I will always be a newbie
Local time
Today, 17:53
Joined
Apr 1, 2012
Messages
489
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?

In the subform, in the text box you want to insert/copy from, in the after update event try, Forms!YourMainFormName!MainFormTextBoxName = SubformTextbox
Forms!YourMainFormName.Refresh

Dale
 

glawson

Registered User.
Local time
Today, 22:53
Joined
Jul 18, 2013
Messages
14
Thanks Dale, but it didn't work (probably something I've not done correctly. Here's what I put in the After Update event of the field in the sub form:

Forms!Form4a!QuarterOrPealID = SubformTextbox
Forms!Form4a.Refresh

Is there more to it than that?
Ta
Gareth
 

rzw0wr

I will always be a newbie
Local time
Today, 17:53
Joined
Apr 1, 2012
Messages
489
Forms!Form4a!QuarterOrPealID = ActualNameHere.

Dale
 

glawson

Registered User.
Local time
Today, 22:53
Joined
Jul 18, 2013
Messages
14
I've tried the following two variations but still not working. Sorry I'm not getting this:

Forms!Form4a!QuarterOrPealID = Quarters and Peals subQuarterOrPealID
Forms!Form4a.Refesh


Forms!Form4a!QuarterOrPealID = Forms!Quarters and Peals sub!QuarterOrPealID
Forms!Form4a.Refesh


If it helps my main form is called "Form4a", the sub form is called "Quarters and Peals sub" and the field on both is called "QuarterOrPealID" (the value copies from one to the other).
 

rzw0wr

I will always be a newbie
Local time
Today, 17:53
Joined
Apr 1, 2012
Messages
489
Define not working.
All names with spaces have to be inside [] to work.
Forms!Form4a!QuarterOrPealID = QuarterOrPealID

Is QuarterOrPealID field the same field from the same table?

Dale
 

glawson

Registered User.
Local time
Today, 22:53
Joined
Jul 18, 2013
Messages
14
I'm not actually typing into the field. It's the auto number/primary key number that is generated when I add a new record in the sub form to table B that I need to automatically copy to the field of the same name in the main form/table A.

The value is not updating to the main form field.

Oh, and thanks for the [] tip - I've learnt something new today.
 
Last edited:

rzw0wr

I will always be a newbie
Local time
Today, 17:53
Joined
Apr 1, 2012
Messages
489
in visit Dates table remove the PK from QuarterOrPealID and make it a number type.
Make sure that in the data tab of your properties sheet that the parent child relationship is set to QuarterOrPealID. Then this should happen automatically. It may take a form4a.refresh.

Dale

Dale
 

glawson

Registered User.
Local time
Today, 22:53
Joined
Jul 18, 2013
Messages
14
I've done as you have suggested but the value is still not copying to the required field.
 

rzw0wr

I will always be a newbie
Local time
Today, 17:53
Joined
Apr 1, 2012
Messages
489
Hmmm.
In the After Update event of the first textbox you add data to, Type forms!form4a.refresh.

Dale
 

glawson

Registered User.
Local time
Today, 22:53
Joined
Jul 18, 2013
Messages
14
Dale, thanks for your help, though I still haven't been able to get it to work. But I have since changed the design of my database so I no longer need that functionality - for now at least.
 

Users who are viewing this thread

Top Bottom