For all Tables where field = "Site", Set Default Value

[xxx]

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.

Thank you so much for your help.

 

[Beetle]

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

Why?

To be more specific, why are you redundantly storing the same data in every table in your application?

 

[xxx]

Because at some point we will be compiling data collected at multiple sites into one database for analysis.

I'm trying to minimize the redundancy involved on the data entry end, but we need this information for every record so once compiled we can still distinguish which data came from which site.

 

[Beetle]

Is this a database you created, or is it something over which you have no control of the design? The best way to minimize redundancy would be to set this up with a proper relational design. There should one table for the Sites along with one or more related tables to store whatever information you're tracking about each site.

If for whatever reason you're going to press ahead with the current table structure, then posting a few more details about your table structure, and a short description of the data entry process may be helpful. Assuming that data entry is being done using a form, then the Before Update event of the form could be used during the data entry process to insert the value.

 

[xxx]

I will admit that I am still familiarizing myself with what "proper" design entails exactly.

I did create the database from ground up. Unfortunately, I'm unable to share a copy of the database. However, I'll try to give some detail.

Data will be collected at multiple sites. A separate, raw copy of the database will be sent to each site which will be returned to us once the study is complete and all data has been entered. We'll then compile all data in one database.

The site user will navigate & enter all data through, essentially, one "view". All other supporting tables & unnecessary forms will be hidden & as inaccessible as possible.

The main form has two combo boxes which allow the user to select the person & type of visit for which they want to enter data. There is also a label identifying the type of visit selected. These combos are linked to every subform contained in a Tab Control below. The Tab Control & the forms contained therein change according to the type of visit selected above.

The idea here is to allow data to be entered for only one person & one type of visit at a time – thus minimizing data entry errors which may contaminate our data. I have tried to eliminate lookups when possible.

I don’t know if my description is helpful at all, but feel free to ask for additional information. I’ll do my best to provide. This is a huge important project, and I want it to be top notch. Quite enjoyable though! For the last year & a half I’ve been with this particular company I’ve been teaching myself & designing databases as much as possible at work (to make all of the data entry a bit more bearable) and sometimes for 4-5 hours after work because I enjoy it that much. Still a lot to learn though.

 

[xxx]

Also, a second study may take place later which will require multiple user access. Do you have any advice on how to make an access database multi user friendly & protect data(base) integrity? This may be an even bigger issue than the one at hand.

 

[Beetle]

A separate, raw copy of the database will be sent to each site which will be returned to us once the study is complete and all data has been entered.

Each copy of the database should contain a table for the Site (or Sites). This table would have fields like;

SiteID (Primary Key)
SiteName

plus any other fields you need for the general information about the Site. The other table (or tables) that store data about whatever information you are collecting at each Site should have a Foreign Key field that stores the SiteID (and only the SiteID - no other data from the Site table should be stored in the related tables). If you know ahead of time which Site(s) each copy is going to be collecting data for, then you could actually enter the Site data ahead of time so the user wouldn't even need to enter it. If there is more than one Site for each copy of the db, then you would likely set up a main form/sub form where the main form would be based on the Sites table and the sub form(s) would be based on the related table(s). The Master/Child link of the Subform Control would automatically handle the insertion of the SiteID value in the FK field of the related tables. If each db copy only tracks info for one Site, then it would be even simpler because you could just make the SiteID value the default for the FK field in the related tables.

If you're going to attempt this (loading the Site info ahead of time for each copy) then you must make sure that every different Site (in each of the different db copies) has a unique ID value, so you can't use Autonumber for this, you must define your own values. The SiteID would be the most important piece of data when it comes to combining all this information together, so you would need to have careful control over that.

Here are some links if you want to do some research on normalization and table design.

A tutorial series by Crystal (Access MVP)

Fundamentals of Relational Design

Allen Browne's Access page

The Access Web

Also, a second study may take place later which will require multiple user access. Do you have any advice on how to make an access database multi user friendly & protect data(base) integrity?

That's going to depend on the nature of the network. Access/Jet works well over a LAN as long as you don't have a large number of concurrent users, but it is not well suited for WAN. If you have the former then you will need to split your database. There would be one copy of the back end (tables and relationships) on a network server and each user would have their own copy of the front end file on their machine.