[AccessDevelopers] Open a Form using a SQL Statement on Load

Tue, 01 Nov 2005 16:14:03 -0800

I have a form that I have been opening based on the value picked in a combo box.  That has been working fine.  On the Form load I have the following code:
 
strRoyalty = Forms!frmSelectEventsToScore.cboRoyaltyClass
strEvent = Forms!frmSelectEventsToScore.cboEventList
 
'Build the Record Source for the Form
strSQLSelect = "SELECT  tblARBAMembers.[LAST NAME], tblARBAMembers.[FIRST NAME], " & _
    "tblContestantEntries.EventName, tblContestantEntries.Score, tblMemberDetails.RoyaltyClass "
 
strSQLFrom = "FROM (tblARBAMembers INNER JOIN tblMemberDetails ON " & _
    "tblARBAMembers.[ARBA NUMBER] = tblMemberDetails.[ARBA NUMBER]) " & _
    "INNER JOIN tblContestantEntries ON tblARBAMembers.[ARBA NUMBER] = " & _
    "tblContestantEntries.ARBANumber "
 
strSQLOrder = "ORDER BY tblARBAMembers.[LAST NAME], tblARBAMembers.[FIRST NAME] "
 

strSQLWhere = "WHERE tblContestantEntries.EventName = " & Chr$(34) & strEvent & Chr$(34) & _
    "AND tblMemberDetails.RoyaltyClass = " & Chr$(34) & strRoyalty & Chr$(34)
 
strSQL = strSQLSelect & strSQLFrom & strSQLWhere & strSQLOrder
Debug.Print strSQL
 
Me.RecordSource = strSQL
DoCmd.Maximize
 
 
Now, I need to change the where statement so that is concatenates several values together into an Or clause.  For instance, if the combo box value is "Junior", then the where statement needs to look at "11 under" or "Lord" or "Lady" which are all kids which are 11 and under, just different according to how they entered the various contests.
 
I have tried building a Select Case statement which tests the value of the combo box and artificially builds the remainder of the where statement like:
 
Select Case strRoyalty
    Case "Junior"
'This works, but puts Lord and Lady and 11 Under on Separate criteria Lines
        strRoyalty = "Lord" & Chr$(34) & "OR " & "tblMemberDetails.RoyaltyClass = " & Chr$(34) & "Lady" & Chr$(34) & "OR " & "tblMemberDetails.RoyaltyClass = " & Chr$(34) & "11 under"
 
    Case Else
        strRoyalty = Forms!frmSelectEventsToScore.cboRoyaltyClass
End Select
 

Dawn Crosier
Application Specialist
"Education Lasts a Lifetime"

 



Please zip all files prior to uploading to Files section.


YAHOO! GROUPS LINKS



Reply via email to