Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Keep Subform Locked Until Main Form Is Completed

Posted on 2016-09-29
25
Medium Priority
?
132 Views
Last Modified: 2016-09-29
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!
0
Comment
Question by:Dustin Stanley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 7
  • 6
25 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41822003
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'.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41822007
set the datasource for the subform to a query using TOP n syntax.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41822013
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Dustin Stanley
ID: 41822035
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
0
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 1000 total points
ID: 41822037
add end if above exit sub
0
 

Author Comment

by:Dustin Stanley
ID: 41822045
db43.jpg
0
 

Author Comment

by:Dustin Stanley
ID: 41822057
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.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41822066
you are supposed to be testing the main form for dirty, the code should be in the main form, and reference the subform
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41822071
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
0
 

Author Comment

by:Dustin Stanley
ID: 41822090
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41822106
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41822134
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.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41822151
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.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41822172
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41822182
Can the subform be requeried and stay on record or stay on new.
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 1000 total points
ID: 41822189
The code belongs in the subform.

Here is the exact procedure I have in several subforms of the Client form.  Apparently, I used the BeforeInsert event rather than the dirty event.
Private Sub Form_BeforeInsert(Cancel As Integer)
    If Me.Parent.txtClientID & "" = "" Then
        MsgBox "Please select a client first.", vbOKOnly
        Cancel = True
        Me.Parent.cboFindClient.SetFocus
        Exit Sub
    End If
End Sub

Open in new window

0
 

Author Comment

by:Dustin Stanley
ID: 41822191
Ok can you tell me why i am only able to see events enter and exit for the Subform?
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41822198
You are focused on the subform control rather than on the subform itself.
0
 

Author Comment

by:Dustin Stanley
ID: 41822280
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.....
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41822405
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41822505
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

0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41822515
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.
0
 

Author Comment

by:Dustin Stanley
ID: 41822538
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

0
 

Author Closing Comment

by:Dustin Stanley
ID: 41822539
Thank you for your patience and teachings. You are guys are Awesome!
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41822653
You are welcome.  Learning about how to use form events correctly is very important.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question