Query works one way but not the other

[RunTime91]

Okay, this is beyond insane

I'm using a passthrough query to populate a combobox

The following queries work perfectly.

**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

** 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

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??

[pbaldy]

If this is copy/pasted, you've got a dash instead of an underscore:

SELECT Db_ID, CONCAT(Db-ID,

What is the actual SQL of what works when you "run this query directly in SQL"?

[RunTime91]

Hey Paul ~ Glad you're out there

Yeah I copy/pasted then screwed up the edit

Below is the exact query that works in SQL

SELECT Db_ID, CONCAT(Db_ID, SPACE(3), Full_Nm) AS FullName
FROM Dbo.vEmployee
WHERE Position LIKE 'CS1%'
ORDER BY Full_Nm

Below is the query I'm trying to get to work in Access

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")

And below is the query that I was using in Access until I discovered the table I need to extract data from on the AfterUpdate Sub Routine of the CmboBox, doesn't have an Emp_ID Field

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")

One other item of note that may or may not make a difference - The Emp_ID's are all the same text length (8) Whereas the Db_ID's can be either 6 or 8

But as stated above the code that doesn't run in Access runs perfectly in SQL



Thanks Paul

[pbaldy]

Are you getting an error, or no results? This is a pass through to SQL Server? The function opens and returns a recordset using the pass through?

[RunTime91]

Oh Geez - This just keeps getting better (weirder!)

So the below two queries run perfect in Access as individual queries

Set Me.CmboAgent.Recordset = GetSQLRst("SELECT Db_ID " & _
                                            "FROM Tri.vEmployee " & _
                                            "WHERE Position LIKE 'CS1%' " & _
                                            "ORDER BY Db_ID")

Set Me.CmboAgent.Recordset = GetSQLRst("SELECT Full_Nm " & _
                                            "FROM Tri.vEmployee " & _
                                            "WHERE Position LIKE 'CS1%' " & _
                                            "ORDER BY Full_Nm")

Now, when I combine them (as in the query below) - it throws a "Property not found" error when I click the dropdown arrow on the ComboBox -

However when I click the OK button on the error then click the dropdown arrow again the error doesn't come back - But the ComboBox is empty

Set Me.CmboAgent.Recordset = GetSQLRst("SELECT Db_ID, Full_Nm " & _
                                            "FROM Tri.vEmployee " & _
                                            "WHERE Position LIKE 'CS1%' " & _
                                            "ORDER BY Full_Nm")

So frustrating

[RunTime91]

Hey Paul - Yes this is a passthrough using a function

And yes, I just threw an error - posted above...

But after playing around with it - I was able to make the error dissappear by changing the Bound column from 1 to 2 which is away from the Db_ID field in the ComboBox

However that still didn't populate the ComboBox

[RunTime91]

Found the issue!!

The Function GetSQLRst has the following code:

            If ReadOnly Then
                .LockType = adLockReadOnly
            Else
                .LockType = adLockOptimistic
            End If

So what I'm assuming is since Db_ID is a Primary Key that code is locking that field and thus won't allow it to populate the ComboBox
Once I commented that section of code out of the Function - The code with the Db_ID ran perfectly.

Now, with that figured out - I wonder what the danger is to just leaving that section commented out?

[pbaldy]

Good job! Looks like ADO, which I don't use much. I guess you'd lose the ability to toggle the LockType property. If you commented that out I assume you're getting whatever the default is.

[RunTime91]

Since I wasn't the original author of that particular function, I decided to just re-write it as a new function without the Boolean section.
My use doesn't require protecting against changing a read only value. Works perfectly.

Paul ~ As always... Thank You, Sir...

[pbaldy]

I appreciate the thanks, but you did all the work.