SQL Control Souce for ListBox

I have a table of ContactRoles that has only 2 relevant fields

cr_ContactId Relational field to Contact table
cr_Role Role name (Text)

The Roles are properties pertaining to contacts which cover a multitude of subjects like what classification the Contact belongs to (Tutor, Participant, B&B Operator etc.) and importantly for the stage I have reached their allergenic, dietary, health and B&B ‘special’ needs.

All ‘special’ needs Roles follow the same format whereas other Roles are not capitalized and do not have leading and trailing ‘asters’.

When a participant is added as a booking on an event I would like to make a button visible that indicates where there are ‘special’ needs Roles to be considered for catering, B&B booking arrangements and/or for access difficulties (e.g. *HEALTH* Wheelchair bound).

I need a SQL statement that will be the Control Source of a Listbox and ascertain whether there is anything to alert the user to.

The Listbox will be part of a Pop-up form called by the button on the Events form that will appear in any record in tContactRoles that matches cr_ContactID and has a cr_Role containing ‘*A’, ‘*D’, ‘*H’ or ‘*N’

Do I do a quick check to see if I need to display the ‘Alert’ button or do I build the Alert form Listbox before I open the Alert form and then only make the Alert button visible if the Listbox row count > 0?

The first issue is how do I combine 4 'Like' elements in the WHERE statement?

SELECT tContactRole.cr_ContactId, tContactRole.cr_Role
FROM tContactRole
WHERE (((tContactRole.cr_ContactId)=[Forms]![fJDWBooking]![jdwbMember]) AND ((tContactRole.cr_Role) Like "[*]A*")) OR (((tContactRole.cr_Role) Like "[*]D*")) OR (((tContactRole.cr_Role) Like "[*]H*")) OR (((tContactRole.cr_Role) Like "[*]N*"));

0

MikeDTEAuthor Commented: 2011-10-31

als315 - thanks for your suggestion. The ContactRoles table is from a 3rd Party CRM database and I am not able to make changes to the structure. The CRM database is subject to upgrades and any additions to the table will be lost in these updates.

capricon1 - this doesn't work perfectly but it's almost there. The table has 411 recoords which have an 'alert' role - i.e. Roles with a leading *. Your SQL Statement finds all 411 records instead of just those matching the Member.

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers. It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

SELECT tContactRole.cr_ContactId, tContactRole.cr_Role
FROM tContactRole
WHERE (((tContactRole.cr_ContactId)=[Forms]![fJDWBooking]![jdwbMember]) AND ((tContactRole.cr_Role) Like "[*]A*" Or (tContactRole.cr_Role) Like "[*]D*" Or (tContactRole.cr_Role) Like "[*]H*" Or (tContactRole.cr_Role) Like "[*]N*"));

0

MikeDTEAuthor Commented: 2011-10-31

I have done some experimenting with SQL:

This works (with the hard-wired '6546' ref or with the [Forms]![fJDWBookings]![jdwbMember}

SELECT tContactRole.cr_ContactId, tContactRole.cr_Role
FROM tContactRole
WHERE (((tContactRole.cr_ContactId)=6546) AND ((tContactRole.cr_Role) Like "*ALLERGY*"));

As soon as you add another 'Like' statement:

SELECT tContactRole.cr_ContactId, tContactRole.cr_Role
FROM tContactRole
WHERE (((tContactRole.cr_ContactId)=6546) AND ((tContactRole.cr_Role) Like "*ALLERGY*")) OR (((tContactRole.cr_Role) Like "*DIET*"));

Yes that works - I missed this coming in because I was working in Query builder.

Now to earn your points could you please give me a nudge in the right direction on the following:

Do I do a quick check to see if I need to display the ‘Alert’ button or do I build the Alert form Listbox before I open the Alert form and then only make the Alert button visible if the Listbox row count > 0?

dim rs as dao.recordset, sql as string
sql="SELECT tContactRole.cr_ContactId, tContactRole.cr_Role
FROM tContactRole
WHERE (((tContactRole.cr_ContactId)=" & [Forms]![fJDWBooking]![jdwbMember]) AND ((tContactRole.cr_Role) Like "[*]A*" Or (tContactRole.cr_Role) Like "[*]D*" Or (tContactRole.cr_Role) Like "[*]H*" Or (tContactRole.cr_Role) Like "[*]N*"))"

set rs=currentdb.openrecordset(sql)

'now test if there will be record/s returned

if rs.eof then 'no records
me.ButtonName.visible = false
exit sub
else
' there is at least a record
me.ButtonName.visible = true
end if

0

MikeDTEAuthor Commented: 2011-10-31

Hi capricorn1

OK I see the logic. Unfortunately the SQL statement syntax you state doesn't work in VBA.

It doesn't like [] brackets for a start

I've got as far as teh code snip below but it's falling over on teh 4th line