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

[misscrf]

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

 

[misscrf]

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.

    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

 

[misscrf]

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