Combo Box on Sub Form won't work

[niccig]

Ok, I created a form[frmSubhours] that has 2 cascading combo boxes (cboFaculty and cboProject). Everything works fine, thanks to previous posts about cascading combo boxes. But now I want this form to be a sub form on [frmPayDetails], and it won't work, I get an error message asking to enter the Parameter value for Forms!frmSubhours!cboFaculty that is used in the row source for cboProject. So I think that as it's now on a subform, I must change how I've referred to cboFaculty in this query, but I don't know how. Please help as I've nearly completed the database. Can you also tell me any other changes I may need to make to get the sub form to work eg. do I need to change the other coding that maks the cascading combo boxes work? Thanks in advance.

Here are the details:

cboFaculty is based off a query from the tbFaculty, you select the faculty name, and this filters cboProject to only show projects for that faculty member from tbProject.

cboProject row source:

SELECT tbProject.ProjectID, tbProject.ProjectName, tbProject.CurrentProject FROM tbProject WHERE (((tbProject.CurrentProject)=Yes) And ((tbProject.FacultyID)=Forms!frmSubhours!cboFaculty)) ORDER BY tbProject.ProjectName;

The code source for frmSubhours is

Private Sub cboFaculty_AfterUpdate()
Me.cboProject.Requery
Me!cboProject.SetFocus
End Sub

Private Sub Form_Current()
Me.cboProject.Requery
End Sub

 

[llkhoutx]

You code

SELECT tbProject.ProjectID, tbProject.ProjectName, tbProject.CurrentProject FROM tbProject WHERE (((tbProject.CurrentProject)=Yes) And ((tbProject.FacultyID)=Forms!frmSubhours!cboFaculty)) ORDER BY tbProject.ProjectName;


is incorrect, specifically the WHERE clause. The "Yes" and Forms!frmSubhours!cboFaculty are parameters, not fields. The parameters must be coded as such, e.g.

WHERE (((tbProject.CurrentProject)=" & -1 & ") And ((tbProject.FacultyID)=" & Forms!frmSubhours!cboFaculty & "))"

assuming both PacultyID and cboFaculty are numbers.

 

[niccig]

Still not quite right

I replaced my WHERE code with yours, and I get a syntax string error. The code now reads:

SELECT tbProject.ProjectID, tbProject.ProjectName, tbProject.CurrentProject FROM tbProject WHERE (((tbProject.CurrentProject)=" & -1 & ") And ((tbProject.FacultyID)=" & Forms!frmSubhours!cboFaculty & "))" ORDER BY tbProject.ProjectName;

What am I still doing wrong? I don't know much about code (as you can probably tell!

 

[niccig]

Numbers?

Faculty ID is a number
cbo Faculty - first column is the faculty ID which has a width of 0, so you only see the Faculty name that's in the second column.

cboFaculty's row source is a query from tbFaculty, and once selected I've stored the faculty number in the facultyID field in tbHours. FrmSubhours is based off tbHours?

Does this make any difference?

 

[WayneRyan]

niccig,

SQL = "SELECT ProjectID, " & _ 
      "       ProjectName, " & _
      "       CurrentProject " & _
      "FROM   tbProject " & _
      "WHERE  CurrentProject = -1  And " & _
      "       FacultyID = " & Forms!frmSubhours!cboFaculty & " "  & _
      "ORDER BY ProjectName;"

Wayne

 

[niccig]

Where do I paste that?

Wayne, do I paste that code into the row source in Properties?

 

[WayneRyan]

niccig,

Yes, that's where it goes. But wait! I was just correcting syntax. I just
read the thread and the problem seems to be:

"I get an error message asking to enter the Parameter value for
Forms!frmSubhours!cboFaculty"

If that's the case then: Forms!frmSubhours!cboFaculty is not the name of
your combo-box.

The reason that Access asks for the parameter is because it can't resolve
Forms!frmSubhours!cboFaculty. There is nothing by that name. Check it
to see that your combo isn't named something like "Combo12".

Wayne

 

[niccig]

Maybe a little closer

I now have this code:

 

[WayneRyan]

niccig:

We appear to be in worse shape now!

Wayne

 

[niccig]

A LITTLE CLOSER..maybe???

this is what I have now, and I get a data mismatch error message:

SELECT ProjectID, ProjectName, FacultyID, CurrentProject FROM tbProject WHERE CurrentProject= -1 And FacultyID = " &Forms!Subhours!cboFaculty.column1 & " ORDER by ProjectName;

Now CurrentProject is a check box, yes or no field.
Faculty ID in tbProject is a number.
cboFacutly has 2 columns, the first is FacultyID (a number) and the 2nd is the FacultyName (text).

Please keep helping...

 

[WayneRyan]

niccig,

OK now!

.Column(0)

They start numbering them at 0. You have .Column(0) and .Column(1)

Wayne

 

[niccig]

combo box name

Wayne,
the combo box is called cboFaculty - I checked in the "other tab" in Properties window.

Now my code and combo box work perfectly on a form called frmSubhours. It's when I try and make frmSubhours be a sub from on frmPayDetails that I started to get all types of problems..

Any ideas???

 

[WayneRyan]

niccig,

We're rolling right through this now!

Below is a link from the FAQ section here, Mile shows the proper syntax
for subform controls.

http://www.access-programmers.co.uk/forums/showthread.php?t=66115

Wayne

 

[niccig]

so far

Wayne,
Now it's this code, but I still get data mismatch error. AAAAAAAAAAAGH!!

SELECT ProjectID, ProjectName, FacultyID, CurrentProject FROM tbProject WHERE CurrentProject= -1 And FacultyID = " &Forms!Subhours!cboFaculty.Column(0) & " ORDER by ProjectName;

It's really late here in LA, and I've got to go home (husband isn't too happy I'm still here)...can I email you on this tomorrow? This is helping me.

 

[WayneRyan]

Sure,

It's late here in Camarillo too.

Post a sample if you can, and we should be able to fix it tomorrow.

See ya,
Wayne

 

[niccig]

thanks

thanks Wayne...i'm pulling my hair out here.....I'll look through the proper syntax for subforms and post a sample tomorrow - won't be until after 3pm LA time though.
Thanks again...I really appreciate it.
Good night....