INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Add New Record on Another Form - Save Record to Get New ID

Add New Record on Another Form - Save Record to Get New ID

(OP)
I have a form that has the detail visible = false, until a user clicks 1 of 2 buttons
1) Look up Record (filter combos to view existing records in listbox - choose one, go to record)
2) Add New Record

For #2, a form pops-up, and I make the main record form visible = false. The user has to choose a value from a combo box, so that when the new record is added, we can automatically assign it to the FK of the combo box. What I need to do though, is from the pop-up form do these things:

1) go to the new record (docmd.gotorecord acnew)
2) set the FKfield/control value to the combo on the pop-up form
3) get the main record ID of the new record we have just gone to (1), so I can run a public function.

I need to know how to do a with me.form
RunCommand acCmdSaveRecord
but from the pop-up form, saving the new record on the main record form.

anyone know how to do that?
Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

RE: Add New Record on Another Form - Save Record to Get New ID

(OP)
I came up with some code, but I know my method is not great for a multi-user application. If someone else tries to add a record at the same time, I could grab the wrong max ID. I have been searching around and I don't get how there isn't a way to get the ID of the row that was added by a specific action. There is @@IDENTITY with SQL, but not within Access VBA.


CODE

Dim mid As Integer
    Dim MCID As Integer
    Dim InsContSQL As String
    Dim Criteria As String
   
    mid = Nz(Me.cboChooseMatch.Column(0), 0)
        InsContSQL "INSERT INTO tblMRecords ( FKMatch ) SELECT " & mid
        MCID = DLookup(MAX("ID"), "tblMRecords")
    End If
    Criteria = "[ID]=" & MCID
    Forms!frmMRecords.Filter = Criteria
    Forms!frmMRecords.FilterOn = True
    Forms!frmMRecords.Visible = True
    Forms!frmMRecords.Detail.Visible = True 

Anyone else have experience with this challenge?

misscrf

It is never too late to become what you could have been ~ George Eliot

RE: Add New Record on Another Form - Save Record to Get New ID

(OP)
I realized the best way to do this was with a SQL stored procedure, with an output parameter, and and ado connection to run the procedure and grab the output value, in vba.

misscrf

It is never too late to become what you could have been ~ George Eliot

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close