Referencing form textbox in SQL ??

[Malcy]

Hi
I am trying to execute an SQL statement and I am getting an error that no value is given for one or more parameters but I cannot fathom it. When I run it as a query it works perfectly and I wondered if it was something to do with referencing the textboxes?

strSQL = "UPDATE [tblStaffDays] SET [tblStaffDays].[bytStatCode] = 2 " & _
"WHERE ((([tblStaffDays].[bytStatCode])<>7 And ([tblStaffDays].[bytStatCode])<>20 And ([tblStaffDays].[bytStatCode])<>10) AND (([tblStaffDays].[lngEmpNo])=[Forms]![frmHolidayEntry]![cboEmp]) AND (([tblStaffDays].[dtmDate]) Between [forms]![frmHolidayEntry]![txtHolStart] And [forms]![frmHolidayEntry]![txtHolEnd]));"

Data is entered on the form and then you hit the button which sets off this code. I have double checked the form name and the textbox names and they are all OK, so why is it giving me problems?
I could run it as a DoCmd.OpenQuery but would prefer to move it to a cnn.execute.
Any help very much appreciated.
Best wishes

Malcy

 

[Mile-O]

What exactly does the error say? Too many parameters: Expected n?

If so, this has been answered in a previous post already this morning.

 

[Malcy]

Thanks Mile
The error message is

No value is given for one or more required parameters

I have tried searching on various keywords but cannot find answers. I feel sure it is something to do with how the textbox on the form is called. Do I need to set some sort of Dim statement? The textboxes are unbound if that is any clue to someone.
Will keep searching
I thought for a horrible moment I might have closed the form before calling the SQL statement but double checking shows that not to be the case.
Best wishes

Malcolm

 

[Malcy]

Rethinking

Perhaps the problem is not with referencing the textboxes.
I decided to try putting variables in which catch the value entered in the text box and I still get the same error message so it is nothing to do with what I thought it was!
Checking the Locals window I am happy that the variables are picking up OK.
My (still not working) code is now
________________
Dim stDocName As String
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim dtmHolStart As Date
Dim dtmHolEnd As Date
Dim intEmp As Integer

Set cnn = CurrentProject.Connection

dtmHolStart = Me.txtHolStart
dtmHolEnd = Me.txtHolEnd
intEmp = Me.cboEmp

' Set [bytStatCode] to 2 for period unless shop is shut, the person does not work that day, or it is a scheduled day off
strSQL = "UPDATE [tblStaffDays] SET [tblStaffDays].[bytStatCode] = 2 " & _
"WHERE ((([tblStaffDays].[bytStatCode])<>7 And ([tblStaffDays].[bytStatCode])<>20 And ([tblStaffDays].[bytStatCode])<>10) AND (([tblStaffDays].[lngEmpNo])= intEmp) AND (([tblStaffDays].[dtmDate]) Between dtmHolStart And dtmHolEnd));"
cnn.Execute strSQL

MsgBox "The holidays have been set.", vbOKOnly, "System information"
DoCmd.Close

Set cnn = Nothing
_______________
As ever, any help greatly appreciated.
Best wishes

Malcy