How to insert a new row into a dynaset after normalising underlying tables

[SCDev]

Trying to insert a new record into a dynaset from a query

SELECT TblClient.[ClientID], TblClient.CreateBy, TblClient.CreateDate, tblZip.City, tblResidence.Residence, tblEthnicity.Ethnicity, TblClient.ClientCode, TblClient.ModDate, TblClient.ModBy,TblClient.Language_Primary,
FROM (tblZip INNER JOIN (tblEthnicity INNER JOIN (TblClient INNER JOIN tblEpisodes ON TblClient.[ClientID] = tblEpisodes.ClientID) ON tblEthnicity.EthnicityID = TblClient.Ethnicity) ON (tblZip.CityID = TblClient.City) AND (tblZip.CityID = TblClient.City)) INNER JOIN (tblResidence INNER JOIN tblzip_tblresidence ON tblResidence.ResId = tblzip_tblresidence.Resid) ON tblZip.CityID = tblzip_tblresidence.cityid;

which is the basis for the main form.

Tried update query

INSERT INTO TblClient ( ClientID )
VALUES (FORMS!frmClient!Combo109);

as well as event procedure

Private Sub Command129_Click()
On Error GoTo Err_Command129_Click

Dim currentRS As DAO.Recordset
Set currentRS = Currentdb.OpenRecordset("qryFrmClient")
currentRS.AddNew
currentRS!ClientID = Str(Me![Combo109])
currentRS.Update
'DoCmd.GoToRecord , , acNewRec
[Combo109] = Null
[Box440].[BackStyle] = 0
[Box155].[BackStyle] = 0
[frmEpisodes].[Form].[Visible] = True
ClientCode.Visible = True
Under16.Visible = True
    
Exit_Command129_Click:
    Exit Sub

Err_Command129_Click:
    MsgBox Err.Description
    Resume Exit_Command129_Click
    
End Sub

and not sure where to look to remedy. This is for a combo box. The underlying tables previously had no restrictions before and the structure was a client table had a lookup to a city table and a residence table, so I added the tblzip_tblresidence table for the constraints, which I'm thinking might be the issue. Currently error messages are "item is not in list" and "cannot append, the db is not open". Seeing as I had 200 split access applications dumped on a newb, I'm looking for any and all suggestions.

 

[theDBguy]

What is the value in the combobox? Try using the actual value in the query to see if you still get an error.

 

[The_Doc_Man]

This might not have anything to do with a solution to your problem, but based on the SELECT query involving TblClient showing several demographics fields, it might not be wise to insert a record containing ONLY a ClientID and with blanks/nulls/zeros (as appropriate for the corresponding default values) into TblClient. If ANY of the other fields have a formal relationship anywhere, things could get awkward. If the goal is to create a new client record to be edited, this might make sense, so I don't want to go too far down that rabbit hole yet. It has a plausible purpose, so I'm just describing what jumps out as an oddity.

Your idea that the ZIP table is a problem isn't really THAT much of a problem unless that SELECT query ever returns more than one record when you have all of your criteria fields populated (vs. NULL). You could have multiple clients in the same ZIP code area, but the other criteria should combine to keep it unique. In fact, with a unique ClientID, that alone should be enough to keep things unique for you. Therefore, I'm not saying it is totally OK, but if you implemented it reasonably, adding another criterion is unlikely to break the query. The only REAL danger is if you have over-constrained it so that you get back zero records when you enter your criteria.

The "Not in list" problem doesn't occur for any of the code fragments you showed us because it is a message that is triggered when you try to select a value from your combo and that value isn't there. The code you are showing us does not (cannot) occur simultaneously with the "not in list" error That click-event handler code from Command129 doesn't help because Command129 isn't a combo box. Access names things according to what they are, and this is clearly an Access-created control-button name, a command button that was the 129th control you created on that form. That means we haven't got the whole story of when and how this problem occurs.

 

[SCDev]

What is the value in the combobox? Try using the actual value in the query to see if you still get an error.

What is the value in the combobox? Try using the actual value in the query to see if you still get an error.

