select highest autonumber

[tomvdduin]

Hi all,

I have a table with primary key p_id (autonumber)
Before I want to add something in this table (read: before I want to open an bound form) I want to insert the p_id into an other table. So I thought that when I click a button, access first finds the highest number, add 1, and use that nr to insert into the table.

This is the code I want to use to get the highest nr:

Dim dbs As DataBase
Dim rst As Recordset
Dim strSQL As String
Dim str As String

strSQL = "select max(p_id) from tbl_PERSOON;"
Set rst = dbs.OpenRecordSet(strSQL)

str = rst!p_id
DisplayMessage (str)
Set rst = Nothing

but on the line "Set rst = dbs.OpenRecordSet(strSQL)" access gives the error: (translated from dutch) "objectvariable or blockvariable With is not set"

what am I doing wrong? Is there an other way around this?

 

[thornmastr]

Access won’t let you do what you want to do. you can get the highest number and add 1 to it, but if you are using an autonumber as a primary key, you have no guarantee that the nuber you have computed is the number you are going to get. For example, the number may have already been assigned and the assigned record deleted in which case it will not be reassigned.

I would suggest you not make that field an autonumber. Define it to be a long number. That way you are responsible for assigning its value not Access.

Consider an equation to compute the value of the next highest number, then fire off a query to insure that number is not already in use. If it is in use, get the next number and test it, etc, ad nauseum. Use the first viable number you get.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[JeremyNYC]

Robert's right that you can't rely upon the autonumber field to give you the next highest number, and I think the method you propose is too vlunerable to the possibility of someone else creating a record in that table while the operation is going on. But 1) the error in your code is happening because you have not set the dbs variable, and that's easy to fix and 2) there is a way to deal with this situation that will get you that number safely.

To set the dbs variable just do this:
set dbs = currentdb

The method I would use is to create a recordset (appendOnly), add the record, set a variable to the value of the Autonumber field in that record, and then when you want to pull up the new record you'll have that ID on hand.

Let me know if any of this is unclear.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[tomvdduin]

Tnx JeremyNYC, I think that I understand you, but as i haven't worked that mutch with VBA, can you give me a start? I really appreciate.

Greetz,

Tom

 

[JeremyNYC]

Tom,

I actually just got avalanched at work, so I don't really have time to do this. But what I would suggest you do is get into the help files (especially if you have a97) and look into things like
recordset
appendonly
add records

Also, it does sound like you're fairly new to this, so maybe you should describe in slightly more detail why you're going about things in this way, or better yet, what exactly it is you're trying to do. It may well be that someone here can point you to a slicker way to go about things.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.