cycle through all the records in a table for a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that displays records of a list of prodcuts. I also have a
table of special codes that I need to link to a text box in this report such
that it will display the special codes in order and then recycle to the
beginning, continuing until all the records in the report have been
displayed. Example: Let's say the table has 3 values - a,b,c. Then this is
what I want to be able to do:

report text box displays
record from table
1 a
2 b
3 c
4 a
5 b
etc.


How to do?
 
Rather than conceptualize this as a cycle through/iterate through problem,
consider using the "set"-oriented feature that Access provides, i.e.,
queries.

If you create a new query in design view, add your two tables (the one with
records 1-...N, plus the table with "a", "b", and "c"), you can use a
Cartesian Product query to get all possible combinations. NOTE: the values
must be in successive ROWS, not in successive COLUMNS for this to work.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I'm not looking for all possible combinations. I just want the a,b,c pattern
to keep repeating for the duration of the report. It doesn't matter what the
a,b or c is paired up with.
 
I sorry, but I'm not visualizing what you're after yet...

I thought you mentioned pairing 1-a, 1-b, 1-c, 2-a, 2-b, ...

Is that something that you want displayed in the report?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I want what I mentioned initially in the chart. (1-a,2-b, 3-c,
4-a,5-b,6-c,7-a, etc.) Thanks for helping me with this.
 
Place a text box (txtHidden) in the report's detail section and set its
control source property to "=1", its running sum property to "over all", and
its visible property to "no". Then place another unbound text box
(txtSpecial) to display the special codes and enter the following code in the
report's detail on format event.

If Me.txtHidden Mod 3 = 0 Then
Me.txtSpecial = "c"
Else
If Me.txtHidden Mod 2 = 0 Then
Me.txtSpecial = "b"
Else
Me.txtSpecial = "a"
End If
End If
 
Correction on the code. It should be:
Select Case Me.txtHidden Mod 3
Case 1
Me.txtSpecial = "a"
Case 2
Me.txtSpecial = "b"
Case 0
Me.txtSpecial = "c"
End Select
 
I see what you are driving at by using the mod function. A bit more
complication. The special codes are specific to each of my clients, but all
come from the same master table. So say there are 25 codes. Client A might
use 5 of them, client B might use 8 of them. What I need to be able to do
is have the results of a query of the table, specific to each client, be the
source of the codes that are being cycled through. (I think your solution is
hardcoding in the a,b and c.)
 
I'm not clear on whether you have a table already that cross-references your
clients and codes...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
In the report's detail on format event, try opening a dao record set of the
client's codes from the master table and cycle through the record set. Keep
the txtHidden and txtSpecial text boxes. The detail on format event code
would be similiar to following:

Dim rstCode As DAO.Recordset
Dim tSpecial As String
Dim i As Long
Dim mSql
Set rstCode = CurrentDb.OpenRecordset("Select code from mastertable where
clientIDfield = clientID order by SortField")

If Me.txtHidden <= rstCode.RecordCount Then
i = CLng(Me.txtHidden) - 1
Else
If Me.txtHidden Mod rstCode.RecordCount = 0 Then
i = rstCode.RecordCount - 1
Else
i = CLng(Me.txtHidden) Mod rstCode.RecordCount - 1
End If
End If
With rstCode
..MoveFirst
..Move i
tSpecial = rstCode!code
End With
Me.txtSpecial = tSpecial
rstCode.Close
 
Back
Top