>-----Original Message-----

>>I have a report grouped by product and then subproduct.  
>>The fields of interest vary by subproduct.  I need to
>>prepare this report each month and the fields to be
>>displayed for each subproduct change from month to month.

>>In my code as shown below, I have a long Select...Case
>>statement and I do the following:
>>-Set all textboxes invisible in my private sub
>>-Make only relevant textboxes visible
>>-Set all labels invisible in another private sub
>>-Make only relevant labels visible

>>1.  The biggest problem with this is that everything is
>>repeated, first for textboxes and then for labels. Can I
>>combine these?  Since the labels and txtboxes are in 2
>>different sections (Groupheader versus Detail), the
labels
>>are not attached to the txtboxes.

>>2.  Also, would it be possible to create user-defined
>>functions called:

>>AddLblToRight(<name of label>) and
>>AddTxtToRight(<name of textbox)

>>that would place the lbl or txtbox to the right of the
>>one immediately to its left and make it visible (as
shown
>>below)?

>>Code fragment follows:
>>********************************************
>>Private Sub Detail_Format(Cancel As Integer, FormatCount
>>As Integer)
>>Select Case Me.Sub_Product_Code
>>Case "40200500004"
>> SetTxtFalse 'sets all txtboxes invisible
>> Me.txtRitsId.Left = Me.txtUSDAmt.Left +
Me.txtUSDAmt.Width
>> Me.txtRitsId.Visible = True
>> Me.txtName.Left = Me.txtRitsId.Left + Me.txtRitsId.Width
>> Me.txtName.Visible = True
>>....

>>Private Sub SubProductHeader_Format(Cancel As Integer,
>>FormatCount As Integer)
>> Select Case Me.Sub_Product_Code
>> Case "40200100029", "40200900005", "40201100022"
>>  SetLblFalse 'sets all labels invisible
>>  Me.lblDesc.Left = Me.lblUSDAmt.Left +
Me.lblUSDAmt.Width
>>  Me.lblDesc.Visible = True
>>....
>>*******************************************

>I think the first thing I would do with this kind of thing
>is to combine all that into the header's procedure.  You
can
>set these properties for the details in the header and
save
>some duplication of the logic.  Assuming the labels are
>positioned the same as the text boxes:

>Private Sub SubProductHeader_Format(Cancel As Integer, _

>Dim lngPosition As Long
>SetTxtFalse 'sets all txtboxes invisible
>SetLblFalse 'sets all labels invisible
>Select Case Me.Sub_Product_Code
>Case "40200500004"
>    lngPosition = Me.txtUSDAmt.Left +
Me.txtUSDAmt.Width
>    Me.txtRitsId.Left = lngPosition
>    Me.txtRitsId.Visible = True
>    Me.lblDesc.Left = lngPosition
>    Me.lblDesc.Visible = True
>    lngPosition = lngPosition + Me.txtRitsId.Left +
>Me.txtRitsId.Width
>    Me.txtName.Left = lngPosition
>    Me.txtName.Visible = True
>    Me.lblName.Left = lngPosition
>    Me.lblName.Visible = True
>    lngPosition = lngPosition + Me.txtName.Left +
>Me.txtName.Width

>That doesn't actually gain you much, but it does suggest
>that a loop may be possible.  The next thing I would
explore
>is to create a table to drive this procedure.  The table
>would contain fields for the product code, the order of
>appearance, the name of the field and possibly some other
>relevant info.  For example:

>Table:  DisplayFields
>Fields: ProductCode - Text??
>            ListOrder - Integer
>            FieldName - Text
>40200500004 1       RitsId
>40200500004 2       Name
>40200500004 3       fieldx
>    . . .
>40200900005 1       Desc
>40200900005 2       Name
>    . . .

>With that in place, you could then replace the Select Case
>logic with code that opens a recordset into the
>DisplayFields table to get the list of fields for the
>current product code.  Then you can loop through the
>recordset to deal with the specified fields.  Here's some
>top of the head air code:

>Private Sub SubProductHeader_Format(Cancel As Integer, _

>Dim db As Database
>Dim rs As Recordset
>Dim strSQL As String
>Dim lngPosition As Long

>Set db = CurrentDb()
>strSQL = "SELECT * FROM DisplayFields " _
>            & "WHERE ProductCode = """ _
>                            & Me.Sub_Product_Code
& """ " _
>            & "ORDER BY ListOrder "
>SetTxtFalse 'sets all txtboxes invisible
>SetLblFalse 'sets all labels invisible

>Set rs = db.OpenRecordset(strSQL, _
>                             dbOpenForwardOnly,
dbReadOnly)
>lngPosition = Me.txtUSDAmt.Left + Me.txtUSDAmt.Width
>Do Until rs.EOF
>    Me("lbl" & rs!FieldName).Left = lngPosition
>    Me("lbl" & rs!FieldName).Visible = True
>    With Me("txt" & rs!FieldName)
>            .Left = lngPosition
>            .Visible = True
>            lngPosition = lngPosition + .Left + .Width
>    End Width
>Loop

>rs.Close : Set rs = Nothing
>Set db = Nothing
>End Sub

>That's sure to be a little simple minded, but it should
>provide the general idea of what I'm talking about.

>Another reason I strongly prefer this kind of table driven
>approach is that it allows you to create a form to allow
you
>to add/edit/delete the items in the field list.  Then you
>would not have to make any changes to your code when the
>field list is changed.

>--
>Marsh
>MVP [MS Access]
>.

>Marsh:
>Thanks for this brilliant idea. I believe I'm very close
>to having this implemented.  The stumbling block is that I
>get a Runtime Error 13 "Datatype mismatch" on the line:

>Set rs = db.openRecordset (strSQL, dbOpenforwardonly,
>dbReadonly)

>The stumbling block is that I
>get a Runtime Error 13 "Datatype mismatch" on the line:

>Set rs = db.openRecordset (strSQL, dbOpenforwardonly,
>dbReadonly)

>-----Original Message-----

>>Marsh:
>>Thanks for this brilliant idea. I believe I'm very close
>>to having this implemented.  The stumbling block is that
I
>>get a Runtime Error 13 "Datatype mismatch" on the line:

>>Set rs = db.openRecordset (strSQL, dbOpenforwardonly,
>>dbReadonly)

>See my reply in the original thread.

>--
>Marsh
>MVP [MS Access]
>.

>Marsh:
>Per others' sugesstions, I added a reference to DAO,
>remove the reference to ADOX and disambiguated the code to
>say DAO.Recordset.

>Now I get a different error, namely Runtime error
>2100: "The control or subform control is too large for
>this selection."