Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can you do a lookup list for a parameter query? 2
- Thread starter aageorge
- Start date
-
2
- #2
Really the only way to do this is to design a seperate small form that has a combobox or listbox loaded with your line names. After the selection and a maybe a click of the print report button the report is opened with a query as its recordsource. The query has SQL code that refers directly back to this form for its parameter for selection.
EXAMPLE SQL:
Select A.*
FROM tblYourTableName as A
WHERE A.YourFieldName = FORMS![frmYourPopupFormName]![cboComboBoxName];[/B]
Post back with questions on setting up this process.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
EXAMPLE SQL:
Select A.*
FROM tblYourTableName as A
WHERE A.YourFieldName = FORMS![frmYourPopupFormName]![cboComboBoxName];[/B]
Post back with questions on setting up this process.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
- Thread starter
- #3
Bob, This is my setup
I have a report called "PPMH report". It is based on a query called "PPMH query" that asks for a line name before it runs. The lines are stored in a table called "Production Summary" under the "Line" field.
So the question how do I setup the entire process? Thanks...
I have a report called "PPMH report". It is based on a query called "PPMH query" that asks for a line name before it runs. The lines are stored in a table called "Production Summary" under the "Line" field.
So the question how do I setup the entire process? Thanks...
Please post your query used in your report and detail the field structure of your table. I will then lead you through the process of setting up the process.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
- Thread starter
- #5
I have the following tables:
Production Summary Efficiency Table
ID - Autonumber(Primary) Line - Text (Primary)
Date - Date/Time Manpower - Number
Shift - Text(Lookup) 110% - Number
Line - Text(Lookup) 135% - Number
Hrs Run - Number PPMH - Number
Hrs Scheduled - Number
Pieces Run - Number
I have the following Make-Table Query:
PPMH Query
Field Table Criteria
Date Production Summary
Line Production Summary [Enter the Line]
Shift Production Summary
PPMH Efficiency Table
Expr1:
Expr2: <7
Bob, I hope this is what you need. I use the table generated by this query called "PPMH Table" to generate the report.
Production Summary Efficiency Table
ID - Autonumber(Primary) Line - Text (Primary)
Date - Date/Time Manpower - Number
Shift - Text(Lookup) 110% - Number
Line - Text(Lookup) 135% - Number
Hrs Run - Number PPMH - Number
Hrs Scheduled - Number
Pieces Run - Number
I have the following Make-Table Query:
PPMH Query
Field Table Criteria
Date Production Summary
Line Production Summary [Enter the Line]
Shift Production Summary
PPMH Efficiency Table
Expr1:
Expr2: <7
Bob, I hope this is what you need. I use the table generated by this query called "PPMH Table" to generate the report.
Okay, let's start by creating a new form.
1. Open a form in design mode(New). Do not select a table or query. This will be an UNBOUND form. Create a ComboBox on the form and call it cboLineCombo. Open the combobox's property list. Select Efficiency Table for the Row Source. set the Bound Column to 1, Column Count = 1, Column Widths = ?? However wide you need it to be to display the Line data. Just enter it as a number in inches. Set Scroll Bars= Neither, AutoCenter= Yes, Modal= No, Popup= No, Navigation Buttons = No. Close and Save the form and call it frmLinePrompt.
2. Create a command button and call it cmbOKButton. Enter Open Report in the Caption Property or whatever you want it to say. Size and place the button on the form where you wish.
3. Put this code in the OnClick Event Procedure of the command button.
DoCmd.OpenReport "rptYourReportName", acViewNormal
4. Use this as an example of the SQL to be placed in your query:
Select A.*
FROM [Production Summary] as A
WHERE A.Line = FORMS![frmLinePrompt]![cboLineCombo];
Use this example to update the WHERE clause in the query that feeds the report. I don't think I have left anything out but if I have please post back with questions.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
1. Open a form in design mode(New). Do not select a table or query. This will be an UNBOUND form. Create a ComboBox on the form and call it cboLineCombo. Open the combobox's property list. Select Efficiency Table for the Row Source. set the Bound Column to 1, Column Count = 1, Column Widths = ?? However wide you need it to be to display the Line data. Just enter it as a number in inches. Set Scroll Bars= Neither, AutoCenter= Yes, Modal= No, Popup= No, Navigation Buttons = No. Close and Save the form and call it frmLinePrompt.
2. Create a command button and call it cmbOKButton. Enter Open Report in the Caption Property or whatever you want it to say. Size and place the button on the form where you wish.
3. Put this code in the OnClick Event Procedure of the command button.
DoCmd.OpenReport "rptYourReportName", acViewNormal
4. Use this as an example of the SQL to be placed in your query:
Select A.*
FROM [Production Summary] as A
WHERE A.Line = FORMS![frmLinePrompt]![cboLineCombo];
Use this example to update the WHERE clause in the query that feeds the report. I don't think I have left anything out but if I have please post back with questions.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
- Thread starter
- #7
Bob,
I did everything you asked. Unfortunately when I click on the command report button the report dosen't open. I doublechecked the code and it seems fine. I also tried selecting a line from the combo box and then opening the report from the database window, it works fine and opens the report corresponding to the selection in the combo box. Based on this I think there is something wrong with code in the command button.
BTW I have a macro on the on open event and on close event for the report. The on open event is setwarnings=false, open query, maximize. The on close event is restore, setwarnings = true. Thanks for all your help so far.
I did everything you asked. Unfortunately when I click on the command report button the report dosen't open. I doublechecked the code and it seems fine. I also tried selecting a line from the combo box and then opening the report from the database window, it works fine and opens the report corresponding to the selection in the combo box. Based on this I think there is something wrong with code in the command button.
BTW I have a macro on the on open event and on close event for the report. The on open event is setwarnings=false, open query, maximize. The on close event is restore, setwarnings = true. Thanks for all your help so far.
Post back with the VBA code from OnClick event procedure as you have it in your button.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
- Thread starter
- #9
Great. I wasn't sure how you wanted the output. Glad it is working for you. Thanks for the Star. Much appreciated.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
Bob, inregards to the same issue:
I also have a query with a column called Title. A report is based on this query. When the report is opened I need a parameter to come up asking which Title they would like a report on. Then the combo box will open and they have their choices.
So, following your instructions above, I made an unbound form and a combo box called ComboCode. I set my rowsource to TBL-ILCode. The title column is actually the second column (the first being the autonumber) so I set the bound column to 2. When I open the form, it is not pulling the titles into the combobox. I can do instructions 2 and 3 above, but do not understand how to do instruction 4. I am assuming this is how to put the parameter into my query telling it to seek the combobox for the value.
Any suggestions? Thanks for the help, JL
I also have a query with a column called Title. A report is based on this query. When the report is opened I need a parameter to come up asking which Title they would like a report on. Then the combo box will open and they have their choices.
So, following your instructions above, I made an unbound form and a combo box called ComboCode. I set my rowsource to TBL-ILCode. The title column is actually the second column (the first being the autonumber) so I set the bound column to 2. When I open the form, it is not pulling the titles into the combobox. I can do instructions 2 and 3 above, but do not understand how to do instruction 4. I am assuming this is how to put the parameter into my query telling it to seek the combobox for the value.
Any suggestions? Thanks for the help, JL
As far as the combobox is concerned check your column count and column widths. The count should be 2 and the column widths should be 0";3" . You see you only want to see the Title so make the first column(autonumber) 0" wide. Now you should see the Titles. Are they sorted properly? If not create a query with just the titles and sort them ascending so the user can find them quickly by typing the first few characters. This query should be saved and entered as the RowSource. You could leave the autonumber off in this query and just have one column. This would require that the Bound Column be changed to 1 and the 0" column width be removed.
Now as far as the RecordSource of your Report goes it should be a query such as the following:
Post back with any further questions.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
Now as far as the RecordSource of your Report goes it should be a query such as the following:
Code:
Select A.*
FROM [[red]YourTableName[/red]] as A
WHERE A.[Title] = FORMS![[red]YourTitleComboBoxFormName[/red]]![cboTitleCombo];Post back with any further questions.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
Well, I must have something really messed up. I went back and looked at the column I want the Combo box to fetch and actually it was the third column. So, this is what I have in the properties box:
Name: ComboCode
Control Source: Blank
Format: Blank
Decimal Places: Auto
Input Mask: Blank
Row Source Type: Table/Query
Row Source: TBL-ILCode
Column Count: 3
Column Heads: No
Column Widths: 0";0";3"
Bound Column: 3
List Rows: 8
List Width: Auto
Can you tell from that what I problem is? Thanks for the help, JL
Name: ComboCode
Control Source: Blank
Format: Blank
Decimal Places: Auto
Input Mask: Blank
Row Source Type: Table/Query
Row Source: TBL-ILCode
Column Count: 3
Column Heads: No
Column Widths: 0";0";3"
Bound Column: 3
List Rows: 8
List Width: Auto
Can you tell from that what I problem is? Thanks for the help, JL
Well that looks okay. Just what is the problem? Nothing is showing up in the combobox? Are you clicking the little arrow to show the possible titles? Sorry, just had to ask that one. If you still can't get it to work why don't you send me a representative sample of the table and the form and let me take a look. My email is in my profile.
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???
- Status
Similar threads
- Locked
- Question
