72 Fields on a MsAccess 2007 form

[access2010]

The biggest problem is you have 72 fields on an Access form, it sounds horrendous and unusable.

Thank you AccessBlaster for your comments, which I agree with.

At the present time we are working on using 56 of the fields.
Nicole

 

[access2010]

Hi Crystal,

I did a YouTube video on pagination using subforms and tab controls.

It somewhat resembles your problem. However it is not the same, in that I assume you have three different sources of information whereas the pagination example I show uses the same source of data, but divided into three sets to establish the pagination look.

The YouTube video below takes you through how I developed the VBA code for this pagination form, and I suspect there might be some relevant code and ideas you might be able to employ in your solution....

Pagination - Nifty Access

Thank You Uncle Gizmo for your video, which I will review while working on our forms?

Nicole

 

[AccessBlaster]

Good luck Nicole with your project!

 

[LarryE]

@ClaraBarton:
I have an idea for you to consider. It's pretty clear you want to use ACCESS forms as a way to view EXCEL data, which is going to be a problem for ACCESS because the data is not normalized into a relational database format. But I have a proposal for you. You stated that some of the data is proprietary and cannot be shared. But EXCEL columns CAN BE individually password protected, so you can password protect and hide the column(s) that you wish to protect, and you could then attach that EXCEL file to a post, and I would be willing to look at it and normalize it into the ACCESS relational model for you. Then I can pass the normalized ACCESS file back to you and you could then import the proprietary column data back into the ACCESS table it belongs in. Below is a screenshot of the EXCEL menu items you would use to protect data in a particular column.

1. Select the column you wish to protect (I selected the F column)
2. Hide the Column (I show it in this example)
3. Select the Review tab
4. Select Allow Edit Ranges
5. Click New
6. Click Protect Sheet
7. Enter a password

This might be a way to protect your data and still pass the EXCEL file to be used to convert to an ACCESS project. Why don't you try it out on one of your spreadsheet columns and then talk it over and let us know what you think.

 

[LarryE]

I think @Uncle Gizmo has a tool that helps to normalize your spreadsheets.

We get this issue a lot and so I was just experimenting with one of my EXCEL sheets. Turns out you can hide and protect Indvidual columns. Cool. Just more new stuff I learned. It's getting kind of crowded up there in my head...

 

[The_Doc_Man]

I haven't got that problem, @LarryE - some folks think my head is nearly empty.

 

[gemma-the-husky]

I am not sure if this has been touched on, but often spreadsheets aren't designed in the same way as access tables. You may not really need 72 different text boxes on your form.

You may need just 3 text boxes on your form, with a continuous form to show 24 rows of data.

 

[Mark_]

You are doing data comparison between three sheets in an excel spreadsheet.
What advantage does Access have over just using a separate sheet in excel to do the same?

I am asking because I just finished up a project where I put together a multi-sheet scheduling system on TEAMs. Main reason I went this how easy it could be shared through TEAMs.

 

[access2010]

We have 3 Excel Spreadsheets with 24 columns on each sheet.

Can we have 72 fields containing this data for ONE item on a Ms Access 2007 form using Tabs?

Thank you.
Crystal

Thank you all for your suggestions. We have created a form with 3 sub forms each with 24 fields relating to the Excel Spread sheets and this seems to be working.
Nicole

 

[access2010]

@ClaraBarton:
I have an idea for you to consider. It's pretty clear you want to use ACCESS forms as a way to view EXCEL data, which is going to be a problem for ACCESS because the data is not normalized into a relational database format. But I have a proposal for you. You stated that some of the data is proprietary and cannot be shared. But EXCEL columns CAN BE individually password protected, so you can password protect and hide the column(s) that you wish to protect, and you could then attach that EXCEL file to a post, and I would be willing to look at it and normalize it into the ACCESS relational model for you. Then I can pass the normalized ACCESS file back to you and you could then import the proprietary column data back into the ACCESS table it belongs in. Below is a screenshot of the EXCEL menu items you would use to protect data in a particular column.
View attachment 116357

1. Select the column you wish to protect (I selected the F column)
2. Hide the Column (I show it in this example)
3. Select the Review tab
4. Select Allow Edit Ranges
5. Click New
6. Click Protect Sheet
7. Enter a password

This might be a way to protect your data and still pass the EXCEL file to be used to convert to an ACCESS project. Why don't you try it out on one of your spreadsheet columns and then talk it over and let us know what you think.

Thank you, we have had success with your suggestion.