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

    Update table (A2k)

    Sorry for 3 post's today

    I seem to have a missing operator in the following "strWhere" statement but am lost as to what it is:-

    Dim strWhere As String
    Dim strSQL As String
    Dim strCar As String

    strWhere = "EstimateNo=" & Me.Job & "Supp=" & Me.Supp
    strCar = Me.Combo25.Column(1)

    MsgBox strWhere 'Check Value is correct
    MsgBox strCar 'Check Value is correct

    strSQL = "UPDATE tblDetails SET Rcar = strCar WHERE " & strWhere
    'DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    'DoCmd.SetWarnings True
    '========

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

    Re: Update table (A2k)

    You forgot And - & is not the same as And:

    strWhere = "EstimateNo=" & Me.Job & " And Supp=" & Me.Supp

    and put in those Chr(34) 's!!!

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

    Re: Update table (A2k)

    "and put in those Chr(34) 's!!!"

    I know, I know

    strWhere = "EstimateNo=" & Chr(34) & Me.Job & Chr(34) & " And Supp=" & Chr(34) & Me.Supp & Chr(34)

    <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22> <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

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

    Re: Update table (A2k)

    This is getting my <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> now

    I'm being asked for a parameter of Rcar and then type mismatch afterwards.

    I've checked the table designs, the destination field looks ok.

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update table (A2k)

    Hang in there Dave... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    1. <LI>I don't know why it's asking you for a parameter on "Rcar" but adding the table name is worth a try... Are you sure that is the correct spelling of the field name?
      <LI>As for the datatype mismatch, you need to change your update statement... You need to concatenate the "strCar" variable into the statement, not include it as a literal... Even if it were a literal value (although highly unlikely), you'd have to enclose "strCar" in quotes...
      <LI>Also, I added a semi-colon at the end of the statement... I think that would have been your next problem...
    Try this...

    strSQL = "UPDATE tblDetails SET [tblDetails].[Rcar] = " & Chr(34) & strCar & Chr(34) & " WHERE " & strWhere & ";"

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

    Re: Update table (A2k)

    Trudi

    Thanks for you efforts, and part way there !!

    I only get the type mismatch now.
    I'll leave it for tonight and give it some more thought tomorrow.
    Thanks again

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update table (A2k)

    Wait!! (I'm determined now... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>)

    What kind of data is in RCar... and strCar (the combo box)?

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

    Re: Update table (A2k)

    Trudi

    Rcar is a text field within tblDetails holding vehicle registrations ie: DU51GHT
    The combo is Combo25 (forgot naming convention, later), Column(1) holds the registration.
    If I msgbox strCar for testing, I get the correct registration showing.

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update table (A2k)

    Okie dokie... I know you want to let it go for the night...

    Tomorrow, I'd try using Trim() on both RCar and strCar in the SQL statement, just in case there's a space in there or something... I've had that happen before....
    The next thing I'd check is the syntax of the whole statement... I'd Debug.Print strSQL and check CAREFULLY that something's not missing or inserted incorrectly... Sometimes it's so obvious when I see it in the Debug Window...

    Have a good night...

  10. #10
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update table (A2k)

    strWhere = "EstimateNo=" & Chr(34) & Me.Job & Chr(34) & " And Supp=" & Chr(34) & Me.Supp & Chr(34)

    if Job or Supp are numeric types you don't want the CHR(34)'s as this will cause your type mismatch problem.

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

    Re: Update table (A2k)

    Well something happened.
    I added the Chr(34) !s as follows into the "strWhere" statement and All records were changed which is not what I wanted.
    I only need to change one record matching EstimateNo & Supp so I see my "strWhere" is Incorrect.
    Basically, when the Courtesy car is Changed on the frmCourtesyCarPlanner, it updates tblDetails with the changed car.


    ' Try to save record
    Private Sub cmdOK_Click()
    On Error GoTo ErrLine
    If Me.Dirty Then
    Form_BeforeUpdate cancel

    Dim strWhere As String
    Dim strSQL As String
    Dim strCar As Variant
    strWhere = Chr(34) & "EstimateNo=" & Chr(34) & Chr(34) & Me.Job & Chr(34) & Chr(34) & " And Supp=" & Chr(34) & Chr(34) & Me.Supp & Chr(34)
    strCar = Me.Combo25.Column(1)
    MsgBox strCar
    strSQL = "UPDATE tblDetails SET Rcar = " & Chr(34) & strCar & Chr(34) & " WHERE " & strWhere & ";"
    'DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    'DoCmd.SetWarnings True
    End If
    If cancel = False Then
    DoCmd.Close acForm, Me.Name, acSaveNo
    End If
    On Error GoTo errtrap
    DoCmd.Close acForm, "frmDateEdit"
    errtrap:
    Exit Sub
    ErrLine:
    MsgBox Err.Description, vbExclamation
    End Sub
    Attached Files Attached Files

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

    Re: Update table (A2k)

    Dave,

    You must have thought "If one Chr(34) is good for my code, lots of Chr(34) must be even better" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    You've gone overboard in this statement:

    strWhere = Chr(34) & "EstimateNo=" & Chr(34) & Chr(34) & Me.Job & Chr(34) & Chr(34) & " And Supp=" & Chr(34) & Chr(34) & Me.Supp & Chr(34)

    The result of this when strSQL is assembled will look like

    UPDATE tblDetails SET Rcar = "DU51GHT" WHERE "EstimateNo=""MyJob"" And Supp=""MySupp";

    As you see, the entire Where-condition has become enclosed in quotes. Since this is not False, it always evaluates to True. Use this instead:

    strWhere = "EstimateNo=" & Chr(34) & Me.Job & Chr(34) & " And Supp=" & Chr(34) & Me.Supp & Chr(34)

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

    Re: Update table (A2k)

    Hans
    Your version was as I had already tried without admitting it via a post.
    I tried with enclosing all, as with my overboard version, the fact that it changed 39 records led me to think, "Well somethings happening", so I can't be far from solving the problem.

    I still get type mismatch even with your version !
    Obviously I'm missing something in the strWhere statement.

  14. #14
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update table (A2k)

    you had

    strWhere = Chr(34) & "EstimateNo=" & Chr(34) & Chr(34) & Me.Job & Chr(34) & Chr(34) & " And Supp=" & Chr(34) & Chr(34) & Me.Supp & Chr(34)

    Assuming EstimateNo and Supp are text strings it should be

    strWhere = "EstimateNo=" & Chr(34) & Me.Job & Chr(34) & " And Supp=" & Chr(34) & Me.Supp & Chr(34)

  15. #15
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update table (A2k)

    what are the datatypes of EstimateNo and Supp??

Page 1 of 2 12 LastLast

Posting Permissions

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