David McAfee
davidmcafee at gmail.com
Thu Oct 7 15:03:39 CDT 2010
A.D., thanks for the code.
I had to modify to work with my ADP.
Right now I have an issue where I have to hit the OK button twice to
make the filter work.
If I put an option stop in the code and step through the function, it
will work on the first click.
This tells me it is a refresh/repaint issue.
I've tried putting DoEvents all over the function as well as before
and after the call, and it still doesn't do it on the first click:
Does anyone have an idea why I would have to press the cmdbutton twice
to get it to work?
This is what I changed your function to:
'***************************
Public Sub P_HideEmptyColumnsNumeric()
On Error Resume Next
Dim ct As Access.Control, fd As DAO.Field
Dim CtSource As String
Dim subtotal As Double
DoEvents
With Me.RecordsetClone
For Each ct In Me.Detail.Controls
DoEvents
Err.Clear
CtSource = ct.ControlSource
If ct.Tag = "Hideable" Then
DoEvents
ct.ColumnHidden = False
Set fd = .Fields(CtSource)
' Select Case CtSource
' Case "invoice_no", "bill_to_cust", "IncDetID"
' 'Always hide these fields
' ct.ColumnHidden = True
' Case "item_no", "description", "quantity",
"DlrPrice", "IncentiveAmt", "Unit Price", "TotalIncentive"
' 'Do nothing, leaving these fields visible
' Case Else
DoEvents
.MoveLast
subtotal = 0
If Not .BOF Then
Do Until .BOF
If CtSource Like "=cdbl(*" Then
subtotal = subtotal +
.Fields(Mid(CtSource, 8, Len(CtSource) - 9))
Else
subtotal = subtotal +
Nz(.Fields(CtSource), 0)
End If
.MovePrevious
Loop
Else
subtotal = 0
End If
DoEvents
If subtotal <= 0 Then
DoEvents
ct.ColumnHidden = True
End If
' End Select
End If
Next
End With
Set ct = Nothing
Set fd = Nothing
On Error GoTo 0
'***************************
This is the call from the parent form:
'************************************************************
Private Sub cmdInvNoEnter_Click()
ClearHeaderFields
ShowHeaderFields (False)
If Nz(Me.txtinvno, "") <> "" Then
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Call the stored procedure, passing it the parameter, returning recordset rs
CurrentProject.Connection.stpSalesHeaderByInvoiceNo Me.txtinvno, rs
'Fill in the fields from the returned recordset
If Not rs.BOF And Not rs.EOF Then
Me.txtInvDate = rs![TranDate]
Me.txtCustNo = rs![CustNo]
Me.txtCustName = rs![CustName]
Me.txtAMno = rs![AMno]
Me.txtAMname = rs![AMname]
Me.txtSSRno = rs![SSRno]
Me.txtSSRname = rs![SSRname]
ShowHeaderFields (True)
DoEvents
Call Me.sbfrmSalesDetailByInvoiceNo.Form.P_HideEmptyColumnsNumeric
'If I dont call it here, it doesnt work
DoEvents
Me.lstDet.RowSource = "EXEC stpSalesDetailByInvoiceNo '" &
Me.txtinvno & "'" 'This is to test the list box vs datasheet subform
Call FormatLB
Else
Me.txtInvDate = ""
Me.txtCustNo = ""
Me.txtCustName = "Invoice number was not found"
Me.txtAMno = ""
Me.txtAMname = ""
Me.txtSSRno = ""
Me.txtSSRname = ""
Me.txtCustName.Visible = True
Me.lstDet.RowSource = ""
End If
rs.Close
Set rs = Nothing
'Me.sbfrmSalesDetailByInvoiceNo.Requery
Me.sbfrmSalesDetailByInvoiceNo.Form.Requery
Else
ShowHeaderFields (False)
Me.sbfrmSalesDetailByInvoiceNo.Visible = False
Me.lstDet.RowSource = ""
End If
End Sub
'*********************************************************
On Sat, Oct 2, 2010 at 9:25 PM, A.D. Tejpal <adtp at airtelmail.in> wrote:
> David,
>
> You wish to effect dynamic hiding of empty columns (having no significant value beyond Nulls or zeros). Datasheet form happens to be well suited for this purpose.
>
> Sample code in VBA module of the form, as given below, should get you the desired results. It is a generic routine, free of specific names for source query and its fields.
>
> Best wishes,
> A.D. Tejpal
> ------------
>
> ' Code in VBA module of datasheet form
> ' (For hiding numeric columns having no
> ' significant value beyond Nulls or zeros)
> '==============================
> Private Sub Form_Load()
> P_HideEmptyColumnsNumeric
> End Sub
> '----------------------------------------------
>
> Private Sub P_HideEmptyColumnsNumeric()
> On Error Resume Next
> Dim ct As Access.Control, fd As DAO.Field
> Dim CtSource As String
>
> With Me.RecordsetClone
> For Each ct In Me.Detail.Controls
> Err.Clear
> CtSource = ct.ControlSource
> If Err.Number = 0 And _
> Not CtSource Like "=*" Then
> ' It is a bound control
> ct.ColumnHidden = False
> Set fd = .Fields(CtSource)
> Select Case fd.Type
> ' Hide numeric columns - if blank
> Case dbText, dbMemo, dbGUID
> Case Else
> .Filter = "Nz(" & CtSource & ", 0) > 0"
> If .OpenRecordset.EOF Then
> ' This column is blank - Hide it
> ct.ColumnHidden = True
> End If
> End Select
> End If
> Next
> End With
>
> Set ct = Nothing
> Set fd = Nothing
> On Error GoTo 0
> End Sub
> '====================================
>
> ----- Original Message -----
> From: David McAfee
> To: ACCESS-L at PEACH.EASE.LSOFT.COM
> Sent: Friday, October 01, 2010 22:52
> Subject: Is there a way to dynamically hide fields if needed
>
>
> (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