Solved: VBA set Table Default Field Value

[Movian]

Now before anyone says it i know this is the wrong way to go about it.


I need a sure fire way to set on the fly (in an .accdr) the default values for fields in a table. Via VBA

i can redesign it in the future but this is the way i need to do it now.

As allways any help is apriciated

[OBP]

I think you will need to do this in a tabledef using the DefaultValue property.
I don't have the code for modifying a Tabledef, only creating one, but You will have to "Open" it first.

[Movian]

On your suggestion i have tried to implement the following

Sub SetDefaultValue(tablename As String, fieldname As String, fieldvalue As Variant)
Dim db As Database, tdf As TableDef, fld As Field
Dim blnReturn As Boolean

Set db = CurrentDb
Set tdf = db.TableDefs(tablename)
Set fld = tdf.Fields(fieldname)

tdf.Fields(fieldname).DefaultValue = fieldvalue

db.Close
Set db = Nothing
End Sub

As the main sub routine that actually does the work and the following code

Private Sub SympDur_DblClick(Cancel As Integer)
'Test code for dynamic user default value settings

Dim tbl As String, ctrlname As String
tbl = Me.RecordSource
ctrlname = Screen.ActiveControl.name
DoCmd.Close acForm, "frmMain"
SetDefaultValue tbl, ctrlname, InputBox("Enter your new default value")
DoCmd.OpenForm "frmMain"
End Sub
On the control that i want to set the default value for.

The problem i face now is i get the following error

"Run Time Error 3422:"
"cannot modify table structure another user has the table open."

But as you can see from the code i have closed the forms. and there should be no code with the table open... any help would be appreciated as i need to get this done today if at all possible.

The only other method i can think of that might produce the same results would be to have a lookup table that the user would be able to edit and each fields default value would be a select from where statement... however this would take a very long time to implement where as if i can get this code to work on a single control here i can get it to work throughout the whole system in a matter of minutes.

[CreganTur]

I think the reason you're having this problem is because frmMain isn't completely closing. Your sub (SympDur_DblClick) is still active during this whole process, since it's the calling routine. You may be closing the Form, but the Form isn't able to completely Unload because it still has code to run in the background.

Instead of setting the default vlaue in your table, could you assign a default value to the form's control? This would cause the desired value to be written to the table when the record changes are saved, or when the user moves to another record.

HTH:thumb

[Movian]

Have had issues doing that. It Seems that my system only wants to use form defaults for unbound fields and table defaults for bound fields.

I actually ended up going with a lookup table. Where by it stores the table name and field and the value. Then when a new patient is created it goes through and puts the value into the applicable fields. This allows the end user to overwrite our defaults in the process too

[OBP]

That is a neat solution. :friends: