Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: deleting records

    Hi there,

    I have created a query which uses multiple tables as its source and uses criteria to select out specific records.

    I have created a form which views the records from the query.
    I have added a delete button so that I can delete the selected record(s).
    This form displays all the records as default view = Continuous Forms. As such the Delete button is repeated for each record.

    The deletion of the records wont work - AHHHH but after thinking about it I know the answer. It is because my original query is made up of mutltiple tables.

    For various reasons I need the multiple table query.

    The name of the table which I want to delete the record from is:
    StaffTraining
    The name of the field within this table which identifies the record is called ID and it is an Auto Number.

    The name of the form which views the query is aStaffTrainingDeleteForm.

    The name of the field on the form (aStaffTrainingDeleteForm) which has a control source of ID (from the query) is called IDSelectField.


    So I figure I need some code that looks at the IDSelectField in the aStaffTrainingDeleteForm then deletes the record in the StaffTraining Table which has the same ID

    Can anyone help me with this code?

    Thanks Karen

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How does DELETE SQL go again?

    DELETE FROM TableName WHERE IDField = Value;

    Or something like that.

    You could also delete the record by using DAO/ADO/ODA/DOA whatever the acronym is... recordsets in VBA.

    I think it's DOA - dead on arrival.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2002
    Posts
    157

    Still having problems with Delete Records

    Thanks for the advice but my lack of experience is showing through.

    I have written the following code based on how I am interpreting your advise.
    *************************
    Private Sub DeleteBtn108_Click()
    On Error GoTo Err_DeleteBtn108_Click

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    'DELETE FROM StaffTraining WHERE StaffTraining.ID = Me.IDSelectField.Value;

    Exit_DeleteBtn108_Click:
    Exit Sub

    Err_DeleteBtn108_Click:
    MsgBox Err.Description
    Resume Exit_DeleteBtn108_Click

    End Sub
    ***************************

    I am getting the following error:
    Compile Error: expected: end of statement


    StaffTraining is the Table.
    ID is the auto number created for the record used in the Table.

    The Delete Button (DeleteBtn108) is on the aStaffTrainingDeleteForm.

    Should I be using StaffTraining.ID.value = instead of StaffTraining.ID =

    I am assuming I can use Me. to look at the current form.
    But do I need to actually refer to the current form
    ie aStaffTrainingDeleteForm.IDSelectField.Value.



    Can you please advise what I am doing wrong and what the correct code should be?

    Regards
    Karen Day

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so theres two issues here
    one the SQL isn't being formed properly and its then not being exectured

    Code:
    strSQL ="DELETE FROM StaffTraining WHERE StaffTraining.ID = " & Me.IDSelectField.Value;
    docmd****nsql(strSQL)
    the reason for assigning the sql to a variable is so that you can examine the SQL that is being sent to the SQL engine to make certain that what is actually being sent is what you think it is. to do this I'd normally put a msgbox whilst debugging
    eg
    msgbox("My Sql is:" & vbcrlf & strSQL)

    the docmd****nsql then runs the value assigned to strSQL (effectively you "delete from a table where something"

    I think you will need to check the value in Me.IDSelectField.Value is correct, you will probably need to do some error trapping / validation to make certain its what you want

    note if Me.IDSelectField.Value was a string then you woudl need to encapusalte the value in quote marks


    eg
    Code:
    WHERE StaffTraining.stringID = " & chr(34) & Me.IDSelectField.Value & chr(34);
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Stupid forum filter even hacks the code

    Something needs to be done about this.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2002
    Posts
    157
    Ok everyone thanks for all the advice.
    Thanks also to Sinndho who made it all quite clear for me by providing this (which works perfectly):

    Private Sub Command108_Click()

    On Error GoTo Err_Command108_Click

    Dim strSQL As String



    strSQL = "DELETE * FROM StaffTraining WHERE StaffTraining.ID = " & Me.ID.Value

    CurrentDb.Execute strSQL, dbSeeChanges

    Me.Requery



    Exit_Command108_Click:

    Exit Sub



    Err_Command108_Click:

    MsgBox Err.Description

    Resume Exit_Command108_Click

    End Sub

    Thanks once again to you all for trying to help me

Posting Permissions

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