Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: What's wrong with this code?

    Case "Private Lesson (Normal Court)"
    strSQL = "UPDATE TBLmember SET CreditValue = CreditValue-4 " & _
    "WHERE MemberID = " & Forms!TBLtennisbooking!MemberID.Value & ";"
    "UPDATE TBLtrainers SET MonthlyLessons = MonthlyLessons+1 " & _
    "WHERE TrainerID = " & Forms!TBLtennisbooking!TrainerID.Value & ";"

    Says compile error, expected line number or label or statement or end of a statement?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This part looks ok:
    strSQL = "UPDATE TBLmember SET CreditValue = CreditValue-4 " & _
    "WHERE MemberID = " & Forms!TBLtennisbooking!MemberID.Value & ";"
    But you need to assing this statement to string also:
    "UPDATE TBLtrainers SET MonthlyLessons = MonthlyLessons+1 " & _
    "WHERE TrainerID = " & Forms!TBLtennisbooking!TrainerID.Value & ";"
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2003
    Posts
    14
    So how should merge the two together somehow into one big string? I don't understand what you mean.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If you merged the two into one SQL statement, it wouldn't execute.
    After building the first string, use
    Docmd.RunSql strSql

    Then build the second string:

    strSql = "Update tblTrainers..."

    and execute that one with
    DoCmd.RunSql strSql
    Inspiration Through Fermentation

  5. #5
    Join Date
    Nov 2003
    Posts
    14
    Quite difficult to do that when my whole code is like this:

    Private Sub Command14_Click()
    'defined as string instead of variant
    Dim strSQL As String



    'use the select case statement instead of IF
    Select Case Me!BookingTypeID
    Case "Normal Court"
    strSQL = "UPDATE TBLmember SET CreditValue = CreditValue-1 " & _
    "WHERE MemberID = " & Forms!TBLtennisbooking!MemberID.Value & ";"
    Case "Club Court"
    strSQL = "UPDATE TBLmember SET CreditValue = CreditValue-2 " & _
    "WHERE MemberID = " & Forms!TBLtennisbooking!MemberID.Value & ";"
    Case "Private Lesson (Normal Court)"
    strSQL = "UPDATE TBLmember SET CreditValue = CreditValue-4 " & _
    "WHERE MemberID = " & Forms!TBLtennisbooking!MemberID.Value & _
    "UPDATE TBLtrainers SET MonthlyLessons = [MonthlyLessons]+1 " & _
    "WHERE TrainerID = " & Forms!TBLtennisbooking!TrainerID.Value & ";"
    Case "Private Lesson (Club Court)"
    strSQL = "UPDATE TBLmember SET CreditValue = CreditValue-5 " & _
    "WHERE MemberID = " & Forms!TBLtennisbooking!MemberID.Value & ";"
    Case Else
    MsgBox "Error"
    End Select

    'runs the select sql Statement
    DoCmd.RunSQL strSQL

    End Sub

    Would I have to define two strSQL? How would I do that.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Based on your bookingtypeid, you are updating tblmember.
    But, for whatever reason, if the bookingtypeid is "Private Lesson (Normal Court)", then you need to update tblmemmber AND tblTrainer.
    Is that correct? If so, then change your code to this:

    Code:
    Private Sub Command14_Click()
    'defined as string instead of variant
    Dim strSQL As String
    
    'use the select case statement instead of IF
    Select Case Me!BookingTypeID
    Case "Normal Court"
    strSQL = "UPDATE TBLmember SET CreditValue = CreditValue-1 " & _
    "WHERE MemberID = " & Forms!TBLtennisbooking!MemberID.Value & ";"
    Case "Club Court"
    strSQL = "UPDATE TBLmember SET CreditValue = CreditValue-2 " & _
    "WHERE MemberID = " & Forms!TBLtennisbooking!MemberID.Value & ";"
    Case "Private Lesson (Normal Court)"
    strSQL = "UPDATE TBLmember SET CreditValue = CreditValue-4 " & _
    "WHERE MemberID = " & Forms!TBLtennisbooking!MemberID.Value & _
    DoCmd.RunSQL strSQL
    strSql = "UPDATE TBLtrainers SET MonthlyLessons = [MonthlyLessons]+1 " & _
    "WHERE TrainerID = " & Forms!TBLtennisbooking!TrainerID.Value & ";"
    Case "Private Lesson (Club Court)"
    strSQL = "UPDATE TBLmember SET CreditValue = CreditValue-5 " & _
    "WHERE MemberID = " & Forms!TBLtennisbooking!MemberID.Value & ";"
    Case Else
    MsgBox "Error"
    End Select
    
    'runs the select sql Statement
    DoCmd.RunSQL strSQL
    
    End Sub
    Note where I inserted the extra RunSql statement.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Nov 2003
    Posts
    14
    Thanks, had to edit that a bit, but now works

Posting Permissions

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