Finding last primary key value

[MrAustin]

Hi all,

I am writing an application to parse a text file, and each portion contains header information, then detail information. I need to relate the detail info the header info, and I want to do this by storing the primary key of the header autonumber field into a related field in the details table. My question is, how do obtain the highest number in a particular field in a table?

For instance, if the primary key of the headers table looked like this:

Key: 11
Key: 13
Key: 14
Key: 15
Key: 17

How would I write, in VBA, the code to pull 17, which is the highest primary key found?

Thanks! Sorry if it sounds confusing

 

[The_Doc_Man]

In VBA without using recordsets, you could use DMax of the field name and table name in question, possibly qualified with a criteria clause as the third argument of the DMax.

In a recordset in VBA, if you opened the recordset and immediately did a .MoveLast, you could read the PK from that record. Remember, you said this was the prime key. Recordsets are returned in PK order. So the last record (assuming your PK isn't "descending" for sort order) is the one you want to see the highest PK in use.

 

[MrAustin]

Thanks Doc,

I'm unfamiliar with using recordsets, but I'm sure with just a little research I can find the answer, as from what I've heard that is the more efficient way to do this (and it's going to have to happen a LOT throughout the course of parsing this file).

Thanks again!