Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2010 Change Default Value for a Table field

Posted on 2013-01-05
7
Medium Priority
?
1,913 Views
Last Modified: 2013-01-06
Hello experts,

I have some number fields in my table that have Default Values set in the Field Properties window. These fields and the default values in them are required as a minimum for every record saved.

Every now and then, these default values will need to be changed. The users of this database are not very technically savvy. I don't want them bumbling around in design view trying to change these values. I need a text box + button press way to change the default value for these specific table fields.

Is this possible?

My database is already built, so I am trying to avoid major changes.

Thanks in advance.
0
Comment
Question by:Jpoppi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 16

Expert Comment

by:Kalpesh Chhatrala
ID: 38746679
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 38746737
You can do this in VBA like this:

Set db = CurrentDb
db.TableDefs("YourTable").Fields("YourColumn").Properties("DefaultValue") = "0"

Note that if the DefaultValue has NOT been previously set for the column, you'll get an error (The property does not exist until you create it).

You should also insure that all users are out of the database before doing this.
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 1000 total points
ID: 38746745
If you have a table called Inventory with a field called Markup, you can add a textbox to  your form called txtDefaultMarkUp and a button called btnChangeDefaultMarkup.  In the button's OnClick event you would have this code:

Private Sub btnChangeDefaultMarkup_Click()
    CurrentDb.TableDefs("Inventory").Fields("MarkUp").Properties("DefaultValue") = txtDefaultMarkup
End Sub

Open in new window


Be sure that the table is not open.  If you have it bound to a form that's open you will get an error so close those forms first.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:Jpoppi
ID: 38746757
Thanks everyone for your responses.

I shall try these methods out straight away. :)
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38746765
In addition, if you want to add the textbox and button to a form that is bound to the table you want to change, you would need to set the form's RecordSource first to a null string, then restore it after changing the default value.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38747204
If this is a multi-user database, the challenge is that you have to make sure users are not using the table you are trying to modify.  Another way to deal with this is to maintain a separate table (tbl_Defaults) in your database with fields: FormName, CtrlName and DefValue.

Then, you can create a separate form for editing these values (not included in sample db).  You can place a "Defaults"  button on an Admin form or even on the particular form where you are entering the data.  When the user clicks this button, you open frm_Defaults using a WHERE condition that limits the continuous forms RecordSource to those where the [FormName] field matchs the name of your form.

Finally, in the form where the users are actually entering the data (frm1 in the example), you can use the Form_Current event to set the value of the controls on the form, rather than setting the default value of the field.  This gives you the flexibility of setting these defaults at run time and having them work for all users in a multi-user environment.

I've used the Eval() function to assess the value of the data entered in your DefValue field, since this must be a text data type.  If the Eval() function generates an error, it is because the default value is a string, and the code jumps to the error handler to process strings.

Take a look at the attached as an example.
DefValues.accdb
0
 
LVL 1

Author Closing Comment

by:Jpoppi
ID: 38749477
Thanks experts for your input.

I was able to use the db.TableDefs solution in my database. These default values won't be changed often, and when they are changed, it will be while no other users are using the database. This was an easy and simple fix for a beginner like me.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question