combo box requery/linking (1 Viewer)

[RpbertS]

Hi on a continuous subform I have two combo boxes, projectname and subprojectname, these are linked to 2 tables and based on what project is selected I want only the certain subprojects associated with that project to be available in the subprojectname combobox.
this is my code so far
-----------
Private Sub RefreshSubProj()

cboSubprojects.RowSource = "SELECT Subprojects.cbosubprojects " & _
"FROM Subprojects WHERE (((Subprojects.cboProjectName)='" & _
cboProjectName.Value & "'));"
cboSubprojects.Requery
If cboSubprojects.ListCount = 0 Then
cboSubprojects.Value = ""
Else
cboSubprojects.SetFocus
cboSubprojects.Dropdown
End If
End Sub
------------
this is what happens it will work for the first record in the subform and allow you to pick the correct subproject BUT for every other project underneath it the only subprojects that are available are the ones for the first record, and if I try to change the projectname combo box it asks for a parameter for subprojectname.

does anyone have any ideas? or can point me in the right direction?

thanks for the time,
Rpb

 

[Travis]

Let me guess here you are primarily a VB programmer?

The solution I believe will be easy, (In fact you may kick yourself)

change this:
------------
cboSubprojects.RowSource = "SELECT Subprojects.cbosubprojects " & _
"FROM Subprojects WHERE (((Subprojects.cboProjectName)='" & _
cboProjectName.Value & "'));"

to this:
--------
cboSubprojects.RowSource = "SELECT Subprojects.cbosubprojects " & _
"FROM Subprojects WHERE (((Subprojects.cboProjectName)='" & _
cboProjectName & "'));"


Even though the Value property is available I find that it does not work as one would expect it to from the VB world. Some limitations and differences we must all except unfortunatly until VB and VBA are actually the same thing.

Hope this helps. (PS I also find the .Text does not always do what I want)

 

[RpbertS]

ohh man travis thanks, I was assigned to work on something else and was just getting back to this today and realized I had a question out here, thanks it helped a great deal..made me feel stupid but still helped =)

thanks again,
Rpb