parameters not working with openrecordset

[kpal29]

This procedure should open a recordset and determine recordcount based on two criteria, year and entity. These values are in list boxes on a form. It works when I pass the values themselves, but is giving me "missing 1 parameters" error when I use the variable.

Dim strsql21 As String
Dim ct21 As Integer
Dim y As Single
Dim e As Variant
y = [Forms]![MainMenu]![Child2].[Form]![Year]
e = [Forms]![MainMenu]![Child2].[Form]![Entity]

Dim RST As DAO.Recordset

Dim strsql121b As String
strsql21b = "SELECT ACTBUD1.Entity, ACTBUD1.Year FROM ACTBUD1 WHERE (((ACTBUD1.Entity)= " & e & "));"

Set RST = db.OpenRecordset(strsql21b)
If RST.RecordCount = 0 Then
MsgBox "There are no records with Year = " & y & " and " & "Entity = " & e
GoTo res

Else

strsql21 = "DELETE ACTBUD1.*, ACTBUD1.Entity, ACTBUD1.Year FROM ACTBUD1 WHERE (((ACTBUD1.Entity)= " & _
e & ") AND ((ACTBUD1.Year)= " & y & "));"

db.Execute strsql21
ct21 = db.RecordsAffected

 

[afryer]

Hi,

This error generally occurs when there is a problem with your query. What I tend to do in these situations is write the SQL to a file (i.e strSQL21b) and then paste this into a query window in Access. When you run this it should show you exactly what is wrong.

Thanks


Andrew