Show/Hide Controls on Each Record Not Working As Expected.

[SunnyJamshedji]

I would like to show/hide some controls, when going from one record to the next in a query, depending on whether the logged in user has rights to view certain fields. I have used Application.Echo False/True, as well as Me.Painting = False/True in Form_Current, but when I use PageUp/PageDown to go between the records, just before it hides the controls using Enable/Visible, a record, I can see the data in the controls in the new record. It does hide them, but on a slow machine or network-based MDB file, it stays long enough that the user can read the info, if they are smart/fast enough. Is there a way to do this by truly turning off painting until I am done. I have a subform as well as many data entry fields on the screen.
Thanks for your time ... Sunny

 

[highandwild]

It;s better to display a form and not a query as you mention.

You could call a function like this from each text box on the form and apply any user based logic you need.

It may be best to hold the criteria in a table instead of hard coding it.

=DisplayOrNot("Narrative",[Narrative])

Public Function DisplayOrNot(strFieldName, varValue)

Select Case strFieldName

Case "OtherAccountName"

DisplayOrNot = IIf(Rnd <= 0.5, varValue, "")

Case "Narrative"

DisplayOrNot = IIf(Rnd <= 0.3, varValue, "")

End Select

End Function

I have just used Rnd as an example.

 

[SOS]

Why not just limit the data returned to the form to that which the user can view?

 

[SunnyJamshedji]

@HighAndWild: Are you speaking of putting this public function in the .Visible property? (Edit: Tried this, but only allows Yes/No.) If that takes place before Form_current (but after data is loaded), then that would do the trick!

@SOS: Because a Salesman can only look at profit data in the records where they are sales person, but all other data in other records.

Thanks for your time ... Sunny

 

[highandwild]

Now I have more info....

Set up a public function as below:

Public Function ShowOrHideProfit(varEmployee, varValue)
ShowOrHideProfit = IIf(varEmployee = varEmployeeId, varValue, "")
End Function

varEmployeeId is either a public variable containing the employee id of the person who is logged in or a reference to a text box on the form containing the employee id.

In the Control Source property of the form text box put the following

=ShowOrHideProfit([EmployeeID], [Profit])

where [Employee] is the field containing the id of your employee and [Profit] is the field containing the profit.

 

[SunnyJamshedji]

@HighAndWild: I guess my form is a little bit more complicated than I am letting on! These controls are already bound to table columns. Revenue Labor, Revenue Material, Total Revenue. Gross Profit Labor, Gross Profit Material, Gross Profit Total. % GP Labor, % GP Material, % GP Total. When numbers are entered for Revenue and GP, the %s are calculated and all are written to the table as well for reporting. And, there are additional formulas under the covers as well, so if field names change, thngs start falling apart, as I use a lot of control names that match table column names and are used as constants. Worked well until now!

First, I tried to do this by setting the ControlSource in Form_Current, with the same results as earlier; flashing value between records.

Second, I tried your method, by passing the controlname and value; I'm all for reusing code! But, this gave me a circular reference, since my control name is [GrossProfitMat], as would be my table column name. Gave me errors.

Third: I added corresponding fields to the query (GPMat as [GrossProfitMat], etc.), to get around the circular reference. Here's what I used:

'------------------------------------------------------------------------------
' Changes ControlSource of GP fields. Called from Control Source of GP Fields.
'------------------------------------------------------------------------------
Public Function ShowGrossProfit(ByVal sGP As String, ByVal sSalesman As String, ByVal bCurr As Boolean) As String
    Dim bShowGP As Boolean
    
    bShowGP = (gSecurityProfile(spBD_GROSS_PROFIT_VIEW) And (SecurityAllowed(spGP_SALES) And (gLoginID = sSalesman))) _
            Or SecurityAllowed(spGP_PRESIDENT) _
            Or SecurityAllowed(spGP_PROJECTMGR) _
            Or SecurityAllowed(spGP_ESTIMATOR) _
            Or SecurityAllowed(spGP_VPOPERATIONS)
        
exitFunc:
    ShowGrossProfit = IIf(bShowGP, IIf(bCurr, FmtCurrStr(sGP, True), Format(sGP, "0.000")), "")
    Exit Function

errHandler:
    Call ShowFuncError("ShowGrossProfit")
    Resume exitFunc
End Function

and, in each controls ControlSource:

=ShowGrossProfit([GPMat],[Salesman],True)

So, I think, I have a Catch-22 of sorts: Either, I can have a bound field that has a fixed ControlSource, or I can have a fixed ControlSource and editing is enabled. Seems like I should be able to programmatically set the ControlSource actual column I want, rather than the value of that column. I think, I might be missing something here; maybe a second set of eyes (brain function) might help!

Wow, what a lot of work, just so that I can fool Access and the user. If

Me.Painting = False

truly stopped the painting, I would not need to do this!

Thanks again for your time and help ... Sunny

 

[SOS]

My work around would be to have two subforms. One with data that everyone can see. One with data that is limited to that person. Then you don't need to have a single form with this issue.

 

[SunnyJamshedji]

As per my original post, each person has field-level, read/write/view access to fields in projects where they are the Salesperson. On other projects they can view most of the project information except for the GP fields. @SOS: I hide the invoice subform anyway, since they do not have access to it due to their rights; this is not a problem as I do it once in Form_Open. Creating/Opening a second form without these fields would then mean they have to access things differently and I have to keep track of two sets of code, or rewrite everything as functions; big task on a complicated screen with many formulas under the covers. Hope this makes sense.

 

[boblarson]

Well, if you don't want them seeing stuff as you move from record to record then you really have no choice but to do what SOS suggested. It isn't working the way you currently have it and having something with separate record sources will allow you to only show what they can see. Yes, it may be a pain in the butt to do, but if you want it to work then you (as the programmer) have to suck it up and do the pain in the butt stuff so that it works easy for the user.

The problem that people sometimes forget is that the more complex the requirements, the more work you have to do as the programmer to make it work for the users to not have to do much.

 

[SunnyJamshedji]

Yeah Bob, in an ideal world, the client would be very understanding that in Access this is practically impossible, but that's not the case. In fact, they think it is easy and wonder why they are paying me in the first place! I have wanted them to move to a two-tier solution with VB.NET and SQL Server (even Express), as I feel it is much more flexible with building all this security into it, but no go on that either! Thanks for your words of encouragement!

 

[boblarson]

I don't normally go the temp table route but what if you used a query to populate a temp table with only records they can see and go about it that way?

 

[SunnyJamshedji]

The form fields are bound to the table, and field-level formulas with business rules (if this=that then do this, etc.) in many of the fields. I don't particularly want to undo anything. You can see a screenshot in the attached ZIP file. I actually called this into MS Access support, and they are looking into it.