form and query and dropdown

Klatuu...

"So, if my assumption is correct, all you need is one query and one report."
correct.

"In the Combo box, you need to present all the possible overpayment types."
correct.

I have the combo box linked to the overpayment table which lists all the
possible reasons for an overpayment.

"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." Correct.. One filter One Report... If
they pick "time keeper error".. Hit the view button.. That is all they will
get is the "time keeper error" report...

So how would I do this????

"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"

That was going to be my next question after I figured this out... Thanks!!!

R~

"Klatuu" wrote:

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~

Well, there are two ways to do it. One is th use the criteria in the query
to filter for the selected type. Then when the user clickes the View button:

DoCmd.OpenReport "MyRepostName", acPreview

The report name will not have to change because it is the same report with
different data.

Now, the other way is to leave the query unfiltered. In other words, don't
put any criteria on the overpayment type. For this way, we will let the
report do the filtering using the Where argument of the OpenReport method.
The Where argument is just like an SQL WHERE clause without the word WHERE.

DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" &
Me.cboOverPayment & "'"

[OverPaymentType] would be the name of the query field where the overpayment
type is carried.

I can't say if one is preferable over the other for any reason.

"Rhett_Y" wrote:

Klatuu...

"So, if my assumption is correct, all you need is one query and one report."
correct.

"In the Combo box, you need to present all the possible overpayment types."
correct.

I have the combo box linked to the overpayment table which lists all the
possible reasons for an overpayment.

"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." Correct.. One filter One Report... If
they pick "time keeper error".. Hit the view button.. That is all they will
get is the "time keeper error" report...

So how would I do this????

"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"

That was going to be my next question after I figured this out... Thanks!!!

R~

"Klatuu" wrote:

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~

Klatuu...

Ok I think I understand......sort of... Now I just need to figure this out:

{ DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" &
Me.cboOverPayment & "'" }

Where would you put this statement? In which field in the properties box or
in the Event after update???

Thanks again for your help..
R~

"Klatuu" wrote:

Well, there are two ways to do it. One is th use the criteria in the query
to filter for the selected type. Then when the user clickes the View button:

DoCmd.OpenReport "MyRepostName", acPreview

The report name will not have to change because it is the same report with
different data.

Now, the other way is to leave the query unfiltered. In other words, don't
put any criteria on the overpayment type. For this way, we will let the
report do the filtering using the Where argument of the OpenReport method.
The Where argument is just like an SQL WHERE clause without the word WHERE.

DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" &
Me.cboOverPayment & "'"

[OverPaymentType] would be the name of the query field where the overpayment
type is carried.

I can't say if one is preferable over the other for any reason.

"Rhett_Y" wrote:

Klatuu...

"So, if my assumption is correct, all you need is one query and one report."
correct.

"In the Combo box, you need to present all the possible overpayment types."
correct.

I have the combo box linked to the overpayment table which lists all the
possible reasons for an overpayment.

"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." Correct.. One filter One Report... If
they pick "time keeper error".. Hit the view button.. That is all they will
get is the "time keeper error" report...

So how would I do this????

"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"

That was going to be my next question after I figured this out... Thanks!!!

R~

"Klatuu" wrote:

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~

Again, we have two options.

I think you said you have a command button named View. It could go in the
Click event of that command button. Then the user selects the option in the
combo box and clicks View to see the report.

OR

You could eliminate the command button and put the code in the After Update
event of the Combo box. That way, the report would pop up as soon as the
user selected an option. The only problem with this is the user may select
an option they did not want by mistake and have to wait for the report to
complete before the can go back and do it again.

I would keep the command button and put the code in the Click event.

"Rhett_Y" wrote:

Klatuu...

Ok I think I understand......sort of... Now I just need to figure this out:

{ DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" &
Me.cboOverPayment & "'" }

Where would you put this statement? In which field in the properties box or
in the Event after update???

Thanks again for your help..
R~

"Klatuu" wrote:

Well, there are two ways to do it. One is th use the criteria in the query
to filter for the selected type. Then when the user clickes the View button:

