Transfer data between tables

[PatAccess]

Hello Very Smart People,

I need some help!
I have 2 tables:
Table 1 has RecID (autonumber), field, field, field
Table 2 has Tbl2RecID (autonumber), RecID(number), field, field, field

Form 1 (from table 1) has a command that opens Form 2 (from table 2)

When I click on Form 2, I would like it to open a blank form WITH RecID already filled out with RecID from table 1

So I need RecID to be automatically copied and pasted in table 2

What is the best way to do this?

Thank you,

 

[Ranman256]

the answer to most every thing is QUERY.
you want an append query, that uses the key on form1 to append it to table2
once copied you can open to it via form1:

sub btnCopy_click()
  docmd.openquery "qaCopy1Rec"
  docmd.openform "form2" ,,,"[id]=" & me.ID
end sub

 

[PatAccess]

Thank you for the response!
I did what you suggested however when I go in table 2, it adds another row and take the previous RecID to append it into the the previous row

I need them to be on the same row while copying that RecID

I hope this makes sense

Thank you

 

[PatAccess]

the answer to most every thing is QUERY.
you want an append query, that uses the key on form1 to append it to table2
once copied you can open to it via form1:

sub btnCopy_click()
  docmd.openquery "qaCopy1Rec"
  docmd.openform "form2" ,,,"[id]=" & me.ID
end sub

:banghead:
On the first form I have:
Private Sub cmdOpenDeff_Click()
If Me.Dirty = True Then Me.Dirty = False
DoCmd.OpenForm "Frm_Deff", acNormal, , "RecID=" & Me.RecID & ""

On the second form I have:
Private Sub Form_Load()
If Me.Dirty = True Then Me.Dirty = False
DoCmd.OpenQuery "Qry_RecID"
End Sub

When I go to click on the button It append ALL the RecID. I only want it to append the one from the current record and stay on that same record in the second form

I've tried everything, it is still adding a new row with the correct appended RecID but now everything else is on a second row

Is there a criteria I can add to my query for it to append only new RecID?

 

[Pat Hartman]

Do not run an append query. You have no data to append except a foreign key. You will just be creating a "blank" record.

The proper way to carry forward a foreign key from another form is to simply reference the pk field of the other form. BUT, the key is to do it in the correct event. You do NOT want to dirty the record before the user does so the normal event you would use is the pop up Form's BeforeInsert event. This event runs only for new records and only once each.

Me.FK = Forms!callingForm!PK

That way, if you open the pop up form to an existing record, your code won't run because it doesn't need to. It only needs to run for NEW records. It needs to run for EVERY new record but never before the user actually dirties the new record.

Form events are not random. They were designed to serve specific purposes. Rarely will multiple events be appropriate for the same purpose. Learning what triggers them helps you to understand which event should be used for what purpose.

 

[PatAccess]

Thank you very much for your help! Got it to work with the BeforeInsert Event. Thanks again

 

[Pat Hartman]

You're welcome.