using a table as a domain table for form entry

[DNRTech]

HI, I'm pretty new to Access and I'm having some trouble with my data entry form.



I have a form with three embedded levels. When I enter data, I use combo boxes to pull one of a select set of values from one table (a domain table) and associate other data to that value.

After I built the form and began testing it I noticed that, instead of using the values from the domain table and populating the foreign key field in the table of data I wanted to associate with the domain values, it was adding values to the domain table from the list given in the combo box (as well as the foreign key field on the other table, which is what I want).

Imagine the combo box shows the values from the domain table, you select one, and the next record you enter data into for that field has the same combo box but contains an extra entry -- the one you just entered in the previous record.

What I want is for the combo box just populate the foreign key field and not add another record to the domain table.

Does anyone know how to fix this? I have a difficult time explaining these things. Please, let me know if I need to clarify anything.

Thanks a bunch.

[pbaldy]

Off the top, it sounds like the domain table is part of the form's source, and it shouldn't be. The combo should get its selections from the domain table, but be bound to some other table. Hard to say for sure without knowing more about the nature of the data.

[DNRTech]

The form's source is a query that calls upon the domain field. Would that make the domain table apart of the form's source?

[pbaldy]

If you mean the domain field in the domain table, yes it would.

[DNRTech]

So while I have you, let me ask you a bit about domains. Domains tables are tables that store specific data that is to be used in other tables, like Male or Female. You can only have these two values so we can relate the primary key from the domain table to foreign keys in other tables which relate to the male and female values. Is this correct?

Does access treat these tables differently by name other than assigning a one to many relationship? Can you have a domain field outside of a domain table? Are domain tables just a term we use to describe a table or field of destinct values, that rarely change, related to other data?

I have a big access book and it barely mentions them, but it seems like an important part of normalization. Would you agree?

Thanks for helping me sort this out, and sorry about the scattered questions?

[pbaldy]

What you are calling a domain table sounds like what I've heard called a lookup table (not sure that either name is the "correct" one). I agree with your description of their use. To Access they're just another table, related to others based on the relationship you give them. I would certainly agree that they are an important part of normalization.

[DNRTech]

Thanks for clarifying domains.

I tried to not source the form from my domain table, but still came up with the same issues. However, I now have a the term "Lookup Tables" to research and I work with that for now to see if I can solve my problem.

Thanks for the tips!

[pbaldy]

No problem. If you don't find the answer, post the db and we'll figure out what's going on.

[DNRTech]

Thanks again for your help. You mentioned about the domin table not being apart of the form's source. I have found that I cannot have an object on my form without it being apart of the form's source. Errors ensue. I know that it is possible but I'm not sure how to set it up yet. I had also trouble making my combo objects bound to another source other than the domain table. If I can figure out why, I know it'll go a long way with my quest to understand forms!

I have posted the db I am working with. I'd like to thank user jzwp11 for helping me with the table structure.

In this database we are entering water quality data processed by a contracted lab. For each date there are several sites that are sampled and for each site there are several analytes. Each analyte has only one result per site, but the database could flexible enough to enter duplicates in the future. I'm just keeping it simple while I try to grip with access fundamentals.

I have entered some sample data in the database. There are supposed to be only 4 unique analytes and 9 sites, but as you can see, the result of entering two sites worth of data has added multiples of what are supposed to be unique values in: tblAnalyte.txtAnalyteName and tblSite.txtSiteName

For the txtSiteName the multiples start at record 10 and for the txtAnalyteName the multiples start at record 5. Data entry is performed via a main form based on the sampling date with two embedded levels of subforms based on site and result.

Please let me know if you have any questions regarding the db or my comments, and thanks again.

[pbaldy]

I don't have 2007 on this machine so I won't be able to look at the sample until later. Generally, you would have your form bound to your main data table, let's call it Transactions. In that table you'd have a field that would be the value from the domain table, let's call it SiteName. The form is only bound to the Transactions table. The combo's control source is that SiteName field within Transactions. The combo's row source would be "SELECT...FROM YourDomainTable". Thus it gets its values from the domain table (the row source property) but saves its values to the Transactions table (its control source property).

