Auto Populate a new record from a form with data from the current form

[bobfin]

Help! I know this should be easy but being new to Access I am struggling.


I have a form. One of the fields on the form is "NCRnumber" which is auto generated. I have another Field called "CARnumber" which is empty field. I want to put a button next to the "CARnumber" that will call the CARform to add a NEW CARform Record but I want to pass the "NCRnumber" from the NCRform to the "NCRNmuber on the CARform. The CARform will auto generate a "CarNumber" then return back to the NCRform and populate the "CARnumber" from the CARform.

Thanks,

Bob

[bobfin]

OK I git this far From the NCRForm this calls the Corrective Action Form and adds a NEW Record

Private Sub Command149_Click()
DoCmd.OpenForm "Corrective Action Form", acNormal, "", acAdd, acNormal
Forms![Corrective Action Form]![NCRNumber] = Me.[NCRNumber]
End Sub
------------------------------------------------------------------
Now I need the "Corrective Action Form" to Return to the "NCRform" and fill in the NEW "CARnumber" it created in the "CARnumber" filed of the NCRform

Thanks,

Bob

[RuralGuy]

The [Corrective Action Form] can reach back into the [NCRForm] through the FORMS collection just as you have done reaching into the [Corrective Action Form] from the [NCRForm] after it was open.

[bobfin]

Ok I am not familiar with Form Collection.
Can you give me an example?

Bob

[RuralGuy]

This line you used is using the FORMS collection:
Forms![Corrective Action Form]![NCRNumber] = Me.[NCRNumber]

[bobfin]

ok I attached this to a button in the NCRform

Private Sub Command149_Click()
DoCmd.OpenForm "Corrective Action Form", acNormal, "", acAdd, acNormal
Forms![Corrective Action Form]![NCRNumber] = Me.[NCRNumber]
Exit_Command149_Click:
Exit Sub
Forms![Corrective Action Form]![CARNumber] = Me.[CAR#]
End Sub

-------------------------------------------------------------
All owrks excpet for
Forms![Corrective Action Form]![CARNumber] = Me.[CAR#]

It does not copy the "CAR"Number back to the NCRform

Bob

[RuralGuy]

This line:
Forms![Corrective Action Form]![CARNumber] = Me.[CAR#]
...needs to be in the [Corrective Action Form] OnUnload event and read:
Forms![NCRForm]![CAR#] = Me.[CARNumber]

[bobfin]

Tried it still foes not work.

I am calling the Corrective Action Form from a button on the NCRform
When I add the new record to the Corrective Action Form and exit I want the CAR# to be populated with the new record I just created.

I am missing something?

Bob

[bobfin]

Ok I get a syntax error fo rthe following

Private Sub Form_Unload(Cancel As Integer)
Forms![NCRform]![CAR#] = Me.[CARNumber]
End Sub

[RuralGuy]

Try the same code in the AfterUpdate event of the [Corrective Action Form] form instead.

[bobfin]

No error this time but it did not work

[RuralGuy]

Display your value in a MsgBox before you copy it to the other form to see what you have.

[bobfin]

The Value comes back NULL

[RuralGuy]

Bingo! The form is in Add Mode and moves to a new record when the current one is saved. Move your code to the BeforeUpdate event of the form instead. That should correct this issue.

[bobfin]

Still did not copy the value over.

Bob