change default value in table field - Easy for user

  • Thread starter Thread starter jassnaround
  • Start date Start date
J

jassnaround

I have a database I have created for a user who needs an easy interface to
use to update the Federal Poverty Level calculation when it is changed by the
government. I currently have the fields "PovertyLevelAmt" and
"AddFamilyMemberAmt" in the "Tbl_Patients" set to the default value for those
fields. It is easy for me to change the default values manually, but the
user will not want to learn how to get to the default value area manually to
change it there. Does anyone have an idea of how she can very easily set the
new default values in either the table fields? I am new to Access
programming and having a hard time visualizing how to set this up. If code
is needed, could someone also help me with that? Appreciate any ideas.
 
I would create a table to store these default values. Then you can set the
Default Value property of a control on a form with something like:
Default Value: =dlookup("PovertyLevelAmt","tblDefaults")
 
To add some to Duane's reply, I often times have two tables of default values
in my applications: one for a user's choices, which is in the FE (Front-End)
of a split application, and one that applies to all users in the BE
(Back-End). That's probably the easiest way to handle this situation.

However, to satisfy my own curiousity, I found a function on Access MVP
Allen Browne's web site that allows changing these types of values in the
underlying table directly:

http://www.allenbrowne.com/func-ADOX.html#ModifyFieldPropAdox

The code requires a reference set to the "Microsoft ADO Ext. 2.8 for DDL and
Security" Object Library. Here is a modified version that changes some
properties for the UnitPrice field, in the Products table, of the sample
Northwind database that ships with Access 2003. This modified version is
still using hard-coded values, but it shouldn't be too difficult to change
the function to accept parameters. Of course, you will need exclusive access
to the database, in order to make this type of design change. So, a DBA for
your customer would need to have all user's out of the database when they ran
a custom function to change these properties. In addition, they would need to
run this code in the BE of a split application, or the code would need to be
modified to set the active connection to the BE database.

Option Compare Database
Option Explicit

Function ModifyFieldPropAdox()
'Purpose: Show how to alter field properties, using ADOX.
'Note: You cannot alter the DefinedSize of the field like this.
'Modified: To show an example of setting and reading the default value,
' validation text and validation rule for the UnitPrice field.

Dim cat As New ADOX.Catalog
Dim col As ADOX.Column
Dim prp As ADOX.Property

cat.ActiveConnection = CurrentProject.Connection
Set col = cat.Tables("Products").Columns("UnitPrice")

' For Each prp In col.Properties
' Debug.Print prp.Name, prp.Value
' Next prp

'Default Value
Set prp = col.Properties("Default")
Debug.Print prp.Name, prp.Value
prp.Value = 20
Debug.Print prp.Name, prp.Value

'Validation Rule
Set prp = col.Properties("Jet OLEDB:Column Validation Rule")
Debug.Print prp.Name, prp.Value
prp.Value = ">=20"
Debug.Print prp.Name, prp.Value

'Validation Text
Set prp = col.Properties("Jet OLEDB:Column Validation Text")
Debug.Print prp.Name, prp.Value
prp.Value = "The minimum acceptable price is $20.00."
Debug.Print prp.Name, prp.Value

'Clean up
Set prp = Nothing
Set col = Nothing
Set cat = Nothing

End Function


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Thanks to both of you who responded. I am not sure how to use the
information yet but I will keep it until I learn a little more about
programming in VBA. I'm sure it will make sense then.
 
Back
Top