Solved

MS Access Keep Subform Locked Until Main Form Is Completed

Posted on 2016-09-29
25
62 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 34

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
 

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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 34

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 34

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 34

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 34

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 34

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 34

Expert Comment

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now