Request for help - Form/subform

[gellerche]

I am doing maintenance on a database that has a subform that's inside a form. Before I made changes to the front and back end, the subform worked fine. A user could enter data in the first row, another row below it would be created, and there were no error messages.

Now, however, if a user enters data in the subform, and there's no other data, this error message shows:

"You tried to assign the Null value to a variable that is not a Variant datatype."

I went into Design view on the subform to Link Child Fields and Link Master Fields to try to relink the subform to the form, but got this error:

"Object variable or with block not set"

Oddly, if there's already a row of data in the subform, adding another row of data works fine.

Does anyone know what's happening here?

Thank you,

Steve Geller

 

[Pat Hartman]

There needs to be a main form record before you can add any records to the subform.

 

[gellerche]

Thank you for your advice, Pat. I do have one question, though. I understand that I need the main form record before the subform. However, even if there were missing records, this shouldn't keep me from linking the child and master in Design view, should it? I guess what I'm asking is: Is there a deeper underlying problem in the dbase (as evidenced by the problem in Design view), and how can I find it?

Thanks again for all your expertise,


Steve Geller

 

[Phonesa]

Speaking of form saving ... is it bad form design to have a form where you have updates to more than one table at once? <whistle>
ie Table Defendants: DefendantID , DefendantDetails
Table Citations: CitationNo , DefendantID

This works for updating existing data, but for new citations where the Defendant is new ... it doesn't like it and says something like "defendantid(for citations) could not be found in table Defendants". I'm currently in the process of searching through the many posts here, but if someone has an idea, please let me know.

 

[gellerche]

I'd say that it's not only OK, but necessary in some cases. (eg. a form where the person enters their name, home phone, work phone, and cell phone should go to two tables - Person and PersonPhone)

 

[Pat Hartman]

gellerche,
each form must be bound to its own query. Is the second subform a child of the mainform or a child of the first subform? If the second subform is dependent on the first subform, the second subform should be on the first subform rather than on the main form. Did any of the field names/ queries change? The form may be corrupted. You may need to rebuild it manually rather than using the wizard. The wizards are helpful but sometimes it's better to do complex things yourself.

Phonesa
The ONLY time you should update multiple tables with a single form is when the tables have a 1-to-1 relationship. In the case you cited, you have a 1-to-many relationship. 1 Defendant to many citations. It doesn't make any sense to add a defendant on the same form as the citation. This is more properly done with a main form for the defendant and a subform for the citation.

 

[gellerche]

Pat:

Sorry for the enormous delay on this. The problem was caused by the Foreign Key (not the PK) in the child table being an autonumber. Apparently, the original designer assumed that the autonumber of the parent's PK (Also an autonumber) would always be synchronized to the child's FK. Something must've happened recently so that was no longer true, and caused the error.

Anyway, I changed the subform so that the FK for the child was matched to the PK of the parent. That solved the problem.

Thanks for your help on this,

Steve Geller