Dynamic Choice - Print or Preview

[Jon]

I'm trying to set an (within options table, tblOptions) a setting that can
be read, whehter all reports should be printed or previewed. I'm happy to
email a sample db if required. When I use the setting I get a Run-time error
'13' Type mismatch!!

I have:

1 table - tblOptions with
1 field - "PrintOrPreview" - a 50 character Text field
1 report - "rptTEST" - just a label on it
1 form - with a button and a combo box to chaing the vaule of
"PrintOrPreview"

The code for the combo box:

Private Sub cmbPrintOrPreview_AfterUpdate()
Call SetPrintChoice
End Sub

Which is:

Function SetPrintChoice()
Dim rst As DAO.Recordset
Dim stSQL As String

stSQL = "SELECT * FROM tblOptions;"
Set rst = CurrentDb.OpenRecordset(stSQL)

If rst.RecordCount > 0 Then 'in case the options record
has gone!
rst.MoveFirst 'in case another
record was added by mistake!
Select Case rst("PrintOrPreview")
Case "ChooseToPreview"
gblPrintOrPreview = "acViewPreview"
Case "ChooseToPrint"
gblPrintOrPreview = "acViewNormal"
Case Else
gblPrintOrPreview = "acViewPreview"
End Select
'let's see what it's showing....
Debug.Print "Print setting: " & gblPrintOrPreview
End If
End Function

And finally the code for the button:

Private Sub cmdPrint_Click()
' DoCmd.OpenReport "rptTEST", acViewNormal
' DoCmd.OpenReport "rptTEST", acViewPreview

'Either of these work, but as soon as I change it to
DoCmd.OpenReport "rptTEST", gblPrintOrPreview
'then I get a "Type mismatch Error"

End Sub

===========================

Surely this has been done before! What am I do wrong? Please help if you
can, thank you

Jon

[Randy Harris]

"Jon" <jon@SPAM_OFFtheexperts.co.uk> wrote in message
news:da**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

I'm trying to set an (within options table, tblOptions) a setting that can
be read, whehter all reports should be printed or previewed. I'm happy to
email a sample db if required. When I use the setting I get a Run-time error '13' Type mismatch!!

I have:

1 table - tblOptions with
1 field - "PrintOrPreview" - a 50 character Text field
1 report - "rptTEST" - just a label on it
1 form - with a button and a combo box to chaing the vaule of
"PrintOrPreview"

The code for the combo box:

Private Sub cmbPrintOrPreview_AfterUpdate()
Call SetPrintChoice
End Sub

Which is:

Function SetPrintChoice()
Dim rst As DAO.Recordset
Dim stSQL As String

stSQL = "SELECT * FROM tblOptions;"
Set rst = CurrentDb.OpenRecordset(stSQL)

If rst.RecordCount > 0 Then 'in case the options record has gone!
rst.MoveFirst 'in case another
record was added by mistake!
Select Case rst("PrintOrPreview")
Case "ChooseToPreview"
gblPrintOrPreview = "acViewPreview"
Case "ChooseToPrint"
gblPrintOrPreview = "acViewNormal"
Case Else
gblPrintOrPreview = "acViewPreview"
End Select
'let's see what it's showing....
Debug.Print "Print setting: " & gblPrintOrPreview
End If
End Function

And finally the code for the button:

Private Sub cmdPrint_Click()
' DoCmd.OpenReport "rptTEST", acViewNormal
' DoCmd.OpenReport "rptTEST", acViewPreview

'Either of these work, but as soon as I change it to
DoCmd.OpenReport "rptTEST", gblPrintOrPreview
'then I get a "Type mismatch Error"

End Sub

===========================

Surely this has been done before! What am I do wrong? Please help if you
can, thank you

Jon

I can see why this is confusing. The second argument for DoCmd.OpenReport
needs to be numeric. Go to the debug screen and try this:

?acviewnormal
0
?acviewpreview
2

Notice that those constants are numeric. You are setting gblPrintOrPreview
to a string value. You need to, either use a select/case statement and then
the appropriate constant, or set gblPrintOrPreview to a numeric value to
begin with.

HTH,
Randy

[Bas Cost Budde]

You use string literals where you should use constants.

Jon wrote:

gblPrintOrPreview = "acViewPreview"

must become
gblPrintOrPreview = acviewpreview

You don't need to use a global variable if you convert the
SetPrintChoice sub to a GetPrintChoice function, assigning the value
found in the table to the function result, and calling that in the
OpenReport method. But that is a different issue.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

[Jon]

Bas Cost Budde Wrote:

You use string literals where you should use constants.
Jon wrote: gblPrintOrPreview = "acViewPreview"

must become
gblPrintOrPreview = acviewpreview

You don't need to use a global variable if you convert the
SetPrintChoice sub to a GetPrintChoice function, assigning the value
found in the table to the function result, and calling that in the
OpenReport method. But that is a different issue.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

Bas and Randy, thank you. It's a sub in my test DB, was a function before!

I now understand where I was going wrong and will sort this - thank you.

Jon