Set rs as recordset does not work 1

[apestaart]

In the code underneith I use "set rs as recordset"
I set its value via a SQL query. I do this two times.
The first one is working ok. But the second one gives me a runtime error 3061: To few parameters. Expected 1 item"
I have tested the query first with the form open and that works perfect, but as soon as I use the SQL in the VB code I get that error. What do I wrong or it there an other way.
Thanks in advance,
Apestaart

Private Sub Form_Current()
Dim Rcount As Integer, nMaanden As Integer, TotZiek As Integer, TotContract As Integer
Dim Frekwentie As Integer, rs As Recordset, strSQL As String, TotContJaar As Integer
Dim Van As Date, Tot As Date, Did As Date, Dud As Date, BeginDat As Date, EindDat As Date, rst As Recordset
'>>>>>>>>>>The first one works correct<<<<<<<<<
strSQL = "SELECT Verzuim.VerzuimID, Werknemers.StationID, Verzuim.RelID, [Datum herstelmelding]-[Datum ziekmelding] AS [Aantal dagen], Verzuim.[Aantal uren ziek], Verzuim.Reden, Verzuim.Oorzaak, Werknemers.[Kontrakt soort], TabStations.Station, TabRegios.Regio, [Keuze ziekmelding].Omschrijving, Werknemers.[Datum in dienst], Werknemers.[Datum uit dienst] FROM ((TabRegios INNER JOIN TabStations ON TabRegios.RegioID = TabStations.RegioID) INNER JOIN Werknemers ON TabStations.StationID = Werknemers.StationID) INNER JOIN ([Keuze ziekmelding] INNER JOIN Verzuim ON [Keuze ziekmelding].Ziekmeldingscode = Verzuim.Reden) ON Werknemers.RelID = Verzuim.RelID;"
 
Set rs = CurrentDb.OpenRecordset(strSQL)
Jaar = Now() - DatePart("y", Now()) + 1 ' set begin van het jaar 1-1-2006

TotContract = 0
TotContJaar = 0
TotZiek = 0
Rcount = 0
rs.MoveFirst
Do While Not rs.EOF
If Me.StationID = rs!StationID Then
TotZiek = TotZiek + rs![Aantal uren ziek]
Rcount = Rcount + 1
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'>>>>>>>>>>>>Here is the second one that gives an error<<<<<<<<<<<<<
Me.StationID.SetFocus
strSQL = "SELECT Werknemers.StationID, Werknemers.[Kontrakt soort], Werknemers.[Datum in dienst], Werknemers.[Datum uit dienst]FROM Werknemers WHERE (((Werknemers.StationID)=[forms]![Statistiek per station]![StationID]));"

Set rs = CurrentDb.OpenRecordset(strSQL) '>>Error 3061<<
rs.MoveFirst
Do While Not rs.EOF
Did = Nz(rs![Datum in dienst], Jaar)
Dud = Nz(rs![Datum uit dienst], Now())
MsgBox "Contract=" & rs![Kontrakt soort]
BeginDat = (-Jaar * (Did <= Jaar) - Did * (Did > Jaar))
EindDat = -Now() * (Dud = Now()) - Dud * (Dud < Now())
TotContract = TotContract + ((((EindDat - BeginDat) / 7) * rs![Kontrakt soort]))
TotContJaar = TotContJaar + (((54 - (Format(BeginDat, "ww"))) * rs![Kontrakt soort]))
Debug.Print BeginDat, EindDat, TotContract, TotContJaar
rs.MoveNext
Loop

 

[AmigoMobility]

If StationID is numberic try:

WHERE Werknemers.StationID)=" & [forms]![Statistiek per station]![StationID]

If StationID is text try this:

WHERE Werknemers.StationID)='" & [forms]![Statistiek per station]![StationID] & "'"

HTH,
Shane

 

[TheAceMan1]

How are ya apestaart . . .

Add the line in [purple]purple[/purple]:

[blue][purple][b]Set rs = Nothing[/b][/purple]
Me.StationID.SetFocus
strSQL = "SELECT Werknemers.StationID, Werknemers.[Kontrakt soort], Werknemers.[Datum in dienst], Werknemers.[Datum uit dienst]FROM Werknemers WHERE (((Werknemers.StationID)=[forms]![Statistiek per station]![StationID]));"

Set rs = CurrentDb.OpenRecordset(strSQL) '>>Error 3061<<
rs.MoveFirst[/blue]

See Ya! . . . . . .

 

[apestaart]

Thanks amigo mobility the code works perfect. Do you know a place on the internet to find the syntax of the changes you recommanded me?
Best regards apestaart