Requery causing duplicate table entry

[MLEBL]

I'm creating a scheduling application for a hospital department that indicates when they've exceeded the minimum required staff for a particular shift. I have a table keeping track of the count and another table that contains the miniumum values based on specific shifts and roles. For example, I know the exact number of staff needed at 2 AM, and I have a staff member working from 11PM through 7AM so I need to indicate that this person will fulfill one of the required slots at 2AM. I also need to account for staff carrying over into a different day, as indicated in this example, so I have to create counts for multiple days (as in day before scheduled date or day after scheduled date). I need to provide this information for multiple days. When I created the information for the initial day, I started getting the non-descript "Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience." I embedded a requery command in the code and it all functions correctly for the first day. When I try and enter information on subsequent days, I get an error that the requery command is creating a duplicate entry in the database. Since this code finds current entries for a role and a shift then adds one to the count, I don't understand how it could be creating duplicate values. Here's the vb code for the module:

Option Compare Database
'OpenArgs:=Me.ScheduleDate & "|" & Me.StaffRole & "|" & Me.StaffShift
Private Sub Form_Load()
DoCmd.Requery
Dim TempRole As Integer
Dim strSQL As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim varSplit As Variant
Dim FinalLoop, LoopCount, TempAMG, TempARN, TempCAR, TempCCA, TempCRN, TempEDU, TempEMT, TempIT, TempLPN, TempMGR, TempPRR, TempRN, TempUC As Integer
Dim Starting As String
Dim OriginalDate As Date
Dim tsd As Date
Dim OriginalDatePlusOne As Date
If Me.OpenArgs <> vbNullString Then
    varSplit = Split(Me.OpenArgs, "|")
    OriginalDate = varSplit(0)
    OriginalDatePlusOne = DateAdd("D", 1, varSplit(0))
    TempRole = varSplit(1)
End If
'if schedule date exists
If DCount("[ScheduleDate]", "CoreScheduleCountOfPosition", "[ScheduleDate] = #" & OriginalDate & "#") Then
    Me.ScheduleDate = varSplit(0)
    tsd = DateAdd("d", 1, OriginalDate)

'if schedule date + 1 exists
    If DCount("[ScheduleDate]", "CoreScheduleCountOfPosition", "[ScheduleDate] = #" & tsd & "#") Then
        FinalLoop = DLookup("Duration", "ListOfShifts", "ID =" & varSplit(2)) + 1
        Starting = DLookup("StartTime", "ListOfShifts", "ID =" & varSplit(2))
        DoCmd.Requery
        Me.Recordset.FindFirst "[ScheduleDate] = #" & OriginalDate & "# and [TimeSlot] = '" & Starting & "'"
'    End If
'update loop
        For LoopCount = 1 To FinalLoop
            Call Initialize
            If TempRole = 1 Then
                Me.ManagerCount = TempMGR + 1
            End If
            If TempRole = 2 Then
                Me.AsstManagerCount = TempAMG + 1
            End If
            If TempRole = 3 Then
                Me.EducCount = TempEDU + 1
            End If
            If TempRole = 4 Then
                Me.ITCount = TempIT + 1
            End If
            If TempRole = 5 Then
                Me.PRRCount = TempPRR + 1
            End If
            If TempRole = 6 Then
                Me.CARCount = TempCAR + 1
            End If
            If TempRole = 7 Then
                Me.ARNCount = TempARN + 1
            End If
            If TempRole = 8 Then
                Me.CRNCount = TempARN + 1
            End If
            If TempRole = 9 Then
                Me.RNCount = TempRN + 1
            End If
            If TempRole = 10 Then
                Me.LPNCount = TempLPN + 1
            End If
            If TempRole = 11 Then
                Me.EMTCount = TempEMT + 1
            End If
            If TempRole = 12 Then
                Me.UCCount = TempUC + 1
            End If
            If TempRole = 13 Then
                Me.CCACount = TempCCA + 1
            End If
            Me.CSCStatus = "W"
            If LoopCount <= FinalLoop Then
                DoCmd.GoToRecord , , acNext
            End If
        Next LoopCount
        Else:
        
