Hello Everyone,
I am pretty new to access Vba development. I hope someone would be able to help me with my database design. My Questions are all grouped at the bottom with regards to the issues.
Brief Design Spec[\b]
My clients have provided several excel templates. Each template has about 60 columns with the same column headings, but only different data. Some of these columns have data already entered by our clients and others have to be filled by data inputters via a database.
Once all the data is collected for the empty columns via the database, these entries should be exported back to the excel sheet in the relevant columns ensuring that none of the existing data gets deleted.
Database Design:
I have designed the database up to the following stage:
Tables:
tblB&S
AssetID PK
Asset_Number (this is the number the clients go by for all records)
……………….. (All other Fields)
tblD&T
AssetID PK
Asset_Number
………………… (All other fields)
Forms:
frmB&S
frmD&T
The tables have been designed according to the excel templates. tblB&S has 60 fields at the moment which has been bounded to frmB&S]. Some of these fields are combo boxes on the form.
The form has been designed to have to a tab control with 2 pages. All the information that has been filled is on the first page and the second is for the data inputting.
There are 4 main combo boxes that the form depends on. Combo box cboEllipse, has about 10 look up values. When a user selects e.g. Gantry from the combo box, there are about 5 text boxes on the form that should be either greyed out or should not be visible to the data inputter. This is the case for all the lookups, certain text boxes are not relevant for its entry.
Another design requirement required if another combo box. cboALocationType has three locations to choose from. Depending on what the users choose, e.g. Depot, then certain text boxes should end up having default values appear in the text boxes, such as NA or is that possible.
QUESTIONS: Exporting to Excel
Question 1: Is it possible to export certain fields from the access database into the relevant columns into excel? Or would I have to import all the data provided by the clients into the database then export once all the data is full entered? Is there a query or can a VB code be constructed that can carry this out?
QUESTION 2: Tab Control
If the export function is possible, is it better to remove the Page 1 on the tab control as this data has already been collected? If not, I would need to hide the Page 1 on the tab control or have it greyed out and have only Page 2 available.
Question 3: TextBoxes
How do I hide certain textboxes depending on the selection made from a combo box?
e.g From cboEllipse
If Gantry is selected. Then txt1, txt4, txt6 is greyed out or not visible.
- How do I have certain text boxes default to show NA depending on a selection made from combo box.
- Am not too sure, but do I have to use select case statements, if yes how do I construct them?
Any help would be appreciated.
I am pretty new to access Vba development. I hope someone would be able to help me with my database design. My Questions are all grouped at the bottom with regards to the issues.
Brief Design Spec[\b]
My clients have provided several excel templates. Each template has about 60 columns with the same column headings, but only different data. Some of these columns have data already entered by our clients and others have to be filled by data inputters via a database.
Once all the data is collected for the empty columns via the database, these entries should be exported back to the excel sheet in the relevant columns ensuring that none of the existing data gets deleted.
Database Design:
I have designed the database up to the following stage:
Tables:
tblB&S
AssetID PK
Asset_Number (this is the number the clients go by for all records)
……………….. (All other Fields)
tblD&T
AssetID PK
Asset_Number
………………… (All other fields)
Forms:
frmB&S
frmD&T
The tables have been designed according to the excel templates. tblB&S has 60 fields at the moment which has been bounded to frmB&S]. Some of these fields are combo boxes on the form.
The form has been designed to have to a tab control with 2 pages. All the information that has been filled is on the first page and the second is for the data inputting.
There are 4 main combo boxes that the form depends on. Combo box cboEllipse, has about 10 look up values. When a user selects e.g. Gantry from the combo box, there are about 5 text boxes on the form that should be either greyed out or should not be visible to the data inputter. This is the case for all the lookups, certain text boxes are not relevant for its entry.
Another design requirement required if another combo box. cboALocationType has three locations to choose from. Depending on what the users choose, e.g. Depot, then certain text boxes should end up having default values appear in the text boxes, such as NA or is that possible.
QUESTIONS: Exporting to Excel
Question 1: Is it possible to export certain fields from the access database into the relevant columns into excel? Or would I have to import all the data provided by the clients into the database then export once all the data is full entered? Is there a query or can a VB code be constructed that can carry this out?
QUESTION 2: Tab Control
If the export function is possible, is it better to remove the Page 1 on the tab control as this data has already been collected? If not, I would need to hide the Page 1 on the tab control or have it greyed out and have only Page 2 available.
Question 3: TextBoxes
How do I hide certain textboxes depending on the selection made from a combo box?
e.g From cboEllipse
If Gantry is selected. Then txt1, txt4, txt6 is greyed out or not visible.
- How do I have certain text boxes default to show NA depending on a selection made from combo box.
- Am not too sure, but do I have to use select case statements, if yes how do I construct them?
Any help would be appreciated.
