Move fields from previous record into new record

[becbec]

I am moving an inventory database from dBaseIV into Access 2000. The dbase database uses a "record count" to increment the Tagnum field by 1. I don't want to use the Access Autonumber or the Access "record count" method. Access deletes records differently than dBase.

When I move to a new record, I want the Tagnum field to increment by 1.

My "Add New Record" is set up to "On Click" read the "DoCmd.GoToRecord ,, acNewRec" command. I am putting the code in the "On Enter" part of this control.

On July 11,2003 BotCow(Programmer) posted a similar question (answered by legos(Programmer)and I am trying to use the same code.

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Dim num As Variant

Set rst = db.OpenRecordset("select * from Inventor&quot
rst.MoveLast
num = rst!Tagnum
num = num + 1
rst.AddNew
rst!Tagnum = num
rst.Update

Where am I going wrong? This is my first post so if I am out of line anywhere, please advise.


Thanks!

BecBec

 

[PaulF]

set the default value to for Tagnum to

= Nz(DMax("TagNum", "Inventor&quot,0) + 1


PaulF

 

[becbec]

Thanks PaulF.....

I tried your line of code and it supplied a new Tagnum but it will not increment after the new record has been entered and saved.

Maybe I am not communicating properly on what I am trying to accomplish. Basically I have two objectives at the same time.

When I click on "Add New Record", it has to read the last Tagnum number, increment it by 1, and insert it into the Tagnum field on the new blank record.

At the same time and independent of the Tagnum code and behind another command button move all of the information in the previous fields of the last record into the new blank record (if necessary).

I have tried several different ways but I am just not getting the job done. I think maybe I can't see the forest for the trees.

Any help will, I assure you, be greatly appreciated.

 

[PaulF]

if you use the wizard to create the command button on your form, you get an option to Duplicate Record, which adds this code

On Error GoTo Err__Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Exit__Click:
Exit Sub
Err__Click:
MsgBox Err.Description
Resume Exit__Click


as for the Default value, it works in the testing I do here, if Tagnum is Text and not Numeric then you have to convert to a Numeric value using the Val() function

= Val(Nz(DMax("TagNum", "Inventor&quot,0)) + 1

PaulF

 

[becbec]

PaulF......

1st) I appreciate your help. I think I have gone brain dead in this particular area.

2nd) I changed my "TagNum" data type to Number and the Nz function now works fine.

3rd) The Duplicate Record code produced by the Command Button Wizard was what I had used originally. It works fine with one exception...... it carries all of the fields forward including the TagNum number field. It does not increment the TagNum going forward. Therein lies my problem.

I get close but nothing works.

Again.....any suggestion would sure be appreciated.

Again also.....I appreciate your interest.

Becbec