'create and update second set if it doesn't exist
            Dim tc As Integer
            For k = 1 To 24
                    If k >= 1 And k <= 10 Then
                        Me.TimeSlot = "0" & k - 1 & ":00"
                    Else:
                        Me.TimeSlot = k - 1 & ":00"
                    End If
                Me.ScheduleDate = tsd
                Call SetToZero
                Me.CSCStatus = "W"
                If k <= 24 Then
                    DoCmd.GoToRecord , , acNewRec
                End If
            Next k
        FinalLoop = DLookup("Duration", "ListOfShifts", "ID =" & varSplit(2)) + 1
        Starting = DLookup("StartTime", "ListOfShifts", "ID =" & varSplit(2))
        Me.Recordset.FindFirst "[ScheduleDate] = #" & varSplit(0) & "# and [TimeSlot] = '" & Starting & "'"
        For LoopCount = 1 To FinalLoop
            Call Initialize
            If TempRole = 1 Then
                Me.ManagerCount = TempMGR + 1
            End If
            If TempRole = 2 Then
                Me.AsstManagerCount = TempAMG + 1
            End If
            If TempRole = 3 Then
                Me.EducCount = TempEDU + 1
            End If
            If TempRole = 4 Then
                Me.ITCount = TempIT + 1
            End If
            If TempRole = 5 Then
                Me.PRRCount = TempPRR + 1
            End If
            If TempRole = 6 Then
                Me.CARCount = TempCAR + 1
            End If
            If TempRole = 7 Then
                Me.ARNCount = TempARN + 1
            End If
            If TempRole = 8 Then
                Me.CRNCount = TempARN + 1
            End If
            If TempRole = 9 Then
                Me.RNCount = TempRN + 1
            End If
            If TempRole = 10 Then
                Me.LPNCount = TempLPN + 1
            End If
            If TempRole = 11 Then
                Me.EMTCount = TempEMT + 1
            End If
            If TempRole = 12 Then
                Me.UCCount = TempUC + 1
            End If
            If TempRole = 13 Then
                Me.CCACount = TempCCA + 1
            End If
            Me.CSCStatus = "W"
            If LoopCount <= FinalLoop Then
                DoCmd.GoToRecord , , acNext
            End If
        Next LoopCount
        End If
    Else:
    
'create if second exists but first doesn't
    DoCmd.GoToRecord , , acNewRec
        If DCount("[ScheduleDate]", "CoreScheduleCountOfPosition", "[ScheduleDate] = #" & OriginalDatePlusOne & "#") Then
           For i = 1 To 24
                Me.ScheduleDate = OriginalDate
                If i >= 1 And i <= 10 Then
                    Me.TimeSlot = "0" & (i - 1) & ":00"
                Else:
                    Me.TimeSlot = (i - 1) & ":00"
                End If
                Call SetToZero
                Me.CSCStatus = "W"
                If i <= 24 Then
                    DoCmd.GoToRecord , , acNewRec
                End If
            Next i
            FinalLoop = DLookup("Duration", "ListOfShifts", "ID =" & varSplit(2)) + 1
            Starting = DLookup("StartTime", "ListOfShifts", "ID =" & varSplit(2))
            Me.Recordset.FindFirst "[ScheduleDate] = #" & varSplit(0) & "# and [TimeSlot] = '" & Starting & "'"
            For LoopCount = 1 To FinalLoop
                Call Initialize
                If TempRole = 1 Then
                    Me.ManagerCount = TempMGR + 1
                End If
                If TempRole = 2 Then
                    Me.AsstManagerCount = TempAMG + 1
                End If
                If TempRole = 3 Then
                    Me.EducCount = TempEDU + 1
                End If
                If TempRole = 4 Then
                    Me.ITCount = TempIT + 1
                End If
                If TempRole = 5 Then
                    Me.PRRCount = TempPRR + 1
                End If
                If TempRole = 6 Then
                    Me.CARCount = TempCAR + 1
                End If
                If TempRole = 7 Then
                    Me.ARNCount = TempARN + 1
                End If
                If TempRole = 8 Then
                    Me.CRNCount = TempARN + 1
                End If
                If TempRole = 9 Then
                    Me.RNCount = TempRN + 1
                End If
                If TempRole = 10 Then
                    Me.LPNCount = TempLPN + 1
                End If
                If TempRole = 11 Then
                    Me.EMTCount = TempEMT + 1
                End If
                If TempRole = 12 Then
                    Me.UCCount = TempUC + 1
                End If
                If TempRole = 13 Then
                    Me.CCACount = TempCCA + 1
                End If
                Me.CSCStatus = "W"
                If LoopCount <= FinalLoop Then
                    If Me.TimeSlot = "23:00" Then
                        Me.Recordset.FindFirst "[ScheduleDate] = #" & OriginalDatePlusOne & "# and [TimeSlot] = '00:00'"
                    Else:
                        DoCmd.GoToRecord , , acNext
                    End If
                End If
            Next LoopCount
        Else:

