If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
form and query and dropdown
Hi all..
Here is what I have: Main table that stores the information overpayment table that is a linked to main table via look up wizard query that pulls all the overpayment types that have been listed I have 8 queries one for each type of overpayment What I would like to do is have a form that does this: User has a drop down list of all the overpayment types (i am assuming it would be linked to the overpayment cell in the maintable?) Once the selection is picked and they a preview report button is shows just that overpayment type in a report. Can this be done? If so how?? R~ |
#2
|
|||
|
|||
form and query and dropdown
You only need one query that will allow all overpayment types.
Your drop down (Combo Box) would not need to be a bound control. It would only need a row source that would contain all the types. You could use a Value List Row Source if all you will ever have is those 8 types, or you could create a table that contains all the types and use it as a rowsource. Once the user has selected a type from the Combo, use The Where argument of the OpenReport method to filter the report. DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" & Me.cboOverPayment [OverPaymentType] is the name of the field (There are no Cells in Access) in your table that has the types. Me.cboOverPayment is the name of the Combo box where the user selects the types. I made the names up, you will have to substitute your own. If you want the report to preview as soon as the user selects a type, put the OpenReport in the After Update event of the combo box. "Rhett_Y" wrote: Hi all.. Here is what I have: Main table that stores the information overpayment table that is a linked to main table via look up wizard query that pulls all the overpayment types that have been listed I have 8 queries one for each type of overpayment What I would like to do is have a form that does this: User has a drop down list of all the overpayment types (i am assuming it would be linked to the overpayment cell in the maintable?) Once the selection is picked and they a preview report button is shows just that overpayment type in a report. Can this be done? If so how?? R~ |
#3
|
|||
|
|||
form and query and dropdown
Thanks for the help, but I am getting errors when I plug in your code. You
spot on with the names of the fields in the table and the name of the cbo. I just had to change the rpt name. But I am still getting errors. I am getting a yellow highlight ('") here and the Me.cboOverPayment. Any Suggetstions, and thanks again! R~ "Klatuu" wrote: You only need one query that will allow all overpayment types. Your drop down (Combo Box) would not need to be a bound control. It would only need a row source that would contain all the types. You could use a Value List Row Source if all you will ever have is those 8 types, or you could create a table that contains all the types and use it as a rowsource. Once the user has selected a type from the Combo, use The Where argument of the OpenReport method to filter the report. DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" & Me.cboOverPayment [OverPaymentType] is the name of the field (There are no Cells in Access) in your table that has the types. Me.cboOverPayment is the name of the Combo box where the user selects the types. I made the names up, you will have to substitute your own. If you want the report to preview as soon as the user selects a type, put the OpenReport in the After Update event of the combo box. "Rhett_Y" wrote: Hi all.. Here is what I have: Main table that stores the information overpayment table that is a linked to main table via look up wizard query that pulls all the overpayment types that have been listed I have 8 queries one for each type of overpayment What I would like to do is have a form that does this: User has a drop down list of all the overpayment types (i am assuming it would be linked to the overpayment cell in the maintable?) Once the selection is picked and they a preview report button is shows just that overpayment type in a report. Can this be done? If so how?? R~ |
#4
|
|||
|
|||
form and query and dropdown
I have it setup now but it will only pull from the "Chief" rpt...
Private Sub cmdView_Click() On Error GoTo Err_cmdView_Click Dim stDocName As String stDocName = "Chief" DoCmd.OpenReport stDocName, acPreview Exit_cmdView_Click: Exit Sub Err_cmdView_Click: MsgBox Err.Description Resume Exit_cmdView_Click End Sub "Klatuu" wrote: You only need one query that will allow all overpayment types. Your drop down (Combo Box) would not need to be a bound control. It would only need a row source that would contain all the types. You could use a Value List Row Source if all you will ever have is those 8 types, or you could create a table that contains all the types and use it as a rowsource. Once the user has selected a type from the Combo, use The Where argument of the OpenReport method to filter the report. DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" & Me.cboOverPayment [OverPaymentType] is the name of the field (There are no Cells in Access) in your table that has the types. Me.cboOverPayment is the name of the Combo box where the user selects the types. I made the names up, you will have to substitute your own. If you want the report to preview as soon as the user selects a type, put the OpenReport in the After Update event of the combo box. "Rhett_Y" wrote: Hi all.. Here is what I have: Main table that stores the information overpayment table that is a linked to main table via look up wizard query that pulls all the overpayment types that have been listed I have 8 queries one for each type of overpayment What I would like to do is have a form that does this: User has a drop down list of all the overpayment types (i am assuming it would be linked to the overpayment cell in the maintable?) Once the selection is picked and they a preview report button is shows just that overpayment type in a report. Can this be done? If so how?? R~ |
#5
|
|||
|
|||
form and query and dropdown
I'm getting a little confused. It is pulling Chief because you explicitly
assign the value "Chief" to stDocName just before you do the OpenReport. If you are wanting to choose the report name in the combo, then it needs to be: stDocName = Me.cboOverpayment (or whatever your combo is named) "Rhett_Y" wrote: I have it setup now but it will only pull from the "Chief" rpt... Private Sub cmdView_Click() On Error GoTo Err_cmdView_Click Dim stDocName As String stDocName = "Chief" DoCmd.OpenReport stDocName, acPreview Exit_cmdView_Click: Exit Sub Err_cmdView_Click: MsgBox Err.Description Resume Exit_cmdView_Click End Sub "Klatuu" wrote: You only need one query that will allow all overpayment types. Your drop down (Combo Box) would not need to be a bound control. It would only need a row source that would contain all the types. You could use a Value List Row Source if all you will ever have is those 8 types, or you could create a table that contains all the types and use it as a rowsource. Once the user has selected a type from the Combo, use The Where argument of the OpenReport method to filter the report. DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" & Me.cboOverPayment [OverPaymentType] is the name of the field (There are no Cells in Access) in your table that has the types. Me.cboOverPayment is the name of the Combo box where the user selects the types. I made the names up, you will have to substitute your own. If you want the report to preview as soon as the user selects a type, put the OpenReport in the After Update event of the combo box. "Rhett_Y" wrote: Hi all.. Here is what I have: Main table that stores the information overpayment table that is a linked to main table via look up wizard query that pulls all the overpayment types that have been listed I have 8 queries one for each type of overpayment What I would like to do is have a form that does this: User has a drop down list of all the overpayment types (i am assuming it would be linked to the overpayment cell in the maintable?) Once the selection is picked and they a preview report button is shows just that overpayment type in a report. Can this be done? If so how?? R~ |
#6
|
|||
|
|||
form and query and dropdown
That is where I was getting confused. I want the list to be of the forms
that one can pick to print. That is all....... I created the one form that runs of a query. I want the person to be able to pick what they want to filter on whether it be: time keeper error/wrong promotion date etc. Once they pick the item then hit preview and the report shows up. That is all I am trying to do...........Hope this makes sense.... R~ "Klatuu" wrote: I'm getting a little confused. It is pulling Chief because you explicitly assign the value "Chief" to stDocName just before you do the OpenReport. If you are wanting to choose the report name in the combo, then it needs to be: stDocName = Me.cboOverpayment (or whatever your combo is named) "Rhett_Y" wrote: I have it setup now but it will only pull from the "Chief" rpt... Private Sub cmdView_Click() On Error GoTo Err_cmdView_Click Dim stDocName As String stDocName = "Chief" DoCmd.OpenReport stDocName, acPreview Exit_cmdView_Click: Exit Sub Err_cmdView_Click: MsgBox Err.Description Resume Exit_cmdView_Click End Sub "Klatuu" wrote: You only need one query that will allow all overpayment types. Your drop down (Combo Box) would not need to be a bound control. It would only need a row source that would contain all the types. You could use a Value List Row Source if all you will ever have is those 8 types, or you could create a table that contains all the types and use it as a rowsource. Once the user has selected a type from the Combo, use The Where argument of the OpenReport method to filter the report. DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" & Me.cboOverPayment [OverPaymentType] is the name of the field (There are no Cells in Access) in your table that has the types. Me.cboOverPayment is the name of the Combo box where the user selects the types. I made the names up, you will have to substitute your own. If you want the report to preview as soon as the user selects a type, put the OpenReport in the After Update event of the combo box. "Rhett_Y" wrote: Hi all.. Here is what I have: Main table that stores the information overpayment table that is a linked to main table via look up wizard query that pulls all the overpayment types that have been listed I have 8 queries one for each type of overpayment What I would like to do is have a form that does this: User has a drop down list of all the overpayment types (i am assuming it would be linked to the overpayment cell in the maintable?) Once the selection is picked and they a preview report button is shows just that overpayment type in a report. Can this be done? If so how?? R~ |
#7
|
|||
|
|||
form and query and dropdown
Sorry, but it is getting more confusing. In your original post, you said you
had 8 queries, one for each overpayment type and you want to select the overpayment type. Now, help me get clear. Does you combo box allow you to select an overpayment type? Is this one report object that reports on the selected overpayment type or are there 8 different reports? If you have 8 reports, what is different about them other than the overpayment type? Do you still have 8 queries or did you change it to one so that it filters by overpayment type? "Rhett_Y" wrote: That is where I was getting confused. I want the list to be of the forms that one can pick to print. That is all....... I created the one form that runs of a query. I want the person to be able to pick what they want to filter on whether it be: time keeper error/wrong promotion date etc. Once they pick the item then hit preview and the report shows up. That is all I am trying to do...........Hope this makes sense.... R~ "Klatuu" wrote: I'm getting a little confused. It is pulling Chief because you explicitly assign the value "Chief" to stDocName just before you do the OpenReport. If you are wanting to choose the report name in the combo, then it needs to be: stDocName = Me.cboOverpayment (or whatever your combo is named) "Rhett_Y" wrote: I have it setup now but it will only pull from the "Chief" rpt... Private Sub cmdView_Click() On Error GoTo Err_cmdView_Click Dim stDocName As String stDocName = "Chief" DoCmd.OpenReport stDocName, acPreview Exit_cmdView_Click: Exit Sub Err_cmdView_Click: MsgBox Err.Description Resume Exit_cmdView_Click End Sub "Klatuu" wrote: You only need one query that will allow all overpayment types. Your drop down (Combo Box) would not need to be a bound control. It would only need a row source that would contain all the types. You could use a Value List Row Source if all you will ever have is those 8 types, or you could create a table that contains all the types and use it as a rowsource. Once the user has selected a type from the Combo, use The Where argument of the OpenReport method to filter the report. DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" & Me.cboOverPayment [OverPaymentType] is the name of the field (There are no Cells in Access) in your table that has the types. Me.cboOverPayment is the name of the Combo box where the user selects the types. I made the names up, you will have to substitute your own. If you want the report to preview as soon as the user selects a type, put the OpenReport in the After Update event of the combo box. "Rhett_Y" wrote: Hi all.. Here is what I have: Main table that stores the information overpayment table that is a linked to main table via look up wizard query that pulls all the overpayment types that have been listed I have 8 queries one for each type of overpayment What I would like to do is have a form that does this: User has a drop down list of all the overpayment types (i am assuming it would be linked to the overpayment cell in the maintable?) Once the selection is picked and they a preview report button is shows just that overpayment type in a report. Can this be done? If so how?? R~ |
#8
|
|||
|
|||
form and query and dropdown
LOL... I took your advice and made the one query...lol....
I still have the other reports that are generated from 8 seperate queries... The way I have the combo box populating is I have a table with the report names. When I put the combo box in the form the wizard poped up and asked what or how I wanted to get the info. I pointed it to the table that lists all the reports. Is this the wrong way to do it? I have also set up like above but the drop down list is populated from an overpayment table that is connected to the maintable via look up wizard. Trying different ways of doing this.. Probably going about it the wrong way..lol.... I am so frustrated..... I don't know how else to do this. I have the queries all setup.. I just want to be able to pick the form from a drop down......and have it a button to print. Or if possible have in the drop down the criteria ie :timekeeper error/ wrong promotion date. Once one is picked you can hit the preview button and it will show the report. I hope this makes sense. I maybe going about this all wrong... but that is why I am asking here.. :-) Thanks again for your help R~ "Klatuu" wrote: Sorry, but it is getting more confusing. In your original post, you said you had 8 queries, one for each overpayment type and you want to select the overpayment type. Now, help me get clear. Does you combo box allow you to select an overpayment type? Is this one report object that reports on the selected overpayment type or are there 8 different reports? If you have 8 reports, what is different about them other than the overpayment type? Do you still have 8 queries or did you change it to one so that it filters by overpayment type? "Rhett_Y" wrote: That is where I was getting confused. I want the list to be of the forms that one can pick to print. That is all....... I created the one form that runs of a query. I want the person to be able to pick what they want to filter on whether it be: time keeper error/wrong promotion date etc. Once they pick the item then hit preview and the report shows up. That is all I am trying to do...........Hope this makes sense.... R~ "Klatuu" wrote: I'm getting a little confused. It is pulling Chief because you explicitly assign the value "Chief" to stDocName just before you do the OpenReport. If you are wanting to choose the report name in the combo, then it needs to be: stDocName = Me.cboOverpayment (or whatever your combo is named) "Rhett_Y" wrote: I have it setup now but it will only pull from the "Chief" rpt... Private Sub cmdView_Click() On Error GoTo Err_cmdView_Click Dim stDocName As String stDocName = "Chief" DoCmd.OpenReport stDocName, acPreview Exit_cmdView_Click: Exit Sub Err_cmdView_Click: MsgBox Err.Description Resume Exit_cmdView_Click End Sub "Klatuu" wrote: You only need one query that will allow all overpayment types. Your drop down (Combo Box) would not need to be a bound control. It would only need a row source that would contain all the types. You could use a Value List Row Source if all you will ever have is those 8 types, or you could create a table that contains all the types and use it as a rowsource. Once the user has selected a type from the Combo, use The Where argument of the OpenReport method to filter the report. DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" & Me.cboOverPayment [OverPaymentType] is the name of the field (There are no Cells in Access) in your table that has the types. Me.cboOverPayment is the name of the Combo box where the user selects the types. I made the names up, you will have to substitute your own. If you want the report to preview as soon as the user selects a type, put the OpenReport in the After Update event of the combo box. "Rhett_Y" wrote: Hi all.. Here is what I have: Main table that stores the information overpayment table that is a linked to main table via look up wizard query that pulls all the overpayment types that have been listed I have 8 queries one for each type of overpayment What I would like to do is have a form that does this: User has a drop down list of all the overpayment types (i am assuming it would be linked to the overpayment cell in the maintable?) Once the selection is picked and they a preview report button is shows just that overpayment type in a report. Can this be done? If so how?? R~ |
#9
|
|||
|
|||
form and query and dropdown
"I have also set up like above but the drop down list is populated from an
overpayment table that is connected to the maintable via look up wizard. Trying different ways of doing this.." This was done on another form..... Trying out different ways... Sorry to mention that.. R~ "Rhett_Y" wrote: LOL... I took your advice and made the one query...lol.... I still have the other reports that are generated from 8 seperate queries... The way I have the combo box populating is I have a table with the report names. When I put the combo box in the form the wizard poped up and asked what or how I wanted to get the info. I pointed it to the table that lists all the reports. Is this the wrong way to do it? I have also set up like above but the drop down list is populated from an overpayment table that is connected to the maintable via look up wizard. Trying different ways of doing this.. Probably going about it the wrong way..lol.... I am so frustrated..... I don't know how else to do this. I have the queries all setup.. I just want to be able to pick the form from a drop down......and have it a button to print. Or if possible have in the drop down the criteria ie :timekeeper error/ wrong promotion date. Once one is picked you can hit the preview button and it will show the report. I hope this makes sense. I maybe going about this all wrong... but that is why I am asking here.. :-) Thanks again for your help R~ "Klatuu" wrote: Sorry, but it is getting more confusing. In your original post, you said you had 8 queries, one for each overpayment type and you want to select the overpayment type. Now, help me get clear. Does you combo box allow you to select an overpayment type? Is this one report object that reports on the selected overpayment type or are there 8 different reports? If you have 8 reports, what is different about them other than the overpayment type? Do you still have 8 queries or did you change it to one so that it filters by overpayment type? "Rhett_Y" wrote: That is where I was getting confused. I want the list to be of the forms that one can pick to print. That is all....... I created the one form that runs of a query. I want the person to be able to pick what they want to filter on whether it be: time keeper error/wrong promotion date etc. Once they pick the item then hit preview and the report shows up. That is all I am trying to do...........Hope this makes sense.... R~ "Klatuu" wrote: I'm getting a little confused. It is pulling Chief because you explicitly assign the value "Chief" to stDocName just before you do the OpenReport. If you are wanting to choose the report name in the combo, then it needs to be: stDocName = Me.cboOverpayment (or whatever your combo is named) "Rhett_Y" wrote: I have it setup now but it will only pull from the "Chief" rpt... Private Sub cmdView_Click() On Error GoTo Err_cmdView_Click Dim stDocName As String stDocName = "Chief" DoCmd.OpenReport stDocName, acPreview Exit_cmdView_Click: Exit Sub Err_cmdView_Click: MsgBox Err.Description Resume Exit_cmdView_Click End Sub "Klatuu" wrote: You only need one query that will allow all overpayment types. Your drop down (Combo Box) would not need to be a bound control. It would only need a row source that would contain all the types. You could use a Value List Row Source if all you will ever have is those 8 types, or you could create a table that contains all the types and use it as a rowsource. Once the user has selected a type from the Combo, use The Where argument of the OpenReport method to filter the report. DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" & Me.cboOverPayment [OverPaymentType] is the name of the field (There are no Cells in Access) in your table that has the types. Me.cboOverPayment is the name of the Combo box where the user selects the types. I made the names up, you will have to substitute your own. If you want the report to preview as soon as the user selects a type, put the OpenReport in the After Update event of the combo box. "Rhett_Y" wrote: Hi all.. Here is what I have: Main table that stores the information overpayment table that is a linked to main table via look up wizard query that pulls all the overpayment types that have been listed I have 8 queries one for each type of overpayment What I would like to do is have a form that does this: User has a drop down list of all the overpayment types (i am assuming it would be linked to the overpayment cell in the maintable?) Once the selection is picked and they a preview report button is shows just that overpayment type in a report. Can this be done? If so how?? R~ |
#10
|
|||
|
|||
form and query and dropdown
Not a problem, thanks for clearing things up a bit.
Okay, if I understand correctly, the 8 reports are identical in presentation - The only difference being which overpayment type is selected. Okay so far? Now, to keep it as simple as possible, I always try to minimize the number of objects I have to use. Eight queries means if a business rule changes or a bug is detected, I have to change it 8 times. The same goes for the reports. Now, we may have 16 places to change! So, if my assumption is correct, all you need is one query and one report. You already have the query working, as I understand. So the one report should have that query as it's record source. That way, the report will only show payment types in the query which is filtered by the combo on the form. To recap, in the query, the criteria row for the overpayment field should by filtered by: forms!MyFormName!MyComboBoxName In the Combo box, you need to present all the possible overpayment types. It is as simple as that. Once you select an overpayment type and run the report, the report opens the query which is filtered on the overpayment type, and that is all that is reported. One thing that may be needed is a title on the report that identified which overpayment type the report is for. All you need for that is a text box with the control source something like : "OverPayment Report For " & forms!MyFormName!MyComboBoxName Let me know if this is what you are after, or if I just haven't had enought coffee yet. "Rhett_Y" wrote: LOL... I took your advice and made the one query...lol.... I still have the other reports that are generated from 8 seperate queries... The way I have the combo box populating is I have a table with the report names. When I put the combo box in the form the wizard poped up and asked what or how I wanted to get the info. I pointed it to the table that lists all the reports. Is this the wrong way to do it? I have also set up like above but the drop down list is populated from an overpayment table that is connected to the maintable via look up wizard. Trying different ways of doing this.. Probably going about it the wrong way..lol.... I am so frustrated..... I don't know how else to do this. I have the queries all setup.. I just want to be able to pick the form from a drop down......and have it a button to print. Or if possible have in the drop down the criteria ie :timekeeper error/ wrong promotion date. Once one is picked you can hit the preview button and it will show the report. I hope this makes sense. I maybe going about this all wrong... but that is why I am asking here.. :-) Thanks again for your help R~ "Klatuu" wrote: Sorry, but it is getting more confusing. In your original post, you said you had 8 queries, one for each overpayment type and you want to select the overpayment type. Now, help me get clear. Does you combo box allow you to select an overpayment type? Is this one report object that reports on the selected overpayment type or are there 8 different reports? If you have 8 reports, what is different about them other than the overpayment type? Do you still have 8 queries or did you change it to one so that it filters by overpayment type? "Rhett_Y" wrote: That is where I was getting confused. I want the list to be of the forms that one can pick to print. That is all....... I created the one form that runs of a query. I want the person to be able to pick what they want to filter on whether it be: time keeper error/wrong promotion date etc. Once they pick the item then hit preview and the report shows up. That is all I am trying to do...........Hope this makes sense.... R~ "Klatuu" wrote: I'm getting a little confused. It is pulling Chief because you explicitly assign the value "Chief" to stDocName just before you do the OpenReport. If you are wanting to choose the report name in the combo, then it needs to be: stDocName = Me.cboOverpayment (or whatever your combo is named) "Rhett_Y" wrote: I have it setup now but it will only pull from the "Chief" rpt... Private Sub cmdView_Click() On Error GoTo Err_cmdView_Click Dim stDocName As String stDocName = "Chief" DoCmd.OpenReport stDocName, acPreview Exit_cmdView_Click: Exit Sub Err_cmdView_Click: MsgBox Err.Description Resume Exit_cmdView_Click End Sub "Klatuu" wrote: You only need one query that will allow all overpayment types. Your drop down (Combo Box) would not need to be a bound control. It would only need a row source that would contain all the types. You could use a Value List Row Source if all you will ever have is those 8 types, or you could create a table that contains all the types and use it as a rowsource. Once the user has selected a type from the Combo, use The Where argument of the OpenReport method to filter the report. DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" & Me.cboOverPayment [OverPaymentType] is the name of the field (There are no Cells in Access) in your table that has the types. Me.cboOverPayment is the name of the Combo box where the user selects the types. I made the names up, you will have to substitute your own. If you want the report to preview as soon as the user selects a type, put the OpenReport in the After Update event of the combo box. "Rhett_Y" wrote: Hi all.. Here is what I have: Main table that stores the information overpayment table that is a linked to main table via look up wizard query that pulls all the overpayment types that have been listed I have 8 queries one for each type of overpayment What I would like to do is have a form that does this: User has a drop down list of all the overpayment types (i am assuming it would be linked to the overpayment cell in the maintable?) Once the selection is picked and they a preview report button is shows just that overpayment type in a report. Can this be done? If so how?? R~ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form based on parameter query | Debba | Using Forms | 8 | April 3rd, 2006 12:09 AM |
Dropdown Box using a Parameter Query? | bernadou | Using Forms | 1 | March 7th, 2006 04:52 PM |
Form for Inputing query parameters. | Karthik | General Discussion | 2 | September 4th, 2005 04:25 AM |
How to use dropdown selections on a form to pass into a query | Jon A | Running & Setting Up Queries | 6 | February 2nd, 2005 01:51 AM |
I set dropdown choices of form to a query. When a choice is not in query anymore any form with that choice is blank. | [email protected] | Using Forms | 0 | January 3rd, 2005 04:46 AM |