Copy specific fields from one form to another form

[rplohocky]

Hello,
I'm trying to figure out how I can click a command button that will select specific fields on one form and paste the values from those fields into another form with matching fields.

I have a form that has another form laying over the top of it. The "base" form has records that I can scroll through. The form that I overlaid is for historical notes. I tied one field together so the overlay will populate whenever a certain field matches. When I come across a record from the main form and want to make notes in the overlaid form, I want to be able to copy over specific fields to the overlaid form.

I've tried these below without success.

<code>
Option Compare Database

Private Sub Command24_Click()


Forms!frm_fuel_fraud_results!ELEMENT_SERVICE_CARD = Forms!frm_Fuel_Fraud_Notes!SERVICE_CARD_NUMBER
'Forms!frm_fuel_fraud_results.ELEMENT_SERVICE_CARD.Value = Forms!frm_Fuel_Fraud_Notes.SERVICE_CARD_NUMBER.Value
'frm_Fuel_Fraud_Results![ELEMENT SERVICE CARD] = Me.[SERVICE CARD NUMBER]
Forms!("frm_Fuel_fraud_Results").ELEMENT_SERVICE_CARD = Me.SERVICE_CARD_NUMBER
'frm Fuel Fraud Results.ELEMENT SERVICE CARD = frmFuel Fraud Notes.SERVICE CARD NUMBER
'Me.ELEMENT SERVICE CARD.Value = Me.SERVICE CARD NUMBER.Value
'Me.feepayeraddress2.Value = Me.address1b.Value
'Me.feepayeraddress3.Value = Me.address1c.Value
End Sub
</code>

Any help would be great!

 

[rplohocky]

This works for me (I tested it out to make sure):

Private Sub Command1_Click()
    
    Me.MyChildForm.Form.MyField = "foo"

End Sub

it assumes you have a button on the main form that has a subform, a button which is named "Command1" and a subform inamed [MyChildForm], with a field on it (textbox control) named "MyField". You don't need to refer to the main form by name since "Me" refers to the enclosing container, in this case the main form itself, which is what has the button in it. But you could do so: Forms.MyParentForm.MyChildForm.Form.F1 = "bar"

I am assuming you will use names that exist in your database, for the fields, controls, forms. You can't use the names I am making up.

Note that if the data is already stored in the database, you don't really need to copy data. You can just make a note and use the keys from the main table as a way to retrieve the data that is relevant to the note.

I still don't understand how this code knows which field on the main form to copy over to the Child form. I need the code to copy over "FieldA" on the main form and paste into "FieldA" on the sub-form, when a click the button. The main form carries data that is recreated everyday. The sub-form is where i can keep permanent notes for a record only if I want to but clicking the button. The macro will select 3 fields from the main for and copy them to the sub-form. This way they remain in a separate table that remains permanent, never updated.
Also i still don't know what "Foo" represents??

 

[welshgasman]

Use the Builder to create the Control Source for the target form. That way you cannot get the naming/syntax incorrect. Also does not matter if you have spaces in the form name or not.
All you have to do is select the correct form and control.

Very handy for novices like myself.:D

 

[xenou]

Hi you are supposed to adapt my example with made up names and values and use your real names and values from your actual database.

 

[rplohocky]

Hi you are supposed to adapt my example with made up names and values and use your real names and values from your actual database.

Xenou,
I don't mean to keep bugging you but none of these things seem to be working. All I want is a button on the sub-form, when clicked will copy the value in a field on the main form. I have spent over 12 hours Googling and posting and still nothing works. VBA errors out for one reason or another. Sometimes it can't find the either form and others it doesn't do anything. I understand I need to modify your code to make it work but after numerous attempts none of them work. I have written miles of code and made numerous modifications to existing code and have never had this much trouble trying to make such a simple macro work.

I was able to find a line of code and it seem to work backwards. It pulled from the sub-form instead of the other way around. I eliminated the spaces in the names of the forms, sometimes that worked and other times it didn't. VBA would find the form, i know this because i use small letters and VBA would switch them to caps where there are caps which tells me it's aware of the form but when i run it, it would say it can't find it!

Here are a few versions i have tried and none of them worked. Do you have any other suggestions?
<code>
'[Forms]![frm fuel fraud results]![ELEMENT_SERVICE_CARD] = [Form]![frm_Fuel_Fraud_Notes]![SERVICE_CARD_NUMBER]

'DRIVER_FIRST_NAME = Me.DRIVER_FIRST_NAME.Value
Me.Parent.frm_Fuel_Fraud_Notes.Form.DRIVER_FIRST_NAME.Value = Me.DRIVER_FIRST_NAME.Value

'Forms!frm_Fuel_Fraud_results.Form.DRIVER_FIRST_NAME = Me.DRIVER_FIRST_NAME

'Me.frm_Fuel_Fraud_results.Form.DRIVER_FIRST_NAME = "driver_first_name"
'Me.DRIVER_FIRST_NAME = [Forms]![frmfuelfraudresults]![DRIVER_FIRST_NAME]

