trouble appending records to a table

I needed to append new records to an existing table and have the primary key integer increment automatically. I couldn't find a single command to do it, so I resorted to this:

Set rstTemp = CurrentDb.OpenRecordset("SELECT LAST(ID) FROM tblTable")

avarData = rstTemp.GetRows(1)

CurrentDb.Execute "INSERT INTO tblTable (ID, Field1, Field2, Field3, Field4) VALUES (" & avarData(0, 0) + 1 & ", " & Forms!frmForm.Value1 & ", " & Forms!frmForm.Value2 & ", " & Forms!frmForm.Value3 & ", " & Forms!.frmForm.Value4 & ")"

That seemed to be working okay, and I began entering records with my module, but now suddenly I find that the first two lines of the above code are repeatedly returning an avarData(0, 0) value of 102. It won't go past 102 even if I enter additional records (manually) so the last ID is greater than 102. The INSERT INTO command can't add a new record because it always tries to enter the (already existing) record 102. Why does SELECT LAST(ID) fail to return the ID of the true last record? It doesn't seem to see any records added after the one with ID 102. I don't understand that at all.

But actually I wish I didn't have to actively manage the incrementation of the ID field at all. ID is the primary key field of the table, and an autonumber field, so it seems like it should be able to take care of itself. But INSERT INTO requires values for all fields. Is there an easier approach that I am missing? It seems like this should be simpler.

You can use ADO to add a new record. It's a bit more flexible because it will also allow you to get the autonumber that Access creates for the new record (one day this may come in handy).


This example is A2K-based where you would want to be sure to set a reference to Microsoft ActiveX Data Objects 2.X Library in the VBA editor. Menu: Tools-References.

Regards,
Tim

Thanks Tim. Your suggestion seems to be working as far as managing the primary key goes, but it generated a new problem: my fourth field is a date, and the validation rule specifies that it must be

Like "1/1/*" Or Like "7/1/*"

In other words it must be January 1st or July 1st. I set the value of the field with

rs("Renewal_Due_Date") = DateAdd("yyyy", 1, Current_BPSD)

When the code gets to rs.Update the validation rule fails and the error message containing the validation text springs up. In the debugger the field clearly reads #1/1/2009# so I can't figure out why it's failing validation. Very strange!

-Marc

Marc,

Something to try: change your validation rule to the following...


...and see if it tests OK (it worked for me in a quick mock-up in A2K).

More info:

http://www.eggheadcafe.com/conversation.aspx?messageid=31982891&threadid=31982891
https://www.microsoft.com/communiti...3ce-bf25-7e650ef0ea18&lang=en&cr=US&sloc=&p=1

Regards,
Tim

Hallelujah! It works! Thanks so much for the advice.

-Marc