If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Open & Read Newly created Table for Reporting ?
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. -- Robert Nusz Sr. Programmer Analyst II |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Mixed up with Relationships..help! | KrazyRed | New Users | 3 | January 26th, 2005 05:03 AM |
Ms-Access 2003 | RC | General Discussion | 23 | January 19th, 2005 08:22 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |