Okay, this is beyond insane
I'm using a passthrough query to populate a combobox
The following queries work perfectly.
Code:
**Original Code**
Set Me.CmboAgent.Recordset = GetSQLRst("SELECT Emp_ID, Full_Nm " & _
"FROM Dbo.vEmployee " & _
"WHERE Position LIKE 'CS1%' " & _
"ORDER BY Full_Nm")
**Modified To Show Emp_ID in ComboBox**
Set Me.CmboAgent.Recordset = GetSQLRst("SELECT Emp_ID, CONCAT(Emp_ID, SPACE(3), Full_Nm) " & _
"FROM Dbo.vEmployee " & _
"WHERE Position LIKE 'CS1%' " & _
"ORDER BY Full_Nm")
** Test Variation of Above Query - Tested when below query failed **
Set Me.CmboAgent.Recordset = GetSQLRst("SELECT Emp_ID, CONCAT(Db-ID, SPACE(3), Full_Nm) " & _
"FROM Dbo.vEmployee " & _
"WHERE Position LIKE 'CS1%' " & _
"ORDER BY Full_Nm")
This Query, which is the one I need to work, pulls an empty recordset
Code:
** Need this query because the table the ComboBox 'AfterUpdate' sub routine pulls data from only has Db_ID **
Set Me.CmboAgent.Recordset = GetSQLRst("SELECT Db_ID, CONCAT(Db-ID, SPACE(3), Full_Nm) " & _
"FROM Dbo.vEmployee " & _
"WHERE Position LIKE 'CS1%' " & _
"ORDER BY Full_Nm")
** This Query was run for test purposes and it also failed **
Set Me.CmboAgent.Recordset = GetSQLRst("SELECT Db_ID, Full_Nm " & _
"FROM Dbo.vEmployee " & _
"WHERE Position LIKE 'CS1%' " & _
"ORDER BY Full_Nm")
Notes:
The Dbo.vEmployee is a View and it pulls both the Emp_ID & Db_ID from the same source table and both have the same formatting
HOWEVER - in the source table the Db_ID is a Primary Key
The ComboBox is set to 2 columns with the first column having a zero width
Regardless of whether I set 1 or 2 as the bound column - Same results
Regardless of whether I use brackets around either [Emp_ID] or [Db_ID] - Same results
And finally...
When I run this query directly in SQL - it works perfectly
Code:
Set Me.CmboAgent.Recordset = GetSQLRst("SELECT Db_ID, CONCAT(Db-ID, SPACE(3), Full_Nm) " & _
"FROM Dbo.vEmployee " & _
"WHERE Position LIKE 'CS1%' " & _
"ORDER BY Full_Nm")
I'm left to believe that the Db_ID being a PK in the source table for the view has something to do with this...but what?
Help??