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!

 

[xenou]

The first line in your code looks generally okay. Does it work?
Forms!frm_fuel_fraud_results!ELEMENT_SERVICE_CARD = Forms!frm_Fuel_Fraud_Notes!SERVICE_CARD_NUMBER

The rest of it looks confusing and probably wouldn't even compile as is so I'm not sure what's going on. As posted I'm pretty sure it's not going to work but not even sure if you expect it to because you seem to be trying different things all at once.

 

[rplohocky]

The first line in your code looks generally okay. Does it work?
Forms!frm_fuel_fraud_results!ELEMENT_SERVICE_CARD = Forms!frm_Fuel_Fraud_Notes!SERVICE_CARD_NUMBER

The rest of it looks confusing and probably wouldn't even compile as is so I'm not sure what's going on. As posted I'm pretty sure it's not going to work but not even sure if you expect it to because you seem to be trying different things all at once.

The other lines were other attempts I found online through various sites.

The first line error's out. It says it can't find the form. I thought maybe spaces were the problem, so I changed the name to have underscores, that didn't work. What's weird is the macro will will see "frm_Fuel_Fraud_Notes" but it won't see "frm_fuel_fraud_results". The button is on the "Notes" form, which may be why but it doesn't work.

 

[xenou]

Don't put spaces in a form name (or a table name, or a field name, or a report name ... you get the idea). That will save you a bit of trouble.

You can try brackets:
[Forms]![frm_fuel_fraud_results]![ELEMENT_SERVICE_CARD] = [Forms]![frm_Fuel_Fraud_Notes]![SERVICE_CARD_NUMBER]

But its up to you to use the names as they actually are (since I don't know what you have, spaces or underscores.

VBA and macros in MSAccess are two different things so you can't compare them at all.

 

[rplohocky]

Don't put spaces in a form name (or a table name, or a field name, or a report name ... you get the idea). That will save you a bit of trouble.

You can try brackets:
[Forms]![frm_fuel_fraud_results]![ELEMENT_SERVICE_CARD] = [Forms]![frm_Fuel_Fraud_Notes]![SERVICE_CARD_NUMBER]

But its up to you to use the names as they actually are (since I don't know what you have, spaces or underscores.

VBA and macros in MSAccess are two different things so you can't compare them at all.

Xenou,
I tried your code above and still no worky. The forms, right now, have spaces in the names but at one point I renamed them and retried all the examples above and nothing changed. Right now the error i'm getting is saying Access can't find frm Fuel Fraud Notes, but what's weird is if I change the one of the letters from capitol to small letters, as soon as i toggle away it switches to caps, so it must be able to see it, right?

I am really stuck on this, I think this should be pretty simple. All I want is to click a button and copy the value into another forms field.

 

[xenou]

The other form must be open. Not sure what "overlaid" means here. If its a subform, that is not the same as a completely different form so the syntax would be different then.

Note that my code has underscores in it and you say you have spaces so my code shouldn't work!

 

[rplohocky]

The other form must be open. Not sure what "overlaid" means here. If its a subform, that is not the same as a completely different form so the syntax would be different then.

Note that my code has underscores in it and you say you have spaces so my code shouldn't work!

I tried your code both ways. With underscores and without.

What i mean by overlaid is, I created a basic form and then created another form, then put the first form into layout view and dragged the second form over the first form. Not sure if that's considered a sub form or not. I've seen sub-forms look like tables attached to the bottom of the main form. This method makes it look like the first form has another from laying on top of it but its embedded into it. I don't know if that makes a difference on the code or not???

I found out how to do it here.

https://support.office.com/en-us/ar...49cb-831a-1e74d6f5f06b#bmcreatesfrmbydragging

 

[xenou]

Hi, Yes that is a subform. It's a little bit of a giveaway in the title of the article. Subforms are referenced through the parent form.

something like this:

Forms!MainForm.SubForm!TxtField = "Foo"

Easier with no spaces as syntax is less prone to errors and you have enough errors to worry about.

Some examples here, but be careful since the site shows you how to refer to controls on a main form, a subform, and a subform with a subform (which can be ignored for now).
http://access.mvps.org/Access/forms/frm0031.htm

 

[rplohocky]

Hi, Yes that is a subform. It's a little bit of a giveaway in the title of the article. Subforms are referenced through the parent form.

something like this:

Forms!MainForm.SubForm!TxtField = "Foo"

Easier with no spaces as syntax is less prone to errors and you have enough errors to worry about.

Some examples here, but be careful since the site shows you how to refer to controls on a main form, a subform, and a subform with a subform (which can be ignored for now).
http://access.mvps.org/Access/forms/frm0031.htm

So now you know, I'm very new at Access I really do appreciate the help! I have been able to get "get Through" most of my situations nad have learned more than I thought possible but sometimes i get hung up one one or two.

In your example above "Forms!MainForm.SubForm!TxtField = "Foo" ". Am i to make the code look like this...
Forms!frm_Fuel_Fraud_Results.frm_Fuel Fraud_Notes!VIN = "what goes here?"
Not to sure how this logic works. How does this code know which field on the main form to pull from and where to insert it into the subform when the command button is clicked?

 

[xenou]

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.