Form, SubForm and Data Entry

[ronnroz]

Hello there,

I have a form that has a subform (2 actually, tabbed). I have a many to many relationship estabslished, and the data on the subform is taken from the relationship table/query. It's working fine.

When adding a new record, if the user accidentally enters something in the subform first, instead of the mainform, and then go back to the main form, an error message pops up "Index or Primary Key cannot contain a Null value" and it won't let us get out of that form at all.

I would like to resolve this problem, by restricting the user to enter info in the mainform first and then go to subform. How can that be done? Elaborate instructions will be highly appreciated.

Thank you all guys, this forum has been of great help and learning tool for me. Thanks for all your support.

Ronnie

 

[trucktime]

Subform

You can disable the subform when the main form opens by putting this in the On Current Event of the main form

Me.yoursubformname.Enabled = False

Then after the user enters data in a field on the main form the subform will be enabled by putting this in the After Update Event of that field:

Me.yoursubformname.Enabled = True

 

[quest4]

How elaborate and tricky would you like to get? You can go as far as the tabs are invisable till the last required txtbox on the main form is filled in. We can be as simple and easy as putting the focus on the first required data object. I have found that that the second method is usually enough, because once that focus is set on the main form, if your relationships are correctly made, the records ID is now assigned and that is probably what was happening to you. In a new record one of the first thing I do is create the new record control number, which is the PKey. hth.

 

[ronnroz]

Thanks Trucktime & quest4 both for replying to my post.

Trucktime - I followed your approach and it's working partially, it does make the subform invisible but it doesn't appear as soon as the first field is filled in. Actually it doesn't appear at all. I did put Me.yoursubformname.Enabled = True in the After Update event of that field, still no luck. Maybe I am doing something wrong.

Quest4 - when I said elaborate, I meant detailed instruction, cause I am a newbie and whatever solution you guys suggest, I would like detailed instruction with that, if possible.
I did try the second approach you mentioned, to put focus on the first field, but when I tab to the subform field or using mouse, click on the subform field, I am able to fill that field in and then trying to come back to the mainform, and it starts showing that error message. I would like my user not to be able to generate this error situation. Maybe it means the first field has to bo filled in before the user can go to the subform.

Please help me with your suggestions. Thank you.

 

[trucktime]

Subform

Did you substitute yoursubformname with the actual name of your subform?

 

[quest4]

In the relationship, do you have the referential integrety checked? How about cascade update and delete? Trucktime also has a good idea. Myself I use a function to check the main form for blanks, before a user can enter the subform, but that is alot of coding and I don't relly think you want to get into that at this time. hth.

 

[ronnroz]

Thank you both, again.

Trucktime - I did replace the name of my subform with yoursubformname.

It's working fine now. Thank you!

I found out what I was doing wrong (too silly to mention here)

 

[quest4]

Your welcome, sorry I was not more help.

 

[trucktime]

Great

I'm glad it works for you.

 

[quest4]

Try this:
If Me.NewRecord Then
Me.SubFrmOrg.Enabled = False
End If
hth.

 

[trucktime]

Subform

quest4 has a good point there.

You can also put a command button on the form (that says 'Edit Record'), and put this in the On Click Event:

Me.SubFrmOrg.Enabled = True

 

[ronnroz]

This is how I did it:

Me.SubFrmOrg.Enabled = Not (IsNull(FirstName))


Thanks guys.