novice access user! Is there a way to change the default value?

[loganvolcom]

Can I create a form that allows the user to enter a new DEFAULT VALUE? The only way i found to change it is to go to the table in design view and change it there. I want the user of the DB to be able to change default values on the fly (when needed).

Thanks.... I have been stuck for a week on this.

[ItsMe]

There is a way. First, you need to categorize the term default into two separate categories. One category would be a default that is applied during Design Time and another would be a default that is applied during Run Time. If you want the User to be able to affect defaults, you need to manage the default during Run Time.

When you go to a table in design view, you are in Design Time. You do not desire a User to have access to design time changes. In other words, changing the default value property of a field at the table level or a control in a form is something that is done in design time.

What you desire is to have the User update a value in a table. Not the Default Value property of a control or a field within the table.

Use the persistent data in the table to determine the value. When the user closes a form or closes the application. The value in the table will not change. Unlike the user changing the Default Value property of a field or control while in Form View.

Long story short, store data in tables. Bind controls and forms to tables and or queries.

[loganvolcom]

Thanks for your help, but I am looking for a way to change a default value in the table. I want to enter a value in a form that will change the default value of a field in the table.
for example .... if "part1" has a default value of "$6.00" i want to be able to change the default value of the part to "$5.50" for all future entries without having to go into the table design view. I dont need an individual record updated.... i need the default changed for all future records.

I am sure that this is quite common in inventory databases.

I really appreciate your help ... Thanks a million!!!!

[ItsMe]

...i need the default changed for all future records...

I understand and the appropriate way to do this (when the User needs to update the value) is to store the data in a table. If you desire to make design time changes, it is possible to do this via VBA code. I can think of a relatively simple way to make a design time change to a control in a form.

To be perfectly honest, I have never tried to automate the process of assigning a default value property to a field within a table.

It may be a little more complex to do this to a table object. I suspect DAO would need to be used to create tabledef object. In addition, certain circumstances would need to exist in order to be capable of Design changes. For instance, you would have to have exclusive rights.

My recommendation is to rethink your table design. Add an additional table that users can maintain the values of.

[loganvolcom]

I will try to use multiple tables. Thanks so much for your help!

[ItsMe]

As I mentioned, there are options to change the Default Value of a control or the Default Value of a field within a table. It just may not be as straight forward as you imagine. The simplest of the two would be to manage it via a bound control in a form. Let us know what you want to do and where you need help.

[loganvolcom]

I am trying .... I will keep you posted. Thanks again for all your help.

[HiTechCoach]

for example .... if "part1" has a default value of "$6.00" i want to be able to change the default value of the part to "$5.50" for all future entries without having to go into the table design view. I dont need an individual record updated.... i need the default changed for all future records.

I have to disagree.

You DO need an individual record updated, even better added (See below)

I am sure that this is quite common in inventory databases.

Yes changing prices in a inventory system is common. It is just data in a field. I have never seen a good accounting/inventory application use the default value of the table properties for the current price..

FWIW: I never use the default value at the table level. Not even in any of my Accounting systems.

The Current price is data which means it needs to be store in a field in a table. When you select an item/product I use the after update event to look up the current price.

My preferred method is to sote the current prince in a chilkd/sub/related table so there is a historey.

table: Inventry Prices
Fields:
InvPriceKey - autonumber - primary key
InvPrice_InventoryKey - Long - foreign key to Inventory tabe
InvPrice_DateStart - Date/Time - date this price started
InvPrice_DateEnd - Date/Time - date this price ends
InvPrice_SalePrice - Currency


The about table is the used in place of the default value property.

I do not store the print in the Invoice Line Item record. I look up the price using the Invoice date. This is following the rules of data normalization to a level most lower end accounting/inventory system never achieve, but should

Something to ponder:
If this is a multi-user database then changing the default value at the table level will be very problematic. To make the design change you will have to have exclusive access to the table. This mean no other user can have the table in use in any way while the default value is getting changed. Not very practical. That is probably the most common reason why the default value at the table level is rarely changed.