I have an unbound input form with several unbound textboxes. Once the user inputs values into these textboxes, he will then click "Continue." The On-Click event of the Continue button will run basic logic to ensure that the entries are formatted correctly. If so, these values will then be appended to a table "tblPEOPLE" through an INSERT INTO statement. This INSERT INTO statement is pretty standard. In the table "tblPEOPLE" is also a field ("PEOPLEID") based on an auto counter (This may be relevant for a solution.).

The last part of the code in the "Continue" button will then take the user to a new subform.

Here's what I want: When this new subform comes up (which is bound to tblPEOPLE), I want it to display the current record. It could possibly be the record with the maximum value of the PEOPLEID field.

Here is how my code is structured so far:

INSERT INTO tblPEOPLE statement....

With Forms ("frmMAIN") !SubSub
.SourceObject = "NewSubform"

End With


From the above, I am looking for the code to insert inbetween the 'SourceObject' line and the 'End With' line, which will take the user to the last record of the table.

Thanks.

Adjust as necessary to work in your With statement:

DoCmd.SubformControlName.SetFocus
DoCmd.GoToRecord, , acLast

hth,
Jack

Thanks Jack.

You are welcome, but remember that this may not be EXACTLY what you need...good luck with this...

Jack

I know. I can modify it.

Thanks.

You are most welcome... I think you see what the code does and, as you say, you can modify it to fit your needs...

Jack

Is your application a multi-user application? Or is there any possibility it may be at some time?

If so, there is a flaw in the logic of going to the "last" or maximum value record. In the small amount of time between your SQL Insert statement and your code to move to the last or max record another user may have successfully inserted another record and you'll go to that one instead of the one you inserted. (Remember Murphy's Law?)

If you are using DAO then I'd suggest you use a Recordset and the .AddNew/.Update pair. Then you can retrieve the Autonumber field value just prior to the .Update (save). This will guarantee you have the correct ID.

I believe ADO has a "@@IDENTITY" value that can also do this. But I don't have any code examples of how that works; perhaps another member can post one.

So, if your app is used by one user at a time then carry on. But if it is multi-user then post back and I'll find a proper example for the Object Model you are using (DAO or ADO).

Hi John,

Fortunately, it will be limited to a single-user. This is just a training database that I've created, and will be limited to just one user. The purpose is to allow the user to, in a practice environment, get used to the functionality and navigation of the actual system which costs a gazillion dollars.

The people I work with still can't believe that this little Access database can imitate the actual system. So far, the only limitation I've found with Access in duplicating every single apparent feature of our system is the continuous form. Other than that, this is totally amazing.

Thanks again,
Rich

Hi,

DAO can use @@Identity too, provided Jet4 is installed.

Useful discussion can be found here.


hth,

d