[MS_AccessPros] Cascading Combo boxes controled by query

 

I have a form with 2 combo boxes.  The first box is used to control the data contained the second.  The second box contains a list of names defined by a query.

SELECT tblEmployee.lngEmployeeID, tblEmployee.lngCertNo, [tblEmployee]![strLastName] & ", " & [tblEmployee]![strFirstName] AS Employee, tblEmployee.lngStatus
FROM tblEmployee
ORDER BY [tblEmployee]![strLastName] & ", " & [tblEmployee]![strFirstName];

This query results exceed the maximum character limit for the combo box.  I decided to added the first box to limit the number of elements by using a where clause.  The where clause is created from the text value contained in the first combo box.  The above query is re structured using a On Change event. The event is as follows:

Private Sub Cbox_Filter_Change()
Dim sqlWhere As String
Dim bval As String
Dim ans As Variant

bval = Cbox_Filter.Text

  sqlWhere = "WHERE ((( '" & [tblEmployee].[strLastName] & "')>='" & Mid(bval, 1, 1) & "'"
   If Mid(Cbox_Filter.Value, 2, 1) = "<" Then
      sqlWhere = sqlWhere + " AND ( " & [tblEmployee].[strFirstName] & ")<" & Mid(Cbox_Filter.Text, 3, 1)
   Else
      sqlWhere = sqlWhere + " AND ( " & [tblEmployee].[strFirstName] & ")<=" & Mid(Cbox_Filter.Text, 3, 1)
   End If

     sqlStr = "SELECT " & [tblEmployee].[lngEmployeeID] & ", " & [tblEmployee].[lngCertNo] _
           & ", " & [tblEmployee].[strLastName] & ", " & [tblEmployee].[strFirstName] & " AS Employee, " & [tblEmployee].[lngStatus] _
           & " FROM tblEmployee" _
           & sqlWhere _
           & " ORDER BY " & [tblEmployee].[strLastName] & ", " & [tblEmployee].[strFirstName] & ";"

End Sub

The string sqlStr is a module global string variable.  I am having an issue with the first sqlWhere assignment.  Error can not find field.

Rod

__._,_.___

---

Posted by: desertscroller@cox.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___