[MS_AccessPros] Building dependent Combo boxes using VBA issue

 

I am currently trying to develop a sql string based on an input from a combo box to filter the selection for a second combo box.  Below is the code being developed.  The value from the 1st combo box (Cbox_Filter) is F<K  which is used to define the where cause.  It appears to create the string okay but when the source row for the second combo box is create and requery the results are a blank combo box.  I have used the create wizard to ensure the results are proper.  However building the query in vba seems have issues.  Not sure if building the expression of lastname and firstname works or the order statement.  Any help would be greatly appreciated.  sqlStr is a global variable for the module.

Using Windows 8.1 and 10 with ACCESS 2010 and 2013.

Rod

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].[strLastName])<""" & Mid(Cbox_Filter.Text, 3, 1) & """"
   Else
      sqlWhere = sqlWhere + " AND ([tblEmployee].[strLastName])<=""" & 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];"

     Me.Cbox_Select.RowSource = sqlStr
     Me.Cbox_Select.Requery

End Sub

__._,_.___

---

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

.

__,_._,___