DoCmd.OpenReport "MyRepostName", acPreview

The report name will not have to change because it is the same report with
different data.

Now, the other way is to leave the query unfiltered. In other words, don't
put any criteria on the overpayment type. For this way, we will let the
report do the filtering using the Where argument of the OpenReport method.
The Where argument is just like an SQL WHERE clause without the word WHERE.

DoCmd.OpenReport "MyRepostName", acPreview, , "[OverPaymentType] = '" &
Me.cboOverPayment & "'"

[OverPaymentType] would be the name of the query field where the overpayment
type is carried.

I can't say if one is preferable over the other for any reason.

"Rhett_Y" wrote:

Klatuu...

"So, if my assumption is correct, all you need is one query and one report."
correct.

"In the Combo box, you need to present all the possible overpayment types."
correct.

I have the combo box linked to the overpayment table which lists all the
possible reasons for an overpayment.

"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." Correct.. One filter One Report... If
they pick "time keeper error".. Hit the view button.. That is all they will
get is the "time keeper error" report...

So how would I do this????

"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"

That was going to be my next question after I figured this out... Thanks!!!

R~

"Klatuu" wrote:

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~

Klatuu..

Sorry for so many posts... But I do appreciate this.......

Ok.. Here is what I did....

I made the combo box. Named it cboOverpaymentType

Created the view button and have it looking at the rptTypeofOverpaymentVer2
code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

I then went in and changed the Docmd.....Code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport "rptOverpaymentTypeVer2", acPreview, ,
"[OverPaymentType] = '" & Me.cboOverPaymentType & "'"
Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

End Sub

I know in the query so far there is a error of "time keeper error".. I
selected it and and I get an error of data mismatched. I think the problem
is it doesn't know where or how to look at the
qryOverpaymentTypeVer2--OverpaymentType. How do I get it to look at that
field in the query?? If that is indead the case....??

I tried setting up the combo box with it linked to the query but it will
only show what I have in the query....and it represented as a
number.......and if I have two of the same things I get:

1
1
15
15
17
1

Etc... So I think I set that one up wrong..so I scratched it and went with
the combo box looking at the tblOverpaymentType instead... I hope this is
making sense...

Thanks again
R~

So far, it looks good. (except for the error)
Lets start by making sure the query works correctly. Just open the query
and see if it opens on it's own from the database window without a problem.

Also, let's fix the combo box. It should have it's own query or table. You
can use the table where the overpayment type is as the source of the query.
In fact, you might try typing this in the row source of the combo (using
correct names, of course):
SELECT DISTINCT OverPaymentType FROM TableNameHere

That will return one occurance of each overpayment type currently in the
table.

Once we get the query working and the combo row source working, we will move
on to getting the report to run correctly.

"Rhett_Y" wrote:

Klatuu..

Sorry for so many posts... But I do appreciate this.......

Ok.. Here is what I did....

I made the combo box. Named it cboOverpaymentType

Created the view button and have it looking at the rptTypeofOverpaymentVer2
code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

I then went in and changed the Docmd.....Code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport "rptOverpaymentTypeVer2", acPreview, ,
"[OverPaymentType] = '" & Me.cboOverPaymentType & "'"
Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

End Sub

I know in the query so far there is a error of "time keeper error".. I
selected it and and I get an error of data mismatched. I think the problem
is it doesn't know where or how to look at the
qryOverpaymentTypeVer2--OverpaymentType. How do I get it to look at that
field in the query?? If that is indead the case....??

I tried setting up the combo box with it linked to the query but it will
only show what I have in the query....and it represented as a
number.......and if I have two of the same things I get:

1
1
15
15
17
1

Etc... So I think I set that one up wrong..so I scratched it and went with
the combo box looking at the tblOverpaymentType instead... I hope this is
making sense...

Thanks again
R~

Ok.....

Fixed the cboOverpaymentType to have the row source read:

SELECT DISTINCT OverPaymentType FROM tblMain

