Check for existing record then Update query

[dbar10]

I have a Master schedule. When complete I have a command button to append to the [Schedule1] table. I want a query to run to check for existing records first (in case they already were appended. If no records found then I want my append query to run. I don't know how to write the code for this, can you help?

Command button: On Click "CommandAppend"
"ScheduleDupChkqry" 'runs first if finds records then:
Open "Update Schedule" form. 'This tells user that this action has already been performed
If No records found then:
Run "SchedulingAppendqry"

Thanks for all your help

 

[dhookom]

Not enough information about your table structures. Generally, you can run an append query based on a LEFT or RIGHT join of your source table to your target table.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

OK sorry about that. Both my queries work independantly I just didn't know how to write the code to make them run on the IIf, then. But I'm always willing to learn from you.

"ScheduleDupChkqry" :
SELECT Schedule1.SchedID, Schedule1.ClientID, Schedule1.WorkerID, Schedule1.AuthID, Schedule1.JobID, Schedule1.ProgramID, Schedule1.Date, +1 AS Record
FROM Schedule1
WHERE (((Schedule1.ClientID)=Forms![Master Schedule]!ClientIDNum) And ((Schedule1.AuthID)=Forms![Master Schedule]!Authorization) And ((Schedule1.JobID)=Forms![Master Schedule]!ComboJob) And ((Schedule1.ProgramID)=Forms![Master Schedule]!ComboProgram) And ((Schedule1.Date)>=Forms![Master Schedule]!Start And (Schedule1.Date)<=Forms![Master Schedule]!TextEndDate));

SchedulingAppendqry:
INSERT INTO Schedule1 ( ClientID, WorkerID, AuthID, ProgramID, JobID, [Date], [Day], Start, [End] )
SELECT MasterSchedMain.ClientID, SchDaySched.Worker, MasterSchedMain.AuthID, Program.ProgramID, MasterSchedMain.JobID, SchedCalDaytbl.Cal, SchDaySched.Day, MasterSchedMain.Starttime, MasterSchedMain.Endtime
FROM (MasterSchedMain INNER JOIN Program ON MasterSchedMain.ProgramID = Program.ProgramID) INNER JOIN (SchedCalDaytbl INNER JOIN SchDaySched ON SchedCalDaytbl.[Day ID] = SchDaySched.Day) ON MasterSchedMain.MasterSchedID = SchDaySched.MasterSchedID
WHERE (((MasterSchedMain.ClientID)=[Forms]![Master Schedule]![ClientIDNum]) AND ((MasterSchedMain.AuthID)=[Forms]![Master Schedule]![Authorization]) AND ((Program.ProgramID)=[Forms]![Master Schedule]![ComboProgram]) AND ((MasterSchedMain.JobID)=[Forms]![Master Schedule]![ComboJob]) AND ((MasterSchedMain.Starttime)=[Forms]![Master Schedule]![Starttime]) AND ((MasterSchedMain.Endtime)=[Forms]![Master Schedule]![Endtime]) AND ((MasterSchedMain.StartDate)<=[SchedCalDaytbl].[Cal]) AND ((MasterSchedMain.ExpireDate) Is Null Or (MasterSchedMain.ExpireDate)>=[SchedCalDaytbl].[Cal]));


Schedule1 table
SchedID Autonumber PK
ClientID Number
WorkerID Number
AuthID Number
JobID Number
ProgramID Number
Date Date/Time
Day Text
Start Date/Time
End Date/Time
Verified Yes/No
Cancel Yes/No
Reason Text
NoNeed Yes/No

I hope this helps, thanks

 

[dhookom]

My first effort would be to link ScheduleDupChkqry to SchedulingAppendqry in a query that includes all records from SchedulingAppendqry. Add SchedID from ScheduleDupChkqry to the grid and set its criteria to:
Is Null

This will append all records from MasterSchedMain etc where there isn't a matching record in ScheduleDupChkQry.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks I will give that a shot. I really appreciate your help. I will get back with you if I have trouble.

 

[dbar10]

I couldn't add the SchedulingAppendqry with ScheduleDupChkqry to the grid. Access wouldn't show the Append query. So I copied the append query and changed it to Select query with the name SchedulingAppqry. Then linked the two by the AuthID. When I run it does not return any record just the column headers. I tried changing the link to ClientID with same results. each query runs by itself but there must be a conflict with the two. Any ideas?

 

[dhookom]

Did you try to change the join to a left or right join?

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Yes, I tried left and right joins with the authid as link and as Clientid as link. Same results as before.

 

[dhookom]

I would join on the fields that would be used to determine if the records were duplicates. I don't think this would be the ID fields.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Ahhh, I see what your saying. My queries might be built on the wrong premise. I will work on this some more and get back with you. Thanks

 

[dbar10]

INSERT INTO Schedule1 ( SchedID, DaySchedID, MasterSchedID, ClientID, WorkerID, AuthID, ProgramID, JobID, [Date], [Day], Start, [End] )
SELECT ScheduleDupChkQry.SchedID, SchedulingAppqry.DaySchedID, SchedulingAppqry.MasterSchedID, SchedulingAppqry.ClientID, SchedulingAppqry.Worker, SchedulingAppqry.AuthID, SchedulingAppqry.ProgramID, SchedulingAppqry.JobID, SchedulingAppqry.Cal, SchedulingAppqry.[Day ID], SchedulingAppqry.Starttime, SchedulingAppqry.Endtime
FROM ScheduleDupChkQry RIGHT JOIN SchedulingAppqry ON (ScheduleDupChkQry.MasterSchedID = SchedulingAppqry.MasterSchedID) AND (ScheduleDupChkQry.DaySchedID = SchedulingAppqry.DaySchedID)
WHERE (((ScheduleDupChkQry.SchedID) Is Null));

The queries look like they are working fine now until I add the SchedID from the ScheduleDupChkQry. I get the error message:
"You tried to assign the Null value to a variable that is not a Variant data type."

What?

 

[dbar10]

I figured it out!!! I had the SchedID field appending to the Schedule1 table. Once I took that out, it seems to work fine. Thanks