Input and Edit Problems Version: 2000

P

PC User

My database for chemical inventories has a lot of data on the main
form and then I added an number of checkboxes which had to go onto
another form which I made as a popup form. I have the popup form
syncronized with the main form through a Record Source query with
criteria linked by InventoryID. The problem is with this popup "input
form" is that can create additional records instead of just editing
the existing record. I've tried to code the form to search through the
table for only the one record that I intended to edit and then change
the existing information to the new information, but I can't get it to
work. It still creates extra unneeded records instead of editing only
the record that was selected. Below is the code that I'm using. If
someone can help me with the code or refer me to an example that does
what I'm intending, please reply.
Code: 
Private Function InsertData()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form
Dim sfGS As Form

Set db = CurrentDb
Set rst = db.OpenRecordset("tblChemicalInventory", dbOpenTable)
Set frm = Forms!frmMain.Form
Set sfGS = frm.[ctlGenericSubform].Form

'If sfGS.[InventoryID] = Me.[InventoryID] Then
'If Me.[InventoryID] = sfGS.[InventoryID] Then

rst.FindFirst "rst![ProgramID] = " & Me.[InventoryID]

With rst
.Edit
'Storage Container
![Aboveground Tank] = Me![Aboveground Tank]
![Underground Tank] = Me![Underground Tank]
![Tank Inside Building] = Me![Tank Inside Building]
![Steel Drum] = Me![Steel Drum]
![Plastic/Nonmetalic Drum] = Me![Plastic/Nonmetalic Drum]
![Can] = Me![Can]
![Carboy] = Me![Carboy]
![Silo] = Me![Silo]
![Fiber Drum] = Me![Fiber Drum]
![Bag] = Me![Bag]
![Box] = Me![Box]
![Cylinder] = Me![Cylinder]
![Glass Bottle] = Me![Glass Bottle]
![Plastic Bottle] = Me![Plastic Bottle]
![Tote Bin] = Me![Tote Bin]
![Rail Car] = Me![Rail Car]
![Other] = Me![Other]
.Update
End With
rst.Close

'Else
'    Exit Function
'End If

End Function

=========================
Private Sub btnSave_Click()
Call InsertData
End Sub
=========================
Thanks,

PC
 
G

George Nicholson

1)
rst.FindFirst "rst![ProgramID] = " & Me.[InventoryID]
Click to expand...
should be:
rst.FindFirst "[ProgramID] = " & Me.[InventoryID]

since FindFirst applies to rst, you don't need to specify "rst" in the
criteria, it is implied. The "rst!" portion of "rst![Program]" is redundant
and not expected by the compiler thereby causing the statement to fail
(not find anything). Simply "[ProgramID] = " will suffice.

2) Set the "Allow Additions" property of your popup to False if you don't
want it to be capable of creating new records.

3) Consider adding the following after FindFirst to make sure you've found a
match:
If rst.NoMatch Then
'Exit sub? Prompt user? Error Message?
' Useful while debugging FindFirst criteria if for no other reason.
Else
With rst
'your existing With...End With
End With
End If

HTH,



PC User said:
My database for chemical inventories has a lot of data on the main
form and then I added an number of checkboxes which had to go onto
another form which I made as a popup form. I have the popup form
syncronized with the main form through a Record Source query with
criteria linked by InventoryID. The problem is with this popup "input
form" is that can create additional records instead of just editing
the existing record. I've tried to code the form to search through the
table for only the one record that I intended to edit and then change
the existing information to the new information, but I can't get it to
work. It still creates extra unneeded records instead of editing only
the record that was selected. Below is the code that I'm using. If
someone can help me with the code or refer me to an example that does
what I'm intending, please reply.
Code: 
Private Function InsertData()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form
Dim sfGS As Form

Set db = CurrentDb
Set rst = db.OpenRecordset("tblChemicalInventory", dbOpenTable)
Set frm = Forms!frmMain.Form
Set sfGS = frm.[ctlGenericSubform].Form

'If sfGS.[InventoryID] = Me.[InventoryID] Then
'If Me.[InventoryID] = sfGS.[InventoryID] Then

rst.FindFirst "rst![ProgramID] = " & Me.[InventoryID]

With rst
.Edit
'Storage Container
![Aboveground Tank] = Me![Aboveground Tank]
![Underground Tank] = Me![Underground Tank]
![Tank Inside Building] = Me![Tank Inside Building]
![Steel Drum] = Me![Steel Drum]
![Plastic/Nonmetalic Drum] = Me![Plastic/Nonmetalic Drum]
![Can] = Me![Can]
![Carboy] = Me![Carboy]
![Silo] = Me![Silo]
![Fiber Drum] = Me![Fiber Drum]
![Bag] = Me![Bag]
![Box] = Me![Box]
![Cylinder] = Me![Cylinder]
![Glass Bottle] = Me![Glass Bottle]
![Plastic Bottle] = Me![Plastic Bottle]
![Tote Bin] = Me![Tote Bin]
![Rail Car] = Me![Rail Car]
![Other] = Me![Other]
.Update
End With
rst.Close

'Else
'    Exit Function
'End If

End Function

=========================
Private Sub btnSave_Click()
Call InsertData
End Sub
=========================
Thanks,

PC
Click to expand...
 
Top