SQL connected table - Movelast

[santosm]

Hi All,
I am working on converting my data to a SQL backend. So far everything seems to work. However, I have one bit of code in the access front end where I need to grab the record ID just after creating it so that I can use it for something else. The "rs.movelast" doesn't return the record number like it did in Access. How can I get that latest record number using code?

Thanks,
Mark

[HansV]

Does your table have an IDENTITY field? If so, you can use the SQL string

SELECT SCOPE_IDENTITY()

to get the latest value of the IDENTITY field.

[santosm]

Hi Hans,
What would be the correct syntax to use to get that value back?

I have rs.update and then don't know what to do next.

Thanks,
Mark

[HansV]

Since you use a recordset, you may be better off using IDENT_CURRENT. The idea is to open a new recordset on the SQL string

SELECT IDENT_CURRENT('name_of_your_table') AS LastID

and then retrieve the value of LastID.

Perhaps Wendell will have a better suggestion - he knows far more about working with SQL Server than I do.

[santosm]

Thanks!

[santosm]

I pasted in the code you provided, changed the area with the table name and it gives an error saying "expected case". Is there a possibility that I need some other reference setup for the SQL stuff?

SELECT IDENT_CURRENT('workorders') AS LastID

Thanks,
Mark

[HansV]

Not as far as I know, but I'm not a SQL Server expert.

[santosm]

Hi Hans,
I ended up using the DMAX function which gets the highest record. I know this is not the best way to do this as another user may create a record in between the .update and DMAX executing. I will search for the best way to do this later on when I get a little bit more time.

Thanks,
Mark

[Wendell]

Hi Mark,
Unfortunately, I don't think there is a perfect answer to this problem. We have typically used the MAX approach in VBA, but we are fortunate to have clients where there are only a few people really doing data entry, so the probability of having a collision is small. Even if you use the query Hans suggested to get the SQL Server Identity value (which should be done as a pass-through query), there is no assurance that somebody won't create a record using that value during the time you are running the code to save a record. If there were some other set of data that makes the record unique, you could search on that to get the newly created primary key, but that is very rarely the case. One trick that we have used is to make the DMAX() function call a bit more than just looking for the max of the key is to add some additional criteria like this:

Code: Select all

lngNewPersonID = DMax("lngPersonID", "tblPeople", "strLastName = """ & Me.strMemberLastName & """ and strFirstName = """ & Me.strMemberFirstName & """")

That makes the probability of getting the wrong value highly unlikely (we hope).

[santosm]

Hi Wendell,
Thanks for the suggestions. I think what you have listed last will work well because it would filter by the customer records in my case. The chance that two or more people will be working on the same customer records will be extremely rare if not nearly impossible.

Thanks,
Mark

[Pat]

I used to use the following:

Code: Select all

    rs.Update
    '   reread record just written to get ID field
    rs.Bookmark = rs.LastModified
    savedID = rs!ID

I don't know if it is still applicable.