Too Few Parameters Error Confusion

[srhollis]

I am trying to design a simple database that has one form (frmSessions) where a person will enter sessions for an upcoming conference we are planning. The purpose of the form is to prevent us from double-booking a room. The form has 7 fields that should get saved to a table called tblSessions. The user must enter the Session Title, Choose a date from a list box, choose a room from a list box, and then Choose a start time from a list box. I have created a "Before Update" event on the start time control that should check for an existing record in the tblSessions table. Basically if the query returns any record and error msg should appear saying CONFLICT. I keep getting an error of "Too few parameters - 3 expected" on the line "Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)"

Any suggestions on what I am doing wrong? Below is the complete code for the trigger...

Private Sub LstSessionStart_BeforeUpdate(Cancel As Integer)
 
Dim dbs As DAO.Database, rs As DAO.Recordset, strSQL As String
 
 
Set dbs = CurrentDb()
strSQL = "SELECT COUNT (sessionID) AS Conflicts FROM tblSessions WHERE (([tblSessions].SessionDate = Me.LstSessionDate)AND ([tblSessions].SessionRoom = Me.CboSessionRoom) AND (Me.LstSessionStart >= [tblSessions].SessionStart) AND (Me.LstSessionStart <= [tblSessions].SessionEnd))"
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
 
If rs.RecordCount <> 0 And Me.NewRecord Then
MsgBox "There is a conflict with this room on this date at this time."
Cancel = True
Me.Undo
End If
 
End Sub

[Seth Schrock]

The problem is that the SQL engine doesn't recognise the control names. You need to exclude them from being inside the quotes using concatenation so that the values that are in the controls get used. Based on the error message, there may be some misspelled field names as well, I don't know.

Try the following:

strSQL = "SELECT COUNT (sessionID) AS Conflicts " & _
         "FROM tblSessions WHERE (([tblSessions].SessionDate = #" & Me.LstSessionDate & _
         "#) AND ([tblSessions].SessionRoom = " & Me.CboSessionRoom & _
         ") AND (" & Me.LstSessionStart & ">= [tblSessions].SessionStart) AND (" & _
         Me.LstSessionStart & "<= [tblSessions].SessionEnd))"