Default Values for initial DB user

  • Thread starter Thread starter Mango-Man
  • Start date Start date
M

Mango-Man

I have looked through the posts, but haven't quite found an answer to this. I
have a database where separate copies will be used by different people. When
they first begin I would like an easy way to ask them for some text or
numerical values that will be used from that point forward as a default value
for certain fields. I am trying to do this in VBA so the user doesn't have to
get into the Table or Form Design portion of Access. Appreciate any help.

MM
 
Do you have a login process in place for the application? Or are you giving
each user a front-end customized to his/her use?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Good morning Jeff - I am using a Switchboard as the initial screen and I have
a form on one of the submenus with buttons to make it easy for the user to
make a Backup, Compact and Repair, and Delete all the values (sample data) in
the tables to prep the database for the new user. On this same form I would
like to have a field where the user can enter a text value, press a command
button, and have this text value placed into the Default Value of a Field in
a Table. Finally I would like to have another field on the same form that
shows the current value of the Default and then the updated value after the
change has been made.
--
"Notre tête est ronde pour permettre à la pensée de changer de sens" - Picabia


Jeff Boyce said:
Do you have a login process in place for the application? Or are you giving
each user a front-end customized to his/her use?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
You are describing a "how", not a "what".

If I understand your description, you want each user to have his/her own
personalized settings.

Like what? (notice we're back to the "what" question -- how depends on what
.... what data you have, what you want to be able to accomplish, ...)

It's hard to offer specific suggestions for a more general situation.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Mango-Man said:
Good morning Jeff - I am using a Switchboard as the initial screen and I
have
a form on one of the submenus with buttons to make it easy for the user to
make a Backup, Compact and Repair, and Delete all the values (sample data)
in
the tables to prep the database for the new user. On this same form I
would
like to have a field where the user can enter a text value, press a
command
button, and have this text value placed into the Default Value of a Field
in
a Table. Finally I would like to have another field on the same form that
shows the current value of the Default and then the updated value after
the
change has been made.
Click to expand...
 
Jeff - Thanks again and appreciate the help. Each copy of the database will
be used by a different group of people. When they first start I need to get a
Project Name from them to be used throughout the database. I have a table
called SAM with a text field called ProjName. I just want to write some VBA
that asks the user to enter 50 chars text (max) and take the value and stuff
it into Default Value of ProjName. This way each new record will have the
default value that was specified as the intial value. Hopefully this provides
enough "what"?
 
So, you're saying you want your database/table of new records (records of
what) to contain a 50 character text string ([ProjName]) ... redundantly?!
What happens if the person entering the ProjName is a ppor speler? What if
no one catches it until after a dozen "new records" have been added?

In Excel, you'd probably need to do it that way.

In Access, you could create a table of ProjectNames, give each an ID
(primary key) column (Autonumber comes to mind), and limit the text of
[ProjNameTitle] to 50 characters. Then, in "each new record" (I'm still not
clear what these new records are records of), you'd only need to store the
ID, not the entire text string. Plus, when the ppor speler corrects his/her
errors, all related (by ID) records are automatically "corrected" ... no
maintenance required!

I'm getting a sense that the tables may reflect more an Excel orientation
than a relational database approach...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff - You are correct that it is not the best approach - ppor speler and
all. I am just unaware of another way to set a single value in Access that
can be used where needed on Forms and Reports to identify the specific
project. As to what the records contain they are a breakdown of the project
tasks - WBS numbers, Task name, Labor resources, Hr estimates, Travel,
Material costs. Excel is not the way to go since it is not conducive to long
text fields and structured reporting. Thought about the separate table idea,
but I only need it to hold a single value. My concern is how to set and
reference a Constant that isn't lost when the database is closed.
--
"Notre tête est ronde pour permettre à la pensée de changer de sens" - Picabia


Jeff Boyce said:
So, you're saying you want your database/table of new records (records of
what) to contain a 50 character text string ([ProjName]) ... redundantly?!
What happens if the person entering the ProjName is a ppor speler? What if
no one catches it until after a dozen "new records" have been added?

In Excel, you'd probably need to do it that way.

In Access, you could create a table of ProjectNames, give each an ID
(primary key) column (Autonumber comes to mind), and limit the text of
[ProjNameTitle] to 50 characters. Then, in "each new record" (I'm still not
clear what these new records are records of), you'd only need to store the
ID, not the entire text string. Plus, when the ppor speler corrects his/her
errors, all related (by ID) records are automatically "corrected" ... no
maintenance required!

I'm getting a sense that the tables may reflect more an Excel orientation
than a relational database approach...

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Actually, that "separate table" would hold ALL the ProjName records! Your
switchboard/startup routine could allow you to determine who the user is,
then "point to" the appropriate ProjName record for that user.

A table of:

trelProjectAssignment
ProjectAssignmentID
PersonID
ProjectNameID

would let you manage who (user) is working on which project ... each person
can only work one project, right?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Mango-Man said:
Jeff - You are correct that it is not the best approach - ppor speler and
all. I am just unaware of another way to set a single value in Access that
can be used where needed on Forms and Reports to identify the specific
project. As to what the records contain they are a breakdown of the
project
tasks - WBS numbers, Task name, Labor resources, Hr estimates, Travel,
Material costs. Excel is not the way to go since it is not conducive to
long
text fields and structured reporting. Thought about the separate table
idea,
but I only need it to hold a single value. My concern is how to set and
reference a Constant that isn't lost when the database is closed.
--
"Notre tête est ronde pour permettre à la pensée de changer de sens" -
Picabia


