form and query and dropdown

Hi again...

Ok I changed the cbo to the tblOverpaymentType. I now see all of the
items within the table. I changed the other code to it now reads:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport "rptOverpaymentTypeVer2", acPreview, ,
"[OverpaymentType]='" & Me.cboOverPaymentType.Column(1) & ""
Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

End Sub

When I click the View Report button I am now getting this error:

syntax error in string in query expression "([OverpaymentType]='TimeKeeping
Error)'

R~

"Klatuu" wrote:

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~

We are Almost there! There is a syntax error. It is hard to see because of
font sizes, etc. But, since we are filtering on the code, not the text. I
think it should be:

"[OverpaymentID] = " & Me.cboOverPaymentType

Since OverpaymentID is a number, we don't need the quotes around it.

"Rhett_Y" wrote:

Hi again...

Ok I changed the cbo to the tblOverpaymentType. I now see all of the
items within the table. I changed the other code to it now reads:

Private Sub cmdView_Click()
On Error GoTo Err_cmdView_Click

Dim stDocName As String

stDocName = "rptOverpaymentTypeVer2"
DoCmd.OpenReport "rptOverpaymentTypeVer2", acPreview, ,
"[OverpaymentType]='" & Me.cboOverPaymentType.Column(1) & ""
Exit_cmdView_Click:
Exit Sub

Err_cmdView_Click:
MsgBox Err.Description
Resume Exit_cmdView_Click

End Sub

When I click the View Report button I am now getting this error:

syntax error in string in query expression "([OverpaymentType]='TimeKeeping
Error)'

R~

"Klatuu" wrote:

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~