troubleshooting Question

Prevent user closing Main form

Avatar of Surveyor1
Surveyor1Flag for United Kingdom of Great Britain and Northern Ireland asked on
Visual Basic ClassicMicrosoft Access
13 Comments1 Solution578 ViewsLast Modified:

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
    !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
    End With
    projectno = Me!projectno
    DWVOnumber = Me!VOnumber
    DWSWI = Me!swnumber
    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
    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 , ""

    Exit Sub

    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

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros