Goto last record or return id of last record

[dondilbert]

I've got a form that has the basic information i.e. firstname, surname, email address for creating a new contact. This form works fine and checks that the data endered isn't duplicated in the database. Once this form has been filled the data is squirted into the main contacts form, at which point i wish to open this form at the record that was just created, so that the rest of the contact details can be filled in. my problem lies with the fact that the second form is based on a querry sorted by the contacts name so I can't simply goto last record as this returns the person with the last surname. How do I goto the last record created or last id in the contacts table as the primary key is an autonumber?

 

[jedraw]

If there is a CreateDate on the entry, then wouldn't the record you are searching for be the one with the newest Date?

If it's autonumber, wouldnt't the record have the highest number?

Select * from MyContacts
where RecCreatedDate = (Select Max(RecCreatedDate) from MyContacts);

or

Select * from MyContacts
where Rec_id = (Select Max(Rec_id) from MyContacts);

 

[TheAceMan1]

How are ya dondilbert . . .

Just a suggestion ... would it not be better to have a form where you enter general information as well as details and be done with it! I see a mainform with subform for this!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[RoyVidar]

First, information is stored in tables, not forms ;-)

At which point is the autonumber generated?

If it's created within the form, you could just pick it up from the control bound to it, and pass it to the main form.

If you're using SQL to insert the record, there are methods to pick up the last ID inserted on the same connection (I know how it can be done with ADO, and I'm sure it can also be done with DAO).

When the ID is found, you could probably use code similar to the below

[tt]with me.recordsetclone
.findfirst "TheId= " & TheFetchedID
if not .nomatch then
me.bookmark = .bookmark
end if
end with[/tt]

in the main form.

Roy-Vidar

 

[dondilbert]

This is where i show my lack of knowledge! I like the idea that Jedraw suggested about select max id and here is my code

DoCmd.RunSQL "Select * from contacts where Rec_id = (Select Max(contact_id) from contacts);"

however the form always halts at this line in the debugger, any suggestions?

Royvidar, the autonumber is generated by an append querry which is run on the submit button, so i'm not sure your solution would work.