For all Tables in Database where Field Name = "Site", Set Defaut Value (1 Viewer)

xxx · Jun 6, 2012

I have a database. Every table in that database has a field called "Site".

When the database is opened for the first time by the end user they will be prompted to enter the name of their site.

I want the value entered to become the default value for the field "Site" in every table contained in the database.

I'm basically looking for some sample code (explained) for how to loop through ALL existing tables and set the default for this field field.

Thank you so much for your help.

AccessMSSQL · Jun 7, 2012

Is this app going to be used by many users with different site logins - or is this a standalone application that you are trying to set up a configuration routine for?

If your tables are linked that could be difficult but if they are local you could loop through the tabledefs and fields and add the default values.

Other option is to add some code to your forms on the BeforeInsert event to stick the site name into the site field.

xxx · Jun 7, 2012

Data will be collected for analysis at multiple sites. A separate copy of the database will be sent to each individual site.

After data collection is complete we will be taking the data from all sites and compiling it into one database to analyze.

I'm try to minimize the redundant data entry, but we need this information for every record so we can distinguish what data came from which site.

xxx · Jun 7, 2012

Also... I'm definitely looking for some example code, if you could oblige.

boblarson · Jun 7, 2012

Why would you store redundant data like that? Unless I'm missing something, SITE should only be in ONE table. Why are you storing it in every table?

xxx · Jun 7, 2012

Because at some point we will be adding data from multiple sites into ONE database.

So eventually, the tables will contain records from multiple sites and we have to be able to tell which site each record came from.

AccessMSSQL · Jun 7, 2012

I do have a suggestion if you are distributing individual applications to each site. Store the site name in a separate table - just one record and when you go to collect the data run a routine that updates the site fields in all the tables to the site name that was entered. It could be part of your final collection routine before you merge the data files. Is this an option?

boblarson · Jun 7, 2012

xxx said:
Because at some point we will be adding data from multiple sites into ONE database.

So eventually, the tables will contain records from multiple sites and we have to be able to tell which site each record came from.

So whatever you do, do NOT use text. Store the name of the site in ONE table with a primary key on it and then use that key (as long integer) in the other tables.

xxx · Jun 7, 2012

Yes. This could be an option. One I was initially going to use.

But for various reasons am now considering this option. If I can't figure out this particular route, I may have to use the one you suggested.

xxx · Jun 7, 2012

BobLarson: would you explain why text shouldn't be used?

boblarson · Jun 7, 2012

xxx said:
BobLarson: would you explain why text shouldn't be used?

1. Indexing is faster using numbers.
2. Numbers take up less space than text
3. Numbers are harder to misspell than text

boblarson · Jun 7, 2012

Oh, and I forgot to add #4 -

Should you misspell the text and add it to every record, it will need to be fixed in every record. If you store the name ONCE and use a number, you will just need to change the text in ONE place instead of hundreds or thousands.

xxx · Jun 7, 2012

But if the user sets the default once on first open (probably validation rule, too)... that should be that, right? I am going to disable that particular field in all of the forms.

xxx · Jun 7, 2012

Ok, I see. So then I can just do something like an update query based on that one site name table. But still I want an efficient way to update every table. Suggestions? Or is my only option to do an update query for every table via wizard?

I guess regardless of where I store the site name - The main thing I want to do is update that field in all tables w/ vba.

I don't want to create a query w/ wizard for each table.

AccessMSSQL · Jun 7, 2012

I think I have a solution but you would need to modify all the forms and set your site column equal to a function that gets an enum value from the config table.
I agree with Bob in that you should make your SiteID a numeric field but you would have to devise a way to keep those numbers unique across sites.
In any event, store your site value in a config table then use an enum config type to get the value.
put this in a global module

Code:

Public Enum ConfigType
    CONFIG_SITEID = 1
End Enum

then create a table called Config with two fields (ID and Value). Make ID Primary Key and Value should be text regardless of what you are storing in the field. Populate table with ID=1 and leave value blank. When user logs in for first time
store your sitename in the Value field.
Use this function to get the config value:

Code:

Function GetConfigItem(ItemID As ConfigType) As Variant
    GetConfigItem = ConvertNulls(DLookup("[Value]", "Config", "[ID]=" & ItemID), "")
End Function

You will need to create another function that returns your config value because the GetConfigItem function won't work on a form controls default value property.

Code:

Public Function GetSiteID() As Long 'or as String depending on if you decide to store your site as text or numeric.
GetSiteID = GetConfigItem(CONFIG_SITEID)
End Function

Then in each form in the Site control you need to set the Default property = GetSiteID()

I tried this out in sample db and it worked.

xxx · Jun 8, 2012

Thank you, Access! I'll let you know how it goes.

For all Tables in Database where Field Name = "Site", Set Defaut Value (1 Viewer)

xxx

Registered User.

AccessMSSQL

Seasoned Programmer-ette

xxx

Registered User.

xxx

Registered User.

boblarson

Smeghead

xxx

Registered User.

AccessMSSQL

Seasoned Programmer-ette

boblarson

Smeghead

xxx

Registered User.

xxx

Registered User.

boblarson

Smeghead

boblarson

Smeghead

xxx

Registered User.

xxx

Registered User.

AccessMSSQL

Seasoned Programmer-ette

xxx

Registered User.

Similar threads

Users who are viewing this thread