'create both sets if neither exists
            For i = 1 To 24
                Me.ScheduleDate = OriginalDate
                If i >= 1 And i <= 10 Then
                    Me.TimeSlot = "0" & (i - 1) & ":00"
                Else:
                    Me.TimeSlot = (i - 1) & ":00"
                End If
                Call SetToZero
                If i <= 24 Then
                    DoCmd.GoToRecord , , acNewRec
                End If
            Next i
            For i = 1 To 24
                Me.ScheduleDate = OriginalDatePlusOne
                If i >= 1 And i <= 10 Then
                    Me.TimeSlot = "0" & (i - 1) & ":00"
                Else:
                    Me.TimeSlot = (i - 1) & ":00"
                End If
                Call SetToZero
                Me.CSCStatus = "W"
                If i <= 24 Then
                    DoCmd.GoToRecord , , acNewRec
                End If
            Next i

            FinalLoop = DLookup("Duration", "ListOfShifts", "ID =" & varSplit(2)) + 1
            Starting = DLookup("StartTime", "ListOfShifts", "ID =" & varSplit(2))
            Me.Recordset.FindFirst "[ScheduleDate] = #" & varSplit(0) & "# and [TimeSlot] = '" & Starting & "'"
            For LoopCount = 1 To FinalLoop
                Call Initialize
                If TempRole = 1 Then
                    Me.ManagerCount = TempMGR + 1
                End If
                If TempRole = 2 Then
                    Me.AsstManagerCount = TempAMG + 1
                End If
                If TempRole = 3 Then
                    Me.EducCount = TempEDU + 1
                End If
                If TempRole = 4 Then
                    Me.ITCount = TempIT + 1
                End If
                If TempRole = 5 Then
                    Me.PRRCount = TempPRR + 1
                End If
                If TempRole = 6 Then
                    Me.CARCount = TempCAR + 1
                End If
                If TempRole = 7 Then
                    Me.ARNCount = TempARN + 1
                End If
                If TempRole = 8 Then
                    Me.CRNCount = TempARN + 1
                End If
                If TempRole = 9 Then
                    Me.RNCount = TempRN + 1
                End If
                If TempRole = 10 Then
                    Me.LPNCount = TempLPN + 1
                End If
                If TempRole = 11 Then
                    Me.EMTCount = TempEMT + 1
                End If
                If TempRole = 12 Then
                    Me.UCCount = TempUC + 1
                End If
                If TempRole = 13 Then
                    Me.CCACount = TempCCA + 1
                End If
                Me.CSCStatus = "W"
                If LoopCount <= FinalLoop Then
                    DoCmd.GoToRecord , , acNext
                End If
            Next LoopCount
        End If
    End If
'End If
DoCmd.Requery
DoCmd.Close acForm, "BuildScheduleCount2"
End Sub

Public Sub Initialize()
            TempAMG = Me.AsstManagerCount
            TempARN = Me.ARNCount
            TempCAR = Me.CARCount
            TempCCA = Me.CCACount
            TempCRN = Me.CRNCount
            TempEDU = Me.EducCount
            TempEMT = Me.EMTCount
            TempIT = Me.ITCount
            TempLPN = Me.LPNCount
            TempMGR = Me.ManagerCount
            TempPRR = Me.PRRCount
            TempRN = Me.RNCount
            TempUC = Me.UCCount
End Sub

