Choose wich report to open

[Chris]

Hi,
I am using Access 2003.
On my form i have an open report button. I have 2 differents reports to open.
I have a selection of my supplier in that form and in the supplierstabel i
have a field for "language".
If my suppliers language is lets say "Dutch" it must open the "Dutch
report". If his language is "French" he must open the "French report". I have
no clue how to get started on this. Make a makro? Write VBA?
Can anyone get me started?

Thanks

 

[Evi]

If the scenario really is as simple as this, put an Option Group onto your
report. The labels could be Dutch and French. You will see the Values column
appear in the Wizard as 1 and 2 -
In the Properties go to Other and Name and name the Option Group, optReports
Click on the Option Group in the Form's design view, choose the Events Tab,
choose Event Procedure next to After Update
Open the code page.




Private Sub optReports_AfterUpdate()
Dim Doc1 As String
Dim Doc2 As String
Dim MyDoc As String

Doc1 = "RptDutch"
Doc2 = "RptFrench"
'substitute the real names of your reports here

Select Case Me.optReports
'looks at which option was chosen in the Option Group
Case 1
'1 was in the Value column in your combo next to your Dutch label
MyDoc = Doc1
Case 2
'2 was in the Value column in your combo next to your French label
MyDoc = Doc2
End Select

DoCmd.OpenReport MyDoc, acViewPreview

End Sub

If you want to do this in future with more reports then I would suggest
putting your report names into a table and using a combo box to choose them.


Evi

 

[Chris]

Thanks Evi,

I've learned something new!

I'ld like to make this option automatic.
In my form i select with a combobox my "supplier"
(In an underlying table of "suppliers" i have a field with the language of
my supplier= "Dutch" or "French")
I'ld like that when i click my reportbutton, automaticly the report with the
same language as my "supplier" opens. Is thas possible?

Thanks

 

[Evi]

Sure. I feel better about your database hearing this. When you create your
combo box (cboSupplier), have not only Supplier ID (which will be the column
you will close up in the Wizard, so that the user cannot see it) and
Supplier (which the user will see) but also Language, the user won't see
this column once it is selected.. If you are really saintly (and have plans
to extend your business further) and have a language table so that your
Supplier table contains a link to it eg a numbe field LanguageID, then add
the link field as the third column in the combo and replace the words
"Dutch" and "French" with the Primary Key number for those languages. You
won't need the quote marks around that number - they are only for text.



Replace Me.optReports with Me.cboSupplier.Column (2)
The reason it is column 2 instead of 3, is because Access starts numbering
Collections like Columns with 0. No idea why!
'Column 0 will contain your Supplier ID number, in the Properties box next
to Column Widths, this first entry will be 0cm. Column 1
'will have your supplier's name, and will show in the combo once selected.
'Column 2 will have the supplier's language or, better still, the link key
to the Language table.

Private Sub cboSupplier_AfterUpdate()
Dim Doc1 As String
Dim Doc2 As String
Dim MyDoc As String

Doc1 = "RptDutch"
Doc2 = "RptFrench"
'substitute the real names of your reports here

Select Case Me.cboSupplier.Column(2)
Case "Dutch"
MyDoc = Doc1
Case "French"
MyDoc = Doc2
End Select

DoCmd.OpenReport MyDoc, acViewPreview

End Sub



Replace

 

[Chris]

Hi Evi,

You have made my day,
Thank you very much!
It's seems so simple now


Greetz Chris

 

[Chris]

Hello, i'm wondering if Evi could help me again.

I have put your code in my cmdbutton, and it works very good.
I have gone a little bit further now and i have 4 reports.

Doc1 = "RptDutch"
Doc2 = "RptFrench"
Doc3 = "RptDutch_NOPV"
Doc4 = "RptFrench_NOPV

The choise depends on Me.cboSupplier.Column (2) where I have the choise
between "Dutch" or "French"
and Me.cboContractid.Column (2) where I have the choise between "Yes" or "No".
If my supplier is "french" en the contractid is "no". I would like to print
Doc4. If my supplier is "Dutch" en the contractid is "yes" I would like to
print doc1.

If have tried
Select Case Me.cboSupplier.Column(2) and Me.cboContractid.Column(2)
Case "Dutch" and "Yes"
MyDoc = Doc1
Case "French" and "Yes"
MyDoc = Doc2
Case "Dutch" and "No"
MyDoc = Doc3
Case "French" and "No"
MyDoc = Doc4

End Select
but there is an error showing: "types don't match"

I you would help me out, it will be a great help.

Thanks in advance

 

[Evi]

Are Yes and No tick boxes? If they are, then the code should read

True
(without a quote mark) for yes and False (without quotes) for No

Remember also that Column(2) may not mean the 2nd column you can see in your
combo. In fact, it is the *3rd* column you can see if you go to the Combo's
Row Source and open the query *In Design View*.
The First column is actually Column(0) but this is the combo boxe's value so
you seldom get to see that.

(I think they've changed this for Acc2007 - imagine the fun that will
cause!)

If you are seeing a column that doesn't exist or are seeing a number column
when it ought to be text, that could also explain the datatype mismatch.

Have you Dim'd all your Docs AS String?

Evi

 

[Chris]

Hi Evi,

The mismatch occures when I add Me.cboContractid.Column(2) to the select case.
I have dim'd al my docs. In my cbobox the query I see in the propretiesbox
has
3 columns, so I presume that is must be the column (2).
I have tried putting the query in one cbobox. But it doesn't work either.

Any ideas?

 

[Evi]

Hi Chris,
Try this. Put an apostrophe in front of the DoCmd.OpenReport line just for
now and replace it with
MsgBox Me.cboContactid.Column(2)
Press the button and run the code to see what the Code actually sees.
Datatype mismatch would occur if you column(2) was a YesNo field (a Boolean
field) and you had put quotes around your "Yes".
It can also be caused if you Dim something as say an Integer but its too
high a number.
It can be caused if you want to see something and the value is Null.
(Check if your combo box has 3 columns in it Properties box next to Column
Count)

Evi