Check for Names in a Query where a report is compiled

[Walshie1987]

Hi, a friend has just emailed me asking if i could post the below question on here:

I have a query that contains, among other things, the name of X people attending X event and another name (from the same table) who is to act as their 'in case of emergency' contact.

However, how can I check using VBA to make sure that the emergency contact isnt also attending the same event, which in turn sets the attributes of various labels on the report saying stuff like "emergency contact is attending this event, please select another".

Thanks in advance.

Hopefully someone can help.

Chris

 

[Remou]

Let's say that the emergency contact is being entered on a form based on the attendees table:

Private Sub EmergencyContact_AfterUpdate()
Set rs = Me.RecordsetClone
rs.FindFirst "AttendeeName='" & Me.EmergencyContact & "'"
If Not rs.NoMatch Then
    MsgBox "Found!"
End If
End Sub

However, too often you will find that the emergency name is Annie Body, but the person attending is A Body, so it may be as well to provide a list of last names that match and allow the user to confirm a match.

 

[WideMonk]

Hi, thanks Walshie.

The emergency contact is placed on a sub-form on the member record. This sub-form is linked to the members table because I can not discriminate different people and have two separate tables, one for members and one for emergency contact (because the emergency contact might also be a member/take part and so I shouldnt really store their details twice).

 

[Remou]

So, your question is ...?

 

[WideMonk]

So my question is... how can I get my report to determine if someone is on the list, as opposed to a query/form doing the work.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

lblEmergContactParticipating.Caption = "Your emergency contact is also attending this event, please select another."

If X is on the list then
  lblEmergContactParticipating.visible = true
else
  lblEmergContactParticipating.visible = false
End if

End Sub

Simon

 

[Remou]

I am a little confused. Do you have something like this?

[tt]tblMember Fields:
ID - PK
EmergContact - FK tblMember

tblAttendees
ID - FK tblMember[/tt]

 

[WideMonk]

Sorry for the delayed reply.

Yes, (from my limited understanding of an FK), thats about it.

Simon

 

[TheAceMan1]

How are ya Walshie1987 . . .

[blue] . . . how can I check using VBA to make sure that the emergency contact isnt also attending the same event, . . .[/blue]

You tell us . . . looking at your table structure [blue]how would you know? . . .[/blue]

See Ya! . . . . . .