Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete a row on opening another form (Access 2000)

    Delete a row on opening another form

    I need to delete a row in the form after i have transferred the data to another form. However i cannot delete the row and i need a help.
    I have 2 forms, frmClients and FCustomers.
    In the form frmClients i have a button that opens the form FCustomers for a new customer and transfers the data :

    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "FCustomers"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
    With Forms!FCustomers
    ![CompanyName] = Me![CompanyName]
    !address = Me!address
    !City = Me!City
    End With
    Me![CustomerID] = Forms![FCustomers]![CustomerID]
    RunCommand acCmdSaveRecord
    DoCmd.Close acForm, Me.Name


    This is OK, the client is converted into a customer,but i need now to delete the client in the form frmClients.
    adding the line RunCommand acCmdDeleteRecord does not delete the client.How can i do it ?

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

    Re: Delete a row on opening another form (Access 2000)

    See my reply to <post#=428110>post 428110</post#>.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a row on opening another form (Access 2000)

    I agree with Hans' original point about using a single table. Much better design. However, if there are conditions beyond your control that prevent you from fixing the database design, a poor second choice would be to run an append query to add a record into the other table, rather than trying to add the information directly to the form.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a row on opening another form (Access 2000)

    I appreciate your suggestion to use one table.You are right.As always. It is also easier to insert a Yes/No field
    and to delete the record with the help of this field.
    I do not want to use this suggestion since i want to avoid the junk in my table.I have some other
    considerations and if you want i could send them to you,but they re not tied up with the coding.
    I would like to ask you, is there any other possibility to delete the last record in the form without using the Yes/No field?.
    My question is,how can i delete the record in the form i am working,after i have opened another form?
    I cannot use the command
    RunCommand acCmdDeleteRecord
    since i have already opened the other form
    and i cannot delete the record before that, since i neeed it for the criteria in opening the form.
    I am afraid what i ask is not possible.In that case i will use the Yes/No field, but i want to be

    certain there is no other possibility.

    Kind regards

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

    Re: Delete a row on opening another form (Access 2000)

    If you really need to use two tables, I would follow jacksonmacd's recommendation. There is no need to open another form, you can add a record directly in the underlying table:
    <code>
    Dim strSQL As String
    strSQL = "INSERT INTO tblCustomers ( CompanyName, Address, City ) VALUES (" & _
    Chr(34) & Me!CompanyName & Chr(34) & ", " & _
    Chr(34) & Me!Address & Chr(34) & ", " & _
    Chr(34) & Me!City & Chr(34) &")"
    CurrentDb.Execute strSQL
    RunCommand acCmdDeleteRecord
    DoCmd.Close acForm, Me.Name
    </code>
    Replace tblCustomers with the name of the customers table.

    Note: the Chr(34)'s surround the text values with quotes.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a row on opening another form (Access 2000)

    Thank you so much for your precious advice.Indeed it will solve my prolem,that took me so mcuh time.
    Why do i get the message "Compile error" in my code?

    strSQL = "INSERT INTO tblCustomers ( CompanyName, Address, City ) "
    VALUES (" & _
    Chr(34) & Me!CompanyName & Chr(34) & ", " & _
    Chr(34) & Me!Address & Chr(34) & ", " & _
    Chr(34) & Me!City & Chr(34) &")"
    When i copied it,i got red colours.

    Best regards

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

    Re: Delete a row on opening another form (Access 2000)

    If you split an instruction into several lines (because it's too long to fit into one line cpmfortably), you must end each line of the instruction except the last one with a spcace followed by the continuation character _. If a string is split across several lines, each part must be enclosed in quotes, and the parts must be concatenated with &.

    You have introduced another line end in the code without doing the above.
    <code>
    strSQL = "INSERT INTO tblCustomers ( CompanyName, Address, City ) "<font color=red> & _</font color=red>
    <font color=red>"</font color=red>VALUES (" & _
    Chr(34) & Me!CompanyName & Chr(34) & ", " & _
    Chr(34) & Me!Address & Chr(34) & ", " & _
    Chr(34) & Me!City & Chr(34) &")"</code>

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a row on opening another form (Access 2000)

    Thank you so much! It is perfect and works great !!

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a row on opening another form (Access 2000)

    Dear Hans,

    All of a sudden i began receiving an error in my sql, the message is syntax error ,missing operator.I cannot explain what has hapened
    because it was ok today . I cannot find the error in my lines,could you have also a look in what i have written
    thank you in advance

    Dim strSQL As String
    strSQL = "INSERT INTO Customers ( CompanyName, Address, City ) " & _
    "VALUES (" & _
    Chr(34) & Me!CompanyName & Chr(34) & ", " & _
    Chr(34) & Me!address & Chr(34) & ", " & _
    Chr(34) & Me!city & Chr(34) & ")"

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

    Re: Delete a row on opening another form (Access 2000)

    Your code is correct. Try inserting a line

    Debug.Print strSQL

    below the code you posted. When you run the code, the SQL string will be displayed in the Immediate window (in the VB Editor). You can inspect it to see if there is anything wrong with it; if you can't find it, you can copy the result to the clipboard and paste it in a reply in this thread.

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete a row on opening another form (Access 2000)

    Dear Hans,

    I get the message Syntax error into insert clause and i cannot find the reason.I am sending you the db, for help

    Best regards

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

    Re: Delete a row on opening another form (Access 2000)

    Take a close look at the code in your database. You will see that it is not the same as the code you posted (or the code that I posted) - there is a small but crucial difference in strSQL.

    VBA and SQL require that you are very precise. Small errors that wouldn't matter much to us cause code not to run at all.

Posting Permissions

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