Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27

    Unanswered: delete a record in access97

    Hi All

    my access97 database uses a main form with a sub form.

    when i delete a record, only the main form part is deleted and the sub form part is still there?

    I am using one of the access default buttons to delete, I havent written my own code to do this.

    can you help?

    ta
    the one and only ada17

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Are the records made up from multiple tables so that it is only being deleted in one place? if thats not the case try to put in
    Docmd.requery "subform_name" after the delete step in the code. That should refresh the subform and the data should then be gone
    Jim

  3. #3
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    Sorry, I wasn't very clear.

    What I meant was that the records are from two tables, the table that the main form is made of, and that which the sub form is made of. When I delete the record in the form view, it deletes both the main part and the sub form part.

    But when i look at the tables, in the main table the customer details have been deleted as required. But in the sub form's base table, the information is there for that record no, without the record no existing in the main table.

    Note, that the tables do not share a relationship. This is because it gives me problems like, you can't to this form, a related record is required in the sub table, etc.

    hopy u can help

    ada
    the one and only ada17

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    if you have a forigen key (something on both records that are the same like a customer number or part number) then you can write a second delete query

    delete * from subform_table where customer_id = forms![main_form]![customer_id]

    add that before the first delete step in the code to the button and that should do it
    Jim

  5. #5
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    Sorry, i don't understand,

    Docmd.requery "subform_name" after the delete step in the code.
    and
    delete * from subform_table where customer_id = forms![main_form]![customer_id]

    I am not using a button which I have written the code for, i am using access' standard one.

    If I was to make a button and write the code for, I do not know which of the above two codes to use.
    I was thinking something like

    delete_click()
    delete current record in mainForm
    delete current record in subForm
    end sub

    so that the same record will be deleted in both the main and the sub form.

    hope u can help


    Originally posted by JDionne
    if you have a forigen key (something on both records that are the same like a customer number or part number) then you can write a second delete query

    delete * from subform_table where customer_id = forms![main_form]![customer_id]

    add that before the first delete step in the code to the button and that should do it
    Jim
    the one and only ada17

  6. #6
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    sorry forgot to mention the field they have in common

    it is the customer number
    the one and only ada17

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    i have neaver used delete current record, so i dont know if that works
    a simple way to do it can be this:

    delete_button_onClick
    docmd.runsql "delete * from [subform_table] where customer_id = [forms]![main_form]![customer_ID_text_box]
    docmd.runsql "delete * from [mainform_table] where customer_id = [forms]![main_form]![customer_ID_text_box]

    me.refresh

    end sub

    im fairly sure that will do what you need

    Jim

  8. #8
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    Hi, big problem

    I amended the code you gave me to this

    Private Sub Delete_Click()
    *
    DoCmd.RunSQL "delete * from [tblCustomerQuestionnaire] where CIS_Number = [forms]![tblCustomerDetails]![CIS_Number]"
    DoCmd.RunSQL "delete * from [tblCustomerDetails] where CIS_Number = [forms]![tblCustomerDetails]![CIS_Number]"

    Me.Refresh

    End Sub
    *

    but this has deleted every single record in the main form and every single record in the sub form

    help!!
    the one and only ada17

  9. #9
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    All the data is gone? or did the form not refresh correctly and the data that didnt apply to the customer id is still in the tables
    i wasnt quite sure about that me.refresh but that sql code is farly strait forward, unless you ahve records that share the same id.
    let me know


    Originally posted by ada17
    Hi, big problem

    I amended the code you gave me to this

    Private Sub Delete_Click()
    *
    DoCmd.RunSQL "delete * from [tblCustomerQuestionnaire] where CIS_Number = [forms]![tblCustomerDetails]![CIS_Number]"
    DoCmd.RunSQL "delete * from [tblCustomerDetails] where CIS_Number = [forms]![tblCustomerDetails]![CIS_Number]"

    Me.Refresh

    End Sub
    *

    but this has deleted every single record in the main form and every single record in the sub form

    help!!

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    easier method, that works in a2000 and i think in a97 is to create a releation ship from the db window and on the relationship turn on cascade update and delete that will automaticaly wipe all records links to the deleted record
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    Hi I made a mistake when I amended the code, where u suggested the form name, I put the table name, thats why it deleted all.

    I have now corrected that problem, but as always, there is something else.

    It shows the message as if it is about to delete a record, but instead if

    'you are about to delete 1 row(s) from the specified table', it says
    'you are about to delete 0 row(s) from the specified table', and says this twice. I press yes twice, but it just stays at the same form, and deletes nothing.

    ada
    the one and only ada17

  12. #12
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    DoCmd.SetWarnings False

    put that at the begining of the sub procedure

    Originally posted by ada17
    Hi I made a mistake when I amended the code, where u suggested the form name, I put the table name, thats why it deleted all.

    I have now corrected that problem, but as always, there is something else.

    It shows the message as if it is about to delete a record, but instead if

    'you are about to delete 1 row(s) from the specified table', it says
    'you are about to delete 0 row(s) from the specified table', and says this twice. I press yes twice, but it just stays at the same form, and deletes nothing.

    ada

  13. #13
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    This stopped the warning, but it still doesnt delete it!

    ada

    Originally posted by JDionne
    DoCmd.SetWarnings False

    put that at the begining of the sub procedure
    the one and only ada17

  14. #14
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    grrrr. no other errors?? im at a loss. the previous post about the relationships is the only other thing that I can think of but ill keep thinking
    Jim

  15. #15
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    When i did this earlier, it didnt help. in fact, everytime i try to add a new record, it says 'You cant add or change a record because a related record is required in table tblCustomerDetails' which is the main form

    i dont understand this as the first thing i change is in the main form.

    i am lost too, but i have to get this sorted (users will include visually impaired people) and it has been doing my head in.

    any help is appreciated.

    Originally posted by m.timoney
    easier method, that works in a2000 and i think in a97 is to create a releation ship from the db window and on the relationship turn on cascade update and delete that will automaticaly wipe all records links to the deleted record
    the one and only ada17

Posting Permissions

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