CODEWITHSUNDEEP
ms-accessvba
Shadyjunior
Shadyjunior

Reputation: 457

TempVars - Get ID for use in next form

In my database the user creates a new record via a form like so;

enter image description here

Upon pressing the save button, the record then gets inserted into ConTblConsumables, Here is the code sample behind the save button;

    Private Sub btnSave_Click()

    Dim strAddNewConsumable As String
    Dim strCompany As String
    Dim strConName As String
    Dim strConID As String
    Dim strCboConSupplier As String
    Dim strConTargetLevel As String
    Dim strConCost As String

        strCompany = Me.ConCompany
        strConName = Me.txtConName
        strConID = Me.txtConID
        strCboConSupplier = Me.CboConSupplier
        strConTargetLevel = Me.txtConTargetLevel
        strConCost = Me.txtConCost

        strAddNewConsumable = "INSERT INTO ConTblConsumables(strConCost, ConName, ConExtraID, Supplier, ConTargetLevel, Cost)" _
                    & "VALUES('" & strCompany & "', '" & strConName & "', '" & strConID & "', '" & strCboConSupplier & "', '" & strConTargetLevel & "', '" & strConCost & "')"

        CurrentDb.Execute (strAddNewConsumable)

        MsgBox "Record Inserted", vbExclamation

End Sub

enter image description here

Once the record has been inserted I then wish to pop-up another form where the user can then assign a parent machine to this part. The reason I wish to do it this way is that several machines can use the same part (Many-to-Many). Which when the machine is selected will then update this table;

enter image description here

The consumable column is a ComboBox with a relationship to the ConTblConsumables table and thus why I require to have the ID from previous to be retained in a TempVar.

I believe that if I was to use TempVars towards the end of the code on the save button, that when the record is inserted into the table ConTblConsumables, that the TempVars records the ID of the row that the record has been inserted into, thus being about to attain the ID for use in the next form.

My question is how do I write this in code? Or even if this is possible?

Thank you

enter image description here

Upvotes: 0

Views: 767

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

As I understand, in table ConTblConsumables you have a key column ID with Autonumber data type and you need to know ID of inserted row. If so, you'd better to use recordset instead of INSERT statement. Something like this:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("select * from ConTblConsumables")

With rst
    .AddNew
    'here ID already known and you can store it in your variable
    TempVars!MyID = !ID.Value
    !strConCost = Me.ConCompany
    !ConName = Me.txtConName
    ....
    .Update
End With

rst.Close
Set rst = Nothing

Upvotes: 1

Related Questions