Copy value from field in subform to field in mainform during data entry

[glawson]

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

 

[rzw0wr]

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]

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]

Forms!Form4a!QuarterOrPealID = ActualNameHere.

Dale

 

[glawson]

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]

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]

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.

 

[rzw0wr]

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]

I've done as you have suggested but the value is still not copying to the required field.

 

[rzw0wr]

Hmmm.
In the After Update event of the first textbox you add data to, Type forms!form4a.refresh.

Dale

 

[glawson]

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.