delete query

  • Thread starter Thread starter jebuss
  • Start date Start date
J

jebuss

I have a small app to print out multiple reports.
The user inputs the data on the form, which writes to the table.
Then the user can choose which reports are to be printed.
When the reports are printed, there is no reason to keep the data, it
is always a one-time thing.
So when the user clicks on the 'Finished" button, I would like to
write code behind the button to delete the record that is currently
stored in the table so that on re-open for the next batch of reports
the user is presented with a 'blank' form for inputting the new data.
Seems like it shouldn't be too hard, but I am unsure what to do.
Please help
TIA
Joanne
 
I have a small app to print out multiple reports.
The user inputs the data on the form, which writes to the table.
Then the user can choose which reports are to be printed.
When the reports are printed, there is no reason to keep the data, it
is always a one-time thing.
So when the user clicks on the 'Finished" button, I would like to
write code behind the button to delete the record that is currently
stored in the table so that on re-open for the next batch of reports
the user is presented with a 'blank' form for inputting the new data.
Seems like it shouldn't be too hard, but I am unsure what to do.
Please help
TIA
Joanne
Click to expand...

The code could be just something like:

Dim iAns As Integer
iAns = MsgBox("OK to empty table?", vbYesNo)
If iAns = vbYes Then
DoCmd.RunSQL "DELETE * FROM tablename;"
End If
 
John
I put the following code behind the button and am getting this error:
Run Time error 3131 Syntax error in FROM clause

Here is the way I put the code in - I am thinking I have something
setup wrong, like the quotes or something?

DoCmd.RunSQL "Delete * FROM Employee Contact Information;"
(with Employee Contact Information being the tablename)

Any clues what I have wrong here?
Thanks for your prompt reply to my first sos - much appreciated!!

Joanne
 
John
I put the following code behind the button and am getting this error:
Run Time error 3131 Syntax error in FROM clause

Here is the way I put the code in - I am thinking I have something
setup wrong, like the quotes or something?

DoCmd.RunSQL "Delete * FROM Employee Contact Information;"
(with Employee Contact Information being the tablename)

Any clues what I have wrong here?
Click to expand...

You're missing brackets around the name. Blanks are significant
delimiters - it is seeing "Delete * FROM Employee" and can't figure
out what the objects Contact and Information are!

Whenever a table or field name contains blanks (or other special
characters such as punctuation) you must enclose the name in [square
brackets]:

DoCmd.RunSQL "Delete * FROM [Employee Contact Information];"

or (perhaps better) avoid the use of blanks in object names
altogether; if you were to ever migrate to SQL Server you'ld need to
get rid of all the blanks anyhow, better to not have them in the first
place! Either use underscores (Employee_Contact_Information) or "camel
case" (EmployeeContactInformation).
 
Thanks again John, and your advice re no spaces is well taken
John said:
John
I put the following code behind the button and am getting this error:
Run Time error 3131 Syntax error in FROM clause

Here is the way I put the code in - I am thinking I have something
setup wrong, like the quotes or something?

DoCmd.RunSQL "Delete * FROM Employee Contact Information;"
(with Employee Contact Information being the tablename)

Any clues what I have wrong here?
Click to expand...

You're missing brackets around the name. Blanks are significant
delimiters - it is seeing "Delete * FROM Employee" and can't figure
out what the objects Contact and Information are!

Whenever a table or field name contains blanks (or other special
characters such as punctuation) you must enclose the name in [square
brackets]:

DoCmd.RunSQL "Delete * FROM [Employee Contact Information];"

or (perhaps better) avoid the use of blanks in object names
altogether; if you were to ever migrate to SQL Server you'ld need to
get rid of all the blanks anyhow, better to not have them in the first
place! Either use underscores (Employee_Contact_Information) or "camel
case" (EmployeeContactInformation).
Click to expand...
 
Back
Top