Trying to get RowSource for a combo box based on the value of a different object

[tfurnivall]

Hi

I have a form with several objects:
Projects -> which have Sources -> which have SourceTypes

The underlying DB has tables for Projects, Sources and SourceTypes:

Table Projects;
Fields:
      ProjectID : AutoNumber;
      etc, etc

Table: Sources;
Fields:
      ID : Autonumber;
      ProjectID : Long;
      Sequence: Long;
      Name: String
      Type: SourceType       ' A different class, see below
      etc, etc

Table: SourceTypes;
Fields:
      ID : AutoNumber;
      Name: String;
      NameSpace : string;
      etc, etc

So, I'm trying to get all the Sources for a given ProjectID, and I'm using the following SQL statement:

SELECT ID, ProjectID, Sequence, Name from Sources WHERE [Sources].ProjectID= MyProject.ProjectID;

[I]Which actually gets coded (because MyProject.ProjectID is a variable) as:
[/I]
"SELECT ID, ProjectID, Sequence, Name from Sources WHERE [Sources].ProjectID= " & MyProject.ProjectID & ";"

What actually happens is that Access jumps in and says (via a Message Box) "Give me a value for Source.ProjectID.

I've just done that in the SQL statement!

How do I get a 'real' SELECT to work for the RowSource?

Tony (rapidly reducing his head to red pulp on the closest available wall)!

 

[CJ_London]

Looks like you are referring to the wrong object.

To clarify, you are building a sql string which is filtered by a control on your form. The control is a combobox which enables the user to select a projectID?

If so, then assuming your combo box has the name of SelectProject your code should be:

"SELECT ID, ProjectID, Sequence, Name from Sources WHERE [Sources].ProjectID= " & Me.SelectProject

Note you don't need the ";". Also your combobox should be unbound otherwise all you will be doing is changing the value for the current record

Also, I see you are using Name and Type as field names. These are reserved words and using them can produce unexpected results and errors - here is a link to Access reserved words. http://support.microsoft.com/kb/286335

I note this

Type: SourceType ' A different class, see below

If you have defined this in your table as a lookup, it will also cause you problems in the future - it should be SourceTypeID: Long

 

[tfurnivall]

Thanks, CJ;

Working from the bottom up:

Quote:
Type: SourceType ' A different class, see below
If you have defined this in your table as a lookup, it will also cause you problems in the future - it should be SourceTypeID: Long

