Printing results of a list box (which are tied to combo boxes)

[jlmeek]

Any help would be much appreciated. I have a form with two combo boxes (campus and provider) and a list box. The user chooses his/her criteria from the combo boxes and after update displays the results in a list box. How can I, if it's even possible, print the results displayed in a list box?

 

[THoey]

It sounds like you already have a query that displays the results in the listbox. Why don't you put a button on the form that runs a report based on that same query? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[jlmeek]

Terry,
Thanks for the suggestion but I tried that last week and it doesn't work. It's as though the query never changes except on the form. Even though the user is making choices it is not updating the query to reflect the criteria (if that makes any sense at all).

-jess

 

[THoey]

Do you remember what your code was for the button? Why don't you list the SQL for the query and the names of your comboboxes and I bet we could get it working for you. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[jlmeek]

Thanks Terry,
Below see my SQL statement, combo box names, and the vba code for the combo boxes.

SQL Statement:

SELECT DISTINCT [qrycombo1].[ISSN], [qrycombo1].[Title], [qrycombo1].[Provider], [qrycombo1].[Frequency], [qrycombo1].[LC], [qrycombo1].[Authoritative], [qrycombo1].[Content] FROM qrycombo1 WHERE ((([qrycombo1].[Provider])=[forms]![frmsearch]![cboprovider]) And (([qrycombo1].[Campus])=[forms]![frmsearch]![cbocampus])) ORDER BY [Title];

Combo boxes:

cboprovider
cbocampus

VBA code for the combo boxes:

Option Compare Database
Option Explicit

Private Sub cbocampus_AfterUpdate()
Forms!frmsearch.TitleInformation.Requery

End Sub


Private Sub cboprovider_AfterUpdate()
Forms!frmsearch.TitleInformation.Requery
End Sub

Private Sub cmdCloseSearch_Click()
On Error GoTo Err_cmdCloseSearch_Click


DoCmd.Close

Exit_cmdCloseSearch_Click:
Exit Sub

Err_cmdCloseSearch_Click:
MsgBox Err.Description
Resume Exit_cmdCloseSearch_Click

End Sub

 

[THoey]

I really think the button would work. Add it, and then in the OnClick property add something like the following code:

    Dim stSQL As String

    stSQL = "SELECT DISTINCT [qrycombo1].[ISSN], [qrycombo1].[Title], " 
    stSQL = stSQL & "[qrycombo1].[Provider], [qrycombo1].[Frequency], "
    stSQL = stSQL & "[qrycombo1].[LC], [qrycombo1].[Authoritative], "
    stSQL = stSQL & "[qrycombo1].[Content] FROM qrycombo1 "
    stSQL = stSQL & "WHERE ((([qrycombo1].[Provider])= '" 
    stSQL = stSQL & [forms]![frmsearch]![cboprovider]) 
    stSQL = stSQL & "' And (([qrycombo1].[Campus])= '" 
    stSQL = stSQL & [forms]![frmsearch]![cbocampus]
    stSQL = stSQL & "')) ORDER BY [Title];"

    DoCmd.RunSQL stSQL

I think that will do it. The hard for me is remembering how to do the single quotes inside the strings double quotes...

Hope that helps... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.