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

    Mix & Match (A2000 SR1)

    I had two pieces of code which I tried to mix, it doesn't quite do what is't supposed to do.
    Basically the SQL inserts the record, then checks if the criteria clashes (duplicate)



    (This is from a after update of a combo<img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Dim CourtesySelect As Integer
    CourtesySelect = DCount("*", "qrySelectCourtesy", "UnitID=" & Me!UnitId & " And " & _
    BuildCriteria("FromDate", dbDate, "<=" & Me!ThruDate) & " And " & _
    BuildCriteria("ThruDate", dbDate, ">=" & Me!FromDate))
    If CourtesySelect > 0 Then
    ' Warn the user
    MsgBox "This Vehicle Is Already Booked"
    ' Cancel the update
    Cancel = True
    End If
    .................................................. ......................This is from a button.........................................
    If IsNull(Me!txtUnitID) Or Me!txtUnitID = "" Or IsNull(Me!txtDateFrom) Or IsNull(Me!txtDateThru) Or IsNull(Me!txtColor) Or Me!txtColor = "" Or IsNull(Me!txtClientSelect) Or Me!txtClientSelect = "" Or IsNull(Me!txtJobSelect) Then
    MsgBox "Invalid Entry, Please Check Your Selection Again"
    GoTo SkipCode
    End If
    Dim strSQL As String
    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
    DrawGrid
    Untoggle
    SkipCode:
    Me.txtUnitID = ""
    Me.txtDateFrom = Null
    Me.txtDateThru = Null
    Me.txtVehicleID = ""
    Me.txtClientSelect = ""
    Me.txtJobSelect = ""
    Untoggle

    .................................................. ............This is where I mixed it................................................ ........................
    If IsNull(Me!txtUnitID) Or Me!txtUnitID = "" Or IsNull(Me!txtDateFrom) Or IsNull(Me!txtDateThru) Or IsNull(Me!txtColor) Or Me!txtColor = "" Or IsNull(Me!txtClientSelect) Or Me!txtClientSelect = "" Or IsNull(Me!txtJobSelect) Then
    MsgBox "Invalid Entry, Please Check Your Selection Again"
    GoTo SkipCode
    End If
    Dim strSQL As String
    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
    CourtesySelect = DCount("*", "qrySelectCourtesy", "UnitID=" & Me!UnitId & " And " & _
    BuildCriteria("FromDate", dbDate, "<=" & Me!ThruDate) & " And " & _
    BuildCriteria("ThruDate", dbDate, ">=" & Me!FromDate))
    If CourtesySelect > 0 Then
    ' Warn the user
    MsgBox "This Vehicle Is Already Booked"
    ' Cancel the update
    Cancel = True
    GoTo SkipCode
    DrawGrid
    Untoggle
    SkipCode:
    Me.txtUnitID = ""
    Me.txtDateFrom = Null
    Me.txtDateThru = Null
    Me.txtVehicleID = ""
    Me.txtClientSelect = ""
    Me.txtJobSelect = ""
    Untoggle
    End If
    End Sub

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

    Re: Mix & Match (A2000 SR1)

    Dave,

    You state
    <hr>it doesn't quite do what is't supposed to do<hr>
    but you don't mention what it was supposed to do and in what way it fails to meet your expectations. <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    I would guess that you need to check if the car has already been booked for (part of) the selected period of time before inserting a record into tblPeriod.

    If so, you need to move the part with <font face="Georgia">If CourtesySelect > 0 Then ... End If</font face=georgia> up; it should be before <font face="Georgia">DoCmd.RunSQL strSQL</font face=georgia>. You don't specify where you are using this pick'n'mix code; please note that the instruction <font face="Georgia">Cancel = True</font face=georgia> is valid for a BeforeUpdate event and for an OnDblClick event, but not for an AfterUpdate or OnClick event.

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

    Re: Mix & Match (A2000 SR1)

    Hans
    Points noted.
    Here's what I've come up with, and it works, (subject to further testing)
    One prob though, tblPeriod is now creating a record thatI don't want.


    If IsNull(Me!txtUnitID) Or Me!txtUnitID = "" Or IsNull(Me!txtDateFrom) Or IsNull(Me!txtDateThru) Or IsNull(Me!txtColor) Or Me!txtColor = "" Or IsNull(Me!txtClientSelect) Or Me!txtClientSelect = "" Or IsNull(Me!txtJobSelect) Then
    MsgBox "Invalid Entry, Please Check Your Selection Again"
    GoTo SkipCode
    End If
    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
    GoTo SkipCode
    End If
    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
    DrawGrid
    Untoggle
    SkipCode:
    Me.txtUnitID = ""
    Me.txtDateFrom = Null
    Me.txtDateThru = Null
    Me.txtVehicleID = ""
    Me.txtClientSelect = ""
    Me.txtJobSelect = ""
    Untoggle
    End Sub
    Attached Files Attached Files

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

    Re: Mix & Match (A2000 SR1)

    Hi Dave,

    The code looks OK to me now; are you sure the unwanted record wasn't left over from a previous attempt?

    If you still have problems, set a breakpoint at the beginning of the code (click in the margin to the left of the line where you want the breakpoint, for example <font face="Georgia">If IsNull(Me!txtUnitID) ... Then</font face=georgia>. Open the form and do whatever calls this code; execution should pause at the line with the breakpoint. Single step through your code - the function key F8 lets you execute the code step by step. You can inspect the value of variables by hovering the mouse pointer above them in the module window, or by activating the Immediate window and typing <font face="Georgia">? variablename</font face=georgia> or <font face="Georgia">MsgBox variablename</font face=georgia>. (By the time you get to DrawGrid, it's better to either let the code run on by pressing F5, or stepping over the subroutines by typing Shift+F8 instead of F8; otherwise, you'll spend endless time in For ... Next loops.)

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

    Re: Mix & Match (A2000 SR1)

    Hans
    I found the problem.
    I had set the forms recordsource to tblPeriod, which it didn't need.
    The combo from where the ColorID comes from, also had a control source from tblPeriod.
    So when I selected my Color, the data was pushed to create an entry (AutoNumber) in the table.
    Removing these items, enables the data to be pushed "only" when asked to do so.

    Thanks for the help.

Posting Permissions

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