Need Help In Printing Current Record in Specific Report

I have to print a specific report (form letter) based upon the value of the
data in field RESULT_CDE. The user executes this form, and upon entering a
code "20" to print Finding letters, it falls through the logic for reading
the DB2 table, inspecting field PRTNO_NUM for code 20 value, extracts records
with this value to temp table DPS_FR_CR20RW. Then code falls through to
open DPS_FR_CR20RW, to examine field RESULT_CDE for values. On specific
values, I need to print the contents of the current record to that specified
form. I'm not quite sure how to do it, whether to use the PrintOut command
or Open.Report function. To top things off, I'm not able to get a match on
the RESULT_CDE values in my compare code statements, and I need assistance to
code the correct syntax to find current record RESULT_CDE values. Any
assistance would be greatly appreciated. I'm finding all the correct number
of records in my temporary dataset, I just can't get a hit on the correct
compare, every display of RESULT_CDE comes back with null value in the
display..... Is what I want to accomplish possible?

Thanks,

Code follows...

Option Compare Database
'The form is used by the Legal System / Financial Responsibilites
Application. It
'is used as a Application Menu that allows the user to select the type
of Report Run
'to be requested: Hearing Letters, Cancellation Letters, or Findings
Letters.
'The code was last revised: 05/09/2005
'Developed on Microsoft Access 2003 Professional By Robert E. Nusz
'Application Support, Department of Public Safety, State of Oklahoma

Private Sub Combo13_Click()

Dim intThisRun As Integer 'used to identify type of run, 10, 13, 20
Dim strSQL As String 'used to hold SQL string for Alter Table
command
Dim strResultCde As String

If Combo13 = 10 Then
MsgBox " Now creating Hearing Letters ! "
DoCmd.runMacro "FRM-CR10RW"
strSQL = "ALTER TABLE DPS_FRQ_CR10RW " & _
"ADD CONSTRAINT PK_CR10RW " & _
"PRIMARY KEY(Case_Num_Yr,Case_Num)"
CurrentDb.Execute strSQL, dbFailOnError
MsgBox " Result Code Is " & RESULT_CDE
intThisRun = 10
MsgBox " This run is a type " & intThisRun
ElseIf Combo13 = 13 Then
MsgBox " Now Creating Cancellation Letters ! "
DoCmd.runMacro "FRM-CR13RW"
strSQL = "ALTER TABLE DPS_FRQ_CR13RW " & _
"ADD CONSTRAINT PK_CR13RW " & _
"PRIMARY KEY(Case_Num_Yr,Case_Num)"
CurrentDb.Execute strSQL, dbFailOnError
intThisRun = 13
MsgBox " This run is a type " & intThisRun
ElseIf Combo13 = 20 Then
MsgBox " Now Creating Finding Letters ! "
DoCmd.runMacro "FRM-CR20RW"
strSQL = "ALTER TABLE DPS_FRQ_CR20RW " & _
"ADD CONSTRAINT PK_CR20RW " & _
"PRIMARY KEY(Case_Num_Yr,Case_Num)"
CurrentDb.Execute strSQL, dbFailOnError
intThisRun = 20
MsgBox " This run is a type " & intThisRun
Else
MsgBox " Value Entered To Combo13 Field Was Invalid, Try Again ! "
End If

