R
RNUSZ@OKDPS
I have a form that is opened by user with drop-down list that lets user
select either a code 10, 13, or 20 to select type of report run for
application.
Each report type; 10, 13, or 20, creates different report types. Form
executes macros FRM-CR10RW if code 10 selected, macro FRM-CR13RW if code 13
selected, macro FRM-CR20RW if Code 20 selected.
Code in form follows:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - -
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
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
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
'If PRTNO_NUM = 10 Then
MsgBox " Hearing Run Requested "
MsgBox " We will print Hearing Letters "
MsgBox " We will print Envelopes "
‘code needed to open DPS_FR_CR10RW
MsgBox " We will print Single Folder Labels "
MsgBox " We will print a New F.R. Alpha List "
'stDocName = "FRR-Alpha-List"
'DoCmd.OpenReport "FRR-Alpha-List", acViewNormal
MsgBox " We will print a New F.R. Docket List "
'DoCmd.OpenReport "FRR-Docket", acViewNormal
MsgBox " We will print Individual Info Sheets "
Case 13
'If PRTNO_NUM = 13 Then
MsgBox " Cancellation Run Requested "
MsgBox " We will print Cancellation Letters "
MsgBox " We will print Envelopes "
Case 20
'If PRTNO_NUM = 20 Then
MsgBox " Findings Run Requested "
MsgBox " We will print Findings Letters "
MsgBox " We will print Envelopes "
Dim strResultCde As String
strResultCde = Mid$(RESULT_CDE, 3, 2)
MsgBox " strResultCde = " & strResultCde
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
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - -
Macro FRM-CR20RW follows:
OPEN QUERY FRQ-CR20RW (view)=DATASHEET (data mode)=EDIT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - -
Query FRQ-CR20RW follows:
SELECT DPS_FR_CASE_RECORDS.CASE_NUM_YR AS CASE_NUM_YR,
DPS_FR_CASE_RECORDS.CASE_NUM AS CASE_NUM, DPS_FR_CASE_RECORDS.TICKLE_DATE AS
TICKLE_DATE, DPS_FR_CASE_RECORDS.PRTNO_NUM AS PRTNO_NUM,
DPS_FR_CASE_RECORDS.PRTD_CDE AS PRTD_CDE, DPS_FR_CASE_RECORDS.FR_FILE_NUM AS
FR_FILE_NUM, DPS_FR_CASE_RECORDS.TYPIST_INIT_TXT AS TYPIST_INIT_TXT,
DPS_FR_CASE_RECORDS.LIC_STAT_CDE AS LIC_STAT_CDE,
DPS_FR_CASE_RECORDS.LIC_DL_STAY_CDE AS LIC_DL_STAY_CDE,
DPS_FR_CASE_RECORDS.RESULT_CDE AS RESULT_CDE, DPS_FR_CASE_RECORDS.REVO_DATE
AS REVO_DATE, DPS_FR_CASE_RECORDS.OFCR_NUM AS OFCR_NUM,
DPS_FR_CASE_RECORDS.ACC_DATE AS ACC_DATE, DPS_FR_CASE_RECORDS.CNTY_NUM AS
CNTY_NUM, DPS_FR_CASE_RECORDS.REQ_RECD_DATE AS REQ_RECD_DATE,
DPS_FR_CASE_RECORDS.HRG_DATE AS HRG_DATE, DPS_FR_CASE_RECORDS.HRG_TIME_TXT AS
HRG_TIME_TXT, DPS_FR_CASE_RECORDS.HRG_AM_PM_TXT AS HRG_AM_PM_TXT,
DPS_FR_CASE_RECORDS.LOC_CDE AS LOC_CDE, DPS_FR_CASE_RECORDS.ATTY_NUM AS
ATTY_NUM, DPS_FR_CASE_RECORDS.LIC_FIRST_NME AS LIC_FIRST_NME,
DPS_FR_CASE_RECORDS.LIC_MIDDLE_NME AS LIC_MIDDLE_NME,
DPS_FR_CASE_RECORDS.LIC_LAST_NME AS LIC_LAST_NME,
DPS_FR_CASE_RECORDS.LIC_SUBT_TXT AS LIC_SUBT_TXT,
DPS_FR_CASE_RECORDS.LIC_ADDR_TXT AS LIC_ADDR_TXT,
DPS_FR_CASE_RECORDS.LIC_CITY_NME AS LIC_CITY_NME,
DPS_FR_CASE_RECORDS.LIC_STATE_CDE AS LIC_STATE_CDE,
DPS_FR_CASE_RECORDS.LIC_ZIP_CDE AS LIC_ZIP_CDE,
DPS_FR_CASE_RECORDS.LIC_ZIP4_CDE AS LIC_ZIP4_CDE,
DPS_FR_CASE_RECORDS.LIC_DL_NUM AS LIC_DL_NUM,
DPS_FR_CASE_RECORDS.SECURITY_AMT AS SECURITY_AMT, DPS_FR_CASE_RECORDS.DOA_NME
AS DOA_NME, DPS_FR_CASE_RECORDS.DOA_ADDR_TXT AS DOA_ADDR_TXT,
DPS_FR_CASE_RECORDS.DOA_CITY_NME AS DOA_CITY_NME,
DPS_FR_CASE_RECORDS.DOA_STATE_CDE AS DOA_STATE_CDE,
DPS_FR_CASE_RECORDS.DOA_ZIP_CDE AS DOA_ZIP_CDE,
DPS_FR_CASE_RECORDS.DOA_ZIP4_CDE AS DOA_ZIP4_CDE,
DPS_FR_CASE_RECORDS.FLAG_CDE AS FLAG_CDE, DPS_FR_CASE_RECORDS.BATCH_DATE AS
BATCH_DATE, DPS_FR_CASE_RECORDS.BATCH_NUM AS BATCH_NUM,
DPS_FR_CASE_RECORDS.MEMO1_TXT AS MEMO1_TXT, DPS_FR_CASE_RECORDS.MEMO2_TXT AS
MEMO2_TXT, DPS_FR_CASE_RECORDS.MEMO3_TXT INTO DPS_FRQ_CR20RW
FROM DPS_FR_CASE_RECORDS
WHERE (((DPS_FR_CASE_RECORDS.PRTNO_NUM)=20))
ORDER BY DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - -
I've thus read my primary table and created a secondary new table named
DPS_FRQ_CR20RW, altered the table creating an new primary index for this
table.
I now need to use this table, open it and read each record, inspecting field
named
RESULT_CDE position 3 & position 4 of 6 digit field for a multitude of
values. On each different value, print a specific form letter. If
RESULT_CDE = 111405, then report FRR-RC14 would be printed due to 14 being in
position 3 & 4 of this field of current record, then loop back through read
process, read next sequential record, inspect record report type, print that
report.
Problem is I'm new to vb, and need guidance on proper use of open database,
read database, close database requirements. Would I need to use a Open
statement against current database or specify explicitly the new table name
DPS_FRQ_CR20RW, then, is there an example of reading sequentialy through this
table, and selecting records to report key reports, how about code needed to
close table?
Any assistance would be greatly appreciated.
select either a code 10, 13, or 20 to select type of report run for
application.
Each report type; 10, 13, or 20, creates different report types. Form
executes macros FRM-CR10RW if code 10 selected, macro FRM-CR13RW if code 13
selected, macro FRM-CR20RW if Code 20 selected.
Code in form follows:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - -
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
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
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
'If PRTNO_NUM = 10 Then
MsgBox " Hearing Run Requested "
MsgBox " We will print Hearing Letters "
MsgBox " We will print Envelopes "
‘code needed to open DPS_FR_CR10RW
MsgBox " We will print Single Folder Labels "
MsgBox " We will print a New F.R. Alpha List "
'stDocName = "FRR-Alpha-List"
'DoCmd.OpenReport "FRR-Alpha-List", acViewNormal
MsgBox " We will print a New F.R. Docket List "
'DoCmd.OpenReport "FRR-Docket", acViewNormal
MsgBox " We will print Individual Info Sheets "
Case 13
'If PRTNO_NUM = 13 Then
MsgBox " Cancellation Run Requested "
MsgBox " We will print Cancellation Letters "
MsgBox " We will print Envelopes "
Case 20
'If PRTNO_NUM = 20 Then
MsgBox " Findings Run Requested "
MsgBox " We will print Findings Letters "
MsgBox " We will print Envelopes "
Dim strResultCde As String
strResultCde = Mid$(RESULT_CDE, 3, 2)
MsgBox " strResultCde = " & strResultCde
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
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - -
Macro FRM-CR20RW follows:
OPEN QUERY FRQ-CR20RW (view)=DATASHEET (data mode)=EDIT
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - -
Query FRQ-CR20RW follows:
SELECT DPS_FR_CASE_RECORDS.CASE_NUM_YR AS CASE_NUM_YR,
DPS_FR_CASE_RECORDS.CASE_NUM AS CASE_NUM, DPS_FR_CASE_RECORDS.TICKLE_DATE AS
TICKLE_DATE, DPS_FR_CASE_RECORDS.PRTNO_NUM AS PRTNO_NUM,
DPS_FR_CASE_RECORDS.PRTD_CDE AS PRTD_CDE, DPS_FR_CASE_RECORDS.FR_FILE_NUM AS
FR_FILE_NUM, DPS_FR_CASE_RECORDS.TYPIST_INIT_TXT AS TYPIST_INIT_TXT,
DPS_FR_CASE_RECORDS.LIC_STAT_CDE AS LIC_STAT_CDE,
DPS_FR_CASE_RECORDS.LIC_DL_STAY_CDE AS LIC_DL_STAY_CDE,
DPS_FR_CASE_RECORDS.RESULT_CDE AS RESULT_CDE, DPS_FR_CASE_RECORDS.REVO_DATE
AS REVO_DATE, DPS_FR_CASE_RECORDS.OFCR_NUM AS OFCR_NUM,
DPS_FR_CASE_RECORDS.ACC_DATE AS ACC_DATE, DPS_FR_CASE_RECORDS.CNTY_NUM AS
CNTY_NUM, DPS_FR_CASE_RECORDS.REQ_RECD_DATE AS REQ_RECD_DATE,
DPS_FR_CASE_RECORDS.HRG_DATE AS HRG_DATE, DPS_FR_CASE_RECORDS.HRG_TIME_TXT AS
HRG_TIME_TXT, DPS_FR_CASE_RECORDS.HRG_AM_PM_TXT AS HRG_AM_PM_TXT,
DPS_FR_CASE_RECORDS.LOC_CDE AS LOC_CDE, DPS_FR_CASE_RECORDS.ATTY_NUM AS
ATTY_NUM, DPS_FR_CASE_RECORDS.LIC_FIRST_NME AS LIC_FIRST_NME,
DPS_FR_CASE_RECORDS.LIC_MIDDLE_NME AS LIC_MIDDLE_NME,
DPS_FR_CASE_RECORDS.LIC_LAST_NME AS LIC_LAST_NME,
DPS_FR_CASE_RECORDS.LIC_SUBT_TXT AS LIC_SUBT_TXT,
DPS_FR_CASE_RECORDS.LIC_ADDR_TXT AS LIC_ADDR_TXT,
DPS_FR_CASE_RECORDS.LIC_CITY_NME AS LIC_CITY_NME,
DPS_FR_CASE_RECORDS.LIC_STATE_CDE AS LIC_STATE_CDE,
DPS_FR_CASE_RECORDS.LIC_ZIP_CDE AS LIC_ZIP_CDE,
DPS_FR_CASE_RECORDS.LIC_ZIP4_CDE AS LIC_ZIP4_CDE,
DPS_FR_CASE_RECORDS.LIC_DL_NUM AS LIC_DL_NUM,
DPS_FR_CASE_RECORDS.SECURITY_AMT AS SECURITY_AMT, DPS_FR_CASE_RECORDS.DOA_NME
AS DOA_NME, DPS_FR_CASE_RECORDS.DOA_ADDR_TXT AS DOA_ADDR_TXT,
DPS_FR_CASE_RECORDS.DOA_CITY_NME AS DOA_CITY_NME,
DPS_FR_CASE_RECORDS.DOA_STATE_CDE AS DOA_STATE_CDE,
DPS_FR_CASE_RECORDS.DOA_ZIP_CDE AS DOA_ZIP_CDE,
DPS_FR_CASE_RECORDS.DOA_ZIP4_CDE AS DOA_ZIP4_CDE,
DPS_FR_CASE_RECORDS.FLAG_CDE AS FLAG_CDE, DPS_FR_CASE_RECORDS.BATCH_DATE AS
BATCH_DATE, DPS_FR_CASE_RECORDS.BATCH_NUM AS BATCH_NUM,
DPS_FR_CASE_RECORDS.MEMO1_TXT AS MEMO1_TXT, DPS_FR_CASE_RECORDS.MEMO2_TXT AS
MEMO2_TXT, DPS_FR_CASE_RECORDS.MEMO3_TXT INTO DPS_FRQ_CR20RW
FROM DPS_FR_CASE_RECORDS
WHERE (((DPS_FR_CASE_RECORDS.PRTNO_NUM)=20))
ORDER BY DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - -
I've thus read my primary table and created a secondary new table named
DPS_FRQ_CR20RW, altered the table creating an new primary index for this
table.
I now need to use this table, open it and read each record, inspecting field
named
RESULT_CDE position 3 & position 4 of 6 digit field for a multitude of
values. On each different value, print a specific form letter. If
RESULT_CDE = 111405, then report FRR-RC14 would be printed due to 14 being in
position 3 & 4 of this field of current record, then loop back through read
process, read next sequential record, inspect record report type, print that
report.
Problem is I'm new to vb, and need guidance on proper use of open database,
read database, close database requirements. Would I need to use a Open
statement against current database or specify explicitly the new table name
DPS_FRQ_CR20RW, then, is there an example of reading sequentialy through this
table, and selecting records to report key reports, how about code needed to
close table?
Any assistance would be greatly appreciated.