Add non-existing records?

  • Thread starter Thread starter Alp Bekisoglu
  • Start date Start date
A

Alp Bekisoglu

Hi all,

Intention: is to get exactly 6 lines/rows of output. There may or may not be
any existing data as a row source.

So; if there is 1 row available, then 5 blank rows needs to be added. For 2
rows, 4 needs to be added, etc...

The result will be printed in a report. The present layout has rectangles to
provide for the "boxes" (or borders) around the data, not the field's own
border.

Graphically the goal is to have something like:
---------
| abcde |
---------
| fgh |
---------
| |
---------
| |
---------
| |
---------
| |
---------
if there are only two rows of data available.

I'm open to any suggestion be it via a query or coding. Earlier on in
..reports I was offered a solution but evethough the preview is correct (has
the 6 rows) the print only gives one row. So it did not work.

Any help, pointer, etc would be highly appreciated.

Thanks in advance,

Alp
 
Alp Bekisoglu said:
Hi all,

Intention: is to get exactly 6 lines/rows of output. There may or may not be
any existing data as a row source.

So; if there is 1 row available, then 5 blank rows needs to be added. For 2
rows, 4 needs to be added, etc...

The result will be printed in a report. The present layout has rectangles to
provide for the "boxes" (or borders) around the data, not the field's own
border.

Graphically the goal is to have something like:
---------
| abcde |
---------
| fgh |
---------
| |
---------
| |
---------
| |
---------
| |
---------
if there are only two rows of data available.

I'm open to any suggestion be it via a query or coding. Earlier on in
.reports I was offered a solution but evethough the preview is correct (has
the 6 rows) the print only gives one row. So it did not work.
Click to expand...
Hi Alp,

Without example tables/queries/data this may be hard
to explain, so will start with a simple example table.

tblTest ID f1
1 abc
2 def
3 ghi
4 jkl
5 mno
6 pqr
7 stu
8 vwx
9 yz


assuming the query you now use for the report source looks like:

qryTest

SELECT tblTest.ID, tblTest.f1
FROM tblTest
WHERE (((tblTest.f1)='abc' Or (tblTest.f1)='ghi'));

We will add a RowNum in a new query based on the above

qryTestCount

SELECT qryTest.f1,
(SELECT COUNT(*) FROM qryTest AS Q WHERE Q.f1<=qryTest.f1) AS RowNum
FROM qryTest
GROUP BY qryTest.f1;

f1 RowNum
abc 1
ghi 2


(You may have to adjust subquery to deal with ties,
but will let that go for now)

Next, we create a simple table

tblDummy RowCnt
1
2
3
4
5
6


We Left Join this dummy table to our query with RowNum
to get source for report.

SELECT tblDummy.RowCnt,
qryTestCount.f1
FROM tblDummy LEFT JOIN qryTestCount
ON tblDummy.RowCnt = qryTestCount.RowNum;


qryReport RowCnt f1
1 abc
2 ghi
3

4

5

6



Please respond back if something was not clear.

Good luck,

Gary Walter
 
Here is some code that will draw 24 equally sized boxes regardless of the
number of records in the report.
Private Sub Report_Page()
Dim intRows As Integer
Dim intLoop As Integer
Dim intTopMargin As Integer
intRows = 24
intDetailHeight = Me.Section(0).Height
intTopMargin = 360
For intLoop = 0 To intRows
Me.CurrentX = 20
Me.CurrentY = intLoop * intDetailHeight + intTopMargin
Me.Print intLoop + 1
Me.Line (0, intLoop * intDetailHeight + intTopMargin)- _
Step(Me.Width, intDetailHeight), , B
Next
End Sub
 
Hi Gary,

Thank you very much for the "crash-course" you took the time without having further details. I will try it. It needs some work-around but gives me the basic idea. I will get back to you if I get stuck but as it seems for now I do not see any blurr points.

Thanks again for your time and advice.

Alp

Alp Bekisoglu said:
Hi all,

Intention: is to get exactly 6 lines/rows of output. There may or may not be
any existing data as a row source.

So; if there is 1 row available, then 5 blank rows needs to be added. For 2
rows, 4 needs to be added, etc...

The result will be printed in a report. The present layout has rectangles to
provide for the "boxes" (or borders) around the data, not the field's own
border.

Graphically the goal is to have something like:
---------
| abcde |
---------
| fgh |
---------
| |
---------
| |
---------
| |
---------
| |
---------
if there are only two rows of data available.

I'm open to any suggestion be it via a query or coding. Earlier on in
.reports I was offered a solution but evethough the preview is correct (has
the 6 rows) the print only gives one row. So it did not work.
Click to expand...
Hi Alp,

Without example tables/queries/data this may be hard
to explain, so will start with a simple example table.

tblTest ID f1
1 abc
2 def
3 ghi
4 jkl
5 mno
6 pqr
7 stu
8 vwx
9 yz


assuming the query you now use for the report source looks like:

qryTest

SELECT tblTest.ID, tblTest.f1
FROM tblTest
WHERE (((tblTest.f1)='abc' Or (tblTest.f1)='ghi'));

We will add a RowNum in a new query based on the above

qryTestCount

SELECT qryTest.f1,
(SELECT COUNT(*) FROM qryTest AS Q WHERE Q.f1<=qryTest.f1) AS RowNum
FROM qryTest
GROUP BY qryTest.f1;

f1 RowNum
abc 1
ghi 2


(You may have to adjust subquery to deal with ties,
but will let that go for now)

Next, we create a simple table

tblDummy RowCnt
1
2
3
4
5
6


We Left Join this dummy table to our query with RowNum
to get source for report.

SELECT tblDummy.RowCnt,
qryTestCount.f1
FROM tblDummy LEFT JOIN qryTestCount
ON tblDummy.RowCnt = qryTestCount.RowNum;


qryReport RowCnt f1
1 abc
2 ghi
3

4

5

6



Please respond back if something was not clear.

Good luck,

Gary Walter
 
Hi Duane,

Thanks for the code which I have already started experimenting with. Now
I'll have to find out the relation of cm to twips (I think that's the
measurement used in the code) for alignment with my textboxes.

In the mean time I think I did resolve my problem with the code suggested by
Steve Arbaugh (in .reports) due to a recordset issue. Somehow I never had
any luck with queries via code! So I just made use of calling an actual
query to return the needed variable.

Thanks again for your time and advice.

Alp
 
Back
Top