Prevent user closing Main form

Experts

I have main form [Createadayworkform] which is used for creating a site record.  However, this form is not comitted/ created until the user clicks on button submit site record and is coded as following code #001

Private Sub CBCreateDaywork_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim DayworkNumber As Long
    Dim id As Long
    'Dim idmem As Long
    Dim projectno As Long
    'Dim VOnumber As Long
    Dim connection As ADODB.connection
    Dim rst As New ADODB.Recordset
    Set connection = CurrentProject.connection
    rst.ActiveConnection = connection
    rst.Open "CMWDayworktable", connection, adOpenDynamic, adLockOptimistic
    'code to add new record from open form
    With rst
    .AddNew
    !id = Me!id
    !DayworkNumber = Me!dwnumber
    !DWdateRaised = Me!DWdateRaised
    !DWDescription = Me!DWDescription
    !DWsubject = Me!DWsubject
    !DWraisedby = Me!DWraisedby.Column(1)
    !DWStatus = Me!DWStatus
    !DWWeekEndingtb = Me!DWWkEnding
    .Update
    End With
    projectno = Me!projectno
    DWVOnumber = Me!VOnumber
    DWSWI = Me!swnumber
       
    rst.Close
    connection.Close
    Set rst = Nothing
    Set connection = Nothing
   
    'code to up DW number
    Set connection = CurrentProject.connection
    rst.ActiveConnection = connection
    Dim sql As String
    sql = "SELECT numberregister.projectno, numberregister.DWnumber FROM numberregister WHERE numberregister.projectno =" & projectno
    rst.Open sql, , adOpenDynamic, adLockOptimistic
    rst!dwnumber = rst!dwnumber + 1
    rst.Update
    rst.Close
    connection.Close
    Set rst = Nothing
    Set connection = Nothing
   
   
    DoCmd.Close acForm, "CMWcreateaDayworkform", acSaveNo
    DoCmd.Close acForm, "CMWorkflow", acSaveNo
    DoCmd.OpenForm "VO Database Control Menu"
 
    MsgBox ("CMW Site Record has been created"), vbInformation, "CMWorkflow"
------------------------------------------------------------------------------------------------------------------
The main form also contains a subform [DWLaboursubform] which is used for data entry for labour usage however if the user enters data into the subform but does not commit the mainform and closes the form then the labour usage inputed into the subform will be created in its data source table but unless the mainform is comitted the data is not really required.  I have tried a method of only making the subform enabled once the mainform is comitted but this causes a problem by incorrectly refrencing the Parent and Child ID incorrectly due to the DW Number being created +1 once the mainform code is executed.

I have worked out a solution by only allowing the user to close the form by either submitting mainform or if the exit button is pressed the following code:

Private Sub Command119_Click()
On Error GoTo Command119_Click_Err

    If Not IsNull(Forms!cmwcreateadayworkform!CMWDWLAbourForm!DWLabourNamecbo) Or Recordset.RecordCount > 1 Then
    MsgBox "A labour Item has been created without submitting a site record. Please create a site record or delete labour records before closing form .", vbOKOnly + vbCritical, "CMWorkflow Required Data"
    Exit Sub
    End If
   
    DoCmd.Close , ""

Command119_Click_Exit:
    Exit Sub

Command119_Click_Err:
    MsgBox Error$
    Resume Command119_Click_Exit

End Sub

The problem comes when the user for example enters record #1, 2, 3 etc... but then moves focus to enter new record.  The code still allows the user to exit without the coded message appearing.  I have tried changing record count to >0 and this solves the problem but if the user has not entred any records the message appears preventing the form from being closed.

Should I be looking to show the subform record count and then use code to look at a field with the record count and then if record count  > 0 then run user message ????????? how would i do this ????

Hope this makes sense.

 Many Thanks in advance
ScreenCapture-001.PNG
Surveyor1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Surveyor1Author Commented:
Anyone helping  on this one ????
0
Chris BRetiredCommented:
You can disable the "X" on the form. They can still use File Close, but this might encourage them to do it the "right" way. Works pretty well for me.

http://support.microsoft.com/kb/245746

Chris B
0
datAdrenalineCommented:
What I do is Cancel the Forms Unload event ..

Private Sub Form_Unload(Cancel As Integer)
    Cancel = True
End Sub

Then when I want to ALLOW the form to close, I set the Form's OnUnLoad Property to a ZLS ("")

