Deleting records using CASE

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a temporary table (tblQualifications) with
attributes [name][rdo1][rdo2][rdo3][rdo4][rdo5][rdo6]and
[rdo7]. The rdo fields correspond to the day of the week
and is true (-1) if it is the employees regular day off.
When I select a date the calendar on a form, if I select
a saturday I want all individuals who are off on
saturday, [rdo7]= -1 to be deleted. I tried the
following code but I get an error (compile error - Sub or
Function not defined) The error happens on the
first "Delete" command.

I am new to this and could really use some help.

Thanks a lot!!!

Private Sub TEST_Click()
Dim intRDO As Integer
intRDO = DatePart("w", [Forms]![frmDeptShiftSELECT]!
[CalValue])

Select Case intRDO

Case 1
Delete tblQualifications.[Empl Nbr]
FROM tblQualifications
WHERE (((tblQualifications.RDOsun) = -1))

Case 2
Delete tblQualifications.[Empl Nbr]
FROM tblQualifications
WHERE (((tblQualifications.RDOmon) = -1))

Case 3
Delete tblQualifications.[Empl Nbr]
FROM tblQualifications
WHERE (((tblQualifications.RDOtue) = -1))

Case 4
Delete tblQualifications.[Empl Nbr]
FROM tblQualifications
WHERE (((tblQualifications.RDOwed) = -1))

Case 5
Delete tblQualifications.[Empl Nbr]
FROM tblQualifications
WHERE (((tblQualifications.RDOthu) = -1))

Case 6
Delete tblQualifications.[Empl Nbr]
FROM tblQualifications
WHERE (((tblQualifications.RDOfri) = -1))

Case 7
Delete tblQualifications.[Empl Nbr]
FROM tblQualifications
WHERE (((tblQualifications.RDOsat) = -1))

End Select
End Sub
 
You don't want the VBA itself to attempt to execute the SQL Statements (as
it won't), but rather, you need to create a recordset of the table using the
OpenRecordset Method on your Database object., which then put your SQL
statement into a string variable, and use the string variable in your
There is also one other thing you can do to make your life a bit less
hecktic with regards to your day of the week code. Let's look at an
example:

Dim strSQL as String, dteRDO as Date, strWeekDay as String, drsEMP as
DAO.Recordset

dteRDO = [Forms]![frmDeptShiftSELECT]![CalValue]

strWeekDay = LCase(Format(dteRDO,"ddd"))

strSQL = "SELECT *" & _
"FROM tblQualifications" & _
"WHERE (tblQualifications.RDO" & strWeekDay & ") = -1;"

Set drsEMP =
<DaoDatabaseObject>.OpenRecordset(strSQL,dbOpenDynaset,dbSeeChanges,dbReadOn
ly)
 
Response below.
I have a temporary table (tblQualifications) with
attributes [name][rdo1][rdo2][rdo3][rdo4][rdo5][rdo6]and
[rdo7]. The rdo fields correspond to the day of the week
and is true (-1) if it is the employees regular day off.
When I select a date the calendar on a form, if I select
a saturday I want all individuals who are off on
saturday, [rdo7]= -1 to be deleted. I tried the
following code but I get an error (compile error - Sub or
Function not defined) The error happens on the
first "Delete" command.

I am new to this and could really use some help.

Thanks a lot!!!

Private Sub TEST_Click()
Dim intRDO As Integer
intRDO = DatePart("w", [Forms]![frmDeptShiftSELECT]!
[CalValue])

Select Case intRDO

Case 1
Delete tblQualifications.[Empl Nbr]
FROM tblQualifications
WHERE (((tblQualifications.RDOsun) = -1))
Click to expand...
<snip>

You need to save the sql statement to a string variable and then execute it:

'***
'Create a variable to store the sql statement
Dim strSQL As String
'***

Then, in your SELECT CASE structure, assign the sql statement to the variable:

'***
Select Case intRDO
Case 1
strSQL = "Delete tblQualifications.[Empl Nbr] " _
& "FROM tblQualifications " _
& "WHERE (((tblQualifications.RDOsun) = -1))"
[...]
[...]
Case Else
strSQL = ""
End Select

If strSQL <> "" Then
'Access 97 or later
CurrentDb.Execute strSQL, dbFailOnError
End If
'***
 
Back
Top