delete one record by from (1 Viewer)

1. i have 3 table.and every table have to relation.
2.i do QRY for 3 table link in from
3.i make from form for add, edit , and delete

my Qustion
1.when i delete in from but table not delete?
** i'm enforce and cascade delete related but can't delete it
in step 1 can't delete it i will do step 2
2.i make new button in code VBA

Private Sub Command123_Click()
DoCmd.RunSQL "Delete * from tbl_com"
Me.Requery
End Sub

it delete all record in table


i want delete 1 record in from and intable

help me pls..

To delete a specific record in a table using SQL you need to add a WHERE-clause to the statement to identify the record to be deleted.

DoCmd.RunSQL " Delete * From tbl_com Where SomeField = Somevalue"

so if you want to delete the current record on a form and the recordID is numeric then.

Docmd.RunSQL " Delete * From tbl_com Where Somefield = " & Me.X

where x is the name of the control on your form that contains the identifier of the record.

JR

It appears you have a non-updateable query for your form's recordsource.

Often, rather than a single query on the three tables, developers would base a form on one of the tables and then use subforms for the related records on the other tables. This structure allows records to be modified.

What are the relationships between the records in the three tables?

tbl_com have SN is PK in this table
tbl_iP is table SN FK
tbl_user SN FK
SN for relationships between the records in the three tables

MY button event = Command123

and

i'm test by k.JR

DoCmd.RunSQL " Delete * From tbl_com Where sn = " & Me.Command123

is error run-time error '438' :
object doesnt support this property or method

Re-read this:

Docmd.RunSQL " Delete * From tbl_com Where Somefield = " & Me.X

where x is the name of the control on your form that contains the identifier of the record

Click to expand...

You want to execute a SQL sting which should look like this:

Delete * From tbl_com where sn = 99

which would delete record(s) where sn = 99

JR

thank you so much MR.JR

i'm Re-rend and try to do and i can do it

>>> DoCmd.RunSQL "DELETE * FROM tbl_com WHERE (sn=me.text)"

when delete it msgbox show me "me.text" and input number for delete (it ok ) but >>

can you tell me again ** i want show msgbox (IN PUT YOUR NUMBER FOR DEL) Not "me.text"

Pls..

Ok one last time, any query is just a sting expression.

Docmd.RunSQL " Delete * From Table where RecId = 9"

now this is just a hardcoded SQL sting and will only be good for one run, so we try and make it more dynamically and we substitute the number 9 with a variable. Now the variable can come from a number of sources human input, grab it from somewhere like a form etc.

Let's grab it for my form, on the from I would have a control which has its controlsource set to my recordID, lets call it txtRecID. Now when a record has focus the recordnumber is exposed for me to use so I can pass it to my code, but since its a variable I can't include it in the SQL stringexpression, I have to put it outside the string so:

DoCmd.RunSQL " Delete * From Table Where RecID=" & Me.txtRecID

Now I assume that the recordID is a number and not TEXT for that uses a different syntax.

Now whenever I run the code it will put the correct recordID in my where-clause and delet that record, but since deletes are permanent with no rollback it is wise to promt the user for confirmation before I execute.

To get human input, would not be wise since they are prone to make mistakes!!.

Hope this clarify thing for you.

JR

thank you very much.
I will try to do that.

this is my from confirm for user.

Private Sub Command123_Click()
Dim Msg As String
Dim Style As String
Msg = "Are you sure you want to delete this record?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Response = MsgBox(Msg, Style, "Film Rental Database")
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tbl_com WHERE (sn=me.text)"
DoCmd.SetWarnings True
Else
MsgBox "You cancelled the delete operation"
End If
Me.Requery
End Sub


thank you everybody for help me and i can do it now..

HI everybody .
help me Please.
i try to delete one field but my botton it delete all.
Attachment can open for look.