Input and Edit Problems Version: 2000

[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.

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

 

[missinglinq]

Just to clarify, are you saying that you're using two forms to enter data in one table, because you've run out of room on your main form, due to the large number of fields?

 

[ajetrumpet]

Yeah, I think that's it Linq. Sounds like a duplicate form of one table, just for editing purposes.

PC User, here are some observations that may be relevant...

* You executed a FINDFIRST command, but did not follow up on it. So, Access found the record, but didn't do anything with it.
* All of your dot notation update code is contained in the "with RST" block. You are only specifying the table to update, not a certain record ("rst" is just dimmed as an "openrecordset", and no filter is specified for any data).
* You have two "IF" blocks with only one "End If" line.
* Set sfGS = frm.[ctlGenericSubform].Form - If this is the popup, it is a separate object. It should probably be specified as one in the Forms collection
(e.g. - Forms!, etc, etc...).

 

[missinglinq]

If it's just a real estate problem, caused by all the textboxes/checkboxes, than rather than two forms with the same record source tey need to use tabbed pages.