AutoNumber field not generating when Tab Control is used (1 Viewer)

mannhusan · Aug 29, 2016

Hi, I am new to Microsoft Access and programming. My company has asked me to develop a database in MS ACCESS 2010. I have created all the tables and forms but I also need a Main form, where there is Tab Control to navigate these different tabs. These tabs represents data in different tables.

What I need is, there is an AutoNumber field on form as well, which I want it to generate a number whenever user enters information in any of the tab and remains the same throughout each tab until user wants to enter new record for new incident.

Please someone help me with this, there are 15 tabs in TAB CONTROL. I can also attach my database file if needed.

I really want this to be done within next few days, so any help would be highly appreciable.

Thank you in advance for your time and efforts.

Regards
Husanjot Mann

Cronk · Aug 29, 2016

Are all your tables related on the same index field?

Posting a copy of your table relationships might be helpful.

mannhusan · Aug 30, 2016

Hi, I have zipped my database with all the tables, forms and relationship. Please help me fix this.
By default, the "Main_Form" will open where I need to AutoNumber to generate for all the tabs.

This zipped database file is Access 2007/2010/2013 version in ".accdb" format.

Thanks.

Cronk · Aug 30, 2016

You need first to get your database table design right. Every table should have a unique key field, preferably an autonumber.

The autonumber does not come from the form, but from the bound table underlying it.

When you've done that, base your main form only on tbl_incident ie don't link to tblPerson. PersonID should be a foreign field in tblIncident, with a dropdown to select the person assigned to the incident.

mannhusan · Aug 30, 2016

Ok but as I mentioned I am not familiar with any relationship or expert with the database.
And if "Main_Form" is only based on tbl_Incident then it won't show other tabs right?
I want all the tabs on a single form and data should go into respective table with same "Incident Number".

Thanks

mannhusan · Aug 30, 2016

Another thing, my company wants to list out Incident details using "Inc_Num" field which is primary field in "tbl_Incident" and that's why I made "Inc_Num" as foreign field in all other tables as well.

So on the Main_Form, it is placed on top of Tab Control so that only one Inc_Num is generated for all the Tabs.

Just letting you know, if that makes any sense or any help.

sneuberg · Aug 30, 2016

mannhusan said:
Ok but as I mentioned I am not familiar with any relationship or expert with the database.

You are going to have to learn this stuff. I've attached some reading material. It shouldn't take too long to get through it.

And if "Main_Form" is only based on tbl_Incident then it won't show other tabs right?

Once you get the design right this will all work out. The data will be connected through the Link Master Fields and Link Child Fields of the subforms.

moke123 · Aug 30, 2016

get rid of the lookup fields in your tables. Please read this ...http://access.mvps.org/access/lookupfields.htm

i would also google articles on normalization. Tables should be tall and thin.

there really isn't a need to format your primary key. if you want to have the INC00 for display purposes that can be done by concatenation on forms and reports later.

sneuberg · Aug 30, 2016

mannhusan said:
Another thing, my company wants to list out Incident details using "Inc_Num" field which is primary field in "tbl_Incident" and that's why I made "Inc_Num" as foreign field in all other tables as well.

So on the Main_Form, it is placed on top of Tab Control so that only one Inc_Num is generated for all the Tabs.

Just letting you know, if that makes any sense or any help.

After you learn the stuff I think you will choose to make the tbl_Incident the Record Source of the main form. It appears that you tried to make the record source for the main table all of the tables. That's not a good idea. It's best to have one table per form.

mannhusan · Aug 30, 2016

moke123 said:
get rid of the lookup fields in your tables. Please read this ...

i would also google articles on normalization. Tables should be tall and thin.

there really isn't a need to format your primary key. if you want to have the INC00 for display purposes that can be done by concatenation on forms and reports later.

I understand that using lookup fields is not good thing but how do I achieve that then. Any help would be appreciated. I need some of the fields to be multi-valued fields and lookup fields.

mannhusan · Aug 30, 2016

sneuberg said:
After you learn the stuff I think you will choose to make the tbl_Incident the Record Source of the main form. It appears that you tried to make the record source for the main table all of the tables. That's not a good idea. It's best to have one table per form.

The thing is then I have already created forms for each table. But its not good in view of navigation, the user will be opening each form to fill out the details then.

Cronk · Aug 30, 2016

If you remove
DoCmd.GoToRecord , , acNewRec
from your Main form's onLoad event, add navigation buttons and remove tblPerson from the MainForm data source, you will see existing records displayed.

But this does not remove the fundamental requirement, as others are pointing out also, to get your table structure right otherwise if indeed you can get anything working correctly, it will always be more difficult.

mannhusan · Aug 30, 2016

Thank you all for your help, but I really don't know what to do. I am not sure about how to do that.

moke123 · Aug 30, 2016

I need some of the fields to be multi-valued fields and lookup fields.

instead of a lookup field, you make a lookup table. then in your form you have a combobox or listbox with its rowsource set to the lookup table. you then store the primary key of your lookup table as a foreign key. Lookup fields hide what is really going on. It looks like your storing "Volunteer" in the table but in reality there is a hidden table and your actually storing the primary key from that table. It causes all sorts of problems going forward.

I would also avoid using MVF's. Better to have another table.

Bottom line is to split all your info to the smallest groups of related information and then join them in another table using the keys.

for instance your incident details table has 60+ fields. included in that is a witness field. What happens if there are 2, 3 or 7 witnesses? You should have a witness table with a primary key, an IncidentID and a witnessID.

AutoNumber field not generating when Tab Control is used (1 Viewer)

mannhusan

New member

Cronk

Registered User.

mannhusan

New member

Attachments

Cronk

Registered User.

mannhusan

New member

mannhusan

New member

sneuberg

AWF VIP

Attachments

moke123

AWF VIP

sneuberg

AWF VIP

mannhusan

New member

mannhusan

New member

Cronk

Registered User.

mannhusan

New member

moke123

AWF VIP

Similar threads

Users who are viewing this thread