Error adding new records on subform

[mlai08]

I have an employee input form which contains an unbound Employee Find text box. It allows users to find an employee and show the employee details on the subform. EmpID (an autonumber) is the master/child link field.

It works fine except that we I add a new record, it gives me an error "You tried to assign the Null value to a variable that is not a variant data type." This problem does not exist if I add new record independently on the subform.

Does anyone has any good idea of resolving this issue. I don't really want to open a separate form for new record input.

A sample database is enclosed to demonstrate the problem. For simplicity, I have removed some of the fields and event procedures on the forms.

Thanks

 

[vbaInet]

You have much bigger problems. Your database contains ONE table with 50+ fields.

Normalise

 

[mlai08]

You have much bigger problems. Your database contains ONE table with 50+ fields.

Normalise

Thanks for your comments but the table is basically normalized. All fields relate to each employee and do not create repeated groups nor redundant data. I could further normalize the table but without significantly improve the system efficiency. In fact, there are other tables linked to this employee tables but I did not include them in the demo database.

Any way, I don't think the question I raised would have anything to do with table normalization. I suspect it might be related to the autonumber EmpID field linked between the master & the child form.

If you have any idea to fix the problem, I would be appreciated.

 

[vbaInet]

Nothing the related to the autonumber field. Your table needs to be normalised as I previously mentioned and you should be using queries as the Record Source of your form. Normalisation isn't all about repetition of data or redundancy. There's more to it. Have a look at the link I provided. In its current state I foresee problems with "fetching" data using queries and your db is not at all optimal.

The problem was fixed in the db I attached in my last post.

 

[mlai08]

I got it now. Thanks for your help.

 

[vbaInet]

No problemo!