Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Prevent user closing Main form

Avatar of Surveyor1
Surveyor1Flag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessVisual Basic Classic
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
Avatar of Surveyor1
Surveyor1Flag of United Kingdom of Great Britain and Northern Ireland image

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

This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answers