Report set-up

[Susanne]

I now have my table set-up in a more normalized format. I have all the
part-serial numbers in one table with one of the fields being the form field
name reference.

tblComponents
EquipNo, Date, PartType .... PartSerialNo Field

The field names are contained in another table that is updated with the
current equipment number and then this information is appended to the
tblComponentList table. It was a way I was able to get the field names to
easily get paired up with the part-serial number that needs to be selected,
since this number is also specific to the PartType. These "Field Names"
appear to the user to only be field names like all labels and are locked and
not enabled. They can just view them.

Now, the hard part for me comes down to creating the report. I need to have
each partserialno to go into the correct field for layout purposes, but
remember that these are in one table from a 1-many relationship. I do not
want to have it in a continuous type subform.

How do you specify that you want from Equipment 1 all components that were
on it on a particular date in a layout with fields going side to side, but
the occasional one that will only have one item on a line? This should work
even if I decide to change the layout of the fields in a random order on the
report.

I know there is coding, but I can't seem to set the control source of an
unbound box to a specific record. I've tried some SQL, but none seem to be
right and I get error messages.

 

[JString]

Hello Susanne,

I have to admit that it looks like you have a very ticky problem on your
hands, and that I'm not sure if I'm understanding exactly what it is you're
trying to do, but since you haven't had any help as of yet I'll give it a
shot.

First of all it sounds like you're hoping to use the 'field name' field in
order to arrange the data from some other field into an array of several
fields, placed end to end, on a report. For some reason this just doesn't
feel right, and I suspect that this strategy might become very difficult to
maintain. If it were me, I would try to build a solution that's more
self-reliant and didn't depend on configuration data stored in any table.

So for what it's worth, here's what I would do (assuming that I"m
understanding you correctly):

I would develop a method in vba which could build the necessary recordset
for a hidden form. That form would be launched in place of the report, and
it would in turn launch the report after building its own recordset. The
report would have its record source set to this hidden form so that it would
load automatically after it's opened.

The method in vba would do something along these lines:

1) The hidden form could be passed a string representing a filter for your
code using the openargs property.

2) A new empty recordset would be created and fields you want on the report
are declared and appended to the recordset's field collection.

Loop1) For each criteria value within the range passed through openargs
(this is what designates a single row in your report)

Loop1.1) Create a new recordset using an SQL statement containing the
criteria value.

Loop1.2) Add a new record in recordset 1 using the addnew method.

Loop2) For each record in recordset 2

Loop2.1) get the component name and assign this value to the field in
recordset 1 that corresponds to some counter value.

<Loop2 Repeats>

Loop1.3) Close Recordset 2 and reset your counter

<Loop1 Repeats>

3) Once this is all finished, assign recordset1 to the hidden form's
recordset property.

4) Open the report.

Of course, I'm sure that there are many variations that you could use.

 

[JString]

The

report would have its record source set to this hidden form so that it would
load automatically after it's opened.

Ignore this ignorant statement. At the moment my mind is scattered around
many different projects on different platforms. You would have to use an ADO
recordset to do this, and you could either assign that recordset to the
report in an ADP file, or you could save the data to a temporary table and
delete it after the report is finished with it. What can I say... reports
are retarded.