Select Case intThisRun
Case 10
' CHECK TO SEE IF THE USER WANTS TO PRINT CASE HEARING LETTERS
NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Hearing Letters Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Legal Letterhead Forms" & Chr$(10)
& " Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseLHHearings", acViewNormal
MsgBox " Now Printing Case Hearing Letters "
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT CASE EVELOPES NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Envelopes Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Envelope Forms" & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseEnvelope", acViewNormal
MsgBox " Now Printing Case Envelopes "
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT FOLDER LABELS NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Folder Labels Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Folder Label Forms " & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-FolderLabel", acViewNormal
MsgBox " Now Printing Case Folder Labels "
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT A NEW ALPHA-LIST
REPORT NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "A New
Alpha-List Report", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Plain Paper Forms " & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-Alpha-List", acViewNormal
MsgBox " Now Printing Alpha-List Report "
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT A NEW DOCKET-LIST
REPORT NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "A New
Docket List Report", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox("Are Plain Paper Forms" & Chr$(10) &
"Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-Docket", acviewNormal
MsgBox "Now Printing New Docket List"
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT NEW INFO-SHEETS NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "New F.R.
Info Sheets Now", _
vbQuestion + vbYesNo)
If Returnal = 6 Then
Returnval = MsgBox("Are Plain Paper Forms" & Chr$(10) &
"Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-InfoSheet", acviewNormal
MsgBox "Now Printing New Info Sheets"
End If
End If

Case 13
' CHECK TO SEE IF THE USER WANTS TO PRINT CASE CANCELLATION
LETTERS NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Cancellation Letters Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Legal Letterhead Forms" & Chr$(10)
& " Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseLHCancellations",
acViewNormal
MsgBox " Now Printing Case Cancellation Letters "
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT CASE EVELOPES NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Envelopes Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Envelope Forms" & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseEnvelope", acViewNormal
MsgBox " Now Printing Case Envelopes "
End If
End If
Case 20

' CHECK TO SEE IF THE USER WANTS TO PRINT CASE FINDINGS LETTERS
NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Findings Letters Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Legal Letterhead Forms" & Chr$(10)
& " Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseLHFindings", acViewNormal
MsgBox " Now Printing Case Hearing Letters "
ReadInTable
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT CASE EVELOPES NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Envelopes Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Envelope Forms" & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseEnvelope", acViewNormal
MsgBox " Now Printing Case Envelopes "
End If
End If

Case Else
MsgBox " No Letters Selected ! ", vbExclamation
End Select

End Sub

Private Sub Command9_Return_Click()
On Error GoTo Err_Command9_Return_Click
'When clicked, this button will redirect the user back to form
FRF-Main-Menu

DoCmd.Close

Exit_Command9_Return_Click:
Exit Sub

Err_Command9_Return_Click:
MsgBox Err.Description
Resume Exit_Command9_Return_Click

End Sub

Function ReadInTable()
MsgBox "Now Executing ReadInTable "
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Establish the connection, cursor type, and open the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenForwardOnly
rst.Open "Select * from DPS_FRQ_CR20RW"
'Print Proper Finding Letter Based on RESULT_CDE Value
'Loop through until EOF
Do Until rst.EOF
If rst!RESULT_CDE = "11" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC11 1 Copy
ElseIf rst!RESULT_CDE = "12" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC12 1 Copy
ElseIf rst!RESULT_CDE = "14" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC14 1 Copy
ElseIf rst!RESULT_CDE = "15" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC15 1 Copy
ElseIf rst!RESULT_CDE = "16" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC16 1 Copy
ElseIf rst!RESULT_CDE = "18" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC18 1 Copy
ElseIf rst!RESULT_CDE = "21" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC21 1 Copy
ElseIf rst!RESULT_CDE = "22" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC22 1 Copy
ElseIf rst!RESULT_CDE = "23" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC23 1 Copy
ElseIf rst!RESULT_CDE = "24" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC24 1 Copy
ElseIf rst!RESULT_CDE = "26" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC16 1 Copy
ElseIf rst!RESULT_CDE = "28" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC28 1 Copy
ElseIf rst!RESULT_CDE = "29" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC29 1 Copy
ElseIf rst!RESULT_CDE = "31" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC31 1 Copy
ElseIf rst!RESULT_CDE = "32" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC32 1 Copy
ElseIf rst!RESULT_CDE = "34" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC34 1 Copy
ElseIf rst!RESULT_CDE = "35" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC35 1 Copy
ElseIf rst!RESULT_CDE = "41" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC41 1 Copy
ElseIf rst!RESULT_CDE = "42" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC42 1 Copy
ElseIf rst!RESULT_CDE = "44" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC44 1 Copy
ElseIf rst!RESULT_CDE = "45" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC45 1 Copy
ElseIf rst!RESULT_CDE Like "5%" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC5x 1 Copy
Else
MsgBox " INVALID RESULT_CDE FOUND = " & RESULT_CDE
End If
rst.MoveNext
Loop
rst.Close

End Function
--
Robert Nusz
Sr. Programmer Analyst II

I've made some changes to the code. I'm now able to print the specific
report, but it is failing to match records in the tables to the current
record case number. I'm now getting #Error on the fields that would have
normally been plugged into the report, although the request to print the
report is automated. So the request has changed some, can someone tell me
why my comparison of form FRF-Letter-Menu![curCaseNumYr] (temporary field)
which has a valid value (say 1990) and FRF-Letter-Menu![curCaseNum]
(temporary field) with a value of 1, can not match up to a record in the
query results FRQ-FOFPC20 with a matching number 19901 in its CaseNumYr
(1990) and CaseNum (1) record? I would like some guidance to make this
easier and syntactically correct... Thanks in advance....


I added a few requirements to the report as filter =
DPS_FR_CASE_RECORDS![CASE_NUM_YR] = forms![FRF-Letter-Menu]![curCaseNumYr]
AND DPS_FR_CASE_RECORDS![CASE_NUM] = forms![FRF-Letter-Menu]![curCaseNum] AND
RESULT_CDE = "11" AND PRTNO_NUM = 20.

The reports record source is FRQ-FOFPC20 query below:

SELECT DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM,
DPS_FR_CASE_RECORDS.PRTNO_NUM, DPS_FR_CASE_RECORDS.PRTD_CDE,
DPS_FR_CASE_RECORDS.FR_FILE_NUM, DPS_FR_CASE_RECORDS.TYPIST_INIT_TXT,
DPS_FR_CASE_RECORDS.LIC_STAT_CDE, DPS_FR_CASE_RECORDS.LIC_DL_STAY_CDE,
DPS_FR_CASE_RECORDS.RESULT_CDE, DPS_FR_CASE_RECORDS.RESULT_MO_CDE,
DPS_FR_CASE_RECORDS.RESULT_YR_CDE, DPS_FR_CASE_RECORDS.REVO_DATE,
DPS_FR_CASE_RECORDS.OFCR_NUM, DPS_FR_USER_TABLE.USER_NME,
DPS_FR_CASE_RECORDS.ACC_DATE, DPS_FR_CASE_RECORDS.HRG_DATE,
DPS_FR_CASE_RECORDS.HRG_TIME_TXT, DPS_FR_CASE_RECORDS.HRG_AM_PM_TXT,
DPS_FR_CASE_RECORDS.LOC_CDE, DPS_FR_HEARING_LOC.LOC_NME,
DPS_FR_HEARING_LOC.CITY_NME, DPS_FR_HEARING_LOC.STATE_CDE,
DPS_FR_HEARING_LOC.ROOM_NME, DPS_FR_HEARING_LOC.MEMO_TXT,
DPS_FR_CASE_RECORDS.LIC_FIRST_NME, DPS_FR_CASE_RECORDS.LIC_MIDDLE_NME,
DPS_FR_CASE_RECORDS.LIC_LAST_NME, DPS_FR_CASE_RECORDS.LIC_SUBT_TXT,
DPS_FR_CASE_RECORDS.LIC_ADDR_TXT, DPS_FR_CASE_RECORDS.LIC_CITY_NME,
DPS_FR_CASE_RECORDS.LIC_STATE_CDE, DPS_FR_CASE_RECORDS.LIC_ZIP_CDE,
DPS_FR_CASE_RECORDS.LIC_ZIP4_CDE, DPS_FR_CASE_RECORDS.LIC_DL_NUM,
DPS_FR_CASE_RECORDS.SECURITY_AMT, DPS_FR_CASE_RECORDS.DOA_NME,
DPS_FR_CASE_RECORDS.DOA_ADDR_TXT, DPS_FR_CASE_RECORDS.DOA_CITY_NME,
DPS_FR_CASE_RECORDS.DOA_STATE_CDE, DPS_FR_CASE_RECORDS.DOA_ZIP_CDE,
DPS_FR_CASE_RECORDS.DOA_ZIP4_CDE, DPS_FR_CASE_RECORDS.FLAG_CDE,
DPS_FR_CASE_RECORDS.BATCH_DATE, DPS_FR_CASE_RECORDS.BATCH_NUM,
DPS_FR_CASE_RECORDS.ATTY_NUM, DPS_FR_ATTORNEY.FIRST_NME,
DPS_FR_ATTORNEY.MIDDLE_NME, DPS_FR_ATTORNEY.LAST_NME,
DPS_FR_ATTORNEY.SUBTITLE_TXT, DPS_FR_ATTORNEY.FIRM_NME,
DPS_FR_ATTORNEY.ADDR1_TXT, DPS_FR_ATTORNEY.ADDR2_TXT,
DPS_FR_ATTORNEY.CITY_NME, DPS_FR_ATTORNEY.STATE_CDE, DPS_FR_ATTORNEY.ZIP_CDE,
DPS_FR_ATTORNEY.ZIP4_CDE, DPS_FR_HEARING_OFCR.OFCR_NME, Concatenate2("SELECT
OTHER_NME, FIRM_NME FROM DPS_FR_CASE_OTHERS WHERE
DPS_FR_CASE_OTHERS.CASE_NUM_YR =" & [DPS_FR_CASE_RECORDS.CASE_NUM_YR] & " AND
DPS_FR_CASE_OTHERS.CASE_NUM =" & [DPS_FR_CASE_RECORDS.CASE_NUM] & " ORDER BY
SEQ_NUM") AS OtherNames
FROM (((DPS_FR_CASE_RECORDS INNER JOIN DPS_FR_ATTORNEY ON
DPS_FR_CASE_RECORDS.ATTY_NUM = DPS_FR_ATTORNEY.ATTY_NUM) INNER JOIN
DPS_FR_HEARING_LOC ON DPS_FR_CASE_RECORDS.LOC_CDE =
DPS_FR_HEARING_LOC.LOC_CDE) INNER JOIN DPS_FR_USER_TABLE ON
DPS_FR_CASE_RECORDS.TYPIST_INIT_TXT = DPS_FR_USER_TABLE.TYPIST_INIT_TXT)
INNER JOIN DPS_FR_HEARING_OFCR ON DPS_FR_CASE_RECORDS.OFCR_NUM =
DPS_FR_HEARING_OFCR.OFCR_NUM
WHERE (((DPS_FR_CASE_RECORDS.PRTNO_NUM)=20))
ORDER BY DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM;


Code in form FRF-Letter-Menu as follows:

Case 20

' CHECK TO SEE IF THE USER WANTS TO PRINT CASE FINDINGS LETTERS
NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Findings Letters Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Legal Letterhead Forms" & Chr$(10)
& " Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseLHFindings", acViewNormal
MsgBox " Now Printing Case Hearing Letters "
ReadInTable
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT CASE EVELOPES NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Envelopes Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Envelope Forms" & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseEnvelope", acViewNormal
MsgBox " Now Printing Case Envelopes "
End If
End If

Case Else
MsgBox " No Letters Selected ! ", vbExclamation
End Select

End Sub

Private Sub Command9_Return_Click()
On Error GoTo Err_Command9_Return_Click
'When clicked, this button will redirect the user back to form
FRF-Main-Menu

DoCmd.Close

Exit_Command9_Return_Click:
Exit Sub

Err_Command9_Return_Click:
MsgBox Err.Description
Resume Exit_Command9_Return_Click

End Sub

Function ReadInTable()
MsgBox "Now Executing ReadInTable "
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Establish the connection, cursor type, and open the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenForwardOnly
rst.Open "Select * from DPS_FRQ_CR20RW"
'Print Proper Finding Letter Based on RESULT_CDE Value
'Loop through until EOF
Dim curCaseNumYr As Integer
Dim curCaseNum As Integer

Do Until rst.EOF
'curCaseNumYr = ""
'curCaseNum = ""
If rst![RESULT_CDE] = "11" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
curCaseNumYr = rst![CASE_NUM_YR]
curCaseNum = rst![CASE_NUM]
MsgBox " curCaseNumYr = " & curCaseNumYr
MsgBox " curCaseNum = " & curCaseNum
DoCmd.OpenReport "FRR-FOFRC11"
'Print current Record to form FRR-FOFRC11 1 Copy
ElseIf rst![RESULT_CDE] = "12" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC12 1 Copy
ElseIf rst![RESULT_CDE] = "14" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC14 1 Copy
ElseIf rst![RESULT_CDE] = "15" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC15 1 Copy
ElseIf rst![RESULT_CDE] = "16" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC16 1 Copy
ElseIf rst![RESULT_CDE] = "17" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC17 1 Copy
ElseIf rst![RESULT_CDE] = "18" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC18 1 Copy
ElseIf rst![RESULT_CDE] = "21" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC21 1 Copy
ElseIf rst![RESULT_CDE] = "22" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC22 1 Copy
ElseIf rst![RESULT_CDE] = "23" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC23 1 Copy
ElseIf rst![RESULT_CDE] = "24" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC24 1 Copy
ElseIf rst![RESULT_CDE] = "25" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC25 1 Copy
ElseIf rst![RESULT_CDE] = "26" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC16 1 Copy
ElseIf rst![RESULT_CDE] = "27" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC27 1 Copy
ElseIf rst![RESULT_CDE] = "28" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC28 1 Copy
ElseIf rst![RESULT_CDE] = "29" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC29 1 Copy
ElseIf rst![RESULT_CDE] = "31" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC31 1 Copy
ElseIf rst![RESULT_CDE] = "32" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC32 1 Copy
ElseIf rst![RESULT_CDE] = "34" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC34 1 Copy
ElseIf rst![RESULT_CDE] = "35" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC35 1 Copy
ElseIf rst![RESULT_CDE] = "37" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC37 1 Copy
ElseIf rst![RESULT_CDE] = "41" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC41 1 Copy
ElseIf rst![RESULT_CDE] = "42" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC42 1 Copy
ElseIf rst![RESULT_CDE] = "44" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC44 1 Copy
ElseIf rst![RESULT_CDE] = "45" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC45 1 Copy
ElseIf rst![RESULT_CDE] = "47" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC47 1 Copy
ElseIf rst![RESULT_CDE] Like "5%" Then
MsgBox " RESULT_CDE = " & rst![RESULT_CDE]
'Print current Record to form FRR-FOFRC5x 1 Copy
Else
MsgBox " INVALID RESULT_CDE FOUND = " & RESULT_CDE
End If
rst.MoveNext
Loop
rst.Close

End Function





"RNUSZ@OKDPS" wrote:

I have to print a specific report (form letter) based upon the value of the
data in field RESULT_CDE. The user executes this form, and upon entering a
code "20" to print Finding letters, it falls through the logic for reading
the DB2 table, inspecting field PRTNO_NUM for code 20 value, extracts records
with this value to temp table DPS_FR_CR20RW. Then code falls through to
open DPS_FR_CR20RW, to examine field RESULT_CDE for values. On specific
values, I need to print the contents of the current record to that specified
form. I'm not quite sure how to do it, whether to use the PrintOut command
or Open.Report function. To top things off, I'm not able to get a match on
the RESULT_CDE values in my compare code statements, and I need assistance to
code the correct syntax to find current record RESULT_CDE values. Any
assistance would be greatly appreciated. I'm finding all the correct number
of records in my temporary dataset, I just can't get a hit on the correct
compare, every display of RESULT_CDE comes back with null value in the
display..... Is what I want to accomplish possible?

Thanks,

Code follows...

Option Compare Database
'The form is used by the Legal System / Financial Responsibilites
Application. It
'is used as a Application Menu that allows the user to select the type
of Report Run
'to be requested: Hearing Letters, Cancellation Letters, or Findings
Letters.
'The code was last revised: 05/09/2005
'Developed on Microsoft Access 2003 Professional By Robert E. Nusz
'Application Support, Department of Public Safety, State of Oklahoma

Private Sub Combo13_Click()

Dim intThisRun As Integer 'used to identify type of run, 10, 13, 20
Dim strSQL As String 'used to hold SQL string for Alter Table
command
Dim strResultCde As String

If Combo13 = 10 Then
MsgBox " Now creating Hearing Letters ! "
DoCmd.runMacro "FRM-CR10RW"
strSQL = "ALTER TABLE DPS_FRQ_CR10RW " & _
"ADD CONSTRAINT PK_CR10RW " & _
"PRIMARY KEY(Case_Num_Yr,Case_Num)"
CurrentDb.Execute strSQL, dbFailOnError
MsgBox " Result Code Is " & RESULT_CDE
intThisRun = 10
MsgBox " This run is a type " & intThisRun
ElseIf Combo13 = 13 Then
MsgBox " Now Creating Cancellation Letters ! "
DoCmd.runMacro "FRM-CR13RW"
strSQL = "ALTER TABLE DPS_FRQ_CR13RW " & _
"ADD CONSTRAINT PK_CR13RW " & _
"PRIMARY KEY(Case_Num_Yr,Case_Num)"
CurrentDb.Execute strSQL, dbFailOnError
intThisRun = 13
MsgBox " This run is a type " & intThisRun
ElseIf Combo13 = 20 Then
MsgBox " Now Creating Finding Letters ! "
DoCmd.runMacro "FRM-CR20RW"
strSQL = "ALTER TABLE DPS_FRQ_CR20RW " & _
"ADD CONSTRAINT PK_CR20RW " & _
"PRIMARY KEY(Case_Num_Yr,Case_Num)"
CurrentDb.Execute strSQL, dbFailOnError
intThisRun = 20
MsgBox " This run is a type " & intThisRun
Else
MsgBox " Value Entered To Combo13 Field Was Invalid, Try Again ! "
End If

Select Case intThisRun
Case 10
' CHECK TO SEE IF THE USER WANTS TO PRINT CASE HEARING LETTERS
NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Hearing Letters Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Legal Letterhead Forms" & Chr$(10)
& " Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseLHHearings", acViewNormal
MsgBox " Now Printing Case Hearing Letters "
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT CASE EVELOPES NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Envelopes Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Envelope Forms" & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseEnvelope", acViewNormal
MsgBox " Now Printing Case Envelopes "
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT FOLDER LABELS NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Folder Labels Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Folder Label Forms " & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-FolderLabel", acViewNormal
MsgBox " Now Printing Case Folder Labels "
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT A NEW ALPHA-LIST
REPORT NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "A New
Alpha-List Report", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Plain Paper Forms " & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-Alpha-List", acViewNormal
MsgBox " Now Printing Alpha-List Report "
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT A NEW DOCKET-LIST
REPORT NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "A New
Docket List Report", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox("Are Plain Paper Forms" & Chr$(10) &
"Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-Docket", acviewNormal
MsgBox "Now Printing New Docket List"
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT NEW INFO-SHEETS NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "New F.R.
Info Sheets Now", _
vbQuestion + vbYesNo)
If Returnal = 6 Then
Returnval = MsgBox("Are Plain Paper Forms" & Chr$(10) &
"Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-InfoSheet", acviewNormal
MsgBox "Now Printing New Info Sheets"
End If
End If

Case 13
' CHECK TO SEE IF THE USER WANTS TO PRINT CASE CANCELLATION
LETTERS NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Cancellation Letters Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Legal Letterhead Forms" & Chr$(10)
& " Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseLHCancellations",
acViewNormal
MsgBox " Now Printing Case Cancellation Letters "
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT CASE EVELOPES NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Envelopes Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Envelope Forms" & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseEnvelope", acViewNormal
MsgBox " Now Printing Case Envelopes "
End If
End If
Case 20

' CHECK TO SEE IF THE USER WANTS TO PRINT CASE FINDINGS LETTERS
NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Findings Letters Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Legal Letterhead Forms" & Chr$(10)
& " Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseLHFindings", acViewNormal
MsgBox " Now Printing Case Hearing Letters "
ReadInTable
End If
End If

' CHECK TO SEE IF THE USER WANTS TO PRINT CASE EVELOPES NOW!
Returnval = MsgBox("Do You Wish To Print" & Chr$(10) & "Case
Envelopes Now", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
Returnval = MsgBox(" Are Envelope Forms" & Chr$(10) & "
Currently In Printer", _
vbQuestion + vbYesNo)
If Returnval = 6 Then
'DoCmd.OpenReport "FRR-CaseEnvelope", acViewNormal
MsgBox " Now Printing Case Envelopes "
End If
End If

Case Else
MsgBox " No Letters Selected ! ", vbExclamation
End Select

End Sub

Private Sub Command9_Return_Click()
On Error GoTo Err_Command9_Return_Click
'When clicked, this button will redirect the user back to form
FRF-Main-Menu

DoCmd.Close

Exit_Command9_Return_Click:
Exit Sub

Err_Command9_Return_Click:
MsgBox Err.Description
Resume Exit_Command9_Return_Click

End Sub

Function ReadInTable()
MsgBox "Now Executing ReadInTable "
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Establish the connection, cursor type, and open the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenForwardOnly
rst.Open "Select * from DPS_FRQ_CR20RW"
'Print Proper Finding Letter Based on RESULT_CDE Value
'Loop through until EOF
Do Until rst.EOF
If rst!RESULT_CDE = "11" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC11 1 Copy
ElseIf rst!RESULT_CDE = "12" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC12 1 Copy
ElseIf rst!RESULT_CDE = "14" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC14 1 Copy
ElseIf rst!RESULT_CDE = "15" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC15 1 Copy
ElseIf rst!RESULT_CDE = "16" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC16 1 Copy
ElseIf rst!RESULT_CDE = "18" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC18 1 Copy
ElseIf rst!RESULT_CDE = "21" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC21 1 Copy
ElseIf rst!RESULT_CDE = "22" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC22 1 Copy
ElseIf rst!RESULT_CDE = "23" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC23 1 Copy
ElseIf rst!RESULT_CDE = "24" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC24 1 Copy
ElseIf rst!RESULT_CDE = "26" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC16 1 Copy
ElseIf rst!RESULT_CDE = "28" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC28 1 Copy
ElseIf rst!RESULT_CDE = "29" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC29 1 Copy
ElseIf rst!RESULT_CDE = "31" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC31 1 Copy
ElseIf rst!RESULT_CDE = "32" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC32 1 Copy
ElseIf rst!RESULT_CDE = "34" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC34 1 Copy
ElseIf rst!RESULT_CDE = "35" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC35 1 Copy
ElseIf rst!RESULT_CDE = "41" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC41 1 Copy
ElseIf rst!RESULT_CDE = "42" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC42 1 Copy
ElseIf rst!RESULT_CDE = "44" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC44 1 Copy
ElseIf rst!RESULT_CDE = "45" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC45 1 Copy
ElseIf rst!RESULT_CDE Like "5%" Then
MsgBox " RESULT_CDE = " & RESULT_CDE
'Print current Record to form FRR-FOFRC5x 1 Copy
Else
MsgBox " INVALID RESULT_CDE FOUND = " & RESULT_CDE
End If
rst.MoveNext
Loop
rst.Close

End Function
--
Robert Nusz
Sr. Programmer Analyst II