VBA code: copy 1 field to another in a different table...

[JMac]

Morning all
I have the following code which works well on a command click on a form for running a query, closing the current open form and opening another.
What I want to squeeze in, is to copy the number which has been updated by the query and use that as a new record in the newly opened table... any thoughts.... thanks...

Private Sub Command36_Click()
On Error GoTo Err_Query_update_for_part_number_Click

Dim stDocName As String

stDocName = "Part number generation update"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Query_update_for_part_number_Click:


DoCmd.Close acForm, Me.Name


DoCmd.OpenForm "Cat data entry"


Exit Sub
Err_Query_update_for_part_number_Click:
MsgBox Err.Description


Resume Exit_Query_update_for_part_number_Click
End Sub

[June7]

One way to pass the value to opening form is with OpenArgs argument. What I don't see in your code is how the part number is generated. How can it be available to the code?

DoCmd.OpenForm "Cat data entry", , , , , , partnumberhere
DoCmd.Close acForm, Me.Name

Then in the Open event of the form, extract the value from the OpenArgs

If IsNull(somefield) Then somefield = Me.OpenArgs

[JMac]

Hi June7
The part number is a simple update query, just collating a couple of fields together.. fieild 1 + field 2 + uniqueID = Part number (all in the first 'part number' table via a form)

I guess the step I need is part of your observation of how the part number is available to the code..

Currently its updated and resides in the last record in the 'part number' table
I want this newly generated number to pass across to the new 'cat data entry form' and become the basis of a new record...
JMac

[June7]

If the part number is just two concatenated fields of the currently open form then they are still available for the code?

[JMac]

Yes, effectively its 3 fields but it doesnt update until I run the query!

[June7]

Does that matter? The 3 fields are part of the open form and have data then they are available for concatenation in the code.

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[JMac]

Well thanks to some embedded pictures the file is currently residing at 20Mb! Maybe Ill PM you with the intricate details, I may try to delete the pics too!!

[June7]

If still too big, can upload to fileshare site such as box.com and post link to the file.