Display different info based combo box

[cactuspete13]

I am trying to set up a report so that if a user selects one of the options from a drop down menu, certain fields display ****'s instead of the actual information. For example, if the user selects Unrestricted from the menu, the name is displayed; if the user selects Restricted, the name is masked with *s.

[June7]

The easiest way is an expression in textbox ControlSource.

=IIf(Forms!formname!comboboxname = "Restricted", "****", [fieldname])

[cactuspete13]

I went to enter this into the text box Control Source: =IIf([Forms]![MainForm]![Report]="Restricted],"***",[Info1Q.DOB]) and I end up getting a #Name? error regardless of which option is selected from the combobox. I also get a Circular Reference error.

A little further explanation of how the database is set up, two tables, 3 queries, one form, one report: Main contains all of the action information; Personnel contains employee information; two queries mirror the Personnel table to feed two different partes of an action with a third query linking those two with the Main table joined by an identification number; MainForm for entering the information; and the report for displaying it.

Thank you for all your assistance.

[June7]

Why prefix with Info1Q? Is there more than one DOB field in report RecordSource?

You show a ] where " should be after Restricted.

Make sure control with expression is not named same as field used in expresssion. This results in circular reference.

[cactuspete13]

I have to display two different sets of personnel information, including DOB. The best way I could think of to do this was to set two queries to show the same information with two different names (Info1Q and Info2Q) then combine the [Main] table with those two queries, using the identification number to link them in using many-to-one relationships (Main to Info1Q and Main to Info2Q), in a third query, CombinedQ. It sounds confusing but it works. That's why there is the prefix before the DOB. The ] after Restricted was a typo.

I changed the name of the control box in the form and the report to ReportOption, still the same problem. The record source for the page is the CombinedQ, could not having that prefix in the control source formula be causing the problem?

[June7]

Circular reference gone and #Name? error still there?

The prefix is needed if the both DOB fields from Info1Q and Info2Q are included in CombinedQ. How is the DOB field name listed in the report list of fields. Look in the dropdown list of any data control ControlSource property.

[cactuspete13]

The DOB fields are listed as Info1Q.DOB and Info2Q.DOB

Thank you for you assistance with this.

[June7]

Then I can't see anything wrong with the expression.

If you want to provide db for analysis, follow instructions at bottom of my post. Identify the objects involved in issue.

[cactuspete13]

Here is a washed down version of the database I am working on.Actions.accdb

[June7]

That clarifies a lot. I thought the combobox was unbound and this was a setting to apply to all records on the report. However, looks like you want this to be a record-by-record condition and the Restricted/Unrestricted value is saved in table for each record.

I changed Report field name to SecurityClass. Access seemed to have issue with Report as name (I expect this is a reserved word).


Consider this sql for the report RecordSource:
SELECT Main.ID, Main.SecurityClass, Main.Party1IDNumber, Main.Party2IDNumber, PersonnelInfo.LastName AS Party1Last, PersonnelInfo.DOB AS Party1DOB, PersonnelInfo_1.LastName AS Party2LastName, PersonnelInfo_1.DOB AS Party2DOB FROM PersonnelInfo AS PersonnelInfo_1 RIGHT JOIN (PersonnelInfo RIGHT JOIN Main ON PersonnelInfo.ID = Main.Party1IDNumber) ON PersonnelInfo_1.ID = Main.Party2IDNumber;


Change the expression to:

=IIf([SecurityClass]="Restricted","***",[Party1DOB])

[cactuspete13]

I changed the name of the field to ReportType and adjusted the source code in the report and it works, records with Restricted selected now show up with a mask of ****. But as I plugged that hole, another appeared. Records with Unrestricted as the ReportType, I get an #Error in that block. So how do I tell it to display the information when something other than Restricted is selected?

[June7]

It worked for me. It doesn't matter what the other choices are. If the value is not 'Restricted' the data will display.

I downloaded your db again. Made these edits:

1. changed fieldname from Report to ReportType

2. changed name of control from Info1Q.DOB to Info1QDOB (removed the dot) so eliminated circular reference


Did you examine the query I suggested?

[cactuspete13]

I see it. Thank you for both assists.