database coordinator high school class (1 Viewer)

martinbaker

New member
Local time
Today, 08:45
Joined
Apr 4, 2020
Messages
5
on a report, need to display an "O" or "M" or "G" in a text box depending on the value of 2 checkboxes for that record. One checkbox (the Missing checkbox) would be checked if that person is "Missing". Another (the Gone checkbox) would be checked if that person is "Gone". And no boxes checked if that person still "OK".
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:45
Joined
Sep 21, 2011
Messages
14,052
Code:
=IIF(Mchk,"M",IIF(Gchck,"G","O"))

where Mchk and Gchk are your checkboxes. If more than one can be checked at a time, this will not work, and you would need a function instead.?

Oh, and welcome to AWF BTW
 

Micron

AWF VIP
Local time
Today, 08:45
Joined
Oct 20, 2018
Messages
3,476
You are trying to interact with a report and set the values of checkboxes? You probably should be using a calculated field in a query that the report is based on. The details of the possibilities are not clear, as Gasman might be suggesting. It can be interpreted that there a maybe as many as 7 possible combinations of the 3.

If there are only 3 (1 or 2 or 3) then the query calculated field might simply use the Switch function to return O, M or G.
 

strive4peace

AWF VIP
Local time
Today, 07:45
Joined
Apr 3, 2020
Messages
1,003
hi Martin,

Welcome! While yes/no fields in the table design may seem logical, most often it is better to use other data types. In this case, I would create a Statuses table with three records, one for each status, and use a StatusID in the table that has the information for the form instead of yes/no fields that will be checkboxes on a form.

Statuses
  • StatusID, Long Integer, PK (primary key)
  • Status, short text, Size=10
records
  • 0, "Ok"
  • 1, "Gone"
  • 2, "Missing"
Since it seems that only one choice should be selected, you could use option buttons in a frame on the form, if that is easier than a combo box to select the value.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:45
Joined
May 7, 2009
Messages
19,175
add code to the report's Detail section On Format Event:
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    ME.txtUnbound = Choose(Abs(Me.Check1) + Abs(Me.Check2) * 2 + 1, "O", "M", "G")
End Sub
 

martinbaker

New member
Local time
Today, 08:45
Joined
Apr 4, 2020
Messages
5
Thanks to all. My days of heavy duty Access programming are well behind me. However, I still like mental challenges and I saw an opportunity to assist my high school classmates to execute an online 60th year reunion. I decided to just add an OMG field to the Address table to display on the report. I had to create and execute 3 Update Queries to populate the field, one for each of the 3 values that are allowed to appear in it.
The 3 cases were:
1. Insert an "M" if "Missing" = "T" and "Gone" = "F"
2. Insert a "G" if "Gone" = "T" and "Missing" = "F"
3. Insert an "O" if neither case applies, since "Gone" and "Missing" cannot be "T" at the same time.
I also set the default value for new "OMG" fields to be "O". On the data entry form I wrote some code for the After Update event on both the "Gone"
and "Missing" checkboxes to only allow the 3 cases above to appear and to place the correct letter into the "OMG" field
Thanks again to all. We all could use some "mental" stimulation these days. Stay well.
 

strive4peace

AWF VIP
Local time
Today, 07:45
Joined
Apr 3, 2020
Messages
1,003
hi Martin,

You're welcome. Its wonderful that you're helping to organize such a momentus reunion! Might I suggest, however, to leave the OMG field blank until OK is actually verified?

Another field that might be of value is DateGone, and it can then be filled if known.

Hope your reunion is a great success!
 

martinbaker

New member
Local time
Today, 08:45
Joined
Apr 4, 2020
Messages
5
One of my classmates asked me if I could include a field in the Address report that would indicate the distance between our classmates' current zip code and the zip code of our old high school. What would the Macro code look like to calculate the distance and record that number in a data field in the Address table? I could then add a button on the data entry form to run the macro when zip codes change. thank you very much.
 

Users who are viewing this thread

Top Bottom