- Joined
- Jun 3, 2009
- Messages
- 2
- Reaction score
- 0
I'm using createquerydef to insert 2 similiar records into my trans table. The second record is overwriting the first. When I step thru the code, it doesn't overwrite. Tried lots of things but nothing works!
Dim qdefr As DAO.QueryDef
Dim strRDY As String
Set db = CurrentDb()
'creating (change trans record) to support updated barcode field change
strRDY = "INSERT INTO TRANS (model_num, trans_type, serial_num, cond_rcvd," & _
"delay_reason, ecn, subcust_cd, icn, compl_dt, nomen, apprv_procd, srvc_label," & _
"equip_type, mfr_cd, on_site, srvc_lab, metrl_cycle, cal_lab_std, next_due_dt," & _
"recall_dt, wl_norm_hrs, phase_id, sched_lab, cust_cd, metercard_flag, spcl_inst," & _
"shop_inv, block92, std_class, bar_cd, time_stamp )SELECT inv.model_num," & _
"'chg', inv.serial_num, inv.cond_rcvd, inv.delay_reason, inv.ecn," & _
"inv.subcust_cd, inv.icn, inv.compl_dt, inv.nomen, inv.apprv_procd, inv.srvc_label," & _
"inv.equip_type, inv.mfr_cd, inv.on_site, inv.srvc_lab, inv.metrl_cycle," & _
"inv.cal_lab_std, inv.next_due_dt, inv.recall_dt, inv.wl_norm_hrs, inv.phase_id," & _
"inv.sched_lab, inv.cust_cd, inv.metercard_flag, inv.spcl_inst, inv.shop_inv," & _
"inv.block92, inv.std_class, inv.bar_cd, Now() AS expr FROM inv INNER JOIN tmptbl " & _
"ON inv.ecn = tmptbl.ecn"
Set qdefr = CurrentDb.CreateQueryDef("", strRDY)
qdefr.Execute
qdefr.Close
Set qdefr = Nothing
Set db = Nothing
'creating (receipt trans record) to support updated barcode field change
strRDY = "INSERT INTO TRANS ( model_num, trans_type, shop_num, cal_job_ord," & _
"rep_job_ord, mod_job_ord, rcvd_dt, serial_num, cond_rcvd, delay_reason, ecn," & _
"subcust_cd, icn, compl_dt, nomen, apprv_procd, srvc_label, equip_type, mfr_cd," & _
"on_site, srvc_lab, metrl_cycle, cal_lab_std, next_due_dt, recall_dt, wl_norm_hrs," & _
"phase_id, sched_lab, cust_cd, metercard_flag, spcl_inst, shop_inv, block92," & _
"std_class, bar_cd, time_stamp )SELECT inv.model_num, 'rec', inv.shop_num," & _
"inv.cal_job_ord, inv.rep_job_ord, inv.mod_job_ord, inv.rcvd_dt, inv.serial_num," & _
"inv.cond_rcvd, inv.delay_reason, inv.ecn, inv.subcust_cd, inv.icn, inv.compl_dt," & _
"inv.nomen, inv.apprv_procd, inv.srvc_label, inv.equip_type, inv.mfr_cd," & _
"inv.on_site, inv.srvc_lab, inv.metrl_cycle, inv.cal_lab_std, inv.next_due_dt," & _
"inv.recall_dt, inv.wl_norm_hrs, inv.phase_id, inv.sched_lab, inv.cust_cd," & _
"inv.metercard_flag, inv.spcl_inst, inv.shop_inv, inv.block92, inv.std_class," & _
"inv.bar_cd, Now() AS expr FROM inv INNER JOIN tmptbl ON inv.ecn = tmptbl.ecn"
Set qdefr = CurrentDb.CreateQueryDef("", strRDY)
' db.QueryTimeout = 20
qdefr.Execute
qdefr.Close
Set qdefr = Nothing
Set db = Nothing
Dim qdefr As DAO.QueryDef
Dim strRDY As String
Set db = CurrentDb()
'creating (change trans record) to support updated barcode field change
strRDY = "INSERT INTO TRANS (model_num, trans_type, serial_num, cond_rcvd," & _
"delay_reason, ecn, subcust_cd, icn, compl_dt, nomen, apprv_procd, srvc_label," & _
"equip_type, mfr_cd, on_site, srvc_lab, metrl_cycle, cal_lab_std, next_due_dt," & _
"recall_dt, wl_norm_hrs, phase_id, sched_lab, cust_cd, metercard_flag, spcl_inst," & _
"shop_inv, block92, std_class, bar_cd, time_stamp )SELECT inv.model_num," & _
"'chg', inv.serial_num, inv.cond_rcvd, inv.delay_reason, inv.ecn," & _
"inv.subcust_cd, inv.icn, inv.compl_dt, inv.nomen, inv.apprv_procd, inv.srvc_label," & _
"inv.equip_type, inv.mfr_cd, inv.on_site, inv.srvc_lab, inv.metrl_cycle," & _
"inv.cal_lab_std, inv.next_due_dt, inv.recall_dt, inv.wl_norm_hrs, inv.phase_id," & _
"inv.sched_lab, inv.cust_cd, inv.metercard_flag, inv.spcl_inst, inv.shop_inv," & _
"inv.block92, inv.std_class, inv.bar_cd, Now() AS expr FROM inv INNER JOIN tmptbl " & _
"ON inv.ecn = tmptbl.ecn"
Set qdefr = CurrentDb.CreateQueryDef("", strRDY)
qdefr.Execute
qdefr.Close
Set qdefr = Nothing
Set db = Nothing
'creating (receipt trans record) to support updated barcode field change
strRDY = "INSERT INTO TRANS ( model_num, trans_type, shop_num, cal_job_ord," & _
"rep_job_ord, mod_job_ord, rcvd_dt, serial_num, cond_rcvd, delay_reason, ecn," & _
"subcust_cd, icn, compl_dt, nomen, apprv_procd, srvc_label, equip_type, mfr_cd," & _
"on_site, srvc_lab, metrl_cycle, cal_lab_std, next_due_dt, recall_dt, wl_norm_hrs," & _
"phase_id, sched_lab, cust_cd, metercard_flag, spcl_inst, shop_inv, block92," & _
"std_class, bar_cd, time_stamp )SELECT inv.model_num, 'rec', inv.shop_num," & _
"inv.cal_job_ord, inv.rep_job_ord, inv.mod_job_ord, inv.rcvd_dt, inv.serial_num," & _
"inv.cond_rcvd, inv.delay_reason, inv.ecn, inv.subcust_cd, inv.icn, inv.compl_dt," & _
"inv.nomen, inv.apprv_procd, inv.srvc_label, inv.equip_type, inv.mfr_cd," & _
"inv.on_site, inv.srvc_lab, inv.metrl_cycle, inv.cal_lab_std, inv.next_due_dt," & _
"inv.recall_dt, inv.wl_norm_hrs, inv.phase_id, inv.sched_lab, inv.cust_cd," & _
"inv.metercard_flag, inv.spcl_inst, inv.shop_inv, inv.block92, inv.std_class," & _
"inv.bar_cd, Now() AS expr FROM inv INNER JOIN tmptbl ON inv.ecn = tmptbl.ecn"
Set qdefr = CurrentDb.CreateQueryDef("", strRDY)
' db.QueryTimeout = 20
qdefr.Execute
qdefr.Close
Set qdefr = Nothing
Set db = Nothing