Change Table Field Default Value through Form Control

[Kevin]

Hi all,

I am preparing to deploy a database to multiple sites (each site's
database is independent) and want to have embedded in every table a
site ID chosen in a "setup" form (so that at the end of the year we can
pull everything together). I think the best way to do this is by
changing the default value for the field's table. I would like to set
the default value equal to the value from a drop down box on the setup
form.

I'm not familiar with VB, so I've tried a macro with a SetValue action
with 3 different item settings:
Tables("Attendance").Fields("CYSiteID").Properties("Default")
[Attendance].[Fields]![CYSiteID].[DefaultValue]
[Tables].[Attendance].[Fields]![CYSiteID].[DefaultValue]

In each case, an error comes up: "The object doesn't contain the
Automation Object..."

I'm very probably wrong about how to go about this. If so, let me
know. I would like to avoid changing the default values on forms as to
insure that the site ID is always insured to get in, no matter how
people enter and edit info. Thanks in advance.

Kevin Jenkins
Program Manager
City Year New York

 

[Bill Mosca]

Here is a function that you can run from an Autoexec macro or a startup form.
It uses a table called Defaults that has a field named "Site"


Public Function SetDefault()
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim strDefault As String

'Get Default value for Site.
strDefault = DLookup("Site", "Defaults")

Set cnn = CurrentProject.Connection
strSQL = "ALTER TABLE Products " _
& "ALTER Column Site varchar(50) Default " & strDefault

cnn.Execute strSQL
MsgBox "Default for site set to " & strDefault & "."

Set cnn = Nothing

End Function

 

[Kevin]

Thanks, Bill, for such a quick reply. I didn't expect a response so
soon!

I have very limited knowledge of VB and I left out a detail in my
question. I have a table that has 16 site ID's and the site name in
separate fields. To make it as easy on the user as possible, I would
like the user to be able to choose the site name from a drop down and
have access set the default values of a site ID field in 7 tables to
the matching site ID.

So, if I choose Boston, then the default value for Site ID in every
table would be 110.

I'm also planning on creating a front and back end. Would this routine
still be possible?

Thanks again for your help.
Kevin Jenkins

Here is a function that you can run from an Autoexec macro or a startup form.
It uses a table called Defaults that has a field named "Site"


Public Function SetDefault()
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim strDefault As String

'Get Default value for Site.
strDefault = DLookup("Site", "Defaults")

Set cnn = CurrentProject.Connection
strSQL = "ALTER TABLE Products " _
& "ALTER Column Site varchar(50) Default " & strDefault

cnn.Execute strSQL
MsgBox "Default for site set to " & strDefault & "."

Set cnn = Nothing

End Function

--
Bill Mosca, Microsoft Access MVP

Hi all,

I am preparing to deploy a database to multiple sites (each site's
database is independent) and want to have embedded in every table a
site ID chosen in a "setup" form (so that at the end of the year we can
pull everything together). I think the best way to do this is by
changing the default value for the field's table. I would like to set
the default value equal to the value from a drop down box on the setup
form.

I'm not familiar with VB, so I've tried a macro with a SetValue action
with 3 different item settings:
Tables("Attendance").Fields("CYSiteID").Properties("Default")
[Attendance].[Fields]![CYSiteID].[DefaultValue]
[Tables].[Attendance].[Fields]![CYSiteID].[DefaultValue]

In each case, an error comes up: "The object doesn't contain the
Automation Object..."

I'm very probably wrong about how to go about this. If so, let me
know. I would like to avoid changing the default values on forms as to
insure that the site ID is always insured to get in, no matter how
people enter and edit info. Thanks in advance.

Kevin Jenkins
Program Manager
City Year New York