Change table field defaults

[paddymx]

I have a form with several numerical fields. Currently I have assigned default values in the table design. I have a pop-up form to change those defaults in the form but that only works for New inputs for the current session. This means the user must reset the defaults each time they use the form. I would like to be able to change the defaults at the table level so the new defaults are there until changed again. Not real experienced is recordsets(rs) or sql syntax. Everything I've tried so far generates errors.



Can this be done using a procedure called from my pop-up's using the forms' values like I'm doing now except update the default values at the table level?

TNX

[lfpm062010]

1) You can change the default value in the MS Access system table "DefaultValue".
currentdb.TableDefs("myTable").Fields(0).DefaultVa lue = "NewDefaultValue"

2) You can change the default value in the Form "DefaultValue" property.
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

or

3) You can create a table to store the default value and look them up at form open.

[AlexHedley]

Are you doing Data Entry via a Form?

You could set the Default Value there instead.

[paddymx]

1) You can change the default value in the MS Access system table "DefaultValue".
currentdb.TableDefs("myTable").Fields(0).DefaultVa lue = "NewDefaultValue"

2) You can change the default value in the Form "DefaultValue" property.
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

or

3) You can create a table to store the default value and look them up at form open.

I'm liking #3 because it fits my skill level. Could I impose on you for some sample code for #1. Would help me better understand what I don't feel comfortable with now.

# 2 is how I'm doing it now.

Thanks for the quick answer!

Patrick.. I'll be marking this as solved.

[paddymx]

Are you doing Data Entry via a Form?

You could set the Default Value there instead.

Thanks, that's how I'm doing it now. See my reply to post #2 above.
TNX

[lfpm062010]

For sample code #1:

'"Table1" is my table' with DNum1, DNum2, DNum3
'DNum1 --> is Fields(0)
'DNum2 --> is Fields(1)
'DNum3 --> is Fields(2)

This will change the default value of
Sub defaultval()
'This will change the DNum1's Default Value to 100
CurrentDb.TableDefs("Table1").Fields(0).DefaultVal ue = 100
'OR
CurrentDb.TableDefs("Table1").Fields("DNum1").Defa ultValue = 100

'This will change the DNum2's Default Value to 1000
CurrentDb.TableDefs("Table1").Fields(1).DefaultVal ue = 1000
'OR
CurrentDb.TableDefs("Table1").Fields("DNum2").Defa ultValue = 1000

'This will change the DNum3's Default Value to 10000
CurrentDb.TableDefs("Table1").Fields(2).DefaultVal ue = 10000
'OR
CurrentDb.TableDefs("Table1").Fields("DNum3").Defa ultValue = 10000

End Sub

[paddymx]

For sample code #1:

'"Table1" is my table' with DNum1, DNum2, DNum3
'DNum1 --> is Fields(0)
'DNum2 --> is Fields(1)
'DNum3 --> is Fields(2)

...

So could I use something like this to change multiple fields

Sub defaultval()
'remove space in DefaultValue before using


CurrentDb.TableDefs("Table1").Fields("DNum1").Defa ultValue = 100
CurrentDb.TableDefs("Table1").Fields("DNum2").Defa ultValue = 1000
CurrentDb.TableDefs("Table1").Fields("DNum3").Defa ultValue = 10000


End Sub

Could it be that elegant ? No sets, no Dims, no RS's, no SQL's, etc. just those lines?

[AlexHedley]

It's because you're changing a Property (Table Definition) of the Table/Field not a Record in the Table.

[paddymx]

For sample code #1:

...

Forgot to add that the #3 worked great using Dlookup()'s to set the forms defaults. Thanks lfpm062010.

[paddymx]

It's because you're changing a Property (Table Definition) of the Table/Field not a Record in the Table.

Cool. I think this falls into the category of "Can't see the forest for the trees". TNX

[lfpm062010]

I have tested this code. It does work.

Sub defaultval()
'remove space in DefaultValue before using

CurrentDb.TableDefs("Table1").Fields("DNum1").Defa ultValue = 100
CurrentDb.TableDefs("Table1").Fields("DNum2").Defa ultValue = 1000
CurrentDb.TableDefs("Table1").Fields("DNum3").Defa ultValue = 10000

End Sub

[paddymx]

I have tested this code. It does work.

Sub defaultval()
'remove space in DefaultValue before using

CurrentDb.TableDefs("Table1").Fields("DNum1").Defa ultValue = 100
CurrentDb.TableDefs("Table1").Fields("DNum2").Defa ultValue = 1000
CurrentDb.TableDefs("Table1").Fields("DNum3").Defa ultValue = 10000

End Sub

Thanks again for the help. Much appreciated.