Edit Recordset

[PC User]

I'm getting an error in my code on the first EDIT command and I'd like to request someone's help. The error is:

Compile error:

Expected Function or variable

Public Function wmConvert_Containers(intContainer As Integer, intInventoryID As Integer)
Dim strContainer As String

    'Convert option value to checkbox value
    'Insert data into table.
        Set db = CurrentDb()
        Set rst1 = db.OpenRecordset("tblChemicalInventory", dbOpenDynaset) 'Source/Target
             
            rst1.MoveFirst
            rst1.FindFirst "InventoryID = " & rst1!intInventoryID
                      
    If intContainer = 1 Then rst1.[B][COLOR="Red"]Edit[/COLOR][/B]![ckAboveground Tank] = 1 Else rst1.Edit![ckAboveground Tank] = 0
    If intContainer = 2 Then rst1.Edit![ckUnderground Tank] = 1 Else rst1.Edit![ckUnderground Tank] = 0
    If intContainer = 3 Then rst1.Edit![ckTank Inside Building] = 1 Else rst1.Edit![ckTank Inside Building] = 0
    If intContainer = 4 Then rst1.Edit![ckSteel Drum] = 1 Else rst1.Edit![ckSteel Drum] = 0
    If intContainer = 5 Then rst1.Edit![ckPlastic/Nonmetalic Drum] = 1 Else rst1.Edit![ckPlastic/Nonmetalic Drum] = 0
    If intContainer = 6 Then rst1.Edit![ckCan] = 1 Else rst1.Edit![ckCan] = 0
    If intContainer = 7 Then rst1.Edit![ckCarboy] = 1 Else rst1.Edit![ckCarboy] = 0

            rst1.Update
    
    rst1.Close
    Set rst1 = Nothing
    db.Close
    Set db = Nothing
    
    
End Function

Thanks,
PC

 

[pbaldy]

From DAO help:

   With rstTemp
      .Edit
      !FirstName = strFirst
      !LastName = strLast
      .Update
   End With

 

[PC User]

Thanks for your help. I got the code to work.

Public Function wmConvert_Containers(intContainer As Integer, intInventoryID As Integer)
    Dim db As DAO.Database
    Dim rst1 As DAO.Recordset

    'Insert data into table.
        Set db = CurrentDb()
        Set rst1 = db.OpenRecordset("tblChemicalInventory", dbOpenDynaset) 'Source/Target
         
            rst1.MoveFirst
            rst1.FindFirst "InventoryID = " & intInventoryID
            rst1.Edit
            
                If intContainer = 1 Then rst1.Fields("ckAboveground Tank") = 1 Else rst1.Fields("ckAboveground Tank") = 0
                If intContainer = 2 Then rst1.Fields("ckUnderground Tank") = 1 Else rst1.Fields("ckUnderground Tank") = 0
                If intContainer = 3 Then rst1.Fields("ckTank Inside Building") = 1 Else rst1.Fields("ckTank Inside Building") = 0
                If intContainer = 4 Then rst1.Fields("ckSteel Drum") = 1 Else rst1.Fields("ckSteel Drum") = 0
                If intContainer = 5 Then rst1.Fields("ckPlastic/Nonmetalic Drum") = 1 Else rst1.Fields("ckPlastic/Nonmetalic Drum") = 0
                If intContainer = 6 Then rst1.Fields("ckCan") = 1 Else rst1.Fields("ckCan") = 0
                If intContainer = 7 Then rst1.Fields("ckCarboy") = 1 Else rst1.Fields("ckCarboy") = 0
    
            rst1.Update
    
    rst1.Close
    Set rst1 = Nothing
    db.Close
    Set db = Nothing
    
End Function

Thanks,
PC

 

[pbaldy]

No problem. I'd point out that it would be much more efficient to open a recordset on the single desired record than opening the whole table and then going to that record:

Set rst1 = db.OpenRecordset("SELECT * FROM tblChemicalInventory WHERE InventoryID = " & intInventoryID, dbOpenDynaset)

You may want to test for EOF to make sure the record was found.

 

[PC User]

Actually, that was probably the best part of your suggestion. I can think of other recordsets in functions that I did previously inwhich that would have simplified the process. I will use it in my project.

Thanks again,

PC