Need solution for complex form

[Dave]

I'm designing a bill of lading form with over 50 fields in 5 tables
for which I need a printed "paper form" version of the data. A header
table will store 18 fields with the form number as the key field; all
other tables will be linked via form number. Here is my dilemna: one
section (TDG) must print 6 columns by 10 rows even if only 2 rows
contain data. Limited experience tells me that on the data entry side,
I need a datasheet so that each TDG record of 6 fields (plus form
number that is linked to main form) could be stored in one table,
otherwise 60 bound controls would be required, many of which would
often be empty. However, the TDG datasheet row limit must be 10, so I
suppose I'd have to loop through the record count when leaving a
record and limit input to 10 (not that I know how yet). I have no idea
how to output (print) 10 rows in the print version regardless of how
many records TDG records there are for that particular form number,
nor am I sure if the print version should be a form or report. Any
ideas out there?

 

[John W. Vinson]

I'm designing a bill of lading form with over 50 fields in 5 tables
for which I need a printed "paper form" version of the data. A header
table will store 18 fields with the form number as the key field; all
other tables will be linked via form number. Here is my dilemna: one
section (TDG) must print 6 columns by 10 rows even if only 2 rows
contain data. Limited experience tells me that on the data entry side,
I need a datasheet so that each TDG record of 6 fields (plus form
number that is linked to main form) could be stored in one table,
otherwise 60 bound controls would be required, many of which would
often be empty. However, the TDG datasheet row limit must be 10, so I
suppose I'd have to loop through the record count when leaving a
record and limit input to 10 (not that I know how yet). I have no idea
how to output (print) 10 rows in the print version regardless of how
many records TDG records there are for that particular form number,
nor am I sure if the print version should be a form or report. Any
ideas out there?

STOP.

Data *STORAGE* and data *PRESENTATION* are different tasks with different
requirements. Your "header" record should be related one-to-many to a child
table with your details data (TDG I'm guessing).

You don't need ANY looping or code to get the desired result in a Report. Use
the Sorting and Grouping dialog to group your report by the "form number"; put
the header data in the group Header. Make the Detail section of the report
just tall enough for ten rows, and set its Can Grow and Can Shrink properties
to No. Done!

 

[Dave]

STOP.

Data *STORAGE* and data *PRESENTATION* are different tasks with different
requirements. Your "header" record should be related one-to-many to a child
table with your details data (TDG I'm guessing).

You don't need ANY looping or code to get the desired result in a Report.Use
the Sorting and Grouping dialog to group your report by the "form number"; put
the header data in the group Header. Make the Detail section of the report
just tall enough for ten rows, and set its Can Grow and Can Shrink properties
to No. Done!
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Thanks, but I'm not sure you picked up that on the data entry side, I
want to limit TDG input to 10 records, and I want 10 rows and 6
columns to display (much like a Word table) in the printed TDG section
even if there is only one TDG record. If this sounds like a hack job
of good db principles, perhaps I should re-think this. Either that, or
I have not entirely understood your message.

 

[John W. Vinson]

Thanks, but I'm not sure you picked up that on the data entry side, I
want to limit TDG input to 10 records, and I want 10 rows and 6
columns to display (much like a Word table) in the printed TDG section
even if there is only one TDG record. If this sounds like a hack job
of good db principles, perhaps I should re-think this. Either that, or
I have not entirely understood your message.

You can store five rows, or eighty rows, or zero rows, or ten rows in the
table. With a proper report design it will print ten rows by six columns (on
eight pages, if you have eighty rows).

You can also use VBA code in the Form's BeforeInsert event to prevent addition
of an eleventh row, but that is a *completely separate problem* from the
formatting of the report. To do so use a form based on your header table,
with a subform based on the TDG table; you can use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*", "TDG", "[FormNumber] = " & Me![FormNumber]) >= 10 Then
MsgBox "Only ten records allowed!", vbOKOnly
Cancel = True
End If
End Sub

Letting the paper layout of a report dictate the structure of your tables is
pretty much a guarantee that you'll get a badly denormalized table structure!
Store the data based on its logical relationships, not on the basis of the
desired printout.