Public Sub SetToZero()
                Me.ManagerCount = 0
                Me.AsstManagerCount = 0
                Me.EducCount = 0
                Me.ITCount = 0
                Me.PRRCount = 0
                Me.CARCount = 0
                Me.ARNCount = 0
                Me.CRNCount = 0
                Me.RNCount = 0
                Me.LPNCount = 0
                Me.EMTCount = 0
                Me.UCCount = 0
                Me.CCACount = 0
End Sub

Any assistance with this would be greatly appreciated!

Thanks

[zmbd]

While I take a second to digest your code please do the following steps:

[Z{Edit} What version of Access are you using? From line 11 it appears that you are using one of the older versions as in 2003 the single line "dim (muli varible) as type" was discouraged and in 2007/2010 will cause you issues. You should go back and change anything looking like:
Dim FinalLoop, LoopCount, TempAMG As Integer
to
Dim FinalLoop As Integer, LoopCount As Integer, TempAMG As Integer
This way when you upgrade you wont run into the typecasting issue.{/edit}]


Please go to your vba editor in MS Access
Then go to the Ribbon/toolbar:> Tools :> Options
Editor tab:
Uncheck "Auto Syntax Check" all this does is add an annoying pop-up that says... you goofed... click ok... the line will already turn red when you mis-form the code.

Check Mark "Require Variable Declaration" (this will place the option explicate at the top of every new code module)
checkmark all of the remaining options.

I like an auto indent of 4; however, I wouldn't go less than 3 or more than 5 just as a personal preference.
The remaining stuff can be left as default.

Now go thru all of your form modules, standard modules, and class modules and make sure that the following two lines are the VERY first two lines in every one of these modules

Option Compare Database 
Option Explicit

Now compile your project.
Fix any errors
Re-compile your project
Repeat the above three lines until you can compile the project without an error

If you do find and correct any errors, we'll need to see the new code.

[Rabbit]

This is only from a cursory glance.

1) On line 95, 151, 163, 231, 244 and you are creating new records. That's your most likely cause of duplicates.

2) Your database design looks unnormalized.

3) You are probably making this much more complicated than it needs to be with all that code. If you have all the necessary data in the tables, all you need is a single query to check whether or not a time frame is covered.

[zmbd]

Please list the relavent tables as follows:

Tablename
[fieldname] (type) (key/index) (relationships)

Example:
tbl_A
[A_pk] (autonumber)(Pr imaryKey)
[A_fk_B] (long)(foriegn key 1 to Many with tbl_b)(required , no nulls)
[A_field1] (text(25))

tbl_B
[B_pk] (autonumber)(Pr imaryKey)(B_1:m _with tbl_a)
[B_fk_C] (long)(foriegn key 1 to Many with tbl_C)(required , no nulls)
[B_field1] (text(25)) Required, no nulls, indexed no duplicates
[B_field2] (long)

Example data would help... just a few records

If needed for privacy
Names can be changed to "John Doe" and "Jane Doe" etc...
Phone numbers to (123)555-0001, (123)555-0002 etc...
Same thing with SSN

I don't need every table, just the ones that feed the form and feed each other.

I don't need every record, just an example or three so long as they reflect the overall data.

-
Why I'm asking is that it appears that you have hard coded a lot of your roles and other information and then are trying hand-bash your human resources against these hardcoded slots to get your counts. This is the source of your issues.

Depending on how your tables are setup, you may be able to do this with a crosstab query and may not need any VBA or at least will be able to cut your code to a smaller and more comprehensible set.
-

I realize that once you've put so much effort into doing the project it's somewhat daunting to have feedback that says...
"there's maybe a better way." Please don't take this as personal comment on your abilities - but as one peer to another in the spirit of a helping hand.

I would highly recommend that you go ahead and read thru the following two links... I have an suspicion that you'll find them useful:
A Tutorial for Access
Database Normalization and Table Structures.

[MLEBL]

Rabbit Responses:
1) the process works fine when I'm modifying the intial date, it's only when I move over to a subsequent date that I get the duplicate error.
2) in what sense? I have created several tables to minimize the amount of data that will be stored in the scheduling table (so instead of name, role, shift, ... being stored each time someone is scheduled, it only has to store the appropriate mapped key integer).
3) UNDERSTATEMENT! This thing is SOOO complex. I suppose I could create an update query that replaces the logic for incrementing the values in the count to simplify this section.

