Cannot enter value into blank field... (1 Viewer)

[maw230]

Ok, i think this is a fairly common newbie problem.
I have a form based on a query that joins several tables linked through Pks and FKs. I am able to use this form to add new records without a problem, but if i want to edit an existing record problems arise.
First I was getting this error "Cannot enter value into blank field on 'one' side of outer join." I read somewhere to change my form and query Recordset types to Dynaset (inconsistent updates) to fix this issue. I was able to change the form and query properties, but only the form property would stay saved when i reopened it. Now I was able to edit existing records, however the values that i added were only going to one table instead of "cascade updating" the tables it was joined to.

 

[maw230]

Actually, with Recordset type set to dynaset (inconsistent) i cant add new records. Changed everything back to Dynaset and i can add new but cant update existing records.

 

[maw230]

It looks like its because of an outer join between two tables. Would that cause a recordset to be non-updateable?
The outer join is necessary for the subform to return all of the records.
Many people seem to practice making each form based on only one table and not a query, but is that really necessary? Maybe it is here..

 

[boblarson]

A common mistake is for someone to include the main form table in the subform recordset. This is simply not required. You include ONLY the child table in the subform and ONLY the main table in the main form. The Master/Child link keeps things synched. You do not need to connect the two in the recordsources.

 

[maw230]

thanks bob, I did not know that. I removed the main form table from the subform recordset query. However, it didn't change my form behavior (not sure if that was your intention).
When I add to an existing record in the subform it will place the new data into one of the tables, but will not cascade update the table joined to it, so it does not become an actual record..
Should I include more details?

 

[boblarson]

When I add to an existing record in the subform it will place the new data into one of the tables, but will not cascade update the table joined to it, so it does not become an actual record..
Should I include more details?

That means that your master/child links are not set properly (most likely). Also, make sure that the AllowEdits property is set for each form.

And if you still can't get it, perhaps a quick upload of the Db here can help. (remember to use bogus data).

 

[maw230]

Allow edits are on. Master/Child links are both the same field..? But they appear to be correct..
Here is the SQL for the recordset query of my subform:

SELECT tblPassFail.PassFail, tblPassFail.Reason, tblPassFail.Errors, tblFines.Fine, tblChecks.CheckReqSentDate, tblChecks.CheckMailedDate, tblChecks.StateInvoiceNum, tblPassFail.Note, tblFines.FineID, tblChecks.CheckNum, tblChecks.CheckID, *
FROM (tblChecks RIGHT JOIN tblFines ON tblChecks.CheckID = tblFines.CheckID) INNER JOIN tblPassFail ON tblFines.FineID = tblPassFail.FineID;

I think my problem is between tblChecks and tblFines (see attached relationships).

It seems like tblFines needs a CheckID regardless of whether or not there was a CheckNum... But that kind of defeats the purpose.

 

[maw230]

I have tried creating the main and all subforms based on individual tables instead of a query joining several tables, but i get the same error. Google offered me this explanation:

The problem appears to be that you are changing the data in a field on the ONE side of a MANY-to-ONE relationship defined by an explicit JOIN. Access has realized that you are doing something improper. I think this action that you attempted violates normalization rules.

The field in the parent record suddenly has to become multi-valued because you now have two records with that field one way, and you have asked Access to make the next record with that field set to another way. But it isn't really another record! It is a JOIN of two recordsets and only ONE of the recordsets supports being multi-valued. (The child record components...) You are trying to change a field that isn't in that part of the JOINed record.

I know that wasn't clear in English, but I think it is correct.

Look at it this way. For the JOIN record, it transparently appears that for Primary Parent Key 1, you can have two different records with Primary Child Keys 1 and 2. But when you start to insert the third record, you change something in the parent that doesn't change the primary key value. (i.e. Primary Parent Key is still 1.) Now Access sees that this non-key field in the parent table depends on a child key, which is backwards.

Hope this helps.

This makes sense to me. I'm just having trouble understanding why Access wants to create a new record rather than letting me edit the existing one with additional info.

 

[boblarson]

The problem still comes back as - you have, in one (or more than one) form - more than one table. Don't do it.

Post a copy of the Db.

 

[maw230]

Ok, here is a copy of the db. I don't know if it will function properly since it will be missing an ODBC connection, but i dont think that will matter in this case.
Thanks for taking a look. im sure its a mess.

 

[boblarson]

Your subform still has three tables in its recordsource. You CAN'T DO THAT. Especially with a ONE to Many to Many. The record source for your subform should be tblPassFail. Then you should have as a subform ON THAT SUBFORM, the tblFines and then on that one tblChecks (at least right now the way your data is structured).

So, yes, you can have subforms on subforms on subforms (up to 7 layers deep). And in this case you need to do so if you want all of those on the form.

 

[maw230]

I'm sorry i completely forgot to give you any details.
Can you look at "frmNoQueries"? As that is the one where i made each subform of off its own table.
And, ignore CheckID i included this on accident.

 

[boblarson]

Okay, so you had your table structure off a bit. tblChecks should have FineID in it but tblFines should not have checkID in it. I modified and modified the subform and it is now working. See my attachment.

 

[maw230]

I can't believe I missed that, but it makes perfect sense.
Thanks for your help, bob. You saved me a lot of time and frustration.
I dont know what i would do without these forums. I might have to buy a book or something! Wait, i have one right here.

 

[maw230]

But alas, another quick question. Will it be necessary to manually add FineID's to tblChecks so that they have matching records? Or do i not need to worry about it...

 

[boblarson]

It will add them automatically from this point out if you use my changes. The links are set to FineID within that set of subforms now instead of CheckID. What you would have to do is go manually add them if they existed before now.

 

[maw230]

bob,
Everything is working well, except now I get this error when trying to enter a value for "Fine" on the main form (the subform works fine):

You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162)

Ive been researching it all morning with no luck. I've read in many places that it may have to do with Master/Child links, which seem fine to me.
Also, that it may have to do with switching between forms and access thinking i am leaving a null value...

 

[boblarson]

I don't see a spot on your main form (at least on the sample you gave me) for Fine. Why do you have that on your main form AND on your subform?

 

[maw230]

If you open frmNoQueries there is a text box for Fine. Try to add a new record and when you get to Fine you will see the error.
What i meant was that when i open the Fine form by itself i can enter a fine without an error. However, when in the main form if i try to enter a fine i get the error. The fine in the main form is the Fine subform. In other words, Fine on the main form is the fine on the subform...

 

[boblarson]