The Error 3022 Duplicate Problem

[boywonder381]

I dimmed all variables and still get the same error 3022.

[boywonder381]

try www.itclosure.com/images/AFormv16Sa.accdb

[June7]

Explicitly declaring data type for variables would not be expected to fix the issue. That was just info about programming practice.

Need info about the business process. Why is this code behind a user login form? Why are you programmatically creating records at login? Why are you using temp tables?

Why are there 4 ScheduleCourses tables instead of just one?

The error handler makes debugging difficult. There are lots of errors built into the code that are handled with ResumeNext. This means cannot disable the error handler. Most of these ResumeNext errors seem to be related to a form that is not open but is referenced in code. Better would be to use code that checks if the form is open and sets variables appropriately, instead of repeatedly referencing objects/controls that aren't available. Or set a Boolean variable and use that in subsequent lines as a condition for referencing the form.

One error that can be immediately fixed is:

LunchBeg = rsShifts!LunchBeg

references LunchBeg but the field name is actually LunchBegin. The ResumeNext error handler allowed that error to slip by.

If you want to avoid the warning popup for table updates, use
DoCmd.SetWarnings False
DoCmd.RunSQL ...
DoCmd.SetWarnings True

or

CurrentDb.Execute ...

strDivide is declared as a string type variable but it is used in division which causes a datatype mismatch error if it is empty. Another runtime error that is passed by the ResumeNext error handler. However, setting this variable as double or integer will still cause an error with the division because can't divide by 0 and if set as variant and allowed to be null this causes an error in another line.

This line errors because of missing r:
rsScheduleCourses![Shifts] = strShifts

rsScheduleCourses1 is not a declared variable.

Every module should have Option Explicit in the header. This will catch errors like misspelled and undeclared variables.

I want the debugger to stop on line that triggers error 3022 - not branch to the error handler, so I commented out all the references to the unopened form and disabled the error handler but keep getting these runtime errors that interfere with debugging the issue of interest. Very annoying.

Run Debug > Compile to catch at least two code errors.

Clean up code. Test, debug. If you still have issues, post revision.

[boywonder381]

OK, Thanks

The reason for the 4 tblScheduleCourses tables are crazy. I need to delete 2 of them. One suppose to be for the history table and the other so the user can play with the scheduler until it is approved. I will clean the code up and try it again... The temp tables are used so when I divide all table records by 5, I will be able to add records to another table and afterward, it delete that temp table and repeat 5 times. I fix the LunchBeg error... All this does is create the table to view a scheduler. The only user I want to create this scheduler is the admin user. I had to split the code because it had reached it capacity of 64k. Once this is created there are 3 to 4 more processes/reports that will use this table.

[June7]

I hope you got the point that the ResumeNext for all non-specified errors is a bad idea.

[boywonder381]

I know... I was using it while coding and found that it was bypassing crucial data. I just haven't deleted it.

[boywonder381]

I found what was wrong. The Scheduler Course table's StartTime field had to be corrupted for some reason. I went in and started just testing that table and it was giving me the 3022 error when I tried to use the the same time per the StartTime field. I deleted it and add it back in and everything started working good. Thanks everyone for your comments.