My bad. I have three Classes: Project; ProjectSource and SourceType. The class definition of ProjectSource includes a SourceType object; the Project Source table (obviously) goes through a painful enumeration of the SourceType again! (Wouldn't it be nice if Access really were an object database!) The code example was using shorthand!

Also, I see you are using Name and Type as field names. These are reserved words and using them can produce unexpected results and errors - here is a link to Access reserved words. http://support.microsoft.com/kb/286335

I am using Name and Type as properties (ie code space names rather than data space names), not field names. (And on further checking, I have actually used SourceType in my code). My understanding is that the qualification (by the object variable name) will eliminate the possible confusion. However, I'm more than willing to change it, it just becomes excessively wordy very fast!

If so, then assuming your combo box has the name of SelectProject your code should be:

Code:
"SELECT ID, ProjectID, Sequence, Name from Sources WHERE [Sources].ProjectID= " & Me.SelectProject
Note you don't need the ";". Also your combobox should be unbound otherwise all you will be doing is changing the value for the current record

I find it hard to consider a SQL statement to be complete if it doesn't have a semi-colon! However, the reference value for Sources.ProjectID is a separate object (MyProjectID), which has a property called ProjectID. Hence the form of the SQL statement. I specifically do not want to use a control value as the reference value. I have it already, in an object!

Sorry to sound so frustrated, but I have read so many "Cascading ComboBoxes" articles, and none of them explain why it is so easy using a control as a reference value and so hard using anything else!

Tony

PS What a difference a meal makes!

Could it be that in order to construct a SQL statement as the RowSource for a ComboBox, the ReferenceValue in the 'WHERE' clause of the SQL Statement must be a 'field' in the combobox? In other words, the only SQL statement that one can use for a combobox is one that references the Bound Column of that combobox? I think that this is an implication of your suggestion, although I have not seen it stated quite so baldly!

 

[CJ_London]

Not sure I understand your thinking but if you have all these classes and not tables as you described in your original post, then in SQL you need to access them via a function to return the value you want. With regards your last sentence you can refer to other columns in combo boxes e.g.

myCombo.Column(0) refers to the first column

 

[tfurnivall]

CJ!

It's 0325 in London!
When do you sleep!

T

 

[tfurnivall]

I'm a little puzzled:

...if you have all these classes and not tables as you described in your original post, then in SQL you need to access them via a function to return the value you want.

Huh? I should be able to get the value of a property by simply referencing it, right? (I suppose that a Get Property is already a function - that's the argument I used to defend the use of Name as a property!) THe actual code for the Project class is:

Option Compare Database

Option Explicit

Private objProjectID As Long
Private objProjectName As String
Private objProjectDescription As String
Private objStartDate As Date
Private objEndDate As Date
Private objExtractStartParm As Date
Private objExtractEndParm As Date
Private objHasParticipantSearch As Boolean
Private objHasContentSearch As Boolean
Private objParticipantsFileName As String
Private objSearchTermsFileName As String
Private objLocationsFileName As String


Property Get ProjectID() As Long
ProjectID = objProjectID
End Property

Property Get ProjectName() As String
ProjectName = objProjectName
End Property

Property Let ProjectName(Value As String)
objProjectName = Value
End Property

Property Get ProjectDescription() As String
ProjectDescription = objProjectDescription
End Property

Property Let ProjectDescription(Value As String)
objProjectDescription = Value
End Property

Property Get StartDate() As Date
StartDate = objStartDate
End Property

Property Let StartDate(Value As Date)
objStartDate = Value
End Property

Property Get EndDate() As Date
EndDate = objEndDate
End Property

Property Let EndDate(Value As Date)
objEndDate = Value
End Property

Property Get ExtractStartParm() As Date
ExtractStartParm = objExtractStartParm
End Property

Property Let ExtractStartParm(Value As Date)
objExtractStartParm = Value
End Property

Property Get ExtractEndParm() As Date
ExtractEndParm = objExtractEndParm
End Property

Property Let ExtractEndParm(Value As Date)
objExtractEndParm = Value
End Property

Property Get HasParticipantSearch() As Boolean

HasParticipantSearch = objHasParticipantSearch

End Property

Property Let HasParticipantSearch(Value As Boolean)

objHasParticipantSearch = Value

End Property

Property Get HasContentSearch() As Boolean

HasContentSearch = objHasContentSearch

End Property

Property Let HasContentSearch(Value As Boolean)

objHasContentSearch = Value

End Property

Property Get ParticipantsFileName() As String
ParticipantsFileName = objParticipantsFileName
End Property

Property Let ParticipantsFileName(Value As String)
objParticipantsFileName = Value
End Property

Property Get SearchTermsFileName() As String
SearchTermsFileName = objSearchTermsFileName
End Property

Property Let SearchTermsFileName(Value As String)
objSearchTermsFileName = Value
End Property

Property Get LocationsFileName() As String
LocationsFileName = objLocationsFileName
End Property

Property Let LocationsFileName(Value As String)
objLocationsFileName = Value
End Property

'   Methods
Sub AddNew(rs As ADODB.Recordset, PName As String, PStart As Date)

With rs
    .AddNew
       ![ProjectName] = PName
       ![StartDate] = PStart
       ![Description] = ""
       ![EndDate] = 0
       ![HasParticipantSearch] = False
       ![HasContentSearch] = False
       ![ParticipantsFileName] = ""
       ![SearchTermsFileName] = ""
       ![LocationsFileName] = ""
    .Update
End With

End Sub

Sub Find(rs As ADODB.Recordset, IDValue As Long)

rs.MoveFirst
If rs.EOF Then
'  Not found! clear the Project fields
   objProjectID = 0
   ProjectName = ""
   ProjectDescription = ""
   StartDate = 0
   EndDate = 0
   ExtractStartParm = 0
   ExtractEndParm = 0
   ParticipantsFileName = ""
   SearchTermsFileName = ""
   LocationsFileName = ""
   HasParticipantSearch = False
   HasContentSearch = False
Else
   rs.Find "ID=" & Format(IDValue)
   LoadFromDatabase rs
End If

End Sub

Sub LoadFromDatabase(rs As ADODB.Recordset)

'   Put database fields into the object properties
With rs.Fields
     objProjectID = ![ID]
     ProjectName = ![ProjectName]
     StartDate = ![StartDate]
     EndDate = ![EndDate]
     ProjectDescription = ![Description]
     HasParticipantSearch = ![HasParticipantSearch]
     HasContentSearch = ![HasContentSearch]
     ParticipantsFileName = ![ParticipantFileName]
     SearchTermsFileName = ![contentFileName]
     LocationsFileName = ![LocationsFileName]
End With

End Sub

Sub Update(rs As ADODB.Recordset)

With rs.Fields
     ![ProjectName] = ProjectName
     ![StartDate] = StartDate
     ![EndDate] = EndDate
     ![ExtractStartParm] = ExtractStartParm
     ![ExtractEndParm] = ExtractEndParm
     ![Description] = ProjectDescription
     ![HasParticipantSearch] = HasParticipantSearch
     ![HasContentSearch] = HasContentSearch
     ![ParticipantFileName] = ParticipantsFileName
     ![contentFileName] = SearchTermsFileName
     ![LocationsFileName] = LocationsFileName
End With
rs.Update

End Sub

Sub Initialize()

End Sub

It's about 2 iterations into development - not any need for Project Status, yet - I'm still trying to get it off the ground!

T