BTW, thanks for the help back in November . . . I warned you I'd be back!

[MLEBL]

zmbd response:
I tried making the changes you suggested and get the same errors. I'm running 2010 (code identified was probably a format I utilized from reviewing old Access suggestions). The only problem is that I started running into problems in my public sub module for Initialize saying that it couldn't find the variables. I've embedded the Dim statement into the sub module to get this working. It still works fine on the first day of scheduling but gives me the duplicate error on subsequent days. I've confirmed that the values I'm pushing are the appropriate values for the date I'm updating. Based on the point in the vb code where I get the error, it's identifying the date/role/shift as existing so it shouldn't be creating a new value. Would you be open to a participating in a webex so you can see this monster first hand. I believe that I've created my tables appropriately based on the functionality that I'm trying to create.

[zmbd]

Rabbit, looks like I cross posted with you!

I just found those same lines... I had to step out after my last post asking for the tables.... hydrogen leak inside one of my instruments. A very bad thing... easy fix... just bad thing.

MLEBL, you did warn us. :)
Tables please.
You shouldn't need any maketable or other things... I think once we see the DB structure the solution will present itself quite nicely. I have faith in Rabbit My SQL master, or perhaps this padawan will be able to find the solution. :)

Sorry, at work pc... webex is barred.

[Rabbit]

1) Again, it was only a cursory glance. But the fact that you are creating records each time it is run is probably the cause of the errors. So that's where you need to start investigating. You need to look at how it's creating those records and what it's putting into those records so that can figure out where it's causing the duplication.

2) You haven't posted your table structure so I can only make inferences about the design. The reason it looks like unnormalized is because you have fields like this: ManagerCount, ITCount, CARCount, etc. On the face of it, having multiple fields for different counts is unnormalized.

3) I don't have the time right now to get into this point but I will come back to it later today. I really do think you are overcomplicatin g the solution.

[MLEBL]

Here's the breakdown of the relevent tables:

Table CoreSchedule
DateTime Date/Time
ScheduleDate Date/Time Primary Key
StaffMember Number Primary Key (1:1 staff table)
StaffShift Number Primary Key (1:1 ListOfShifts table)
StaffRole Number lookup in ListOfPostiion table
Status Text Primary Key ("W"-working, "F"-final)
StaffShiftStart Date/Time
StaffShiftEnd Date/Time

Table CoreScheduleCou ntOfPosition
ScheduleDate Date/Time Primary Key
TimeSlot Text Primary Key
ManagerCount Number
AsstManagerCoun t Number
EducCount Number
ITCount Number
PRRCount Number
CARCount Number
ARNCount Number
CRNCount Number
RNCount Number
LPNCount Number
EMTCount Number
UCCount Number
CCACount Number
CSCStatus Text

Table ListOfPosition
ID AutoNumber Primary Key
Position Text

Table ListOfShifts
ID AutoNumber Primary Key
ShiftDescriptio n Text
StartTime Text
EndTime Text
Duration Number

Table Staff
ID AutoNumber Primary Key
StaffName Text
Role Number Lookup in ListOfStaffRole
PreferredArea Number Lookup in ListOfAreas
PrimaryContactN umber Text
FTEStatus Number Lookup in ListOfFTE
PreferredShift Number Lookup in ListOfPreferred Shift
PreferredHours Number Lookup in ListOfPreferred Hours
HireDate Date/Time
DeployDate Date/Time
Agency Yes/No
Traveler Yes/No
ActiveStatus Yes/No
DistantEmployee Yes/No
Wave Number Lookup in Wave

[Rabbit]

I still don't have the time right now to go into the SQL solution but a quick note on the design. Most of it looks normalized except for CoreScheduleCou ntOfPosition.

The structure should be:
ScheduleDate Date/Time
TimeSlot Text
Position Text
StaffNeeded Number

The reason you would want to normalize down to this level is for future changes. If you add a new position to your old design, you would have to change the design of the table and update all your queries, reports, and forms. Whereas the normalized design only requires adding a row into that table. Everything else will handle itself.