Jeff Boyce said:
So, you're saying you want your database/table of new records (records of
what) to contain a 50 character text string ([ProjName]) ...
redundantly?!
What happens if the person entering the ProjName is a ppor speler? What
if
no one catches it until after a dozen "new records" have been added?

In Excel, you'd probably need to do it that way.

In Access, you could create a table of ProjectNames, give each an ID
(primary key) column (Autonumber comes to mind), and limit the text of
[ProjNameTitle] to 50 characters. Then, in "each new record" (I'm still
not
clear what these new records are records of), you'd only need to store
the
ID, not the entire text string. Plus, when the ppor speler corrects
his/her
errors, all related (by ID) records are automatically "corrected" ... no
maintenance required!

I'm getting a sense that the tables may reflect more an Excel orientation
than a relational database approach...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Mango-Man said:
Jeff - Thanks again and appreciate the help. Each copy of the database
will
be used by a different group of people. When they first start I need to
get a
Project Name from them to be used throughout the database. I have a
table
called SAM with a text field called ProjName. I just want to write some
VBA
that asks the user to enter 50 chars text (max) and take the value and
stuff
it into Default Value of ProjName. This way each new record will have
the
default value that was specified as the intial value. Hopefully this
provides
enough "what"?
--
"Notre tête est ronde pour permettre à la pensée de changer de sens" -
Picabia


:

You are describing a "how", not a "what".

If I understand your description, you want each user to have his/her
own
personalized settings.

Like what? (notice we're back to the "what" question -- how depends
on
what
.... what data you have, what you want to be able to accomplish, ...)

It's hard to offer specific suggestions for a more general situation.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Good morning Jeff - I am using a Switchboard as the initial screen
and
I
have
a form on one of the submenus with buttons to make it easy for the
user
to
make a Backup, Compact and Repair, and Delete all the values (sample
data)
in
the tables to prep the database for the new user. On this same form
I
would
like to have a field where the user can enter a text value, press a
command
button, and have this text value placed into the Default Value of a
Field
in
a Table. Finally I would like to have another field on the same form
that
shows the current value of the Default and then the updated value
after
the
change has been made.
--
"Notre tête est ronde pour permettre à la pensée de changer de
sens" -
Picabia


:

Do you have a login process in place for the application? Or are
you
giving
each user a front-end customized to his/her use?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have looked through the posts, but haven't quite found an answer
to
this.
I
have a database where separate copies will be used by different
people.
When
they first begin I would like an easy way to ask them for some
text
or
numerical values that will be used from that point forward as a
default
value
for certain fields. I am trying to do this in VBA so the user
doesn't
have
to
get into the Table or Form Design portion of Access. Appreciate
any
help.

MM
Click to expand...
Click to expand...
Click to expand...
 
Mango-Man said:
Jeff - You are correct that it is not the best approach - ppor speler and
all. I am just unaware of another way to set a single value in Access that
can be used where needed on Forms and Reports to identify the specific
project. As to what the records contain they are a breakdown of the project
tasks - WBS numbers, Task name, Labor resources, Hr estimates, Travel,
Material costs. Excel is not the way to go since it is not conducive to long
text fields and structured reporting. Thought about the separate table idea,
but I only need it to hold a single value. My concern is how to set and
reference a Constant that isn't lost when the database is closed.
Click to expand...


Jeff still makes a good point. At some time, you will need
to deal with more than one project and want to have a
different project name. This means that you need a table of
projects (probably with more information than just a name).

I sometimes summarize the rules of Normalization this way:
If you ever need to change a user created value,
it should require exactly one field in one record
in one table to be edited.

Either way you need a table where the user value is saved.
The form's Load event can then be used to retrieve the saved
value using a simple DLookup and stuffes into a text box's
DafaultValue property.

Jeff said that the saved value should be the PK of the
project name in the projects table so different users and or
different groups never have to enter a project name after it
is initially created and if it needs a spelling correction,
all places where is is used do not have to have be edited
too. Another concern is what will you do with your current
idea if a user works on two projects, even if only for a
short transition period?
 
Jeff/Marshall - Good ideas about tying user to a project; however, each
project team will use a different copy of the database for a specific
project. There won't be multiple projects in a single database. The database
is mainly for tracking/estimating/developing tasks within a single project so
the PK is on each task number. I just need a way to collect a text string
from the user - the project name - and store it with that particular copy of
the DB. It does not need to be in a table since there will always be just one
value for this project name, but as you have pointed out, I would like to
have the ability to correct it if necessary or someone decides to rename the
project. I also have to be able to use the value in reports and forms. Seems
like my choices are a string saved to a txt file, a single value in a
separate table (seems like overkill), or a global constant of some sort.
Appreciate any other thoughts that folks have on this.
 
Mango-Man said:
Jeff/Marshall - Good ideas about tying user to a project; however, each
project team will use a different copy of the database for a specific
project. There won't be multiple projects in a single database. The database
is mainly for tracking/estimating/developing tasks within a single project so
the PK is on each task number. I just need a way to collect a text string
from the user - the project name - and store it with that particular copy of
the DB. It does not need to be in a table since there will always be just one
value for this project name, but as you have pointed out, I would like to
have the ability to correct it if necessary or someone decides to rename the
project. I also have to be able to use the value in reports and forms. Seems
like my choices are a string saved to a txt file, a single value in a
separate table (seems like overkill), or a global constant of some sort.
Appreciate any other thoughts that folks have on this.
Click to expand...


I think I should ammend my beiwd normalization summary:

If you ever need to change a user created value,
it should require exactly one field in one record
in one table in one database to be edited.

I think having a separate database for each project will
eventually turn out to be a nightmare.

A table with one or a few records and a few fields for user
settings is a standard in many databases and is way easier
to deal with than using another file.
 
Back
Top