Not Allow data entry in Subform if Form Header hasn't been filled out (1 Viewer)

[gstreichan]

Dear Experts,

I have a form sourced on a table with general data:

Autonumber (Request_Nr)
Requestor Name
Urgency
Submitted_Date

And a subform with
Request Nr (child field to Form Autonumber)
Material_Description
Mtl_Code

The requestor is supposed to fill out the header first and then populate the subform with details of material descriptions. However, several times the requestor is filling out the details prior to creating the header then the form Request Nr is still as "(new)". Then when requestor enters details in subform, the table subform is sourced on, doesn't get any number because the autonumber was not created yet. The requestor goes then to the header, fills out and data entered in subform "disappears" of course. I am looking for a way not to subform.enable=False if Form.Request_Nr has not yet been generated or equal "(new)". I don't know where to enter this event or code to do it. Basically, I want the subform not to be editable if there is no number in form Request_Nr field, if the header hasn't been filled out yet.

In what event I should write it, in form or subform, and what vba code for that?

 

[GPGeorge]

I like to use the Enter event of the subform control.
If the parent form is a new record, which means it is dirty, but hasn't yet been saved, I inform the user the main form must be completed and the data validated before anything can be added to the subform and set focus to one of the other controls on the main form.

 

[LarryE]

You can simply disable the subform if a new record hasn't been created yet and then enable it after it has been created.

 

[CJ_London]

I usually hide it so user has nothing to click on

 

[The_Doc_Man]

I'm with CJ on this one. Don't show anything you don't want your users to see until they are ready to see it.

 

[GPGeorge]

I hold to the opposite view regarding hiding and showing controls. If a control is irrelevant, i.e. it is intended only for certain roles like an admin or supervisor, it should just be hidden, of course. That's not what we're talking about here, though.

I see no value to displaying a big empty space -- which you have to do when you hide that subform control occupying that space -- only to magically show something there at some point.

I can see disabling and reenabling a subform this same way, but using the Enter event of the subform control requires a few lines of code to totally block entry to the subform control on new records that haven't yet been saved to the parent table.

Private Sub MySubformControl_Enter()

    If Me.Dirty THEN
        Me.FirstRequiredField.SetFocus
        MsgBox "Enter the Order before adding line items to it."
    End If

End Sub

 

[tvanstiphout]

I hold to the opposite view regarding hiding and showing controls. If a control is irrelevant, i.e. it is intended only for certain roles like an admin or supervisor, it should just be hidden, of course. That's not what we're talking about here, though.

I see no value to displaying a big empty space -- which you have to do when you hide that subform control occupying that space -- only to magically show something there at some point.

I can see disabling and reenabling a subform this same way, but using the Enter event of the subform control requires a few lines of code to totally block entry to the subform control on new records that haven't yet been saved to the parent table.

Private Sub MySubformControl_Enter()

    If Me.Dirty THEN
        Me.FirstRequiredField.SetFocus
        MsgBox "Enter the Order before adding line items to it."
    End If

End Sub

This is what we implemented in Northwind Dev Edition, OrderDetails form. Works well.

 

[Pat Hartman]

I use the subform's BeforeInsert event. You don't care about anything UNLESS the user tries to enter data in the subform before entering/choosing a mainform record. All you need in that event is three lines of code
An Error message.
Me.Undo
Cancel = True

KSS. You don't need to worry about toggling a state and I also don't like the big blank space. You don't need to worry if the main form is saved or not. All you care about is - does the main form record have an autonumberID present. If it does, then the mainform record has been saved or chosen so you don't need to worry about an unsaved dirty record because it can't happen. Access forces the main form record to be saved if it is dirty when you click into the subform. Your validation code in the main form will keep a bad record from being saved. You do have validation code in the main form's BeforeUpdate event, don't you?

Also, my version of Access seems to recognize the absence of a mainform record and raise an error if I type in the subform so even without my normal validation code, I see an error. It is still better to use the BeforeInsert event to check because you can give the user a better error message.