Changing a default value in a table

[peterkennett]

I am designing a database that will be used by many different offices, and each office may want to change the defaults I have in a table.

Rather than teach each user how to go in and change a table's default values, I want to create a user-friendly form that allows them to change the default values in the linked table.

Let's say I have a form field "office" on my form that is linked to the table field [department] in my table. The field [department] has a default of "shipping".

I want to let the user enter "receiving" into the form's "office" field and then click on a tiny button to "set as default" - which will then run some code to basicaly do something like:

department.default.value = me.office.value

But the property ".default.value" doesn't exist.

Is this possible, or do I have to maintain a separate table of default values and use code to update form data as needed?

Thanks!

PK

[FishVal]

Hi, there.

Th property is the folowing:

CurrentDb.TableDefs("TableName").Fields("FieldName").DefaultValue = ...
 

[missinglinq]

Just to clarify, are you talking about each office having its own table, or does each office have its own frontend connecting to a single, common backend/table? If everyone is sharing a single table you need to be changing the default value at the form level, not the table level.

Welcome to TheScripts!

Linq ;0)>

[peterkennett]

Each office gets their own copy of the database. The data is not shared or linked, it is for their own use. I could go in and show each office how to customize the table defaults to their specific office, or write up some instructions on how to do this, but I want to share this database with hundreds of offices around the world so that's not the best, user-friendly way to do it.

PK

[peterkennett]

Fish, thanks for the code. It works, but alas, I have the same table open on the form that I want to make changes to, so I get an error!

Arghg!

I guess there is no way to update the defaults on a table that is open and being accessed. I think I need to store the defauts in a separate table.

PK

[missinglinq]

Why do you need to (or would you want to) have the table open when you're changing the default value? Just create a utility form for the sole purpose of changing the default!

Place a button (Call it ChangeDefault) and an unbound textbox (call it txtDefValue) on the form, then incorporating the code Fish provided:

Private Sub ChangeDefault_Click()
If Not IsNull(Me.txtDefValue) Then
 CurrentDb.TableDefs("YourTableName").Fields("YourFieldName").DefaultValue = Me.txtDefValue
  MsgBox "Default Value has been changed to " &    Me.txtDefValue
Else
  MsgBox "You must enter a new Default Value before you can assign it!"
  txtDefValue.SetFocus
End If
End Sub
 

This example is for changing a single Default Value, but you could, of course, easily modify to change multiple values.

Linq ;0)>

[peterkennett]

Thanks!

That is exactly what I did, but not with the nice error trap for non entries. Again, thanks for the help everyone! This is a VERY COOL forum!

PK

[MRA2]

Worked Great Thanks so much!!!!

@missinglinq

[rccline]

FANTASTIC! Thank missinglinq for that great post! I searched high and low for a solution to changing the defaul TableDefs using VBA...

Many Kudos to you!!

Best regards,
Robert