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:
========================================
Quote:
Compile error:

Expected Function or variable

========================================
Code:
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.Edit![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

 

[Dirk Goldgar]

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:
========================================
Quote:
Compile error:

Expected Function or variable

========================================
Code:
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.Edit![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

First, this line doesn't make sense:

rst1.FindFirst "InventoryID = " & rst1!intInventoryID

.... unless, that is, the table contains both a field named "InventoryID" and
a field named "intInventoryID", and you want to find the first record where
those two fields are equal. I suspect you want to find the record that
matches the intInventoryID that was passed to the function. You could do
that like this:

rst1.FindFirst "InventoryID = " & intInventoryID

Second, you're not using the .Edit method correctly. You should first call
the .Edit method to put the recordset into edit mode, then update the fields
as needed, then call the .Update method to save the changes. Like this:

'----- start of (untested) revised code -----
Set rst1 = _
db.OpenRecordset("tblChemicalInventory", dbOpenDynaset)

With rst1
.FindFirst "InventoryID = " & intInventoryID
If Not .NoMatch Then

.Edit

If intContainer = 1 Then
![ckAboveground Tank] = 1
Else
![ckAboveground Tank] = 0
If intContainer = 2 Then
![ckUnderground Tank] = 1
Else
![ckUnderground Tank] = 0
If intContainer = 3 Then
![ckTank Inside Building] = 1
Else
![ckTank Inside Building] = 0
If intContainer = 4 Then
![ckSteel Drum] = 1
Else
![ckSteel Drum] = 0
If intContainer = 5 Then
![ckPlastic/Nonmetalic Drum] = 1
Else
![ckPlastic/Nonmetalic Drum] = 0
If intContainer = 6 Then
![ckCan] = 1
Else
![ckCan] = 0
If intContainer = 7 Then
![ckCarboy] = 1
Else
![ckCarboy] = 0
End If
End If
End If
End If
End If
End If
End If

.Update

End If

.Close

End With
'----- end of (untested) revised code -----

This code does seem unnecessarily convoluted, and can probably be improved.
Also, you should probably just open your recordset on a query that returns
the one record you want to update, rather than opening it on all records i
the table and then finding that record. For example,

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

 

[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