I have the cbo looking in the tblMain where the info is stored.. When I
view the drop down all I am getting are the numbers no names...but their are
no duplicates! ( I hope this is the table I should be having the cbo looking
in)

I opened the query and it opens with out a hitch... I shows all 6 test
records and reasons for the overpayment. I looked in the main table and
there are 6 records and they match. So I know the query works. I took out
all of the filters except :is not null: so now it is just a regular query
showing me all of the records that have an overpayment type, the ones that
don't have a type are not shown.....


Thanks again for your help...
Rhett


"Klatuu" wrote:

So far, it looks good. (except for the error)
Lets start by making sure the query works correctly. Just open the query
and see if it opens on it's own from the database window without a problem.

Also, let's fix the combo box. It should have it's own query or table. You
can use the table where the overpayment type is as the source of the query.
In fact, you might try typing this in the row source of the combo (using
correct names, of course):
SELECT DISTINCT OverPaymentType FROM TableNameHere

That will return one occurance of each overpayment type currently in the
table.

Once we get the query working and the combo row source working, we will move
on to getting the report to run correctly.

"Rhett_Y" wrote:

Klatuu..

Sorry for so many posts... But I do appreciate this.......

Ok.. Here is what I did....

I made the combo box. Named it cboOverpaymentType

Created the view button and have it looking at the rptTypeofOverpaymentVer2
code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

I then went in and changed the Docmd.....Code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport "rptOverpaymentTypeVer2", acPreview, ,
"[OverPaymentType] = '" & Me.cboOverPaymentType & "'"
Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

End Sub

I know in the query so far there is a error of "time keeper error".. I
selected it and and I get an error of data mismatched. I think the problem
is it doesn't know where or how to look at the
qryOverpaymentTypeVer2--OverpaymentType. How do I get it to look at that
field in the query?? If that is indead the case....??

I tried setting up the combo box with it linked to the query but it will
only show what I have in the query....and it represented as a
number.......and if I have two of the same things I get:

1
1
15
15
17
1

Etc... So I think I set that one up wrong..so I scratched it and went with
the combo box looking at the tblOverpaymentType instead... I hope this is
making sense...

Thanks again
R~

Okay, I learned something new. So, your overpayment types in tblMain are not
the text, but a code. (that is a good thing) So, what I need to know is how
do you associate the code with the text? Is there a separate table that has
that?

If so, we need to change our Combo box again.
Also, what data type is the code? I think that explains why you got the
data type error.

"Rhett_Y" wrote:

Ok.....

Fixed the cboOverpaymentType to have the row source read:

SELECT DISTINCT OverPaymentType FROM tblMain

I have the cbo looking in the tblMain where the info is stored.. When I
view the drop down all I am getting are the numbers no names...but their are
no duplicates! ( I hope this is the table I should be having the cbo looking
in)

I opened the query and it opens with out a hitch... I shows all 6 test
records and reasons for the overpayment. I looked in the main table and
there are 6 records and they match. So I know the query works. I took out
all of the filters except :is not null: so now it is just a regular query
showing me all of the records that have an overpayment type, the ones that
don't have a type are not shown.....


Thanks again for your help...
Rhett


"Klatuu" wrote:

So far, it looks good. (except for the error)
Lets start by making sure the query works correctly. Just open the query
and see if it opens on it's own from the database window without a problem.

Also, let's fix the combo box. It should have it's own query or table. You
can use the table where the overpayment type is as the source of the query.
In fact, you might try typing this in the row source of the combo (using
correct names, of course):
SELECT DISTINCT OverPaymentType FROM TableNameHere

That will return one occurance of each overpayment type currently in the
table.

Once we get the query working and the combo row source working, we will move
on to getting the report to run correctly.

"Rhett_Y" wrote:

Klatuu..

Sorry for so many posts... But I do appreciate this.......

Ok.. Here is what I did....

I made the combo box. Named it cboOverpaymentType

Created the view button and have it looking at the rptTypeofOverpaymentVer2
code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

I then went in and changed the Docmd.....Code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport "rptOverpaymentTypeVer2", acPreview, ,
"[OverPaymentType] = '" & Me.cboOverPaymentType & "'"
Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

