Solved

MS Access Keep Subform Locked Until Main Form Is Completed

Posted on 2016-09-29
25
51 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
Comment Utility
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
Comment Utility
set the datasource for the subform to a query using TOP n syntax.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
Comment Utility
add end if above exit sub
0
 

Author Comment

by:Dustin Stanley
Comment Utility
db43.jpg
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 7

Expert Comment

by:COACHMAN99
Comment Utility
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
Comment Utility
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
Comment Utility
Can the subform be requeried and stay on record or stay on new.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
You are focused on the subform control rather than on the subform itself.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you for your patience and teachings. You are guys are Awesome!
0
 
LVL 34

Expert Comment

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

7 Experts available now in Live!

Get 1:1 Help Now