inputing query with data from combobox in parentform to listbox in subform

Question

I have an ms-access db that has a form (FormA) with two sub forms (subFormB and subFormC). I am trying to use the value of a combobox (combo2) in FormA to get values (from a table) which will be input into subFormB and subFormC. i.e whatever values the user selects from combo2 will be used as a filter to query a table in the db and the values will be input into listboxs in either subFormB or subFormC

My code runs well with FormB but i cant seem to get it to work for FormC

The values that are displayed in combo2 depend on the value of another combo box (combo1).
If combo1 is "staff name" then the values in combo2 are string (names) and results are input into subFormB
If combo1 is "project name" then the values in combo2 are numeric (numbers)and results are input into subFormC

an example of my code is below

Private Sub Combo2_AfterUpdate()

If Combo1 = "Staff Name" Then     
subFormB.Visible = True
ltemp = "SELECT Staff.department"
ltemp = ltemp & " FROM Staff "
ltemp = ltemp & " WHERE Staff.staff_name = '" & combo2 & "' "
Me!subFormB.Form.List3.RowSource = ltemp


If Combo1 = "Project Number" Then
subFormC.visible = True         
TID = "SELECT Contracts.TargetIssueDate"
TID = TID & " FROM Contracts "
TID = TID & " WHERE Contracts.cms = combo2 "

Me!subFormC.Form.List25.RowSource = TID
End Sub

In other words the first part of my code works but the second part (starting from the second if statement) doesnt. i feel it is because the value of combo2 at this instance is numeric, and the problem is from the query but i dont know how to rewrite the query so that it would work.

HansUp HansUp · Accepted Answer · 2014-01-17T16:12:49

In the first query, the value of combo2 is included in the WHERE clause.

But the second query includes the combo's name instead of its value. In other words, this is the WHERE clause built by the code ...

WHERE Contracts.cms = combo2

In that situation, I would expect Access to treat combo2 as a parameter and ask you to supply a value for it. But you didn't mention that, so something more may be going on.

The code includes ...

If Combo1 = "Staff Name" Then

... but there is no closing End If later. Perhaps, you've shown us an abbreviated version of your actual code, and the actual version does include End If?

Rather than sorting out those details, I'll suggest a different approach. Make a backup copy of your db file. Use this query as the RowSource for List3 on subFormB.

SELECT Staff.department
FROM Staff
WHERE Staff.staff_name = Forms!FormA!combo2;

Then, in Combo2_AfterUpdate of FormA, just Requery the subform's listbox, or even the entire subform, instead of altering the listbox RowSource ... one of these two ...

Me!subFormB!List3.Requery 
Me!subFormB.Requery

If that approach is satisfactory, apply the same strategy to the other subform . And if it fails completely, revert back to your backup db.

inputing query with data from combobox in parentform to listbox in subform

1 Answers