the combobox is a drop down that is supposed to show all the current client codes And allow for new ones to be entered. the value is a the client code from the client table

SELECT TblClient.ClientID, TblClient.ClientCode, TblClient.CreateDate, tblEthnicity.Ethnicity, tblResidence.Residence, Year(Now())-IIf(Int(Mid([ClientCode],5,2)) Between 0 And 25,Int("20" & Mid([ClientCode],5,2)),Int("19" & Mid([ClientCode],5,2))) AS Age
FROM (tblZip INNER JOIN (tblEthnicity INNER JOIN (TblClient INNER JOIN tblEpisodes ON TblClient.[ClientID] = tblEpisodes.ClientID) ON tblEthnicity.EthnicityID = TblClient.Ethnicity) ON tblZip.CityID = TblClient.City) INNER JOIN (tblResidence INNER JOIN tblzip_tblresidence ON tblResidence.ResId = tblzip_tblresidence.Resid) ON tblZip.CityID = tblzip_tblresidence.cityid
ORDER BY TblClient.ClientCode;

 

[SCDev]

This might not have anything to do with a solution to your problem, but based on the SELECT query involving TblClient showing several demographics fields, it might not be wise to insert a record containing ONLY a ClientID and with blanks/nulls/zeros (as appropriate for the corresponding default values) into TblClient. If ANY of the other fields have a formal relationship anywhere, things could get awkward. If the goal is to create a new client record to be edited, this might make sense, so I don't want to go too far down that rabbit hole yet. It has a plausible purpose, so I'm just describing what jumps out as an oddity.

oh. That is exactly the case. the client table has a fk to the zip table, which has a fk to a zip_residence table, and that zip_residence table has a fk from the residence table. So thanks for that info

Your idea that the ZIP table is a problem isn't really THAT much of a problem unless that SELECT query ever returns more than one record when you have all of your criteria fields populated (vs. NULL). You could have multiple clients in the same ZIP code area, but the other criteria should combine to keep it unique. In fact, with a unique ClientID, that alone should be enough to keep things unique for you. Therefore, I'm not saying it is totally OK, but if you implemented it reasonably, adding another criterion is unlikely to break the query. The only REAL danger is if you have over-constrained it so that you get back zero records when you enter your criteria.

so how would I constraint cities to counties if I dont have a table for the relationships? I figured a table with the relationships hardcoded would help, thus the existence of the zip_residence table

The "Not in list" problem doesn't occur for any of the code fragments you showed us because it is a message that is triggered when you try to select a value from your combo and that value isn't there. The code you are showing us does not (cannot) occur simultaneously with the "not in list" error That click-event handler code from Command129 doesn't help because Command129 isn't a combo box. Access names things according to what they are, and this is clearly an Access-created control-button name, a command button that was the 129th control you created on that form. That means we haven't got the whole story of when and how this problem occurs.

I get the "not in list" when I try to enter a new value in the combobox. It might be because I just suck at using access. i enter a value into the combobox, press the command button (command129) and think its supposed to work. But I've never used access before december, and even now i'm just tasked with keeping it alive. so whatever more information you need let me know. I can show you the before and after edits I made if that helps

 

[SCDev]

What is the value in the combobox? Try using the actual value in the query to see if you still get an error.

I can run the query alone and it works

 

[The_Doc_Man]

so how would I constraint cities to counties if I dont have a table for the relationships? I figured a table with the relationships hardcoded would help, thus the existence of the zip_residence table

Your idea of table-based validation ISN'T WRONG!!!! My criticism was NOT about your concept. Only about a potentially clunky implementation, particularly now when I see your intent is county, city, zip as a self-related combination.

There are many ways to do this. How about a SINGLE TABLE which lists zip, city, and county together? It won't matter if the zip table contains both incorporated and unincorporated areas because you can make that table have a compound unique key based on the combination of three single-field keys that aren't necessarily individually unique. For instance, I have a cousin who lives outside the city limits of McCalla (AL) for which the zip code is 35111. Both the city limits and the unincorporated areas are in Tuscaloosa County, but one would be Tuscaloosa, McCalla, 35111 and the other would be Tuscaloosa, unincorporated, 35111 as TWO entries in your validation table. Then you can reduce the number of joins to one JOIN with an ON clause that has three AND-linked sub-clauses from the same table. Mechanically, that is SLIGHTLY less work for Access (because fewer tables to manage) and yet gives you plenty of validation abilities. AND doesn't stray far from your original concept.

I get the "not in list" when I try to enter a new value in the combobox.

I have to believe that you get that BEFORE you enter a new value. But the question is how that is handled? Are you using the NotInList event to catch the Not In List error or are you using a general error handler? Or how are you adding the missing data? That is the crux of the problem.

We are looking at two different things here. One is the validation concept. I have to repeat, your idea is not wrong. The problem is not there, or is only there in specifics of implementation.

The problem appears to be in how you manage the discrepancy when you discover it. We need to focus more on how you detect and then correct the Not In List condition.

 

[SCDev]

Your idea of table-based validation ISN'T WRONG!!!! My criticism was NOT about your concept. Only about a potentially clunky implementation, particularly now when I see your intent is county, city, zip as a self-related combination.

Most likely. Lookup fields blew my mind when I first saw them in a table.

There are many ways to do this. How about a SINGLE TABLE which lists zip, city, and county together? It won't matter if the zip table contains both incorporated and unincorporated areas because you can make that table have a compound unique key based on the combination of three single-field keys that aren't necessarily individually unique. For instance, I have a cousin who lives outside the city limits of McCalla (AL) for which the zip code is 35111. Both the city limits and the unincorporated areas are in Tuscaloosa County, but one would be Tuscaloosa, McCalla, 35111 and the other would be Tuscaloosa, unincorporated, 35111 as TWO entries in your validation table. Then you can reduce the number of joins to one JOIN with an ON clause that has three AND-linked sub-clauses from the same table. Mechanically, that is SLIGHTLY less work for Access (because fewer tables to manage) and yet gives you plenty of validation abilities. AND doesn't stray far from your original concept.

Ah. I thought about that before but DBA mind said "thats not how you normalise a database" but Access is different.

I have to believe that you get that BEFORE you enter a new value. But the question is how that is handled? Are you using the NotInList event to catch the Not In List error or are you using a general error handler? Or how are you adding the missing data? That is the crux of the problem.

We are looking at two different things here. One is the validation concept. I have to repeat, your idea is not wrong. The problem is not there, or is only there in specifics of implementation.

The problem appears to be in how you manage the discrepancy when you discover it. We need to focus more on how you detect and then correct the Not In List condition.

I see why you're the Master level jedi. I do have a event procedure for "not in list", which runs the append query from before

INSERT INTO TblClient ( ClientID )
VALUES (FORMS!frmClient!Combo109);

but it sounds like I should change the tables first so I will do that and update the queries after

 

[The_Doc_Man]

Ah. I thought about that before but DBA mind said "thats not how you normalise a database" but Access is different.

OK, for future reference, the THREE-TABLE approach you mentioned is OVER-normalizing. Access is not THAT different when it comes to normalizing and what you were describing would have been legal and probably could be implemented reasonably. It is not that Access is different, but that you have overlooked a significant factor in your data layout. When the three fields are related and would have potentially had formal restrictive relationships anyway, you can simplify things quite a bit.

The idea of having "city", "county", and "zip" for validation CAN be done independently by having three tables. Having three tables kind of implies that you are doing this as independent validation. But this is actually a case of "if all you have is a hammer then all you see are nails." If you have three separate tables, you appear to have three independent factors.

