Listbox if Previously Chosen - *** Formula?

[misscrf]

I have a previous thread on a related topic here:
thread702-1623619

Now I am working on a new challenge. I have a main Events form. On that a subform of contacts, who have been invited to that event.

There is a button on the main form, which will open a pop-up form. The pop-up form has a multi-select listbox of the contacts, which can be selected. Once selected and the button on the pop-up form clicked, a record is created on the event subform for each contact, inviting them to the current event, and the pop-up form is closed.

Here is my issue. when a user launches the pop-up form, there is nothing to tell them if a contact in the listbox is already invited to this event.

I thought about doing any of the following:

A) filtering contacts out of the pop-up form listbox if they exist in the (not visible) subform of the events form

B) make a formula in the control source of the listbox to look for the contactid value in the subform. I did this. It looks like this:

Last Name: IIf([tblContact]![PKContactdID]=[Forms]![frmEvent]![frmSubEventInvite].[Form]![cboContact],"*** " & [txtLName],[txtLName])

This works for the first contact in the invited subform, but not for the rest.

If anyone has any thoughts about how to accomplish something like this, I would love the suggestions!

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

Howdy misscrf . . .

I'd go with [blue]A)[/blue]. There's no break in logic flow with this method. Below is a sample query that lists all uninvited contacts for the current event. Note this is just to give you the idea ... I don't know table structure, names ... ect:

[blue][tt]SELECT PKContactID, ContactName
FROM tblContact
WHERE [PKContactID] NOT IN (SELECT PKContactID
			                FROM tblContact
                            WHERE [EventID] = Forms!MainEvents!EventID)[/tt][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[misscrf]

Thanks, Aceman. I believe I tried that and it only filtered out the first contact that had been invited. It didn't filter out all of them. I will try again and see if I can post the sql that I had to check if there are any issues there.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

It did end up working. I just needed to follow your method and link to the tables not the forms. Here is the code:

SELECT tblContact.PKContactdID, tblContact.txtLName AS [Last Name], tblContact.txtFName AS [First Name], tblContact.txtCompany AS Company, tblContact.txtDept AS Department, tblContact.txtTitle AS Title
FROM tblContact
WHERE (((tblContact.PKContactdID) Not In (Select FKContact from tblEventInvite where [FKEvent] = Forms![frmEvent]![PKEventID] )))
ORDER BY tblContact.txtLName, tblContact.txtFName;

Thanks for your help!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

misscrf . . .

Do you understand how it works?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[misscrf]

oh yeah, I am populating the list box for all contacts where the contact's id is not in the event invites table where the event id = the current event id on the open events form.

Right?


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[TheAceMan1]

misscrf . . .

Great ... [blue]now thats Poetry in Motion![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]