Delete me

[echo0001]

Hello

I need the code for taking the value entred into a text box and then deleting its record from a table. this will happen on the OnClick event of a command button.

The text box is named "Text40"
The field the value will be added to is named "Allocated To"
The Table the field is located in is named "Allocated"

Thank you for your time.

 

[boblarson]

Let me give you a piece of advice. Instead of posting a question asking for the solution to every issue you run into, how about trying to do some research and working on it yourself? I'm not trying to be mean, but how are you going to learn if you don't try it first. I mean, this one is so simple that I'm surprised that the thought never occurred to you to go into the QBE grid, create a delete query that does exactly as you want and then go to the query's SQL view to see how it is structured.

Then there is Google. I think entering something like
Access Delete Query

might generate enough responses to figure it out.

 

[echo0001]

ok i thank you for your comments and will endevor to try harder, this is the code i had got so far. from past codes google and SQL sever 2005 step by step by microsoft press, and i still cant get it to work.

Private Sub Command42_Click()
If Not IsNull(Me![Text40]) Then
Dim stSQL
stSQL = "DELETE FROM [Allocated] ([Allocated To])" & _
" Values ('" & Me![Text40] & "')"
DoCmd.RunSQL stSQL
End If
DoCmd.Close acForm, "View/Update Test Equipment", acSavePrompt
DoCmd.OpenForm "View/Update Test Equipment", acNormal
End Sub

i do know that me being a starter with VBA is a pain and i will try harder next time.

 

[boblarson]

Again, try going into the query by example grid of ACCESS (not SQL Server) and create a delete query there. Then go to VIEW > SQL VIEW to see how it makes a delete query.

 

[namliam]

Private Sub Command42_Click()
If Not IsNull(Me![Text40]) Then
Dim stSQL
stSQL = "DELETE FROM [Allocated] ([Allocated To])" & _
" Values ('" & Me![Text40] & "')"
DoCmd.RunSQL stSQL
End If
DoCmd.Close acForm, "View/Update Test Equipment", acSavePrompt
DoCmd.OpenForm "View/Update Test Equipment", acNormal
End Sub

i do know that me being a starter with VBA is a pain and i will try harder next time.

Beyond what bob has said... I would like to put this to you...
Dim stSQL
As what? You are leaving out the most important part of the declaration, what type it should be.
Dim stSQL as String in this case... that AS part is very important!

! vs . it is generaly accepted to use . where possible, this allows for "early binding" and makes your code less prown to errors. So instead of Me![Text40] use Me.Text40

Text40
Dont leave controls in their original default name, this is going to get hard to maintain FAST. If you would call this control txtToBeDeleted (or something) you
1) Instantly know what it is supposed to do
2) Can much more easily find the control on the form (even if it is big) because its Label will have a simular text to it.

[Allocated To]
Generaly try not to use spaces or special characters in any names anywhere. This will (eventually) give you problems. Keep it clean.

View/Update Test Equipment
Again the special characters and forms and queries and tables should have a naming convention in place. Tables start with tbl, queries qry, forms frm, etc.
This way it is much easier to refer back to something.

Hint on your problem:
Values is part of the "Insert into" statement, NOT delete.

Question
Why close and open the form?

Remark
An alternative to "DoCmd.RunSQL" is "CurrentDB.Execute" this will execute your query without those "annoying" popups saying "you are about to delete are you sure?"

 

[echo0001]

Thank You for your help i have created a delete query with the where clase directed to the text box and used VBScript to run the query and the reload the form, thank you for helping me with my general Db house keeping.

 

[georgedwilkinson]

VBScript? Really?