Trouble changing the Row Source for a Combo Box Access 2007 - SQL 2000

[Fran Lombard]

Very strange since I can programatically (VBA) change the rowsource on other forms with no problems.

Access 2007 Project
SQL 2000 Server


The form is a datasheet representing purchase material detial lines. I'm recording material purchases (receipts) and associating a project code and a project phase code to the line item. What I want to do is limit the list of phases to only those that belong to the project of the same line. In the after_update event of the project code i'm
I'm attempting to modify the rowsource of the phase combox.

The row source as defined in the Forms editor is
Select * from ProjectPhaseQuery

The code in the used to modify the rowsource (called by the ProjectCode_AfterUpdate) is as follows

Private Sub SetProjectPhaseRowSource()
Dim mySql As String
Dim myProjectCode As Long

myProjectCode = Nz(Me.ProjectCode, 0)

mySql = " "
mySql = mySql & " SELECT * FROM ProjectPhaseQuery"
mySql = mySql & " Where ProjectID = " & myProjectCode

Me.ProjectPhase.RowSource = mySql

Debug.Print mySql

End Sub


I'm getting no rows in the drop down. The debug statement produces a query when run in sql analyzer runs fine and produces the desired result - it's just not showing in the combo.

Any ideas? I'm going crazing - many hours and no more hair.

 

[Access Developer]

A Combo Box in a Datasheet? Are you using a "Lookup Field" and trying to
change the Field defintion? If the ComboBox just appears in Datasheet View,
that's likely the case; if you had to place a Combo Box control on the Form,
then maybe it's not "datasheet view".

I'm thinking we may have some misunderstanding of terms.

Larry Linson
Microsoft Office Access MVP