Populating report from code

  • Thread starter Thread starter Aino
  • Start date Start date
A

Aino

Hi

I am really not sure to get this done.

Based on input from a form, I process a query, ending up assigning my
desired results to variables inside a loop.
Now I would like to display the results in a report.

At first I thought I could just add textboxes as desired to the report
inside the loop before opening it: Nope.
Then I thougth of using listboxes and adding the results as new lines
to those from the loop: No can do.
Instead I thougth I would create a new recordset, fill it with my
results, and then use this as the recordsouce: Can't be done.

Could someone please point me in the rigth direction?


Here is the code:
===============================
Private Sub btnOK_Click()

Dim dbs As DAO.Database
Dim rstAvailTimes As DAO.Recordset
Dim rstCars As DAO.Recordset
Dim rstAvailCar As DAO.Recordset
Dim strCriteria As String
Dim strSQL As String
Dim strCar As String
Dim strDay As String
Dim datFrom As Date
Dim datTo As Date
Dim datMaxForCar As Date
Dim datMinForCar As Date
Dim datTimeMax As Date
Dim datTimeMin As Date
Dim strTimeMax As String
Dim strTimeMin As String
Dim strAdrMax As String
Dim strAdrMin As String

On Error GoTo Err_btnOK_Click

strDay = cmbDay
datFrom = txtFrom
datTo = txtTo

Set dbs = CurrentDb
strCriteria = "[Day] = '" & strDay & "' AND NOT [Car] IN " _
& "(SELECT [Car] FROM [qryTimes] WHERE [Day] = '" & strDay _
& "' AND [TimeMax] > #" & datFrom & "# AND [TimeMin] < #" &
datTo & "#)"
strSQL = "SELECT * FROM [qryTimes] WHERE " & strCriteria
Set rstAvailTimes = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rstAvailTimes.EOF Then
MsgBox "There are no available cars between kl. " & datFrom &
" og kl. " & datTo & " on a " & strDay
GoTo Exit_btnOK_Click
End If

strSQL = "SELECT DISTINCT [Car] FROM [qryTimes] WHERE " &
strCriteria
Set rstCars = dbs.OpenRecordset(strSQL)
rstCars.MoveFirst
Do While Not rstCars.EOF
datTimeMax = #12:00:00 AM#
datTimeMin = #11:59:00 PM#
strTimeMax = ""
strTimeMin = ""
strAdrMax = ""
strAdrMin = ""
strCar = rstCars![Car]
rstAvailTimes.Filter = "[Car]= " & strCar
Set rstAvailCar = rstAvailTimes.OpenRecordset

With rstAvailCar
.MoveFirst
Do While Not .EOF
datMaxForCar = rstAvailCar![TimeMax]
datMinForCar = rstAvailCar![TimeMin]
If datMaxForCar <= datFrom Then
If datMaxForCar > datTimeMax Then
datTimeMax = datMaxForCar
strAdrMax = rstAvailCar![AdrMax]
End If
ElseIf datMinForCar >= datTo Then
If datMinForCar < datTimeMin Then
datTimeMin = datMinForCar
strAdrMin = rstAvailCar![AdrMin]
End If
End If
.MoveNext
Loop
End With 'rstAvailCar

If datTimeMax <> #12:00:00 AM# Then strTimeMax = datTimeMax
If datTimeMin <> #11:59:00 PM# Then strTimeMin = datTimeMin

' Debug.Print strCar & vbTab & strTimeMax & vbtab &
strAdrMax & vbTab & strTimeMin & vbTab & strAdrMin
rstCars.MoveNext
Loop


GoTo Exit_btnOK_Click

Err_btnOK_Click:
MsgBox Err.Description

Exit_btnOK_Click:
If Not rstAvailCar Is Nothing Then
rstAvailCar.Close
Set rstAvailCar = Nothing
End If
If Not rstAvailTimes Is Nothing Then
rstAvailTimes.Close
Set rstAvailTimes = Nothing
End If
If Not rstCars Is Nothing Then
rstCars.Close
Set rstCars = Nothing
End If
If Not dbs Is Nothing Then
dbs.Close
Set dbs = Nothing
End If

End Sub
===================

Best regards
Aino
 
Hi Marsh

