getting results from 7 query's and populate text labels

[schalkse]

getting results from queries and populate text labels

Ok, i have tried and searched but could not find a good start to a solution for my problem.
I have a calendar form, which shows the dates and under it text labels.
Now whenever something is present in my table (tblKalender) on those dates, i want it to show in the text labels under the correct date.
Now, i have the results through a query, but i dont want to link the query to the form. Only thing that i have trouble with now is getting the information i need to show to those labels. I have written some code (early stages) which i want to show me the results for testing, later on i can fill the fields. I keep getting an error saying not enough parameters, expected is 1.

Can anybody take a look at the code or help me with this?
My query for day one is called qerKal1 and the fields i need to get back are the hour and the task. The query gets it's criteria from the label above that day (lbDag1), which shows the date.

I have this code in a module and when the form is opened it runs the fucntion.

Option Compare Database
Option Explicit

Const strSQLWhere1 As String = "" & _
"SELECT qerKal1.Uur, qerkal1.Taak, qerKal1.Datum " & _
"FROM qerKal1 " & _
"WHERE (((qerKal1.Datum) = [Forms]![frmKalender]![lbDag1].[Caption]));"

Public Function fFillDay1()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim strText As String
Dim strText2 As String

strSQL = strSQLWhere1
Set db = CurrentDb


Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
Do Until rs.EOF
If strText = "" Then
strText = rs!Uur
strText2 = rs!Taak
Else
strText = strText & ", " & rs!Uur
strText2 = strText & "," & rs!Taak
End If
rs.MoveNext
Loop

rs.Close
MsgBox strText & strText2
Set rs = Nothing
Set db = Nothing

End Function

 

[schalkse]

nobody

Nobody some insight in this? I only want the results from each query in text labels. So i need some insight in how you can get the results out of a query without setting it as a recordsource of a form.

 

[Bat17]

try this:-
Option Compare Database
Option Explicit

Public Function fFillDay1()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim strText As String
Dim strText2 As String

strSQL = "SELECT qerKal1.Uur, qerkal1.Taak, qerKal1.Datum " & _
"FROM qerKal1 " & _
"WHERE (((qerKal1.Datum) = '" & [Forms]![frmKalender]![lbDag1].[Caption] & "'));"
Set db = CurrentDb

Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
Do Until rs.EOF
If strText = "" Then
strText = rs!Uur
strText2 = rs!Taak
Else
strText = strText & ", " & rs!Uur
strText2 = strText & "," & rs!Taak
End If
rs.MoveNext
Loop

rs.Close
MsgBox strText & strText2
Set rs = Nothing
Set db = Nothing

End Function

it assumes that qerKal1.Datum is a text field, if not then knock out the single quotes

HTH

Peter

 

[schalkse]

Thanks Peter, But still the same error. Can't figure out what is wrong? if have almost the same function running for sending email and that does work perfect.

qerkal1.Datum is a field in a query where i want the date in "lbDag1" (text label)to be compared with.

Still saying, not enough parameters, expecting 1.

its giving me the hives

Or has it something to do with "Set db = CurrentDb" Since my form has nor recordsource?

 

[Bat17]

Its getting too near the end of shift but I hope I have this right.
The parameter is in the underlying query I think, Hopefully this should flush it out

Public Function fFillDay1()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As dao.QueryDef
Dim prm As dao.Parameter

Dim strSQL As String
Dim strText As String
Dim strText2 As String


strSQL = "SELECT qerKal1.Uur, qerkal1.Taak, qerKal1.Datum " & _
"FROM qerKal1 " & _
"WHERE (((qerKal1.Datum) = '" & [Forms]![frmKalender]![lbDag1].[Caption] & "'));"
Set db = CurrentDb

Set qdf = db.QueryDefs(strSQL)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenForwardOnly)

Do Until rs.EOF
If strText = "" Then
strText = rs!Uur
strText2 = rs!Taak
Else
strText = strText & ", " & rs!Uur
strText2 = strText & "," & rs!Taak
End If
rs.MoveNext
Loop

rs.Close
MsgBox strText & strText2
Set qdf = Nothing
Set rs = Nothing
Set db = Nothing
end function

edited some paste errors

 

[schalkse]

still an error Peter

Now it's complaining about the querydef.

oh, what can life be a bitch when something don't work