End Sub

I know in the query so far there is a error of "time keeper error".. I
selected it and and I get an error of data mismatched. I think the problem
is it doesn't know where or how to look at the
qryOverpaymentTypeVer2--OverpaymentType. How do I get it to look at that
field in the query?? If that is indead the case....??

I tried setting up the combo box with it linked to the query but it will
only show what I have in the query....and it represented as a
number.......and if I have two of the same things I get:

1
1
15
15
17
1

Etc... So I think I set that one up wrong..so I scratched it and went with
the combo box looking at the tblOverpaymentType instead... I hope this is
making sense...

Thanks again
R~

Hi again...

I have an tblOverpaymentType that lists all of the over payment types. I
then have a tblMain that has the OverPaymentType setup as a lookup to the
tblOverpaymentType.... I did it this way so the end user can pick the
correct one with out having to type it in....

So yes there is a seperate table that has all of the overpayment
types....and the main table field of overpayment type is a lookup...

Datatype in the tblMain for the field of OverPaymentType is set to "Number"
because of the lookup Wizard is what it set it to

In the tblOverpaymentType I have the following two fields:

OverPaymentId = Autonumber
OverPaymentType = Text

R~

"Klatuu" wrote:

Okay, I learned something new. So, your overpayment types in tblMain are not
the text, but a code. (that is a good thing) So, what I need to know is how
do you associate the code with the text? Is there a separate table that has
that?

If so, we need to change our Combo box again.
Also, what data type is the code? I think that explains why you got the
data type error.

"Rhett_Y" wrote:

Ok.....

Fixed the cboOverpaymentType to have the row source read:

SELECT DISTINCT OverPaymentType FROM tblMain

I have the cbo looking in the tblMain where the info is stored.. When I
view the drop down all I am getting are the numbers no names...but their are
no duplicates! ( I hope this is the table I should be having the cbo looking
in)

I opened the query and it opens with out a hitch... I shows all 6 test
records and reasons for the overpayment. I looked in the main table and
there are 6 records and they match. So I know the query works. I took out
all of the filters except :is not null: so now it is just a regular query
showing me all of the records that have an overpayment type, the ones that
don't have a type are not shown.....


Thanks again for your help...
Rhett


"Klatuu" wrote:

So far, it looks good. (except for the error)
Lets start by making sure the query works correctly. Just open the query
and see if it opens on it's own from the database window without a problem.

Also, let's fix the combo box. It should have it's own query or table. You
can use the table where the overpayment type is as the source of the query.
In fact, you might try typing this in the row source of the combo (using
correct names, of course):
SELECT DISTINCT OverPaymentType FROM TableNameHere

That will return one occurance of each overpayment type currently in the
table.

Once we get the query working and the combo row source working, we will move
on to getting the report to run correctly.

"Rhett_Y" wrote:

Klatuu..

Sorry for so many posts... But I do appreciate this.......

Ok.. Here is what I did....

I made the combo box. Named it cboOverpaymentType

Created the view button and have it looking at the rptTypeofOverpaymentVer2
code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

I then went in and changed the Docmd.....Code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport "rptOverpaymentTypeVer2", acPreview, ,
"[OverPaymentType] = '" & Me.cboOverPaymentType & "'"
Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

End Sub

I know in the query so far there is a error of "time keeper error".. I
selected it and and I get an error of data mismatched. I think the problem
is it doesn't know where or how to look at the
qryOverpaymentTypeVer2--OverpaymentType. How do I get it to look at that
field in the query?? If that is indead the case....??

I tried setting up the combo box with it linked to the query but it will
only show what I have in the query....and it represented as a
number.......and if I have two of the same things I get:

1
1
15
15
17
1

Etc... So I think I set that one up wrong..so I scratched it and went with
the combo box looking at the tblOverpaymentType instead... I hope this is
making sense...

Thanks again
R~

Gotcha.

