MS ACCESS: Reopen userform viewing user's last saved record

Hello Team

Can you please advise on the following?

When saving a record from a user form, I would like the form to reopen again viewing the record that has just been saved by the user.

When I save a record, the userform closes as this triggers the requerying of the tables etc.

There are multiple users of the dbase feeding into the backend so what I specifically need is to obtain the last unique record id (BusinessID) created by the current user?

For example:  UserID MXON01 creates a new record with a unique BusinessID of 234.  

As there are multiple users feeding into a backend at the same time, using the
DoCmd.RunCommand acCmdRecordsGoToLast

Open in new window

would not be appropriate.

Any suggestions would be much appreciated.

Thank you

Dale JamesTherapistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Well at first you need a global variable ..lets name it gID
Then on the saving method you set
gID = Me.ID

Open in new window

When you re -open the Form
Private Sub Form_Open(Cancel As Integer)
    If gID > 0 Then
        Me.Form.Recordset.FindFirst ("ID = " & gID)
        gID = 0
    End If
End Sub

Open in new window

Fabrice LambertConsultingCommented:
Hmmm, maybe you should take a look at the Observer design pattern.
It'll avoid the usage of a global variable (mistakes  happen so fast).
John TsioumprisSoftware & Systems EngineerCommented:
@Fabrice interesting idea...a bit of overkill for this case but interesting nontheless.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:
How are you saving the record?

Usually I include a "Save" button, which has a Click event which looks like:

Private Sub cmd_Save_Click

    'check for required fields here

    me.Dirty = False   'This saves the current record but does not move off that record in the form.

End Sub

Open in new window

You don't really need to requery the form.

If your using Access as the back-end for your data, then you could use the OnDirty event to record the value of the ID field into a variable that is declared as private to the form (not a particular event on the form).  Then, if you feel you must perform a requery, you can use the findFirst method to move to that record, that would look something like:
with me.recordsetclone
    .findfirst "ID = " & lngID
    if .noMatch = true then 
        msgbox "record not found"
        me.bookmark = .bookmark
    end if
end with

Open in new window

I am confused by your question because it isn't necessary to close the form to save a record.  Just use
If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If

Open in new window

If you are asking how to save the last recordID a user accessed so the next time he opens the app, he can resume at that record, you need a table.  The table would have the  User's ID, the form he was using, and the ID of the record he was operating on.

PS - UserForms are something completely different from Access Forms.  When working with Access, forms are called simply Forms.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale JamesTherapistAuthor Commented:
Thank you for everyone's contribution.  As always...very much appreciated.

Yes, you all correct, there is no actual need for the form to close in this particular case.  Seems as though it was case of, 'but that's the way it's always been done' mentality. So it does turn out to be a simple of case of [code]DoCmd.RunCommand acCmdSaveRecord[/code]

Thank you once again.
John TsioumprisSoftware & Systems EngineerCommented:
Just a small note...the solution you picked just does not corresponds to what you asked..its the correct way to do things but ....
Dale JamesTherapistAuthor Commented:
Hello John

Sorry of the delay with my response as I have been out of the country.

Yes you are correct but as I was working on the solutions the requirement changed and it did evolve into that which was different to the original request.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.