Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting rows (Access97)

    I have a problem that I'm not even sure how to approach.
    I have created a table "SON" that contains a number of fields, one of which is called "AREA CODE".
    I also have another table containing only 2 fields, "AREA CODE" and Description. "AREA CODE" for both tables can contain one of four possible values, 01,02,03 or 04. These corespond to four different descriptions, Dublin, Wicklow, Kildare and Wexford respectively, in the second table.

    On one of the forms in the Database There is a list of check boxes labelled Dublin, Wicklow, Kildare and Wexford. I want to add a command button that, when pressed, will delete any rows in the table "SON" where the "AREA CODE" for that row corresponds to a checkbox that is not ticked. For example if Dublin is not ticked, all the rows where the "AREA CODE" entry is '01' will be deleted. So I will end up with a table containing only the rows that contain the "AREA CODES" corresponding to the checkboxes that are ticked.

    I would be really grateful for any help anyone can give me with this.
    Thanks
    Triona

  2. #2
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting rows (Access97)

    Thanks for your help Hans. That code isn't working for me. There was no Next at the end of the for loop which meant that it wouldn't compile so I added one at the end of the function, maybe this is the problem? All thats happening is that its going through each checkbox and giving the MsgBox "no records have been deleted" 4 times, regardless of whether or not any of the checkboxes have been ticked.
    Thanks
    Triona

  3. #3
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting rows (Access97)

    I'm now getting a compile error that I wasn't getting before, "Syntax error in FROM clause". I've changed the name of the table that contains the rows to be deleted from SON to tbl" & Me.txtSalesOrder & ", where txtsalesOrder is a text box that contains the sales order number of a particular sale. I've put square brackets around it but I get the error with or without sqaure brackets. The line looks like this as I have it:

    strSQL = "DELETE * FROM [tbl" & Me.txtSalesOrder & "] WHERE" & strWhere

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

    Re: Deleting rows (Access97)

    Thanks for pointing out the missing Next. I have corrected my previous reply; the inserted instruction is bold. (You could have deduced its location yourself from the indentation of the code).

    I hope it'll work now; as you found out it's untested air code.

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

    Re: Deleting rows (Access97)

    <P ID="edit" class=small>(Edited by HansV on 11-Aug-03 13:37. Corrected errors.)</P>I have assumed that:
    - The command button is named 'cmdDelete'.
    - The check boxes are named 'chkArea01' to 'chkArea04'.
    - AREA CODE is a text field (if it is numeric, omit both occurrences of & Chr(34)

    Private Sub cmdDelete_Click()
    Dim strSQL As String
    Dim strWhere As String
    Dim i As Integer

    For i = 1 To 4
    If Not (Me.Controls("chkArea" & Format(i, "00")) = True) Then
    strWhere = strWhere & " Or [AREA CODE] = " & Chr(34) & Format(i, "00") & Chr(34)
    End If
    Next i

    If strWhere = "" Then
    MsgBox "No records will be deleted.", vbInformation
    Else
    ' Get rid of first " Or "
    strWhere = Mid(strWhere, 5)
    strSQL = "DELETE * FROM SON WHERE " & strWhere
    DoCmd.RunSQL strSQL
    End If
    End Sub

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

    Re: Deleting rows (Access97)

    You can try to find out what goes wrong yourself by single stepping through the code or by displaying the value of variables. For example, if you insert a statement

    MsgBox strSQL

    immediately below strSQL = ..., you can inspect the SQL statement that has been generated. You will see that there is no space between WHERE and what comes after. This can be remedied by inserting a space after WHERE, before the quotes, or by changing Mid(strWhere, 5) to Mid(strWhere, 4).

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Deleting rows (Access97)

    Post your loop here so we can have a look see what you are doing. So if a checkbox is not set you want to delete that area's records, is that right?
    What is in strWhere at the end of the loop?

  8. #8
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting rows (Access97)

    The loop is just the one Hans posted up. Its running without any compiling errors but no records are deleted regardless of whether any of the boxes are checked or not. Could the problem have to do with the fact that I don't have the checkboxes bound to any control source?

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

    Re: Deleting rows (Access97)

    What is the result of single-stepping and/or of MsgBox strSQL?

    Attached is a small demo (Access 97 zipped) that contains a working example of the code.
    Attached Files Attached Files

  10. #10
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting rows (Access97)

    When I single stepped through it, the code stops at MsgBox "no records have been deleted", so the StrSQL is never being run. And when I put in MsgBox StrSQL after StrSQl=........ no msgBox appears.

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

    Re: Deleting rows (Access97)

    As you can see from my demo, the code basically works, so there must be something wrong in your setup. Apparently, strWhere is always empty; you will have to find out why checking or unchecking the check boxes makes no difference - it should!

  12. #12
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting rows (Access97)

    I just ran the Code getting rid of this if else statement so that theres no if strWhere= "", and I got a syntax error in the WHERE clause. The result of the MsgBox strSQL this way was shown up as:

    DELETE * FROM [xTBLPROJ5213081] WHERE

    I don't know if this is of any help?

  13. #13
    Lounger
    Join Date
    Jul 2003
    Location
    Dublin, Ireland, Republic of
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting rows (Access97)

    the demo isn't working for me, I'm getting a can't find project or library error and the 'Private Sub cmdAct_Click()' is highlighted yellow, and Format is hightlighted Blue.

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

    Re: Deleting rows (Access97)

    No, this is not of any help. The If statement is necessary precisely to prevent this error. I repeat that you have to find out WHY strWhere remains empty regardless of the state of the check boxes.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting rows (Access97)

    You have a broken reference. Open any module to get into the Visual Basic Editor and then choose Tools-->References from the menu and see if any of the listed references show MISSING in front of the name. Try unchecking any of those that show MISSING and then recompile the code.
    Charlotte

Posting Permissions

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