The problem is, that in my report I want to combine a subset of the
original query by combining data from different records in each report
line.
The result of the original query is a list of cars, where and when
each of their routes start, where and when there route stops and day
of the week. None of the fields are unique:
Day Car TimeMin AdrMin TimeMax AdrMax

After the user has entered a day and a timeinterval, for which he
needs a car, the report should give a list of available cars, when and
where they stop their previous route (Max, if any on that day) and
when and where they should start their next route (Min, if any on that
day). This mean, that the max-data comes from one record, min-data
from another. There can be other data (routes) for the same car and
day, which should be ignored, since they are not relevant for the
given time interval.

I have no idea how to construct an sql or a where-clause, that can
select these data. But since I needed a query, 3 recordsets and some
code to achieve my results, I guess it just shows I am not that
familiar with SQL.

I hope you (or someone else) will take the time to guide me.

Best regards
Aino
 
I am grateful that you are taking the time to try to solve this.
I think a query along these lines will return the cars that
are available on the specified day and time. (The subquery
finds cars that are busy and the Left Join and Is Null
criteria excludes them.)
...
Click to expand...

That was the same I was going for with my strCriteria in the code:
strCriteria = "[Day] = '" & strDay & "' AND NOT [Car] IN " _
& "(SELECT [Car] FROM [qryTimes] WHERE [Day] = '" & strDay _
& "' AND [TimeMax] > #" & datFrom & "# AND [TimeMin] < #" &
datTo & "#)"
I am not sure though, why you have the last "And X.TimeMax < Forms!
theform.txtStart".
Then, I think another query can then find the data for the
previous and next route:

SELECT Day, Car, TimeMax, AdrMax, TimeMin, AdrMin
FROM GetCars As G
WHERE (TimeMax = (SELECT Max(X.TimeMax)
FROM GetCars As X
WHERE X.Car = G.Cars)
Or TimeMax Is Null)
And (TimeMin = (SELECT Min(X.TimeMin)
FROM GetCars As X
WHERE X.Car = G.Cars)
Or TimeMin Is Null)
Click to expand...

I am sorry to say, it doesn't work. I do not get all the cars
available. TimeMax and TimeMin are never null in GetCars. And the
times (and adresses) I want returned, are not neccessarily max and min
for the given car on the given day. If for instance the car drives 5
routes that day, and is available in the requested time interval
between route 2 and 3, I would like returned TimeMax and AdrMax for
route 2 (so I know when and where it is available from) and TimeMin
and AdrMin for route 3 (so I know when it is no longer available, and
where it needs to be at that time).
...so I will leave it to you to test
this idea and decide if you want to pursue it or go back to
using code to calculate the data (which may be even more
complicated).
Click to expand...

Actually the code in my original post gave the correct results, only
since they came out as variables, I could only present them in the
immediate window or a message box.

I am beginning to think that if I can get the results into a none-
editable form by creating an adhoc recordset, maybe I should use this
instead of a report. After all, I can give the form a white background
and set all fields to locked, maybe I can make the user think it is a
report?
Or maybe someone else will get interested in the problem and
jump in with a cleaner/efficient idea.
Click to expand...

Any help is more than welcome!

Best regards
Aino
 
If you have the desired recordset, then assigning it to a
form's Recordset property might be all you need.
Click to expand...

By now I have given up on this as well.
Since it is a fabricated recordset, it is not possible to use it for a
form.

I have tried setting certain attributes as recommended by Lyle
Fairfield in <http://groups.google.fr/group/comp.databases.ms-access/
msg/ae637cf71a8d8360>.

I have tried going though a stream as described by Florian Esser in
<http://groups.google.fr/group/microsoft.public.access.formscoding/msg/
d59c62f523b1fbbc>

I have tried a sub converting the fabricated recordset to something
looking like a bound recordset, code delivered by Steve Jorgensen in
<http://groups.google.com/group/comp.databases.ms-access/msg/
de67ac684186964e>

All of it to no avail. I am using MS Access 2000. I believed the
difficult part would be to get the right data, I would never have
thought that getting the data presented in something else than a
message box would be the killing part!

It seems I have to construct an SQL to solve this. I just feel very
disillusioned rigth now.

Best regards
Aino
 
Back
Top