I have a table within my DB that contains quality verification limits for specifi parts for a manufacturing facility. There are individual fields for upper and lower size limits for inspections A through Z plus a few other standard checks (~60 fields total). This fields are used to populate 3 reports, each similarly formatted, with the upper and lower limits shown for A-Z and printed by the user to be filled out during the manufacturing process (A-K on report1, L-Q on report2, R-S report3). The user prints these forms based on a series of selection criteria based on the part and the manufacturing operation in a separate Pop-Up form.
Now to the issue...
Not all fields are used for each part number, some may only need one inspection and I don't want to print all 3 reports if only the first is needed. What I'm thinking would be the best method is to simply loop through each field and count the number of Non-Null values, then a select case statement to evaluate the count and determine which reports need to be printed. But I'm not sure the code I would need to loop through each field within a specific range... can anyone help? I'm familiar with Loops just not with looking through a list of fields without writing If Then statements for each one (which i'd rather not do for 120 fields)
Im thinking something like this (not tested yet)
Am I on the right track here?
Additionally, and this is something that would be cool to do but not necessary, is to have each column on the reports automatically size based on the number of used fields. For example, if one part only requires inspections A-C, I don't need to take up the extra space on the printed sheet with columns D-K. So i would like to resize the columns to maximize their width on the page, increasing the area for the user to write in his measurements.
Any suggestions or links to other similar threads (search didn't return an adequate answer) would be greatly appreciated.
Thanks in advance!
Now to the issue...
Not all fields are used for each part number, some may only need one inspection and I don't want to print all 3 reports if only the first is needed. What I'm thinking would be the best method is to simply loop through each field and count the number of Non-Null values, then a select case statement to evaluate the count and determine which reports need to be printed. But I'm not sure the code I would need to loop through each field within a specific range... can anyone help? I'm familiar with Loops just not with looking through a list of fields without writing If Then statements for each one (which i'd rather not do for 120 fields)
Im thinking something like this (not tested yet)
Code:
Private Sub btnPrintJobPackageComplete_Click()
Dim rst As DAO.Recordset
Dim fld As Field
Dim InspCount as Int
Set rst = "SELECT ..." 'SQL for all ~120 relevant fields
While Not rst.EOF
For Each .....
If Not IsNull(rst(fld).Value Then
InspCount = InspCount + 1
End If
rst.MoveNext
Wend
Additionally, and this is something that would be cool to do but not necessary, is to have each column on the reports automatically size based on the number of used fields. For example, if one part only requires inspections A-C, I don't need to take up the extra space on the printed sheet with columns D-K. So i would like to resize the columns to maximize their width on the page, increasing the area for the user to write in his measurements.
Any suggestions or links to other similar threads (search didn't return an adequate answer) would be greatly appreciated.
Thanks in advance!