Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Code is deleting too many records!

    Hello I am trying to delete a row from a listbox using the following code -
    Code:
    strSQL = "DELETE tblLinkUserVisits.userID FROM [tblLinkUserVisits] WHERE [tblLinkUserVisits].[userID]=" & Me![List71].Column(1)
    
     DoCmd****nSQL strSQL
    The table is "tblLinkUserVisits", it only has two fields (userID & visitID), and I am trying to delete a row from the listbox (list71) with the selected userID eg visitID 160, UserID 22.

    It is working, BUT the code is actually deleting UserID 22 from all the other visitID's which have userID 22 in the same row of the table. UserID is column1.

    The table is an intersection table between tblUsers (userID) and tblVisits (visitID).

    How do I stop this?

    Many thanks
    Last edited by moss2076; 09-08-10 at 14:06.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Specify the visit ID

    if you want to delete something based on two criteria then the second criteria MUST also be part of the where clause

    incidentally you may also hit problems as text.string values should be encapsualted with a ' or "
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Could you possibly provide an example? Im not very good!

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    I tired -
    Code:
    strSQL = "DELETE tblLinkUserVisits.userID, visitID FROM [tblLinkUserVisits] WHERE [tblLinkUserVisits].[userid]=" & Me![List71].Column(1)
    But how do I specify visitID as a second criteria?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    delete from MyTable where aStringColumn = "blah" and aNumericColumn=0987089
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    I tried this but it doesnt work. Both fields are string -
    Code:
     strSQL = "DELETE tblLinkUserVisits.* FROM [tblLinkUserVisits] WHERE [tblLinkUserVisits].[userid]=" & Me![List71].Column(1) And [tblLinkUserVisits].[visitID] = " & Me![List71].Column(0)"
    I've never tried a delete query where there is multiple criteria. Both fields are integer.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're putting the string together funny. Put a breakpoint on that line and see what you're actually trying to send. You're missing an ampersand-doubleqoute somewhere in there.

    I suspect the details of "it doesn't work" probably were pointing you in that direction...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the & symbol is used for string concatenation in VB/VBA but it is not used in that context in SQL

    some people (me included) prefer to insert the text quote symbol using chr$(34). why? it makes the code easier to read and not prone to Access's annoying habit of adding extra symbols

    Code:
    strSQL = "delete from MyTable where "
    strSQL =strSQL & aStringColumn = " & chr$(34) & "blah" & chr$(34) & " and aNumericColumn=" mynumericvalue
    the point made by Teddy about settign a breakpoint and examining the SQL is a good one, its amazing how often the code you think you are sending to the SQL engine isn't. if you get errors in SQL examine them first. you can also ruefione the query design in the query designer (in SQL view) untill you have got the SQL correct

    to me a column name that includes 'ID' sthould be numeric, I'd use 'Type' for a string/text column, but thats just me
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2004
    Posts
    364
    Im sorry Im really no good at this, I can't work out how to do it

    I attempted this, but still no luck, Access cant find the field refered to in your expression..

    Code:
    strSQL = "delete * from tblLinkUserVisits where " & [tblLinkUserVisits].[userID] = " & chr$(34) & Me![List71].Column(1) & chr$(34) & " And [tblLinkUserVisits].[visitID] = " Me![List71].Column(0)"
    Last edited by moss2076; 09-09-10 at 13:28.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you put a watch on the code and examined the SQL?

    if so does what you have written look like SQL?

    if so have you tried to paste that code into the query browser and see if you can diagnosr what is wrong.

    if you are testing a delete statement then I'd suggest you temporarily replace the "delete" with "select *", so that you can refine the query without having to recreate the data each time.

    I'm expecting you to have something like

    Code:
    delete from tblLinkUserVisits
    where [tblLinkUserVisits].[userID] = " & chr$(34) & myvalueforUserID " & chr$(34) &
    " and  [tblLinkUserVisits].[visitID] = " & myValueForVisitID & ";"
    you will need to supply your own values or variables or controls in place of myValueFor......
    if VisitID is string/text then you will need to encapsulate the myValueForVisitID with chr$(34) as MyValueForUserID
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2004
    Posts
    364
    Well if I put a watch on it, the value states "expression not defined in conext", im not sure what it means.

    If I put this sql into the query browser -
    Code:
    DELETE *
    FROM tblLinkUserVisits
    WHERE tblLinkUserVisits.userID=" &  myvalueforUserID " & " and  [tblLinkUserVisits].[visitID] = " & myValueForVisitID & ";";
    I have to enter the visitID, but not the userID, yet they are both numerical values, and it says you are about to delete 0 rows..

  12. #12
    Join Date
    Aug 2004
    Posts
    364
    I've also tried this code, but Im getting a runtime error 3075 syntax error in string query expression -
    Code:
    strSQL = "DELETE tblLinkUserVisits.userID, tblLinkUserVisits.visitID FROM tblLinkUserVisits WHERE [tblLinkUserVisits].[userID] ='" & Me![List71].Column(1) & "' AND [tblLinkUserVisits].[visitID] = '" & Me![List71].Column(0) & ""
    The error message is picking up the correct userID and visitID however, but im getting the error.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you have some consistency issues going on here
    sometimes you say the columns are numeric, sometimes they are numeric

    when you debug the SQL, what are you actually seeing, what is the value of strSQL?

    by definition the SQL you have just posted won't work
    ...because you haven't replace myvalueforuserid with a user id
    nor have you replaced myvalueforvisitID with a visit ID

    ..those values are dependant on your application
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Aug 2004
    Posts
    364
    They are both numeric values, the userID I am testing is 5 (set to number in the table) and the visitID is 161 (set to number in the table).

    If I add a break point to the last code I posted and add the strSql to a watch, the value is true.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    according to post #6 'both fields are string'
    now you are claiming both are numeric.
    what is the correct version?

    so you can drop the chr(34)
    what does your VBA look like
    what does your actual SQl look like.. to examine the actual SQL you will need to break on the execution and display the SQL?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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