By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,700 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

Data Entry mulitple entries problem

P: 5
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
Jun 12 '14 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 3,482

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...please 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAdd_Click()
  2.     Dim db As Database
  3.     Dim rst As Recordset
  4.     Dim strSQL As String
  5.     Set db = CurrentDb()
  6.     strSQL = "SELECT * FROM tblMain;"
  7.     Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
  8.     If Not rst.EOF Then
  9.         With rst
  10.             .AddNew
  11.             !Field1 = Me.cboField1
  12.             !Field2 = Me.cboField2
  13.             !Field3 = Me.cboField3
  14.             ...
  15.             .Update
  16.         End With
  17.     End If
  18.     rst.Close
  19.     db.Close
  20.     Set rst = Nothing
  21.     Set db = Nothing
  22. 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!
Jun 12 '14 #2

Post your reply

Sign in to post your reply or Sign up for a free account.