Changing default in table field with tableDefs

[Vet Tech]

As a relative newbie I’m trying to amend the default value of a field
in a table using tableDefs but I’m not quite getting there. I’m using
A2003.

This is a database that is used at several branch locations and each
branch needs to be able to select the default value it wants for a
field called VaryMonth in a table called tblBoxes. VaryMonth can have
a value in the range of 2 to 6.

The branch should select a value in that range from a combo box called
cmbVaryMonth in a form called frmFront which is not bound to the
table.

I’ve started out with this……………..

Private Sub cmbVaryMonth_AfterUpdate()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strTableName As String
Set db = CurrentDb()
strTableName = "tblBoxes"
currTable = db.TableDefs(strTableName)

setTable.Fields!VaryMonth.DefaultValue = Me.CmbVaryMonth.Value
End Sub

It goes through to the last line and then it gives up with error 424.


Can someone please assist?

Yvonne

 

[RonaldoOneNil]

Your last line says setTable.fields. . . when the previous line says
currTable = . . .

 

[Vet Tech]

Your last line says setTable.fields. . . when the previous line says
currTable = . . .

Ok, I have taken out one line, so it now reads...

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strTableName As String
Set db = CurrentDb()
strTableName = "tblBoxes"

Set Table.Fields!VaryMonth.DefaultValue = Me.VaryMonth.Value


But it still gives Run Time Error '424'

 

[Vet Tech]

I'm changed things and it appear sto be almost there. It now reads....



Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strTableName As String
Set db = CurrentDb()
strTableName = "tblBoxes"


fld.Fields("VaryMonth").DefaultValue = Me.VaryMonth.Value



It now stops at 'Fields' on the last line.saying compile error - m|
ethod or data member not found

Does this help?

 

[RonaldoOneNil]

Try this

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strTableName As String
Set db = CurrentDb()
strTableName = "tblBoxes"
Set tdf = db.TableDefs(strTableName)
Set fld = tdf.Fields("VaryMonth")
fld.DefaultValue = Me.VaryMonth.Value

 

[Vet Tech]

Try this

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strTableName As String
Set db = CurrentDb()
strTableName = "tblBoxes"
Set tdf = db.TableDefs(strTableName)
Set fld = tdf.Fields("VaryMonth")
fld.DefaultValue = Me.VaryMonth.Value

Ronaldo,

Sorry but that didn't do anything. No warnings or anything.

VT

 

[RonaldoOneNil]

There won't be any warnings if the code is OK. Check your table design. When
I tried it, it put a default value in the relevant field.

 

[Vet Tech]

There won't be any warnings if the code is OK. Check your table design. When
I tried it, it put a default value in the relevant field.

Checked again and the result was there.

Ronaldo, you are a gem. Many thanks