Exclude a record in a subform which is displayed in the main form & calculate birthdays

[Mikey C]

Hi all

I have a main form which displays a specific contacts details and a
subform which displays all contacts for the same company (with a link
which will display the selected alternative contacts details in the
main form if double clicked)

Now ... What I would like to do is exclude the record from the
alternative contacts subform if it is already displayed in the main
form - any idea how to do this?

Also

I'm recording the DOB and relationship start dates for each operator /
contact. I would like to create a subform that will calculate
birthdays and anniversaries, and then display these for one week prior
to event until one week after the event (if could be green before
event & red afterwards would be great!!)

Can anyone point me in the right direction?

Please be gentle - as you can tell, I'm a bit of a novice!

Thanks

 

[strive4peace]

Hi Mikey

1. I will assume you have an autonumber contactID in your table. For
the RecordSource in the subform, under contactID, make this criteria:
<> forms!mainformname!contactID

ContactID will need to be on the main form and the name of the control
will need to be the same as the ControlSource. If your field is not
named ContactID, adjust accordingly

on the current event of the main form, you will need to requery the
subform so it reevaluates the criteria

the code behind the form in the [Event Procedure] would be -->
Me.subform_controlname.requery


2. use Conditional Formatting

here is a start and then see if you can figure the rest out:

click on your DOB control (am assuming DOB is also the control Name)

from the menu: Format, conditional Formatting

Expression Is --> (Date() - DateSerial( Year(Date()), Month([DOB]),
Day([DOB])) < 7

choose green for the ForeColor


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Mikey C]

Brilliant! Thanks Crystal

 

[strive4peace]

you're welcome, Mikey happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*