[AccessD] Is there a way to dynamically hide fields if needed

David McAfee davidmcafee at gmail.com
Fri Oct 1 15:11:19 CDT 2010


The Sample that you sent me works great!

Thanks.

I'd still like to get it working on a continuous form (or datasheet),
but as for now the Listbox will make it simple enough to read.

I think using the recordset .Fields.Count like you did will help me out.

Thanks again,
David

On Fri, Oct 1, 2010 at 12:43 PM, Kenneth Saylor <saylork at ipfw.edu> wrote:
> Not sure if this is what you're after.
>
> I made a table from your second to last data. Then I created two queries.
> One is of the table data, the other computes the column values.
>
> The first query feeds a listbox , the second is used in a function to find
> column sums equaling zero.
>
> Here is the second query:
> SELECT Sum(QTest.DlrPay) AS SDlrPay, Sum(QTest.SvcMgr) AS SSvcMgr,
> Sum(QTest.PartsDept) AS SPartsDept, Sum(QTest.SvcAdv) AS SSvcAdv,
> Sum(QTest.Contest) AS SContest, Sum(QTest.SvcDept) AS SSvcDept,
> Sum(QTest.SvcTech) AS SSvcTech
> FROM QTest;
> This is the function:
>
> 'Use query to find zero columns. Starting at the last query field,
> 'replace the current width with a computed width; a default value for
> non-zero
> 'or zero otherwise. The new box width is also calculated and returned
> Public Function SetColWidths(ByVal ctl As Control) As String
>   Dim rs As DAO.Recordset
>   Dim CurrWidths As Variant
>   Dim CurrFld As Single
>   Dim Cntr As Long
>   Dim Pntr As Long
>   Dim ListWidth As Long
>
>   Const DEFAULT_WIDTH As String = "576"
>
>   CurrWidths = Split(ctl.ColumnWidths, ";")
>   Pntr = UBound(CurrWidths)
>   Set rs = CurrentDb.OpenRecordset("qryColumnWidth")
>   With rs
>     For Cntr = .Fields.Count - 1 To 0 Step -1
>       CurrFld = CSng(.Fields(Cntr))
>       If CurrFld = 0 Then
>         CurrWidths(Pntr) = "0"
>       Else
>         CurrWidths(Pntr) = DEFAULT_WIDTH
>       End If
>       ListWidth = ListWidth + CLng(CurrWidths(Pntr))
>       Pntr = Pntr - 1
>     Next
>
>     For Cntr = Pntr To 0 Step -1
>       ListWidth = ListWidth + CurrWidths(Pntr)
>     Next
>
>     SetColWidths = Join(CurrWidths, ";") & "|" & CStr(ListWidth)
>   End With
> End Function
>
> The code behind the control is:
> Private Sub Form_Current()
>   Dim WidthRet As String
>   Dim Pntr As Long
>
>   With Me
>     WidthRet = SetColWidths(.lstTest)
>     Pntr = InStr(WidthRet, "|")
>     .lstTest.ColumnWidths = Left$(WidthRet, Pntr - 1)
>     .lstTest.Width = CLng(Mid$(WidthRet, Pntr + 1))
>   End With
> End Sub
>
> This function automatically adjusts to any number of fields.
>
> HTH (and is what you need). : )
>
> P.S. I'm sending a sample DB directly to you.
>
> Kenneth Saylor
> Chemistry Maintenance
> Indiana-Purdue University Fort Wayne
>>>> David McAfee <davidmcafee at GMAIL.COM> 10/1/2010 1:22 PM >>>
> (Crossposted on AccessD)
>
>
> I'm working in an ADP.
>
> I have the pseudo pivot table since I am working in SQL2000 (PIVOT
> wasn't available until SQL2005)
>
> SELECT
> SplitID,  IncDetID, SplitTypeID, SplitAmt
> FROM tblSplit
> WHERE IncDetID = 5199
>
> returns
>
> SplitID  IncDetID SplitTypeID SplitAmt
> 36          5199        1             15.00
> 37          5199        7               5.00
>
>
> My pseudo pivot table:
> SELECT IncDetID,
>                SUM(CASE SplitTypeID WHEN 1 THEN SplitAmt ELSE 0 END) AS
> DlrPay,
>                SUM(CASE SplitTypeID WHEN 2 THEN SplitAmt ELSE 0 END) AS
> SvcMgr,
>                SUM(CASE SplitTypeID WHEN 3 THEN SplitAmt ELSE 0 END)
> AS PartsDept,
>                SUM(CASE SplitTypeID WHEN 4 THEN SplitAmt ELSE 0 END) AS
> SvcAdv,
>                SUM(CASE SplitTypeID WHEN 5 THEN SplitAmt ELSE 0 END) AS
> SvcDept,
>                SUM(CASE SplitTypeID WHEN 6 THEN SplitAmt ELSE 0 END) AS
> SvcTech,
>                SUM(CASE SplitTypeID WHEN 7 THEN SplitAmt ELSE 0 END) AS
> Contest
>        FROM tblSPlit
>        GROUP BY IncDetID
>
> returns:
>
> IncDetID  DlrPay   SvcMgr PartsDept SvcAdv  SvcDept SvcTech Contest
> 5199        15.00         0.00       0.00        0.00       0.00  0.00
> 5.00
>
>
> In the actual final display, there are many tables joined to this, so
> the resultset really looks like:
>
> CustNo   InvNo  ItemNo Qty UnitPrice IncentiveAmt TotalIncentive
> DlrPay SvcMgr PartsDept SvcAdv SvcDept SvcTech Contest
> 07235     5452   02951    6   54.95 20.00 120.00 15.00 0.00 0.00 0.00
> 0.00 0.00  5.00
> 07235     5452   03111  12   40.95 17.00 204.00 15.00 0.00 0.00 0.00
> 0.00 0.00  2.00
> 07235     5452   01121  24   30.95 20.00 480.00 15.00 0.00 0.00 0.00
> 0.00 0.00  5.00
> 07235     5452   01161  12   36.95 25.00 300.00 15.00 0.00 0.00 0.00
> 0.00 0.00 10.00
> 07235     5452   06011  12   47.95 22.00 264.00 15.00 0.00 0.00 0.00
> 0.00 0.00   7.00
> 07235     5452   10521  12   41.95 19.00 228.00 15.00 0.00 0.00 0.00
> 0.00 0.00   4.00
>
> I'd really like it to look like this:
> CustNo InvNo ItemNo Qty UnitPrice  IncentiveAmt TotalIncentive DlrPay
> Contest
> 07235   5452  02951  6     54.95   20.00  120.00 15.00 5.00
> 07235   5452  03111  12   40.95   17.00  204.00 15.00 2.00
> 07235   5452  01121  24   30.95   20.00  480.00 15.00 5.00
> 07235   5452  01161  12   36.95   25.00  300.00 15.00 10.00
> 07235   5452  06011  12   47.95   22.00  264.00 15.00 7.00
> 07235   5452  10521  12   41.95   19.00  228.00 15.00 4.00
>
> I'm still debating on displaying this as a listbox or subform on the main
> form.
>
> I'm thinking if it is in a listbox, simply loop through the last 7
> colums. If the entire column is 0 then make that column a 0" width in
> the list box.
> If I go with a datasheet subform, run a recordset clone and if all
> values for a given field are 0 then make that field hidden.
>
> any ideas?
>
> I have to do this on a form and on a report (print out of the form) so
> I'm trying to think of a good way to do this that will apply to both.
>
> Thanks,
> David
>
>
>




More information about the AccessD mailing list