Create Table Primary Key after Make Table Query And Update Table

G

Guest

I have an application that requires data to be updated after reports are
created. The user is prompted from form for a report type needed via
drop-down list (Combo13). User selects option(s) = 10 - Hearing Letters, or
13 – Cancellation Letters, or 20 – Finding Letters. Upon click, form code
executes following code to run Macro, pulling select records from table
creating temporary table that will need to be updated after reports are
created. If user selects 10, only table records with value 10 in PrtNo_Num
are selected, if user selects 13, only table records with value 13 in
PrtNo_Num are selected, etc….
Once the reports are ran, then field PrtNo_Num in temp file is set to null,
and records need to be written back to originating table so an update table
needs to be performed.
I also need to create a primary key on the temporary table for reporting
purposes. Primary key fields are Case_Num_Yr and Case_Num in that order.
I’ve tried the following code but get invalid syntax error, could someone
provide corrected syntax to build primary key for temporary table? Code
follows: Help is much appreciated, thanks, Robert

Private Sub Combo13_Click()
If Combo13 = 10 Then
MsgBox " Now creating Hearing Letters ! "
DoCmd.runMacro "FRM-CR10RW"
ALTER TABLE DPS_FRQ_CR10RW ADD CONSTRAINT PK_CR10RW
PRIMARY KEY(Case_Num_Yr,Case_Num)
(run reports code)
UPDATE DPS_FR_CASE_RECORDS
SET DPS_FR_CASE_RECORDS.PrtNo_Num = “â€
WHERE DPS_FR_CASE_RECORDS.Case_Num_Yr =
DPS_FRQ_CR10RW.Case_Num_Yr AND
DPS_FR_CASE_RECORDS.Case_Num =
DPS_FRQ_CR10RW.Case_Num
ElseIf Combo13 = 13 Then
MsgBox " Now Creating Cancellation Letters ! "
DoCmd.runMacro "FRM-CR13RW"
ALTER TABLE DPS_FRQ_CR13RW ADD CONSTRAINT PK_CR13RW
PRIMARY KEY(Case_Num_Yr,Case_Num)
(run reports code)
UPDATE DPS_FR_CASE_RECORDS
SET DPS_FR_CASE_RECORDS.PrtNo_Num = “â€
WHERE DPS_FR_CASE_RECORDS.Case_Num_Yr =
DPS_FRQ_CR13RW.Case_Num_Yr AND
DPS_FR_CASE_RECORDS.Case_Num =
DPS_FRQ_CR13RW.Case_Num
ElseIf Combo13 = 20 Then
MsgBox " Now Creating Finding Letters ! "
DoCmd.runMacro "FRM-CR20RW"
ALTER TABLE DPS_FRQ_CR20RW ADD CONSTRAINT PK_CR20RW
PRIMARY KEY(Case_Num_Yr,Case_Num)
(run reports code)
UPDATE DPS_FR_CASE_RECORDS
SET DPS_FR_CASE_RECORDS.PrtNo_Num = “â€
WHERE DPS_FR_CASE_RECORDS.Case_Num_Yr =
DPS_FRQ_CR20RW.Case_Num_Yr AND
DPS_FR_CASE_RECORDS.Case_Num =
DPS_FRQ_CR20RW.Case_Num
Else
MsgBox " Invalid Selection Chosen, Try Again ! "
End If

End Sub

-------------------------------------------------------------------------------------------------
Macro FRM-CR10RW follows:
OpenQuery (queryname=)FRQ-CR10RW
(view=)DATASHEET
(datamode=)EDIT
-------------------------------------------------------------------------------------------------

Query FRQ-CR10RW 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_CR10RW
FROM DPS_FR_CASE_RECORDS
WHERE (((DPS_FR_CASE_RECORDS.PRTNO_NUM)=10))
ORDER BY DPS_FR_CASE_RECORDS.CASE_NUM_YR, DPS_FR_CASE_RECORDS.CASE_NUM;
 
M

MGFoster

RNUSZ@OKDPS wrote:
I also need to create a primary key on the temporary table for reporting
purposes. Primary key fields are Case_Num_Yr and Case_Num in that order.
I’ve tried the following code but get invalid syntax error, could someone
provide corrected syntax to build primary key for temporary table? Code
follows: Help is much appreciated, thanks, Robert

Private Sub Combo13_Click()
If Combo13 = 10 Then
MsgBox " Now creating Hearing Letters ! "
DoCmd.runMacro "FRM-CR10RW"
ALTER TABLE DPS_FRQ_CR10RW ADD CONSTRAINT PK_CR10RW
PRIMARY KEY(Case_Num_Yr,Case_Num)
(run reports code)
UPDATE DPS_FR_CASE_RECORDS
SET DPS_FR_CASE_RECORDS.PrtNo_Num = “â€
WHERE DPS_FR_CASE_RECORDS.Case_Num_Yr =
DPS_FRQ_CR10RW.Case_Num_Yr AND
DPS_FR_CASE_RECORDS.Case_Num =
DPS_FRQ_CR10RW.Case_Num
< snip >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need to place the SQL commands in strings and then run the SQL
commands using DAO or ADO. The following uses DAO (be sure it is
checked in the VBA module Tools > References list box).

Dim strSQL 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

... etc. ...

To set a column value to NULL you do not use the empty string "", you
set it to NULL. The update statement you have won't work, because the
WHERE clause is incorrect. Try the following:

strSQL = "UPDATE DPS_FR_CASE_RECORDS " & _
"SET PrtNo_Num = NULL " & _
"WHERE EXISTS (SELECT * FROM DPS_FRQ_CR20RW " & _
"WHERE DPS_FR_CASE_RECORDS.Case_Num_Yr = Case_Num_Yr " & _
"AND DPS_FR_CASE_RECORDS.Case_Num = Case_Num)"

CurrentDB.Execute strSQL, dbFailOnError

The same goes for your other UPDATE commands.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnfMGoechKqOuFEgEQKiwwCfSd55G/iMs6/mZHJCQ2OBosrKwjQAn0hX
atFslXBjTe0VU26pYVVMZS0F
=bOi3
-----END PGP SIGNATURE-----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top