[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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.

Sample ‘special’ needs Roles follow:

*ALLERGY* Cats
*DIET* Vegetarian
*HEALTH* Asthmatic
*NB&B* Needs ground floor room

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'));

Open in new window

0
MikeDTE
Asked:
MikeDTE
  • 6
  • 6
1 Solution
 
als315Commented:
Why you can't add fielt to table tContactRole with boolean value (special = true)?
In this case you can use very simple queries and functions like dcount for counting "special" roles of participant.
0
 
Rey Obrero (Capricorn1)Commented:
try this as the ROW SOURCE of the Listbox


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:
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.


0
Fill in the form and get your FREE NFR key NOW!

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.

 
Rey Obrero (Capricorn1)Commented:
how about this

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:
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*"));

You get all 411 matches rather than those matching member 6546






0
 
Rey Obrero (Capricorn1)Commented:
MikeDTE,

did you try my last post ?
0
 
Rey Obrero (Capricorn1)Commented:
see the placement of  "(" and ")" on my last query at http:#a37058913 
0
 
MikeDTEAuthor Commented:
Hi capricorn1

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?
0
 
Rey Obrero (Capricorn1)Commented:
are all these things happening in form "[fJDWBooking]" ?
0
 
MikeDTEAuthor Commented:
There is a button on fJDWBooking that will only be visible if the current contact record has a *ALLERGY* or *DIET* or *HEALTH* or *NB&B* record in tContactRoles.

If the button appears and is clicked I load fJDWRoles and show a List Box with the results of the SQL Statement you have provided.
0
 
Rey Obrero (Capricorn1)Commented:

you can use the sql statement to open a recordset

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:
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
strSQL = "SELECT tContactRole.cr_ContactId, tContactRole.cr_Role "
strSQL = strSQL & "FROM tContactRole"
strSQL = strSQL & "WHERE (tContactRole.cr_ContactId =" & lngContactID & ") "
strSQL = strSQL & "AND (tContactRole.cr_Role Like " * A * " Or tContactRole.cr_Role Like " * D * " Or tContactRole.cr_Role Like " * H * " Or tContactRole.cr_Role Like " * N * ");"

Open in new window

0
 
MikeDTEAuthor Commented:
OK all resolved

For sake of completeness and as a guide to others following in my footsteps the code below works in Access VBA.

The points are yours - thank you capricorn1
Dim RS As DAO.Recordset
Dim strSQL As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str4 As String
    
str1 = Chr(34) & "*ALLERGY*" & Chr(34)
str2 = Chr(34) & "*DIET*" & Chr(34)
str3 = Chr(34) & "*HEALTH*" & Chr(34)
str4 = Chr(34) & "*NB&B*" & Chr(34)
      
strSQL = "SELECT tContactRole.cr_ContactId, tContactRole.cr_Role "
strSQL = strSQL & "FROM tContactRole "
strSQL = strSQL & "WHERE (tContactRole.cr_ContactId = " & lngContactID & ") "
strSQL = strSQL & "AND (tContactRole.cr_Role Like " & str1 & " Or tContactRole.cr_Role Like " & str2 & " Or tContactRole.cr_Role Like " & str3 & " Or tContactRole.cr_Role Like " & str4 & ");"

Set RS = CurrentDb.OpenRecordset(strSQL)

'now test if there will be record/s returned

If RS.EOF Then   'no records
  
   Forms!fJDWRoles.AlertsButtonB.Visible = False
    
Else
    
   ' there is at least a record
    
   Forms!fJDWRoles.AlertsButtonB.Visible = True
    
End If

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now