Private Sub Command119_Click()
On Error GoTo Command119_Click_Err

    If Not IsNull(Forms!cmwcreateadayworkform!CMWDWLAbourForm!DWLabourNamecbo) Or Recordset.RecordCount > 1 Then
    MsgBox "A labour Item has been created without submitting a site record. Please create a site record or delete labour records before closing form .", vbOKOnly + vbCritical, "CMWorkflow Required Data"
    Exit Sub
    End If
   
    Me.OnUnLoad = "" 'This prevents the event from firing
    DoCmd.Close , ""

Command119_Click_Exit:
    Exit Sub

Command119_Click_Err:
    MsgBox Error$
    Resume Command119_Click_Exit

End Sub


-----

More later ... gotta run!!!!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Surveyor1Author Commented:
Thank you so far guys but I have tried datAd proposal which does not seem to solve the issue that if the user moves away from the current labour record i.e. record #1 to add new record and closes at this point the on_click procedure allows the form to close [Command119].   If I set  If Not IsNull(Forms!cmwcreateadayworkform!CMWDWLAbourForm!DWLabourNamecbo) Or Recordset.RecordCount > 0  instead of >1Then this prevents the form from closing until the user either commits the mainform by pressing button "Create Daywork" or by deleting subform records.  The problem here is if a user opens the form in error or needs to close the form to come back later the current solution (i.e >0) prevents the user closing the form (Command119_Click msg Box appears)  although no Labour records have been added. I guess the subform add record is counting as a record ????.

Alternatively would it be better to code a routine whih checks if records exsist and run a VB Code  ????  and how would this be done ??  Sorry fairly new to this and still walking ;-).

Many Thanks

Anthony
0
Surveyor1Author Commented:
datAdrenaline

Your solution also prevents me from opening form in design view ?????  DB but also prevents create daywork btn procedure from showing error and highlighting the following as the problem

 **DoCmd.Close acForm, "CMWcreateaDayworkform", acSaveNo**
    DoCmd.Close acForm, "CMWorkflow", acSaveNo
    DoCmd.OpenForm "VO Database Control Menu"

Any Ideas ???
 
0
Surveyor1Author Commented:
Solved it should be using >=1 not >0.

0
ptykwylxCommented:
You can use global variables as a flag.
Add this to your form,
Public g_ExitForm As Boolean
Private Sub Form_Load()
   g_ExitForm = False
End Sub
Private Sub Form_Unload(Cancel As Integer)
   If Not (g_ExitForm) Then
       Cancel = True
   End If
End Sub
When you load the form, the flag will be false.  No one will be able to close the form until the flag is true.  You just put
g_ExitForm=True
anyplace in your code where you want to let people close the form.
0
Surveyor1Author Commented:
However if i enter a labour record the code works well but if the same record is deleted (i.e. no labour records entred) the message still appears ??? is this a refresh problem or does Access still count deleted records ???
0
Surveyor1Author Commented:
Thank you pty but this does not solve the issue raised in my last post ?

0
Surveyor1Author Commented:
I think I need somehow for my code to check the recordcount for the filtred subform Table as the current code "I Guess" is looking at all records in the subform Table and not the fact that the subform is loaded as a query based on parent id and child ID.  How do i make the code refer only to recordcount for filtred table/ query ??? Is this correct or am i off track ???
0
Surveyor1Author Commented:
I have finally solved it this afternoon!!!!!  I added textbox to footer of subform [Recordcount] and added control source =count("*") and then changed my code on command119 as follows:

Private Sub Command119_Click()
On Error GoTo Command119_Click_Err

    If Not IsNull(Forms!cmwcreateadayworkform!CMWDWLAbourForm!DWLabourNamecbo) Or (Forms!cmwcreateadayworkform!CMWDWLAbourForm!RecordCount) >= 1 Then
    MsgBox "Labour records have been created without submitting a site record. Please create a site record or delete labour records before closing form .", vbOKOnly + vbInformation, "CMWorkflow Site Record Not Submitted"
    Exit Sub
    End If
       
    DoCmd.Close , ""

Command119_Click_Exit:
    Exit Sub

Command119_Click_Err:
    MsgBox Error$
    Resume Command119_Click_Exit

End Sub

Thank you for any input
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
datAdrenalineCommented:
>> Your solution also prevents me from opening form in design view ?????   <<

Only if you try to go into design view from form view because the form unloads then reloads. So with the permenent Cancel = True, that is what you will get.  The information I posted was not intended to be the complete solution to your woes, it was merely the technique I have often used to prevent a form from closing.  I thought you would pack the conditions around when to disable the event <dazed>

EIther way, I am glad you were able to create a solution!
0
Surveyor1Author Commented:
datA thank you for your feedback do you think the solution is a good enough fix or could it be streamlined with better coding ?

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.