asked on

MS Access Keep Subform Locked Until Main Form Is Completed

I have a main form called "Form" and a subform on the main form. That Subform is called "ProdSubForm".

The are linked Master to child by the field "SkuID".

"Form" is from the table called "SKUs" and "ProdSubForm" is from the table called "Products".

There is a relationship between the tables "SKUs" and "Products" on the "SkuID" field.

I need "ProdSubForm" to be locked until the info is put into the main form called "Form".

Until this happens there is no "SkuID" produced in the "SKUs" table and there for no "ProductID" in the "Products" table can be produced and synced back to "SkuID".

I have coded in VBA so far

Private Sub Form_Current()
If IsNull(Me.SkuID) Then
   Forms!Form1.Form![ProdSubForm].Enabled = False
Else
   Forms!Form1.Form![ProdSubForm].Enabled = True
End If
End Sub

Open in new window

But the subform "ProdConSubForm" stays locked until I go to next or previous record and come back.

The only time "ProdConSubForm" Should be locked is during a add new record. I have a add new record button called "btnAddNewRecord".

How do I fix this and maybe add a error message or such to info the user to fill in the Main "Form" first.

Also is there a way to set a specific number of records on the contentious "ProdConSubForm".

The main form "Form" is a single record form.

Thank you!

COACHMAN99

did you try unlocking the sub-form when the main form datachange or afterupdate events fire?
also, using a reserved word 'Form' to name the form is probably not a good idea 'frmMain'.

COACHMAN99

set the datasource for the subform to a query using TOP n syntax.

PatHartman

Rather than locking the subform, you just need to prevent edits so I use code in the subform's On Dirty event.

Private Sub Form_Dirty(Cancel As Integer)
If IsNull(Me.Parent.SkuID) Then
   msgbox "Please add a Product before adding a Product.", vbOKOnly
    Cancel = True
    Me.Parent.SKUID.SetFocus
    Exit sub
End Sub

Open in new window

Dustin Stanley

ASKER

ok i used

Private Sub Form_Dirty(Cancel As Integer)
If IsNull(Me.Parent.SkuID) Then
   MsgBox "Please add a product before adding a Product.", vbOKOnly
    Cancel = True
    Me.Parent.SkuID.SetFocus
    Exit Sub
End Sub

Open in new window

This gives me a compile error: Block If without End If

I will be changing the name of the form it is just what it is right now for testing.

I have tried doing a datachange from one of the main form fields and it doesn't help and a data change requery sets the subform back to the original record instead of a new record

SOLUTION

COACHMAN99

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dustin Stanley

ASKER

Dustin Stanley

ASKER

The expression you entered has an invalid reference to the parent property. run time error 2452.

The subform only allows enter and exit events. I couldn't find any other events in the properties.

COACHMAN99

you are supposed to be testing the main form for dirty, the code should be in the main form, and reference the subform

COACHMAN99

e.g.
Private Sub Form_Dirty()
If IsNull(Me.SkuID) Then
Forms!Form1.Form![ProdSubForm].Enabled = False
Else
Forms!Form1.Form![ProdSubForm].Enabled = True
End If
End Sub

Dustin Stanley

ASKER

Ok I just want some clarification. I am more of a hands on type of person. Experience is the key with me.

e.g.
Private Sub Form_Dirty()
If IsNull(Me.SkuID) Then
Forms!Form1.Form![ProdSubForm].Enabled = False
Else
Forms!Form1.Form![ProdSubForm].Enabled = True
End If
End Sub

Does this go into the subform or Main form body?

Private Sub Form_Dirty(Cancel As Integer)
If IsNull(Me.Parent.SkuID) Then
MsgBox "Please add a product before adding a Product.", vbOKOnly
Cancel = True
Me.Parent.SkuID.SetFocus
Exit Sub
End Sub

and this one goes into the Main form. I understand both codes are not used at the same time.

Private Sub Form_Dirty()
If IsNull(Me.SkuID) Then
Forms!Form1.Form![ProdSubForm].Enabled = False
Else
Forms!Form1.Form![ProdSubForm].Enabled = True
End If
End Sub

Is still not automatically unlocking the Subform "ProdSubForm".

It doesn't give errors but it just keeps the subform locked until I go to next record and back.

Dustin Stanley

ASKER

The reason I ask which form it goes into is because you said Main but at the top it says Private Sub Form_Dirty() i thought that Private and then the form is the main directory. Correct me if i'm wrong. Thanks for the help.

Dustin Stanley

ASKER

OK I used in the main form named "Form"

