By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

Deleting a Record after selecting info with a combo box, access 2003

P: 4
Hello;

I am attempting to use a command button "Delete Current Assignment Record" on a form to delete a user-selected record from a table entitled "Registration" with Key field "Registration ID".

I am using a combo box named "Select Task" that displays info from three tables to enable the user to select a record. The form's data source is the table Registration. column 7 within the combo box = the Registration ID.

the on click event for the command button is:

Private Sub Delete_Current_Assignment_Record_Click()
Delete [Registration].* from Registration where [Registration].[Registration ID] = [select task].Column(7)
End Sub

I have also tried using the acCmdDelete run command, but am unsure how to make sure the table Registration is set prior to execution.

MS ACCESS 2003;
OS Windows 2000 svc pack 4


suggestions on how to make this work properly would be appreciated.
Jan 16 '09 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
Hi. To execute an SQL statement from VBA code you need to use either the DoCmd.RunSQL method, or the Execute method of the current database object. Trying to run the SQL the way you have done at present is certain to lead to the VBA compiler informing you there is a syntax error.

The normal way to approach this task is to build a SQL statement as a text string, then use RunSQL or Execute to execute the statement. Building it as a string is not difficult, but there are slight differences in approach if the WHERE clause is referring to a numeric value or a string, as string literals have to be enclosed in single quotes. Both versions are shown below, but make sure you use the correct one and delete the other in your final version:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Delete_Current_Assignment_Record_Click()
  2. Dim strSQL as String
  3. strSQL = "Delete * from Registration where [Registration ID] = '" & Me![select task].Column(7) & "'" ' this one is the string version
  4. strSQL = "Delete * from Registration where [Registration ID] = " & Me![select task].Column(7) ' this one is the numeric version
  5. CurrentDb.Execute strSQL
  6. End Sub
The Execute method does not generate any user warnings; RunSQL does. To use RunSQL instead of Execute the warnings have to be turned off then on again after execution as shown below:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. DoCmd.RunSQL strSQL
  3. DoCmd.SetWarnings True
Welcome to Bytes!

-Stewart
Jan 16 '09 #2

P: 4
Thank You...I am trying the CurrentDb.Execute version...but get an error

"too few parameters. expected 1." at the

CurrentDb.Execute strSQL statement.
Jan 22 '09 #3

P: 4
This caused a prompt for me to enter a registration ID, and upon entering deleted all the information in the table, vice the record that had the same registration ID...it does reflect the right registration ID in debug, before running the do command, but prompts for entry of the Registration ID and then deletes all info in the table...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Delete_Current_Assignment_Record_Click()
  2. Dim strSQL As String
  3. strSQL = "Delete * from Registration where [Registration ID] = " & Me![select task].Column(7)
  4. DoCmd.SetWarnings False
  5. DoCmd.RunSQL strSQL
  6. DoCmd.SetWarnings True
Jan 22 '09 #4

P: 4
I'm guessing that for some reason, when the "delete from" runs that the table Registration referenced in the code is not being addressed (current focus?) in any case, getting a prompt to key in the Registration ID when I run it."

any further suggestions are appreciated.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Delete_Current_Assignment_Record_Click()
  2. Dim strSQL As String
  3. strSQL = "Delete * from Registration where [Registration ID] = " & Me![select task].Column(7)
  4. DoCmd.SetWarnings False
  5. DoCmd.RunSQL strSQL
  6. DoCmd.SetWarnings True
Jan 23 '09 #5

Expert Mod 2.5K+
P: 2,545
Hi again. When you use a RunSQL statement it 'does what it says on the tin'; there is no possibility of your SQL string not addressing table Registration - the SQL statement explicitly names that table and no other.

The fact that you are being asked for a value for Registration ID suggests that the underlying field in that table which you are wishing to match is not actually called 'Registration ID' but something else (e.g. RegistrationID). Check the actual name of the field in table Registration, because it must be spelled exactly as you see it in table design view. Otherwise, the SQL will be referring to a non-existent field and the delete will not function at all (since no existing row will match the SQL Where clause if it is referring to a non-existent field).

-Stewart
Jan 24 '09 #6

100+
P: 675
At this point, I would use the tools Access supplies to check the SQL statement.

1. Stop the code at line 5, the DoCmd.RunSQL strSQL
2. In immediate window, display the contents of strSQL with ?strSQL - then copy entire string to clipboard
3. From Database window, select "Create query in Design view"
4. Close the ShowTable window without selecting anything
5. Click "SQL" on left end of query design toolbar (if not SQL, click downarrow next to designview icon
6. Clear "SELECT;"
7. Paste from clipboard your SQL statement.
8. Change DELETE to SELECT and click tableview icon on toolbar
9. Use designview to modify query until you get correct single record to display when you run query.
10. Switch to SQLview, and use this to compare with your string, still in immediate window. Replace "SELECT" with "DELETE"

OldBirdman
Jan 25 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.