'Forms!frmfuelfraudnotes.Form.DRIVER_FIRST_NAME.Value = Me.DRIVER_FIRST_NAME.Value

'Me.DRIVER_FIRST_NAME.Value = Forms!frmfuelfraudnotes.Form.DRIVER_FIRST_NAME.Value

'Me!DRIVErFIRSTNAME = Me![DRIVER_FIRST_NAME].Form!frmfuelfraudresults.Value
'Forms!frm_fuel_fraud_results!ELEMENT_SERVICE_CARD = Forms!frm_Fuel_Fraud_Notes!SERVICE_CARD_NUMBER
'Forms!frm_fuel_fraud_results.ELEMENT_SERVICE_CARD.Value = Forms!frm_Fuel_Fraud_Notes.SERVICE_CARD_NUMBER.Value
'frm_Fuel_Fraud_Results![ELEMENT SERVICE CARD] = Me.[SERVICE CARD NUMBER]
'Forms!("frm_Fuel_fraud_Results").ELEMENT_SERVICE_CARD = Me.SERVICE_CARD_NUMBER
'frm Fuel Fraud Results.ELEMENT SERVICE CARD = frmFuel Fraud Notes.SERVICE CARD NUMBER
'Me.ELEMENT SERVICE CARD.Value = Me.SERVICE CARD NUMBER.Value
'Me.feepayeraddress2.Value = Me.address1b.Value
'Me.feepayeraddress3.Value = Me.address1c.Value
</code>

 

[welshgasman]

PMFJI, but I think it is time to ask why you want to 'copy' these values.?
The whole point of a database is lack of duplication of data. If you have it on the parent form, why do you need it on the subform?
Each time you refer to the subform, you should be able to refer to the record in the main form for that data.?

However you can use the Builder for the correct syntax and just put the expression in the control source of the control on the sub form.?

 

[xenou]

It's a little scarey that you don't know the name of your field yet or controls yet. Can you at least figure out if your controls have spaces, underscores, or not? It should be obvious to you that at least one of the references won't work if you are using one with spaces and one without spaces and you really have to get rid of the one that is wrong. If you are using VBA it is simple - use the reference exactly the same as the control name. If it has spaces put brackets around the name, otherwise brackets are optional.

If you are using MSAccess macros then nothing in any of the posts in this thread matters - MSAccess macros are not vba are all of the examples so far are using VBA. Can you also figure out if you want to use VBA or MSAccess macros?

I understand if you are frustrated but trying different things basically wildly and blindly is only making things worse! Try things out that you can learn from. For instance:

create a button on the main form.
for the button code write the vba code:

msgbox Me.DRIVER_FIRST_NAME

Now, you will either get an error or a drivers first name. If you get an error, you know you haven't got the right reference. (and you will also stop trying to use Me.Driver_First_Name in your main form, right?! -- because now you have learned that this is not a valid reference). If you don't get an error than great - now you know one piece of the puzzle, which is getting the driver first name from the main form. So you tuck this away and move on to the next piece of the puzzle. Step by step, one thing at a time, in a logical way that you can learn from, so you can figure out each part of the code one piece at a time.

 

[rplohocky]

It's a little scarey that you don't know the name of your field yet or controls yet. Can you at least figure out if your controls have spaces, underscores, or not? It should be obvious to you that at least one of the references won't work if you are using one with spaces and one without spaces and you really have to get rid of the one that is wrong. If you are using VBA it is simple - use the reference exactly the same as the control name. If it has spaces put brackets around the name, otherwise brackets are optional.

If you are using MSAccess macros then nothing in any of the posts in this thread matters - MSAccess macros are not vba are all of the examples so far are using VBA. Can you also figure out if you want to use VBA or MSAccess macros?

I understand if you are frustrated but trying different things basically wildly and blindly is only making things worse! Try things out that you can learn from. For instance:

create a button on the main form.
for the button code write the vba code:

msgbox Me.DRIVER_FIRST_NAME

Now, you will either get an error or a drivers first name. If you get an error, you know you haven't got the right reference. (and you will also stop trying to use Me.Driver_First_Name in your main form, right?! -- because now you have learned that this is not a valid reference). If you don't get an error than great - now you know one piece of the puzzle, which is getting the driver first name from the main form. So you tuck this away and move on to the next piece of the puzzle. Step by step, one thing at a time, in a logical way that you can learn from, so you can figure out each part of the code one piece at a time.

Thanks for your help and patience with my issue! I did finally figure it out using this statement.

Me!DRIVERFIRSTNAME = Forms!frmfuelfraudresults!DRIVERFIRSTNAME

Thanks again!!

 

[xenou]

Great. And I am a little heartened to see you have no spaces in your control names That is something very few experienced programmers will allow (who wants to deal with extra syntax or potential name problems?).