Brian, thanks for replying. Can you bear with me and let me make sure that I
understand the logic behind what I'm doing?
*QUERY1 - Obtains a list of city names and corresponding state [mcity]
[mstate]from table CUSTOMERINFO
*QUERY2 - Asks for city & state [mcity] [mstate] and brings up record from
table CUSTOMERINFO. Used by REPORT2
*REPORT2 - Uses QUERY2 to display the given record in a certain format.
*FORM7
-Has a listbox (LIST11) that obtains records from the table CUSTOMERINFO
through QUERY1
Properties:
Row Source Type is set as Table/Query
Row Source is SELECT [Query1].MState, [Query1].MCity FROM [Query1];
Bound Column 1
-Has a command button SUBMIT7
Properties:
ON CLICK Event Procedure - Right now it brings up the REPORT2. The code that
corresponds to the SUBMIT7 button looks like this:
Private Sub SUBMIT7_Click()
On Error GoTo Err_SUBMIT7_Click
Dim stDocName As String
stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
(After reading your email I commented out the above command and tried
Dim stRepName As String stDocName = "REPORT2"
DoCmd.OpenReport stRepName, acPreview, , "[MonitorCity]='" & Me![List11] & "'"
It brings up the report but I get an error message displayed on the report
where the data should be. Obviously I don't know what I'm doing)
Exit_SUBMIT7_Click:
Exit Sub
Err_SUBMIT7_Click:
MsgBox Err.Description
Resume Exit_SUBMIT7_Click
End Sub
I want to run FORM7, which displays a list of cities and corresponding
states (this part works fine). The person chooses the record they want to
look at and clicks on the SUBMIT button. At this point, using the code that
you suggested,the form brings up the report, but obviously the data is not
being passed to the report correctly, as there is an error.
I hope this makes sense. Thanks!
Brian Bastl said:
J.E.,
What is the bound column of your ListBox? Is the bound column numeric or
text? What is its row source?
You can either use the where condition in the query of the report's record
source:
SELECT Whatever, City, State, ... FROM YourTable _
WHERE (((YourTable.City) = Forms!YourForm!YourListBox));
or you can use it within the command button code to open the report.
If bound column is numeric, then the following would be what you're after.
DoCmd.OpenReport "Report", acPreview,,"[City]=" & Me![Your City Combo]
Otherwise, if it's text, then you need to change it slightly to:
DoCmd.OpenReport "Report", acPreview,,"[City]='" & Me![Your City Combo] &
"'"
exploded would look like = "[City]= ' " & Me![Your City Combo] & " ' "
Post back if you need additional help.
Brian
J.E. Armstrong said:
I have a form with a list box (a query is attached to this list box via the
RowSource in the ListBox properties). The query works fine - it brings up all
the cities and corresponding states as needed. I want the form to allow
someone the ability to choose a state & city of their choice - then this data
needs to be passed on to a report (which will use the data for a
corresponding state & city query). For the life of me, I can't find out how
to do this using the online help or tutorials. I have played with the
command buttons, read about all the wrong things and hope someone can point
me in the right direction.
Any advice or keywords would be appreciated.
Thanks!