Private Sub Form_Dirty(Cancel As Integer)
If IsNull(Me.Parent.SkuID) Then
   msgbox "Please add a Product before adding a Product.", vbOKOnly
    Cancel = True
    Me.Parent.SKUID.SetFocus
    Exit sub
End Sub

Open in new window

No errors. But still does not automatically unlock the "ProConSubForm"

Also I see in the coding the message box but none ever appears either.

Even after going to next record and back ProdConSubForm is still locked!

My fault on the coding understanding Private sub doesn't refer to the Form as in SUB FORM...I just overlooked it.

COACHMAN99

You are close.
Why reference parent when your code is in the main form?

try in the datachange or afterupdate events as well.
I have to run to a meeting - will check later today
cheers.

PatHartman

I forgot to add that the reason the code you have isn't working is because the Current event fires when the subform gets the focus. It isn't until the the record is dirtied that the foreign key is populated. So SKUID in the subform would always be null in the Current event of a new record. Therefore, the form never unlocks so you can type into it to cause the foreign key to be populated.

Dustin Stanley

ASKER

Can the subform be requeried and stay on record or stay on new.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dustin Stanley

ASKER

Ok can you tell me why i am only able to see events enter and exit for the Subform?

PatHartman

You are focused on the subform control rather than on the subform itself.

Dustin Stanley

ASKER

Ok I set it up like this for now.

Private Sub Form_Current()
If IsNull(Me.SkuID) Then
   Forms!Form1.Form![ProdSubForm].Enabled = False
Else
   Forms!Form1.Form![ProdSubForm].Enabled = True
End If
End Sub

Private Sub SKU_AfterUpdate()
If IsNull(Me.SkuID) Then
   Forms!Form1.Form![ProdSubForm].Enabled = False
Else
   Forms!Form1.Form![ProdSubForm].Enabled = True
End If
End Sub

Open in new window

SKU is a mandatory field in the form for SKUs. So the first code:

Private Sub Form_Current()
If IsNull(Me.SkuID) Then
   Forms!Form1.Form![ProdSubForm].Enabled = False
Else
   Forms!Form1.Form![ProdSubForm].Enabled = True
End If
End Sub

Open in new window

locks down the subform when you add a new record.

and the code:

Private Sub SKU_AfterUpdate()
If IsNull(Me.SkuID) Then
   Forms!Form1.Form![ProdSubForm].Enabled = False
Else
   Forms!Form1.Form![ProdSubForm].Enabled = True
End If
End Sub

Open in new window

When the user inserts a SKU in the SKU field the code reevaluates the form unlocking the subform.

Any issues anyone can think of.....

PatHartman

You seem to be fixated on locking the subform but as I said earlier, there is no need to Lock the subform. Whether the subform is locked or not is relevant only if the user tries to change something and the code I provided handles that.

As you can see by your "solution", it takes code in two procedures whereas if you use my solution, the code goes in ONE place and it works in ALL cases since it is in the correct event. The code belongs in the subform rather than in the main form. The BeforeInsert event fires ONLY when a new record is being added and ONLY once after the first character is typed ANYWHERE in the subform.

When you don't use the correct event, you always have to write extra code and you frequently leave gaping holes because you didn't anticipate something that Access might do automatically.

Dustin Stanley

ASKER

Ok I get what you are saying and I was accidentally putting the code into the main form. I thought I was on the subform but I wasn't. The only part i don't get is what would I replace of mine with your

cboFindClient

Open in new window

in:

Me.Parent.cboFindClient.SetFocus

Open in new window

PatHartman

My own code is setting focus to a search combo on the main form. Since you want the user to go back to the main form, choose whatever control is logically the first control he would type into but NOT the autonumber ID control since he doesn't enter that. In the procedure I wrote for you, I set focus to the autonumber ID because I didn't know the name of any other control. Sorry my example left off the closing "End If". That's what happens when you type air code for someone.

Dustin Stanley

ASKER

Ok works perfect. I am going to a control text box field called SKU and I had this code on the subform and was referring back to the main form. Here is a good cheat sheet site http://access.mvps.org/access/forms/frm0031.htm

The code I finalized with was:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.SkuID & "" = "" Then
        MsgBox "Please enter SKU first.", vbInformation, "Warning"
        Cancel = True
       Me.Parent!SKU.SetFocus
        Exit Sub
    End If
End Sub

Open in new window

Dustin Stanley

ASKER

Thank you for your patience and teachings. You are guys are Awesome!

PatHartman

You are welcome. Learning about how to use form events correctly is very important.