Hello,

the following code is related to a query Q_Zaalboeking.


Dim strLijstVandaag As String
strLijstVandaag = "select * from Q_Zaalboeking " _
& "where Startdatum = #" & Me!Startdatum2 _
& "# and lokaalID = " & zaalID

The SQL of this query is like this:

SELECT LesLokalenPlanning.Startdatum, LesLokalenPlanning.BoekingID, LesLokalenPlanning.LokaalID, LesLokalenPlanning.Starttijd, LesLokalenPlanning.Eindtijd
FROM LesLokalenPlanning;


Is it possible to combine the two in vba so that I can remove the query?

Thanks in advance,
Sven.

Is this what you are talking about?
Dim strLijstVandaag As String

strLijstVandaag = _
"SELECT LesLokalenPlanning.Startdatum, LesLokalenPlanning.BoekingID, " & _
"LesLokalenPlanning.LokaalID, LesLokalenPlanning.Starttijd, " & _
"LesLokalenPlanning.Eindtijd " & _
"FROM LesLokalenPlanning " & _
"WHERE Startdatum = #" & Me!Startdatum2 & "# " & _
"AND lokaalID = " & zaalID & ";"

Hello,

unfortunately there is another sql with the same query so I tried myself to change it into VBA

this is the old code refering to the query Q_Zaalboeking:
Set rs = CurrentDb.OpenRecordset("select * from Q_Zaalboeking where year(Startdatum) = " & Me!txtJaar & " and month(Startdatum) = " & m & " and lokaalID = " & Me.txtlokaalid)

This is the query:
Q_zaalboeking = SELECT LesLokalenPlanning.Startdatum, LesLokalenPlanning.BoekingID, LesLokalenPlanning.LokaalID, LesLokalenPlanning.Starttijd, LesLokalenPlanning.Eindtijd
FROM LesLokalenPlanning

This is my solution but I don't know that this is the right way to do it:

Dim strsql as string

strSQL = "SELECT LesLokalenPlanning.Startdatum, LesLokalenPlanning.BoekingID, " & _
"LesLokalenPlanning.LokaalID, LesLokalenPlanning.Starttijd, " & _
"LesLokalenPlanning.Eindtijd " & _
"FROM LesLokalenPlanning " & _
"wHERE year(Startdatum) = " & Me!txtJaar & " and month(Startdatum) = " & m & " and lokaalID = " & Me.txtlokaalid) "

Set rs = CurrentDb.OpenRecordset (strsql)

Is this correct ?

Sven.

Why do you want to eliminate the queries? They take up very little room in your db.

I have heard it is more efficient to have a query stored in the Db than to create one on the fly in VBA. Seemingly thats because Access can optimise it once and then run it lots of times.

If you have a lot of query's you don't know anymore where all the query's are used. When I can do it in vba for me it is much more tracable and afterwards easier to manage.

Maintainability is an important issue as long as you do not have any performance problems. It looks to me like the last SQL string you made should work just fine.

Hello again,

i tried to change it in the code but it isn't working anymore ...

So this is the old code:

Function fBoekingen()

Dim i As Integer
Dim m As Integer
Dim dt As Date
Dim rs As Recordset

For m = 1 To 12
Set rs = CurrentDb.OpenRecordset("select * from Q_Zaalboeking where year(Startdatum) = " & Me!txtJaar & " and month(Startdatum) = " & m & " and lokaalID = " & Me.txtlokaalid)
If rs.RecordCount <> 0 Then
While Not rs.EOF
i = Day(rs!Startdatum)
Me("s" & m)("t" & i).BackColor = vbRed
rs.MoveNext
Wend
End If
Next m
rs.Close
Set rs = Nothing

End Function


This is my new code:

Function fBoekingen()

Dim i As Integer
Dim m As Integer
Dim dt As Date
Dim rs As Recordset

Dim strSQL As String

strSQL = " SELECT LesLokalenPlanning.Startdatum, LesLokalenPlanning.BoekingID, " & _
" LesLokalenPlanning.LokaalID, LesLokalenPlanning.Starttijd, " & _
" LesLokalenPlanning.Eindtijd " & _
" FROM LesLokalenPlanning " & _
" WHERE year(Startdatum) = " & Me!txtJaar & " and month(Startdatum) = " & m & " and lokaalID = " & Me.txtlokaalid & ";"

For m = 1 To 12
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount <> 0 Then
While Not rs.EOF
i = Day(rs!Startdatum)
Me("s" & m)("t" & i).BackColor = vbRed
rs.MoveNext
Wend
End If
Next m
rs.Close
Set rs = Nothing

End Function


There is no error, but there is also no result.

What did I do wrong?

Put a MsgBox "[" & strSQL & "]" after you build the string to see what you have. Have you single stepped the code to see what is happening?

hello,

the month is always equal to 0 and this is what I don't understand because in the code there is a loop for m from 1 to 12

You will need to rebuild the string each time after the For m = 1 To 12.