获取ID用于后续表单：数据库表单新增记录保存代码技术咨询

Hey there! Let's break down your two Access questions step by step—both are common scenarios, so I've got you covered.

TempVars are ideal for this because they’re persistent across all Access objects (forms, reports, modules) without the hassle of global variables or clunky query string passes. Here’s how to implement it smoothly:

Step 1: Capture the New Record’s ID After Saving #

Once you save your new consumable record, grab its unique ID (I’m assuming your ConTblConsumables has an autonumber primary key named ConID). Add this right after your save logic:

' After successfully saving, get the new record's ID
Dim newConID As Long
' Using Recordset.LastModified is more reliable than DMax for multi-user environments
rs.Bookmark = rs.LastModified
newConID = rs!ConID
' Store it in TempVars
TempVars!NewConsumableID = newConID

If you’re not using a recordset (like in the SQL approach), DMax("ConID", "ConTblConsumables") works, but note it might not be 100% accurate if multiple users are adding records simultaneously.

Step 2: Retrieve the ID in the Next Form #

In the next form’s Form_Load event, pull the value from TempVars and use it wherever needed. Don’t forget to check if the TempVar exists to avoid errors:

Private Sub Form_Load()
    If TempVars.Exists("NewConsumableID") Then
        ' Replace txtTargetIDField with your actual control name
        Me.txtTargetIDField = TempVars!NewConsumableID
        ' Optional but recommended: Clear the TempVar after use to prevent reusing old values
        TempVars.Remove "NewConsumableID"
    End If
End Sub

Your current code uses string variables for all fields (which is risky for numeric/currency data) and unparameterized SQL (prone to syntax errors with special characters and SQL injection). Let’s rewrite this to be safer, cleaner, and more robust:

Recommended Approach: Using DAO.Recordset #

This method directly interacts with the table’s recordset, handling data types automatically and avoiding messy SQL strings:

Private Sub btnSave_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim newConID As Long
    
    ' Always add error handling to catch issues like missing fields or data type mismatches
    On Error GoTo ErrorHandler
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("ConTblConsumables", dbOpenDynaset)
    
    ' Add a new record
    rs.AddNew
    rs!ConCompany = Me.ConCompany
    rs!ConName = Me.txtConName
    ' REMOVE THIS LINE IF ConID IS AN AUTONUMBER PRIMARY KEY:
    rs!ConID = Me.txtConID
    rs!ConSupplier = Me.cboConSupplier
    rs!ConTargetLevel = Me.txtConTargetLevel ' Ensure this matches your table's numeric data type
    rs!ConCost = Me.txtConCost ' Use Currency type in your table for cost fields
    ' Add any additional fields from your form here
    rs.Update
    
    ' Grab the new record's ID (critical for passing to the next form)
    rs.Bookmark = rs.LastModified
    newConID = rs!ConID
    
    ' Store the ID in TempVars
    TempVars!NewConsumableID = newConID
    
    ' Clean up resources
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    ' Optional: Navigate to your next form and close the current one
    DoCmd.OpenForm "YourNextFormName"
    DoCmd.Close acForm, Me.Name
    
    Exit Sub
    
ErrorHandler:
    MsgBox "Error saving record: " & Err.Description, vbExclamation
    ' Clean up even if an error occurs
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Key Notes:

• If ConID is an autonumber (auto-generated primary key), delete the line rs!ConID = Me.txtConID—Access will handle this automatically.
• Double-check that the field names in rs!FieldName exactly match your table’s column names (case doesn’t matter, but spelling does!).
• Error handling ensures users get a clear message instead of a cryptic Access error.

Alternative: Parameterized SQL Query #

If you prefer using SQL, parameterized queries eliminate the risk of SQL injection and syntax errors from special characters (like apostrophes in company names):

Private Sub btnSave_Click()
    Dim qdf As DAO.QueryDef
    Dim newConID As Long
    
    On Error GoTo ErrorHandler
    
    ' Create a parameterized INSERT query
    Set qdf = CurrentDb.CreateQueryDef("", _
        "INSERT INTO ConTblConsumables (ConCompany, ConName, ConSupplier, ConTargetLevel, ConCost) " & _
        "VALUES (@Company, @Name, @Supplier, @TargetLevel, @Cost)")
    
    ' Assign values from your form controls to the parameters
    qdf.Parameters("@Company") = Me.ConCompany
    qdf.Parameters("@Name") = Me.txtConName
    qdf.Parameters("@Supplier") = Me.cboConSupplier
    qdf.Parameters("@TargetLevel") = Me.txtConTargetLevel
    qdf.Parameters("@Cost") = Me.txtConCost
    
    ' Execute the query (dbFailOnError ensures we catch any issues)
    qdf.Execute dbFailOnError
    
    ' Get the new record's ID
    newConID = DMax("ConID", "ConTblConsumables")
    TempVars!NewConsumableID = newConID
    
    ' Clean up
    Set qdf = Nothing
    
    ' Navigate to your next form
    DoCmd.OpenForm "YourNextFormName"
    DoCmd.Close acForm, Me.Name
    
    Exit Sub
    
ErrorHandler:
    MsgBox "Error saving record: " & Err.Description, vbExclamation
    Set qdf = Nothing
End Sub

Content originally asked on Stack Exchange by Shadyjunior