Data Entry mulitple entries problem

[tinamiller1]

I have a main form and data entry subform. When the main form is selected, the user can view records that exist in the main table in order to determine if they want to complete a data entry form or head over to the update form. The data entry form uses unbound for comboboxes because they need to select from other tables where they can see all items. I have a total of 12 comboboxes that pull data from 12 various tables. Then I have the hidden _id fields from my main table. My database is normalized and the 12 tables that have a relationship to the main have data that will never be update or altered in anyway. Only the main table is altered and an example is I have a state table that lists all 2 letter codes for states and the primary key is st_id which is in the main table. The end user can see the 2 letter state codes because of the rowsource I have. My form record source is a query that has all the relationships so only the _id fields are updated in the main table. Now, some have said bound is best, but when dealing with multiple relationships like this, and the users needing options to select items from other tables, then those have to be unbound.

My problem here is I want to add a button so when the user clicks add, it will add the record but not clear what they entered because they might need to enter the same information and only change 1 item. For example they entered all the data and selected CA for ST_CD and now want to enter all the same data but for NY. When they add right now it clears all fields and they have to select again and I don't want that. Or they don't want that.

The other issue is a button to clear their selection IF they need to enter complete new records

Right now I just have a reset button and say make everything null

[twinnyfo]

Tina,

I think we may need a little better explanation. If there are relationships between the Main Table and the 12 other Tables, I'm not sure I understand why the controls are all unbound.

However, based on your description of "how" you want users to be able to enter data, that would justify an unbound control. If htis is the case, the user enters all their data and clicks "Add" and those entries are copied into the Main Table.

I think I understand...pl ease correct me.

If this is the case, then your answer is pretty simple (if the subform is NOT in a Parent-Child relationship with the Main form AND you have unbound controls.

Here is a starting point:

Private Sub cmdAdd_Click()
    Dim db As Database
    Dim rst As Recordset
    Dim strSQL As String
    Set db = CurrentDb()
    strSQL = "SELECT * FROM tblMain;"
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rst.EOF Then
        With rst
            .AddNew
            !Field1 = Me.cboField1
            !Field2 = Me.cboField2
            !Field3 = Me.cboField3
            ...
            .Update
        End With
    End If
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing
End Sub

This should update your Main Table, but not clear anything on the subform.

The other issue of clearing all the combo boxes would be similar, but the code would just set each combo box to "" or null.

Let me know if this hepps to get you on the right track!