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

[kfanarmy]

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.

[Stewart Ross]

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:

Private Sub Delete_Current_Assignment_Record_Click()
Dim strSQL as String
strSQL = "Delete * from Registration where [Registration ID] = '" & Me![select task].Column(7) & "'" ' this one is the string version
strSQL = "Delete * from Registration where [Registration ID] = " & Me![select task].Column(7) ' this one is the numeric version
CurrentDb.Execute strSQL
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:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Welcome to Bytes!

-Stewart

[kfanarmy]

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

"too few parameters. expected 1." at the

CurrentDb.Execute strSQL statement.

[kfanarmy]

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...

Private Sub Delete_Current_Assignment_Record_Click()
Dim strSQL As String
strSQL = "Delete * from Registration where [Registration ID] = " & Me![select task].Column(7)
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

[kfanarmy]

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.

Private Sub Delete_Current_Assignment_Record_Click()
Dim strSQL As String
strSQL = "Delete * from Registration where [Registration ID] = " & Me![select task].Column(7)
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

[Stewart Ross]

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

[OldBirdman]

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