Tables changing the default value of a field by a user through a f

[jimbo]

I have a table where I import data which I need to update periodicalloy from
another source.. I do this by:
1)Importing the source data into a new table.
2)Deleting all the records in the existing table but retain all the existing
feild names.
3)Copy the records from the new table into the existing table retaining the
existing feild names.
4)All the new copied records show a value coming from a default value stored
in a existing filed in the table
The question is;_
I want a user with no access experinence to be able to change the default
value through a form to olivate the need to go to the table design and
physicaly change the default value for all the records either before or after
the update.. I.e the default value is 1 and I want the user to change this to
2.

 

[Allen Browne]

If you are comfortable writing VBA code, you could:

a) Create yourself a table to store the default values for controls on a
form.
Fields might be something like this:
DocName Text Name of the form this applies to.
CtrlName Text Name of the control to apply this
default value to
DfltValue Text Actual text to assign to the control.

b) Use the Open event of the form to OpenRecordset on the records for
Me.Name, and loop through the records, assigning the value of DfltValue to
Me(rs!CtrlName).DefaultValue

c) Provide an interface where the user can set the default values they want,
and you assign them to the field of the table (with any delimiters needed
for the data type.)

 

[David W. Fenton]

If you are comfortable writing VBA code, you could:

a) Create yourself a table to store the default values for
controls on a form.
Fields might be something like this:
DocName Text Name of the form this applies
to. CtrlName Text Name of the control to
apply this
default value to
DfltValue Text Actual text to assign to
the control.

b) Use the Open event of the form to OpenRecordset on the records
for Me.Name, and loop through the records, assigning the value of
DfltValue to Me(rs!CtrlName).DefaultValue

c) Provide an interface where the user can set the default values
they want, and you assign them to the field of the table (with any
delimiters needed for the data type.)

I would never do it that way.

Instead, I'd create a table where the user could store personal
configuration data, and set the Default values for the controls from
data looked up from that table in the OnLoad event of the forms.

It just makes no sense to ever go into design mode in a production
app, in my opinion.

 

[Allen Browne]

I didn't follow that, David.

I'm not suggesting design view. Rather, storing the default values to be
applied to controls on forms, and applying them in Form_Open.

Advantages (compared to using the DefaultValue of the field in the table):
- works for unbound controls too.
- can be adapted so different users can have different defaults.
- can be adapted to use expressions not legal in Default Value in table
design.
- Averts a bug where Access misapplies a default value from a lookup table
when creating a record in the related table.

Perhaps I didn't understand your comment.

 

[David W. Fenton]

I didn't follow that, David.

Precisely because you assumed that I was suggesting something
different from what you wrote. In fact, I don't know what in the
world I was reading when I responded, but I basically restated what
you did!

D'oh!

Anyway, my only quibble is using the OnOpen event. I think OnLoad is
safer, because then you can be sure that the controls already exist,
which they may not at the time the OnOpen event fires.