When I get more time, I will go into the SQL solution.

[NeoPa]

Looking through the other comments briefly, it seems they cover the same sort of comments I am thinking of. You should not be considering storing half the information you are storing, but query it from the existing data. The db design is the most important aspect of any project and Normalisation should be the basis of any design. The more complex the design, the more important the Normalisation rules are. There is already a link to the article that I would like to draw your attention to. Please read it. Without that understanding it will be very difficult to make any progress on this project at all.

One point I would raise though, is that finding overlapping dates (or timestamps) is another fundamental of a project such as this. You may find some interesting points on the fundamentals of this in Time Interval Overlapping (MS Access 2003).

[MLEBL]

I started out normalizing to this level, but due to client requirements I couldn't figure out how to incorporate this into the design. The requirement is that the manager creates the required staffing numbers for each hour and each position in a spreadsheet design via this Access tool. The count table refers back to the requirements table to make sure that all appropriate positions are filled. This whole thing is easier to show than to explain, so maybe when you get some time I can establish a webex to show you how this works. I only have test staff in the table currently (Micky Mouse . . . etc), so it wouldn't be a problem showing you the database and contents.

[NeoPa]

I mean no disrespect, but if you're still thinking in terms of continuing with what you have already, I see no reason to believe that you could explain your situation clearly and succinctly even with a webex session, which would involve a level of time commitment on one of our parts. I see no signs of you having a clear enough appreciation of your own situation even, let alone the clarity to explain to people who all know better anyway (and have been trying to impart to you the results of their experience). It's possible I'm wrong, I suppose, but I'm not planning to spend a fair bit of time proving something I'm already reasonably certain of, to someone who isn't happy to accept advice after they've asked for it. Even when three separate and experienced helpers have all come on here and replied with the same basic advice.

I sympathise with you for the hole you've got yourself into, but until you stop digging there's little we can say to help anyway.

[MLEBL]

You may not have meant it, but it comes off as completely disrespectful. I've indicated that I initially started down the road indicated by all three experts only to have been derailed by the client's requests. My lack of experience with Access is probably the root of the issue, but I still don't see where you've provided direction to assist (only pointing to online articles on database normality). If you read Rabbit's post, all of my tables appear to be normailzed with the exception of the CoreScheduleCou ntOfPosition which I explained has to refer back to a detailed list of expected staffing counts created by the client. I suppose I'll have to continue muddling through this on my own.

[zmbd]

MLEBL:
whew... take a deep breath.
Give Rabbit and myself a chance to take a look here too.

We all understand that occasionally the end-user doesn't have the greatest idea or has a perceived need that we have to work with/around or teach them that there is a better way to do the task. Hopefully once we’ve hit things with a hammer or two, you’ll have a tool to work with to train the user.

I’ll also make note that you did not tell us until post #12 that table CoreScheduleCou ntOfPosition is a workaround for a customer requirement. It would have been most helpful to know that when you posted the table outline. We can only work with what you give us. Thus, I surmise Neopa’s frustration with the apparent lack of interest on your part in what we’ve suggested so far.

Now. If you look at when I posted the links all you had posted was code; that in itself has a few issues, and even then I could tell that there was some issue with the design of the database; however, without the tables I couldn’t even begin to help you unless I tried to build a database myself to solve your project. Something I just don’t have the time today to do.

Going back to the your tables that I asked for to confirm what I had suspected in that your database was not truly normalized. In that Rabbit found even one table that is not normalized, then by definition, makes the entire database non-normalized. HOWEVER, Master-Rabbit stated that there might be a SQL solution to your quandary; thus, all is not lost.

So, once again, please take a deep breath and let’s continue to work thru this.

As you can already see, providing little details like customer requirements will help us to understand what you’re after; therefor, if there are any more such tidbits you would like to share, now is the time do so.

As for the webex session… as I told you in my earlier post, My IT blocks all such third party services so you’re out of luck on that part from me. They even block skype and magic jack!

Anyway back to the lab to put out another issue. Shame I had to spend the time trying to calm people down here and in lab instead of doing the fun stuff in either place. Sigh
v:-(