Display text message dependent on form selection

[Emelina Bumsquash]

There is a main opening page [Demographics] in my database and I would like
there to be text box which displays an 'alert' or piece of information
dependent on other tables / forms in the database. There are only 4 different
'alerts' I want:

1) display 'PARTICIPANT' if 'Recruitment' = 1 on 'Recruitment' form AND
'Dropouts' = 1 or 0 or Is Null on 'Random' Form

2) display 'NON-PARTICIPANT' if 'Recruitment' = 2 or 0 or Is Null

3) display 'PARTICIPANT DECEASED' if 'Recruitment' = 1 on 'Recruitment' form
AND 'Dropouts' = 4 on 'Random' Form

4) display ' FORMER PARTICIPANT (WITHDRAWAL) if 'Recruitment' = 1 on
'Recruitment' form AND 'Dropouts' = 2 or 3

Is this possible? I'm guessing this will need various SQL statements in the
Demographics form code about looking up values across different tables /
forms but am very much a beginner in this so any help would be most
appreciated!

 

[TedMi]

I would recommend basing the alerts on values obtained from database tables,
not from forms. What if the other forms aren't open? What if the various
forms aren't synchonized to the same person? Please provide more info on the
structure of your data.
-TedMi

 

[Emelina Bumsquash]

The forms correspond to the tables anyway, so yes, the alert can be based on
values obtained from database tables.

The forms aren't open at the same time - i was thinking i'd need some kind
of lookup function. to synchronise to the same person, the lookup would have
to include ensuring the unique 'studyID' was synchronised.

Not sure in what format to provide more info about the data but in case it
helps for this query

TABLE: DEMOGRAPHICS
Field1 = StudyID: unique identified
Field2 = Current Status - this is the field i want to auto-update; was
thinking perhaps 'on open' of the form/table

TABLE: RECRUITMENT
Field1 = StudyID: unique identified
Field2 = Textbox denoting Consent to join study? 1=yes, 2 = no

TABLE: RANDOM
Field1 = StudyID: unique identified (Join with Recruitment)
Field2 = Dropouts. 1= not as prescribed but still in study, 2 = full dropout
no follow up, 3 = full dropout no follow up, 4 = Deceased

So Field 1 in all tables must be synchornised to ensure it's the same person
that the lookup refers to and to link this back to my original post:

1) Current status displays 'PARTICIPANT' (or equivalent code) if Field2 in
Recruitment Table = Yes (or 1) and Field2 in RANDOM table = either 1 or 0/Is
Null to denote no entry and therefore no dropout.

Does that make any better sense? Apologies that my terminology is probably
sub par!

I would recommend basing the alerts on values obtained from database tables,
not from forms. What if the other forms aren't open? What if the various
forms aren't synchonized to the same person? Please provide more info on the
structure of your data.
-TedMi

There is a main opening page [Demographics] in my database and I would
like
there to be text box which displays an 'alert' or piece of information
dependent on other tables / forms in the database. There are only 4
different
'alerts' I want:

1) display 'PARTICIPANT' if 'Recruitment' = 1 on 'Recruitment' form AND
'Dropouts' = 1 or 0 or Is Null on 'Random' Form

2) display 'NON-PARTICIPANT' if 'Recruitment' = 2 or 0 or Is Null

3) display 'PARTICIPANT DECEASED' if 'Recruitment' = 1 on 'Recruitment'
form
AND 'Dropouts' = 4 on 'Random' Form

4) display ' FORMER PARTICIPANT (WITHDRAWAL) if 'Recruitment' = 1 on
'Recruitment' form AND 'Dropouts' = 2 or 3

Is this possible? I'm guessing this will need various SQL statements in
the
Demographics form code about looking up values across different tables /
forms but am very much a beginner in this so any help would be most
appreciated!