Default Values in Tables

[apartain]

How do I programatically change the default value of a field in a table? I will need to prompt the user for the new default value, as well, in order to fully automate the process.

[MMcCarthy]

How do I programatically change the default value of a field in a table? I will need to prompt the user for the new default value, as well, in order to fully automate the process.

It's a dangerous thing to do as you are giving the user programming control. Don't do it without a very good reason.

The code you would need would be.

 
Dim strDefaultVal As String
 
  strDefaultVal = InputBox "Prompt user for Entry")
  Me.FieldName.DefaultValue = strDefaultVal
  Me.Requery

[Killer42]

Me.FieldName.DefaultValue = strDefaultVal
Me.Requery

Wow! Is that it?!

I thought you'd need to dig into TableDef objects, FieldDef collection, and so on. Cool!

I suppose it's a lot simpler since you're starting from within Access - I tend to look at it from the VB6 viewpoint, where I'm sure I would have to do all that stuff.

[MMcCarthy]

Wow! Is that it?!

I thought you'd need to dig into TableDef objects, FieldDef collection, and so on. Cool!

I suppose it's a lot simpler since you're starting from within Access - I tend to look at it from the VB6 viewpoint, where I'm sure I would have to do all that stuff.

Yes there is a lot of functionality in the Access library.

Someday, you know 'when you have time and nothing else to do' you should check out the msdn references to Access VBA libraries.

Mary

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_2003_ta/html/odc_landoffice03_vba.asp

[apartain]

I know this sounds like a really dumb question, but I am very new at VBA in Access. Where do I put the code you just gave me?

If you think this is dangerous, is there another way to do it so when the sales tax changes, it won't change all of the records up to the point of the change?

[Killer42]

The default value only affects new records being created on a table.

[MMcCarthy]

I know this sounds like a really dumb question, but I am very new at VBA in Access. Where do I put the code you just gave me?

If you think this is dangerous, is there another way to do it so when the sales tax changes, it won't change all of the records up to the point of the change?

The best way to handle making this kind of change is to give the user a popup form opened on a command button click to change the sales tax rate.

Create this form with a textbox to put the new sales tax in and a command button to apply it. When you have this done and the form is opening based on clicking the button on the main form. Let me know.

Give me the new form name.
The name of the textbox
The name of the command button
and
The name of the control on the main form that is set to the field for the sales tax.

Mary

[apartain]

OK, done.

New popup form name: ChangeSTForm
Text Box: NewRate
Command Button: Command3
Control on main form set to field for sales tax: Form3.[Sales Tax]

The best way to handle making this kind of change is to give the user a popup form opened on a command button click to change the sales tax rate.

Create this form with a textbox to put the new sales tax in and a command button to apply it. When you have this done and the form is opening based on clicking the button on the main form. Let me know.

Give me the new form name.
The name of the textbox
The name of the command button
and
The name of the control on the main form that is set to the field for the sales tax.

Mary

[MMcCarthy]

OK, done.

New popup form name: ChangeSTForm
Text Box: NewRate
Command Button: Command3
Control on main form set to field for sales tax: Form3.[Sales Tax]

Ok put the following code behind the Command3 command button

 
Private Sub Command3_Click()
 
  If NOT IsNull(Me.NewRate) Then
	Forms![Form3]![Sales Tax].DefaultValue = "=" & Me.NewRate
  Else
	Msgbox "You haven't entered a new sales tax figure"
	Me.NewRate.SetFocus
	End Sub
  End If
 
  Forms("Form3").Refresh
  DoCmd.Close
 
End Sub

Let me know if you've any problems.

[PEB]

I think that this concerns only the field property in a form, but not the table!

For the table properties... The tableDefrs are waiting for you ;)

:)

Wow! Is that it?!

I thought you'd need to dig into TableDef objects, FieldDef collection, and so on. Cool!

I suppose it's a lot simpler since you're starting from within Access - I tend to look at it from the VB6 viewpoint, where I'm sure I would have to do all that stuff.