My <zip,city,county> table takes advantage of two facts. First, those items are NOT independent. They are clearly related. In my earlier example of McCalla (AL) as 35111 in Tuscaloosa county, I would never have Metairie (LA) with a zip of 35111 because that combination is not possible. Metairie (LA) would have a different zip (in the 70000's). When there is a strict relationship between two potential fields, they often belong together.

Second, if you had to do proper validation using multiple tables, you would also have to separately validate that McCalla actually DOES go with zip 35111, so there would have to be a formal relationship between zip and city anyway. By enumerating the choices in a single table, you reduce the amount of work required (including data entry overhead) to enumerate the separate but related validation records. It becomes trivial to assure that valid choices are kept together, more intimately associated by being in the same table than would be the case of two tables with a relationship. This even makes oddball combos possible.

Consider Kansas City KS and Kansas City MO. You would need different county and zip values for them even though they have the same name (since you weren't tracking states). And inside each city/county combination you would have multiple zip records that would be a many-to-one relationship if you had split tables. New Orleans, for example, has maybe thirty zip codes in Orleans parish.

If you just list the zips with the correct county and city names, it's all together. And it IS normalized to be all in one table because of the existing geographical relationship. So tell your DBA mind to look for relationships in the validation data as well as in the raw data.

Here's a hint: Validation has several purposes. ONE of them is to minimize storage requirements. Combining three tables doesn't change the space required to store the valid options - but it DOES reduce the amount of space required to maintain the relationship FKs that having separate tables would have required.

 

[SCDev]

OK, for future reference, the THREE-TABLE approach you mentioned is OVER-normalizing. Access is not THAT different when it comes to normalizing and what you were describing would have been legal and probably could be implemented reasonably. It is not that Access is different, but that you have overlooked a significant factor in your data layout. When the three fields are related and would have potentially had formal restrictive relationships anyway, you can simplify things quite a bit.

It felt overkill. Having one client could have a fk to the <city, county, zip> table on the primary id of said table, then referencing the <city, county, zip> table already has the relationships, and i can put any constraints needed on that table.

The idea of having "city", "county", and "zip" for validation CAN be done independently by having three tables. Having three tables kind of implies that you are doing this as independent validation. But this is actually a case of "if all you have is a hammer then all you see are nails." If you have three separate tables, you appear to have three independent factors.

after thinking about it from your point of view ive actually changed this stance. the end user can only change the city anyway, the zip and county only show up in a report. so technically all i need to hammer is the first nail, and the rest are can already be done.

My <zip,city,county> table takes advantage of two facts. First, those items are NOT independent. They are clearly related. In my earlier example of McCalla (AL) as 35111 in Tuscaloosa county, I would never have Metairie (LA) with a zip of 35111 because that combination is not possible. Metairie (LA) would have a different zip (in the 70000's). When there is a strict relationship between two potential fields, they often belong together.

Second, if you had to do proper validation using multiple tables, you would also have to separately validate that McCalla actually DOES go with zip 35111, so there would have to be a formal relationship between zip and city anyway. By enumerating the choices in a single table, you reduce the amount of work required (including data entry overhead) to enumerate the separate but related validation records. It becomes trivial to assure that valid choices are kept together, more intimately associated by being in the same table than would be the case of two tables with a relationship. This even makes oddball combos possible.

True. Which in this case is counterproductive. Client wants cities tied to certain zips tied to certain counties

Consider Kansas City KS and Kansas City MO. You would need different county and zip values for them even though they have the same name (since you weren't tracking states). And inside each city/county combination you would have multiple zip records that would be a many-to-one relationship if you had split tables. New Orleans, for example, has maybe thirty zip codes in Orleans parish.

If you just list the zips with the correct county and city names, it's all together. And it IS normalized to be all in one table because of the existing geographical relationship. So tell your DBA mind to look for relationships in the validation data as well as in the raw data.

Good point.

Here's a hint: Validation has several purposes. ONE of them is to minimize storage requirements. Combining three tables doesn't change the space required to store the valid options - but it DOES reduce the amount of space required to maintain the relationship FKs that having separate tables would have required.

Efficiency is efficient. Thanks for these tips! Learning alot about Access.

 

[SCDev]

@The_Doc_Man Your tips helped! thanks alot appreciate it. Once I consolidated the county, zip, and city with a unique constraint, I redid the queries to select the 1 table instead of 3. worked perfectly and so much less code.

 

[The_Doc_Man]

worked perfectly and so much less code.