How to prevent a Subform to create a new Datasheet (2of2) (1 Viewer)

Raskahl

New member
Local time
Today, 13:50
Joined
Jul 4, 2020
Messages
2
Hello guys I'm new to the Forum and relatively unexperienced with Access,
I Need to create a large Form for medical data Insertion and since the number of values to insert was Huge i had to create two subforms.
To facilitate moving from subform to subform i added a lost Focus on the last entry of the subform to jump directly to the main form and then to the next subform.
Sadly when i press TAB from the last entry it does correctly jump to the next subform but it also creates an additional datasheet (2 of 2) in the subform,
how can I prevent that? It's annoying since all the entered values disappear and so confuse the People that are doing the data Entry and also is a potential for mistakes.
Thanks alot in advance
P.S. sometimes it seems to work flawlessly and sometimes it jumps to somewhere random, could it be because of different Access Versions?
 

Attachments

  • DATAENTRY.accdb
    3.8 MB · Views: 158

theDBguy

I’m here to help
Staff member
Local time
Today, 05:50
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Sounds like you could try setting the Cycle property of the subform to Current Record.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:50
Joined
Feb 19, 2002
Messages
42,981
Your problem is caused by a lack of normalization. You have many repeating groups. It is time to take off your Excel hat and put on your Relational Database hat. With Excel, the data is stored as you see it. That isn't how relational databases work. Please spend some time reading about normalization before proceeding and further. It looks like you have 5 instances of each data point. I would have offered more concrete suggestions but I don't speak the language of your form. The thing to understand is that you can display "rows" of data horizontally so that the result looks much like a spreadsheet.

You also have a lot of poorly formed names. Object names, including tables and fields, should be comprised of ONLY letters (upper/lower), numbers, and the underscore. There should be NO embedded spaces or any other special characters except the underscore. Just because Access allows you to build names that will give you trouble with VBA doesn't mean that you should.

And finally, you have designed what looks like a 1-1-1 relationship but you are using autonumbers in the second and third tables and that is incorrect. The "parent" table uses an autonumber and the dependent child records would use long integer and be populated with the linking id from the parent table. Also, the only reason you have this 1-1-1 relationship is because you have two many fields to fit everything in one table. Once you have properly fixed the repeating groups, you will still have way more fields than you should have in a single table but you will be in a better place to go forward. The parent table contains the data that is constant for each row, the child tables contain (in your case) 5 rows for each parent row.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:50
Joined
Jul 9, 2003
Messages
16,245
Regarding normalisation you might find my blog Excel in Access useful:-

 

Raskahl

New member
Local time
Today, 13:50
Joined
Jul 4, 2020
Messages
2
Hey guys thanks a lot for your fast and exhaustive answers,
I would love to convert to a relational database but honestly I'm afraid I would screw up all the work I did till now (alot of hours) and also im not sure I would be able... (as said before, I'm not a programmer, just an amateur that needed a way to store data for a medical study and was already proud of what I had achieved although I now understand it's quite a mess hehe)
I did however change the ID to long integer in the 2 parent tables as you suggested and also changed the cycle property.
I will definitely try to use a relational database the next time I will do an access Form =)
Or otherwise would anyone of you do it to this database? Of course with reasonable monetary compensation ;)
Best thanks to all of you!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:50
Joined
Feb 19, 2002
Messages
42,981
If this is a one-time study, let it be if you are happy with how it works. However, if you will need to perform this study again or perhaps perform other studies, it will be well worth the effort to clean up the schema design because it will make your analysis easier.

Spreadsheets are a matrix of data and what you see is what you get so how.

I would have bid on your project but the language barrier makes it too hard. Good luck.
 

Users who are viewing this thread

Top Bottom