[DNRTech]

In that table you'd have a field that would be the value from the domain table, let's call it SiteName.

...will a foreign key work for this value as opposed to the actual SiteName, a text field not an ID field?

The combo's control source is that SiteName field within Transactions.

...if I can use a foreign key here then this would fix the errors I was getting.

The combo's row source would be "SELECT...FROM YourDomainTable". Thus it gets its values from the domain table (the row source property) but saves its values to the Transactions table (its control source property).

...I'm going to try this and get back to you. This is exciting!

[pbaldy]

Yes, typically you would store the key value from the lookup table in the transaction table. I used SiteName because I saw it in your post.

[DNRTech]

Yep, that did it. So in review:

Using domain tables / lookup tables requires that the form's source is not the domain table but rather the table that the data entry will be occurring, in my case tblResult. One can find the source of the form in the property sheet under the Record Source dialog to check what table the form is sourced.

To use a Combo Box to call data from a domain table one needs to add a Combo Box from the design tools with the Control Source as the foreign key related to the domain table, in my case either tblSampleEventSite.fkSiteID or tblResult.fkAnalyteID.

Then, to populate the foreign key field with the primary key from the domain table - while displaying the desired information from the domain table in the Combo Box on the form - we need to:

1. Change the Row Source to a query that pulls both the primary key and the desirable information from the domain table, in my case "SELECT pkAnalyteID, txtAnalyteName FROM tblAnalyte; " for one of my Combo Boxes.

2. Then, also in the Property Sheet, set the Column Count to 2 and the Column Width to 0",1". This will allow the value of the combo box to include both results from the Row Source but hide the first, not sure here.

3. Then set the bound column to "1". This will pull the primary key from the two column query described in the Row Source dialog, I think.

Let me know if this sounds right because it seems to have fixed it.

I recieved some tutorial help from this site: www.trigonblue.com/AccessLookup.htm but this focused primarily on using the wizard, which I'm not a fan of.

[pbaldy]

Thoughts in line:

Yep, that did it. So in review:

Using domain tables / lookup tables requires that the form's source is not the domain table but rather the table that the data entry will be occurring, in my case tblResult. One can find the source of the form in the property sheet under the Record Source dialog to check what table the form is sourced. Correct

To use a Combo Box to call data from a domain table one needs to add a Combo Box from the design tools with the Control Source as the foreign key related to the domain table, in my case either tblSampleEventSite.fkSiteID or tblResult.fkAnalyteID. Correct

Then, to populate the foreign key field with the primary key from the domain table - while displaying the desired information from the domain table in the Combo Box on the form - we need to:

1. Change the Row Source to a query that pulls both the primary key and the desirable information from the domain table, in my case "SELECT pkAnalyteID, txtAnalyteName FROM tblAnalyte; " for one of my Combo Boxes. Correct

2. Then, also in the Property Sheet, set the Column Count to 2 and the Column Width to 0",1". This will allow the value of the combo box to include both results from the Row Source but hide the first, not sure here. Correct

3. Then set the bound column to "1". This will pull the primary key from the two column query described in the Row Source dialog, I think. More accurately, it binds the first column in step 1 above, which may or may not be the primary key. It is in your example, but if you reversed the fields in the SELECT it would bind the name rather than the ID. My long-winded point being that it doesn't pull the primary key necessarily, it pulls the numbered column. It's on you to make sure that's the primary key column.

Let me know if this sounds right because it seems to have fixed it.

I recieved some tutorial help from this site: www.trigonblue.com/AccessLookup.htm but this focused primarily on using the wizard, which I'm not a fan of. Nor am I, but in this case the combo wizard would have set all that up for you.

[DNRTech]

Cool, thanks for your feedback and help.