# Solved: Access - Forms creating multiple records in "add" mode



## VBA2010 (Aug 9, 2010)

VBA: 
Using Access 2003, Microsoft VB 6.5
I have an Access table with over 100 fields.
I created 3 forms and one subform (all bound to the same table), for the users to enter their information. Each field in the table is updated in only one form (e.g., cannot change the value of a field on Form1 in Form2)
Form1 includes 3 buttons which allow the user to toggle from one form to the other (including back to Form1, since the buttons part of Form1 remains visible even thought the other forms are open).
Form1 is opened in _add mode _from a Switchboard.
Form2 & Form3 can only be opened through Form1 (by clicking on one of the buttons).
The subform is on Form2. In most cases, the user will enter data into the subform before entering data in Form2's controls.
The Navigation Buttons, Record Selectors, Close Button and Control Box are all set to "No" on all forms.
The only way to close Form2 and Form3 is through Form1, which has an Exit button to (presumably) save the data and close all 3 forms.

*At First, when adding a new record:*
I would open Form1 in _Add_ mode. The table has an index key, (id), which is an AutoNumber.
Form2 and Form3 were opened in add mode.
I assumed (*wrongly*) that all the data entered would be stored in one record. I ended up with 4 records, with different id #s, with data from each of the 3 forms and one with data from the subform.
So I changed my code to open Form2 & Form3 in edit mode, linking them with the id from Form1.
I still ended up with multiple records.

*I tried another tactic:*
_Since I have no problems editing data on already existing records_, I decided to create a new record from the Switchboard, when the user selects "New Transfer", and _BEFORE_ opening Form1.
Instead of opening Form1 in _add_ mode, I open it in _edit_ mode and link it to the newly created id.
Form2 and Form3 are also opened in _edit_ mode, using the id as the link.
Now I get the "Write Conflict" message when I click on the Exit button on Form1 - see attached jpg:
"Save Record" - I lose the changes I made on Form2 (including the subform) & Form3.
"Copy to Clickboard" is useless, because I am the only user in this application, and the system is mistaking the entries in Form2 & Form3 as coming from someone else. 
"Drop Changes" brings up the "*You can't save this record at this time*" error message. and if I click "Yes" (to close the database object), it goes back to the Write Conflict message. (If I choose "Drop Changes a second time, it still brings up the "can't save" message, and a "Yes" to that finally closes the forms. BUT I still lose the data from the other forms. One time it actually saved the data from the other forms, but not Form1, and also retained the data I used to create the record in the first place. This may have been a fluke since I made the mistake of using my mousewheel on the subform (see explanation below in "Addendum")

Any suggestions? I have been wracking my brains since last week, and searching for similar situations on the web, but have not been able to find anything useful. I did come across a couple of places where the suggestion was to create unbound forms and use SQL queries to fill data on the forms or save data to the table. NOT what I want to do, so I thought I'd give myself one more chance to find another solution.

Addendum:
An additional problem I encounter is on the subform - the controls are in columns - although all Navigation buttons have been disabled, the data still scrolls (to a blank form) if I accidentally use my mouse wheel. this, of course, causes problems because the application thinks I want to create a new record with the same id. How can I disable the mouse wheel scroll?

I've attached the a doc file with the pertinent code from both the Switchboard and Form1, and also an image of the Write Conflict message.

Thanks for your help!


----------



## OBP (Mar 8, 2005)

VBA2010, welcome to the forum, you will tend to get quicker responses to Access/Excel/Word questions if you post them on the Business Applications forum.
What you actually need is 1 single form with Tabs on it, because the form and it's tabs all use the same record source at the same time you do not get the multiple record entries.
I am not sure that 100 fields in one table is the right way to enter your data, but without seeing the Table and data structure it is hard to be sure.
I am also not sure why you would need a Subform, as they are normally reserved for entering data in to a Related table to the Forms Record Source (main table).


----------



## VBA2010 (Aug 9, 2010)

Thank you, OBP.
I am new to the forum, and I was not sure where to post. I had seen another thread where they told the poster to post under Software Development because it was a programming qustion. Anyway, I will keep that in mind for next time.
You are quite correct about using tabs and subforms. Long story on the use of the ssubform, which I won't bore you with now.

Tabs worked beautifully, and I got rid of the subform also.
Thank you for your advice.


----------



## OBP (Mar 8, 2005)

Well done on fixing it. :up:


----------



## VBA2010 (Aug 9, 2010)

Thanks again!


----------

