S
srpatel
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
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 Cannot have duplicate Values (this is the number the
clients go by for all records)
..................... (All other Fields)
tblD&T
AssetID PK
Asset_Number Cannot have duplicate values
...................... (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.
And 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?
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
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 Cannot have duplicate Values (this is the number the
clients go by for all records)
..................... (All other Fields)
tblD&T
AssetID PK
Asset_Number Cannot have duplicate values
...................... (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.
And 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?