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 cmbPrintOrPrevi ew_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.OpenR ecordset(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("PrintOrPre view")
Case "ChooseToPrevie w"
gblPrintOrPrevi ew = "acViewPrev iew"
Case "ChooseToPr int"
gblPrintOrPrevi ew = "acViewNorm al"
Case Else
gblPrintOrPrevi ew = "acViewPrev iew"
End Select
'let's see what it's showing....
Debug.Print "Print setting: " & gblPrintOrPrevi ew
End If
End Function

And finally the code for the button:

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

'Either of these work, but as soon as I change it to
DoCmd.OpenRepor t "rptTEST", gblPrintOrPrevi ew
'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_OFFth eexperts.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 cmbPrintOrPrevi ew_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.OpenR ecordset(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("PrintOrPre view")
Case "ChooseToPrevie w"
gblPrintOrPrevi ew = "acViewPrev iew"
Case "ChooseToPr int"
gblPrintOrPrevi ew = "acViewNorm al"
Case Else
gblPrintOrPrevi ew = "acViewPrev iew"
End Select
'let's see what it's showing....
Debug.Print "Print setting: " & gblPrintOrPrevi ew
End If
End Function

And finally the code for the button:

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

'Either of these work, but as soon as I change it to
DoCmd.OpenRepor t "rptTEST", gblPrintOrPrevi ew
'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.OpenRepor t
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 gblPrintOrPrevi ew
to a string value. You need to, either use a select/case statement and then
the appropriate constant, or set gblPrintOrPrevi ew to a numeric value to
begin with.

HTH,
Randy

[Bas Cost Budde]

You use string literals where you should use constants.

Jon wrote:

gblPrintOrPrevi ew = "acViewPrev iew"

must become
gblPrintOrPrevi ew = 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: gblPrintOrPrevi ew = "acViewPrev iew"

must become
gblPrintOrPrevi ew = 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