Filter listbox based on subform 2

[FastLearnerIThink]

In one of my databases I have 3 or 4 combo box fields, which are located in a subform, that allow entries from the drop down list or by searching, When the find button is clicked, a search form opens up and populates based on the find button clicked. Example, when the Manufacturer find button is clicked, the listbox is populated with Manufacturers. The user then starts typing what to look for in a textbox and the list becomes smaller after each keystroke showing only entries containing the words being formed in the textbox. This works great, thanks to all the tips I have been able to find in this forum.

However, when I click the Model find button, I want the search box to show only the models related to the manufacturer. However, I have not being able to figure this out. When in the subform, the drop down list shows only the models related to the manufacturer, but when I click on the find button, I don't get the same results. With a WHERE statement in the search form (highlighted), I get no results, without it, I get all Models irregardless of manufacturer. But will only allow models related to the Manufacturer to be inserted into the subform. Would anyone please look at my code and advise what I may be overlooking?

Select Case ....

Case "IMod"
    Me.lstResults.ColumnCount = 3
    Me.lstResults.ColumnWidths = ".25 in; .25 in; 3.75 in"
    Me.lstResults.ColumnWidth = 4.5
    strSql = "SELECT qryModel.ModelID, qryModel.fldManufID, qryModel.fldModel FROM qryModel"
    strOrder = "ORDER BY qryModel.fldModel"
[red]    strWhere = strWhere & "WHERE qryModel.fldManufID = " & Forms![frmJobRep]![sfmJobRepDet].[Form]!cboManuf [/red]

Case ...
End Select

MsgBox "lstResults = " & strSql & " " & strOrder & " " & strWhere

    Me.lstResults.RowSource = strSql & " " & strOrder & " " & strWhere
    Me.txtEntriesCount = Me.lstResults.ListCount & " Entries."

Msgbox is only for test purposes and appears to be the correct SQL statement, however, no results appear in the listbox.

Any and all responses greatly appreciated. Please let me know if further information is required.

Thank you all.

-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno

 

[flyover789]

1. the WHERE clause should be before the ORDER BY clause.
2. to get the value of the cbobox on the subform use:

[Forms![frmJobRep]![sfmJobRepDet]!cboManuf.value

with [sfmJobRepDet] being the Name of the subform-control.

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[TheAceMan1]

How are ya FastLearnerIThink . . . . .
[ol][li][blue]strOrder[/blue] should come after [blue]strWhere[/blue] for proper syntax. OrderBy is always last.[/li]
[li]Is [blue]fldManufID[/blue] text or numeric?[/li]
[li]If [blue]cboManuf[/blue] is not the bound column of the combobox, you'll have to pin it down with:

[blue]cboManuf.Column(?)[/blue]

[/li][/ol]

See Ya! . . . . . .

 

[FastLearnerIThink]

Hello flyover89

Had already tried your suggested code without the .value. Not really required since it's numeric. But not putting the WHERE statement before the ORDER BY was the oversight. Did as you suggested and it is now resolved. Thanks! A star for you.

Hello AceMan1

As per your numbered suggestions and recommendations.
1. As mentioned to flyover, this was the oversight. Placed the WHERE before the ORDER BY and problem solved.
2. fldManufID is numeric so pinning down the column wasn't needed.

And of course, a star for you too since you both had the right answer. And now I can continue with the next step.

And a star to everyone that has taken the time to provide answers in the forum for the rest of us. Greatly appreciated!

-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno