David McAfee
davidmcafee at gmail.com
Tue Oct 12 17:45:05 CDT 2010
Found it!
I simply had to move the subform requery before calling the function
on the subform.
On Thu, Oct 7, 2010 at 1:03 PM, David McAfee <davidmcafee at gmail.com> wrote:
> 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
>