Solved

MS Access Keep Subform Locked Until Main Form Is Completed

Posted on 2016-09-29
25
75 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
  • 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 36

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 250 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 36

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 36

Accepted Solution

by:
PatHartman earned 250 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 36

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 36

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 36

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 36

Expert Comment

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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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