Using newly created Autonumbers

[Krystoff]

Hi all,

I have some code that is going to make a new record in a table thus generating an autonumber.

Then I want to take that autonumber and append that with some of the original data and paste it into another table.

Whats the easiest way of doing something like that? I know I can do it the hard way and append to the first table. Use a unique field from the orig table to find the newly created record and use then create a new append query based off of that. Seems like there should be an easier way though.

Also, this is an ADP project so I can use SQL stored procedures if that would make it easier. I know there is a output variable part in stored procedures but I don't know how to use it yet.

Any help is appreciated!

Chris

 

[Rolliee]

Use a cmd button to add a new record. The wiz can do that for you. Then after your:

DOCmd.GoToRecord,,acNew

command ( or something like the above)

save the ID as a long variable

DIM savID as long

savID = me.fields(0) 'this assumes the autonumber in field postion one.

The open the foreign tables and do your thing.

Rollie

 

[jbento]

I did something with a command button with the following. There are 2 main forms involved. I have a frm_test and a frm_test1. Both forms have 2 subforms. I have a Command button on frm_test that only captures all data except for data in the subforms. The code below does that data capture. I click the command button on frm_test and it copies the main data and opens frm_test1. I have a command button on frm_test1 that will capture all subform data from frm_test subforms. The code is as follows:

[Forms]![frm_test].SetFocus
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
[Forms]![frm_test1].SetFocus
[Forms]![frm_test1]![frm_sub3a].SetFocus
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdPaste
[Forms]![frm_test1]![frm_sub3a]![MainID] = MainID

You can repeat that code above for all subforms you may have.

This is a round about way of doing, but I have search high and low for an easier way to do this, and I couldn't find it.

I hope it helps

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!