So, the row source for the combo needs to be tblOverPaymentType.
It needs to have 2 columns. The bound column should be OverPaymentID.
The in the columnwidths property of the combo, set the first column width to
0 and the second wide enought to display the text. so, it would look
something like 0";1.5"

Now, since OverPaymentID is the bound column, that should make your
OpenReport pick up the correct value and data type.

Note that the first column of the combo, which is the bound column, will be
returned when you use Me.cboOverPaymentType
But, when you want to look at specific columns, column numbers for combos
start with 0, so the first column is column(0). In this case,
Me.cboOverPaymentType.Column(1) will return the text.
The reason I mention that is you want the text for your report title, so
instead of
Me.cboOverPaymentType You will need Me.cboOverPaymentType.Column(1)

So, change the cbo properties (again) and try to run the report. Let me
know what happens.

"Rhett_Y" wrote:

Hi again...

I have an tblOverpaymentType that lists all of the over payment types. I
then have a tblMain that has the OverPaymentType setup as a lookup to the
tblOverpaymentType.... I did it this way so the end user can pick the
correct one with out having to type it in....

So yes there is a seperate table that has all of the overpayment
types....and the main table field of overpayment type is a lookup...

Datatype in the tblMain for the field of OverPaymentType is set to "Number"
because of the lookup Wizard is what it set it to

In the tblOverpaymentType I have the following two fields:

OverPaymentId = Autonumber
OverPaymentType = Text

R~

"Klatuu" wrote:

Okay, I learned something new. So, your overpayment types in tblMain are not
the text, but a code. (that is a good thing) So, what I need to know is how
do you associate the code with the text? Is there a separate table that has
that?

If so, we need to change our Combo box again.
Also, what data type is the code? I think that explains why you got the
data type error.

"Rhett_Y" wrote:

Ok.....

Fixed the cboOverpaymentType to have the row source read:

SELECT DISTINCT OverPaymentType FROM tblMain

I have the cbo looking in the tblMain where the info is stored.. When I
view the drop down all I am getting are the numbers no names...but their are
no duplicates! ( I hope this is the table I should be having the cbo looking
in)

I opened the query and it opens with out a hitch... I shows all 6 test
records and reasons for the overpayment. I looked in the main table and
there are 6 records and they match. So I know the query works. I took out
all of the filters except :is not null: so now it is just a regular query
showing me all of the records that have an overpayment type, the ones that
don't have a type are not shown.....


Thanks again for your help...
Rhett


"Klatuu" wrote:

So far, it looks good. (except for the error)
Lets start by making sure the query works correctly. Just open the query
and see if it opens on it's own from the database window without a problem.

Also, let's fix the combo box. It should have it's own query or table. You
can use the table where the overpayment type is as the source of the query.
In fact, you might try typing this in the row source of the combo (using
correct names, of course):
SELECT DISTINCT OverPaymentType FROM TableNameHere

That will return one occurance of each overpayment type currently in the
table.

Once we get the query working and the combo row source working, we will move
on to getting the report to run correctly.

"Rhett_Y" wrote:

Klatuu..

Sorry for so many posts... But I do appreciate this.......

Ok.. Here is what I did....

I made the combo box. Named it cboOverpaymentType

Created the view button and have it looking at the rptTypeofOverpaymentVer2
code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

I then went in and changed the Docmd.....Code thus far:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport "rptOverpaymentTypeVer2", acPreview, ,
"[OverPaymentType] = '" & Me.cboOverPaymentType & "'"
Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

End Sub

I know in the query so far there is a error of "time keeper error".. I
selected it and and I get an error of data mismatched. I think the problem
is it doesn't know where or how to look at the
qryOverpaymentTypeVer2--OverpaymentType. How do I get it to look at that
field in the query?? If that is indead the case....??

I tried setting up the combo box with it linked to the query but it will
only show what I have in the query....and it represented as a
number.......and if I have two of the same things I get:

1
1
15
15
17
1

Etc... So I think I set that one up wrong..so I scratched it and went with
the combo box looking at the tblOverpaymentType instead... I hope this is
making sense...

Thanks again
R~