Cascading with 2 combo box and a text box with a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 4 controls that I am working with, 2 combo box's [clientid] and [acct]
and one text box[comments] and subform1. What i would like to do is have the
user select the [clientid] and then have the second combo box query only the
results from the first combo box and then let the user enter keywords in a
textbox and query from the results of the second combo box. Is this possible?
I would appreciate any help.
 
Hi, Raj.

The first part is easy. In the AfterUpdate event procedure of the first
combo box, reset the RowSource property of the second. The right hand side
of the equation must evaluate to a valid string:

Me!MySecondComboBox.RowSource = "SELECT field1, field2 FROM MyTable WHERE
YourField = " & Me!MyFirstComboBox

or, as an example:

Me!RepID.RowSource = "SELECT RepID, RepName FROM Reps WHERE RegionID = " &
Me!RegionID

For the second part, you will have to build an SQL query statement from the
entered words, using a delimiter such as a comma or semicolon between each
keyword if desired:

commercial; high-rise; beachfront

You can then use the Split function which parses the words to an array, and
loop through them to build your string, then execute the query with the
RunSQL method:

Dim arrMyArray() As String
Dim i As Integer
Dim strSearchFld As String
Dim strSQL As String

arrMyArray = Split(YourKeywordField, ";")
strSearchFld = "*" & "' OR MyField = Like " & "'" & "*"
strSQL = "WHERE MyField = Like " & "'" & "*"
For i = 0 To UBound(arrMyArray)
' Strip any leading or trailing spaces the user might have entered
arrMyArray(i) = Trim(arrMyArray(i))
strSQL = strSQL & arrMyArray(i) & strSearchFld
Next
strSQL = Left(strSQL, Len(strSQL) - Len(strSearchFld) + 2)
DoCmd.RunSQL strSQL

Hope that helps.
Sprinks
 
Back
Top