Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    End If - Else - Exit Sub etc (A2000)

    I had the following code working ok until I started to Play about with the main form.
    The code starts and looks for the first 4 Unbounds if they are Null and contain no data.
    If they are, the user is warned to populate them.
    Given that the 4 are now populated, the code proceeds to enable a popup to fill in the last 2 unbounds.
    Its not happening.
    The code proceeds to try and run the SQL that populates the field.
    I think the problem lies with the Exit Subs etc and the order they are in.
    A little help un-jumbling them would be appreciated.

    Public Sub cmdDrawGrid_Click()
    'Check if the following Unbound texts have no data:
    'txtUnitID, txtFromDate, txtThruDate, txtColor
    If Me.txtUnitID = "" Or IsNull(Me.txtDateFrom) = True Or IsNull(Me.txtDateThru) = True Or Me.txtColor = "" Then
    MsgBox "You Have Missed A Selection"
    Exit Sub
    Else
    'check if duplicate data exists in tblPeriod:
    Dim CourtesyCheck As Integer
    CourtesyCheck = DCount("*", "tblPeriod", "UnitID=" & Me!txtUnitID & " And " & _
    BuildCriteria("FromDate", dbDate, "<=" & Me!txtDateThru) & " And " & _
    BuildCriteria("ThruDate", dbDate, ">=" & Me!txtDateFrom))
    'If a duplicate has been encounterred, warn the user:
    If CourtesyCheck > 0 Then
    ' Warn the user
    MsgBox "This Vehicle Is Already Booked"
    ' Cancel the update
    Exit Sub
    End If
    'If the Unbounds as above now contain data, leaving only txtJobSelect & txtClientSelect
    'To be entered, open the form frmClientSelect
    If IsNull(Me.txtJobSelect) Or IsNull(Me.txtClientSelect) Then
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmClientSelect"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
    End If
    End If
    End If
    'when frmClientSelect has closed and populated the two remaining unbounds,
    'txtClientSelect & txtJobSelect, we check again t see if a duplicate exists or overlaps
    'in tblPeriod

    Dim strSQL As String
    Dim CourtesySelect As Integer
    CourtesySelect = DCount("*", "tblPeriod", "UnitID=" & Me!txtUnitID & " And " & _
    BuildCriteria("FromDate", dbDate, "<=" & Me!txtDateThru) & " And " & _
    BuildCriteria("ThruDate", dbDate, ">=" & Me!txtDateFrom))
    If CourtesySelect > 0 Then
    ' Warn the user
    MsgBox "This Vehicle Is Already Booked"
    ' Cancel the update
    Exit Sub
    End If
    'Given now,that all Unbounds contain data,
    'and no duplicates are found, we can update tblPeriod with all entrants & draw the grid.
    strSQL = "INSERT INTO tblPeriod ( UnitID, FromDate, ThruDate, colorkey, client, job) VALUES ( [Forms]![frmCourtesyCarPlanner]![txtUnitID], [Forms]![frmCourtesyCarPlanner]![txtDateFrom], [Forms]![frmCourtesyCarPlanner]![txtDateThru],[forms]![frmCourtesyCarPlanner]![txtcolor],[forms]![frmCourtesyCarPlanner]![txtclientselect],[forms]![frmCourtesyCarPlanner]![txtJobSelect] )"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    On Error GoTo NextLine
    ' depends where frmcourtesyCarPlanner has been opened from.

    Forms!frmDetails!RCar = "Yes"
    NextLine:
    DrawGrid
    'set all toggles to blue
    Dim Z As Integer
    For Z = 1 To 17
    Me("tglUnit" & Format$(Z, "00")).ForeColor = vbBlue
    Next Z
    Untoggle
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    Dave,

    Your code has one "End If" too many: in

    <font face="Georgia">DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
    End If
    End If
    End If
    'when frmClientSelect has closed and populated the two remaining unbounds,</font face=georgia>

    you have 3 "End If" in a row; there should be only two. Apart from that, the code seems OK.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    Thanks for that Hans, I hadn't noticed the typo, but.

    The problem still resides.
    When the 4 unbounds are populated and the user press's the command button, the pop-up doesn't pop.
    Their is all indication that the code is trying to populate the table.
    Checking the table afterwards, the record hasn't been saved anyway.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    Dave -

    to "help to unjumble": redesign/rewrite the Sub such that you use several (it looks like 4 or 5) additional functions. The code will start to look like
    <pre> if CheckUnbound then
    if CheckDuplicates then
    ' etc.
    end if
    end if
    </pre>


  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    What do you get if you set Warnings to True before running the SQL?
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    Note to others reading this thread:

    The problem has been solved by the poster - the test IsNull(...) on a text box didn't behave as expected because in another routine, the text box had been set to an empty string "" instead of Null. Adding a test for ... = "" fixed it.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    Thanks all.
    As Hans has pointed out, it was fixed with the following line of code:

    If IsNull(Me.txtJobSelect) Or Me.txtJobSelect = "" Or IsNull(Me.txtClientSelect) Or Me.txtClientSelect = "" Then

    To make things even more efficient I also changed the start of the code to:

    'Check if the following Unbound texts have no data:
    'txtUnitID, txtFromDate, txtThruDate, txtColor
    If IsNull(txtUnitID) Or Me.txtUnitID = "" Then
    MsgBox "Please select a vehicle.", vbCritical
    Exit Sub
    ElseIf IsNull(txtDateFrom) = True Then
    MsgBox "Please Select A From Date", vbCritical
    Exit Sub
    ElseIf IsNull(txtDateThru) = True Then
    MsgBox "Please Select An End Date", vbCritical
    Exit Sub
    ElseIf IsNull(txtColor) Or Me.txtColor = "" Then
    MsgBox "Please Select An Action By It's Colour.", vbCritical
    Exit Sub
    End If

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    If you want to be user-friendly, you can set the focus to a control if the user left a text box empty. I used the text boxes themselves in the example below, but you can set the focus to another control if desirable (for instance, a day toggle if txtDateFrom or txtDateThru is empty, or the colorkey combo box is txtColor is empty).

    'Check if the following Unbound texts have no data:
    'txtUnitID, txtFromDate, txtThruDate, txtColor
    If IsNull(txtUnitID) Or Me.txtUnitID = "" Then
    MsgBox "Please select a vehicle.", vbCritical
    txtUnitID.SetFocus
    Exit Sub
    ElseIf IsNull(txtDateFrom) = True Then
    MsgBox "Please Select A From Date", vbCritical
    txtDateFrom.SetFocus
    Exit Sub
    ElseIf IsNull(txtDateThru) = True Then
    MsgBox "Please Select An End Date", vbCritical
    txtDateThru.SetFocus
    Exit Sub
    ElseIf IsNull(txtColor) Or Me.txtColor = "" Then
    MsgBox "Please Select An Action By It's Colour.", vbCritical
    txtColor.SetFocus
    Exit Sub
    End If

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    Again Thanks.

    That was my next step.
    BTW
    None of my users are friendly <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    If your users aren't friendly, maybe it's because you're not user-friendly.

    Just kidding!

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    Quite right there, my reputation precedes me.
    Have a good day

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    Slightly simpler and faster:

    <pre> If (Me.txtJobSelect & "") = "" Or (Me.txtClientSelect & "") = "" Then
    </pre>

    Legare Coleman

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End If - Else - Exit Sub etc (A2000)

    Thanks for that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •