Form to copy data: show records that would create duplicate PK's (1 Viewer)

[cnstarz]

First thing: My db is on a different network, so I can't provide a sample. I can only provide a screenshot and retyped VBA code.

In my DB, we task out our different units (squadrons/divisions) to do different things on a daily basis. We don't want to over-task them, so I created a way for us to give each unit/squadron a "capacity". My unbound form has a chart that counts how many times a unit's been tasked that day along with their capacity so we can easily see if we're over-tasking them.

There will be times when one unit's daily capacity will not change from day-to-day, and there will be times when a unit's capacity will change (IE: holidays, personnel emergencies, etc). So, instead of having our guys manually set the same capacity every for a unit every single time, I created a way for them to copy them from one day to another. Unit capacities are kept in a table called tbUnitCapacity_History that consists of these fields:

UnitCapacity_History_ID (AutoNumber -- to give each record a unique number)
DCOUnit_ID (FK*, Text -- the name of the tasked Unit/Squadron)
CapacityDate (*, Date/Time -- the day that the Unit's capacity applies to)
Capacity (Number -- Unit's available capacity for that day)
Copy (Yes/No -- Checkbox for the user to tell which Unit capacities to copy)

* DCOUnit_ID and CapacityDate are Composite PrimaryKeys. A unit should not have multiple records/capacities on the same day.

Here is what the form looks like:

How the form works is this: A user chooses a day/date from the combo-box at the top of the form. The subform on the left (based off tbUnitCapacity_History) reloads displaying Unit Capacities that have been entered for the day the user selected. The chart on the right also reloads showing a count of tasks that each unit has been tasked for that day along with their remaining capacity (Capacity minus CountOfTasks for that day). The chart is based on a query that queries a table where all the tasks are maintained.

The checkbox (Copy) in the subform lets the user select which capacities they want to copy. The combobox above the chart is where the user can specify which day the user wants to copy the selected capacities to. Finally, the button "Go!" executes the copy:

Private Sub btCopyCaps_Click()
On Error GoTo Err_Handler

        Dim strSQL1 As String
        Dim strSQL2 As String
        Dim strNewDate As String
        Dim db As DAO.Database
        Dim rsQ As DAO.Recordset
        
    [COLOR=SeaGreen]'Select all records for date selected that user wants to copy[/COLOR]
        strSQL1 = "Select tbUnitCapacity_History.Copy, tbUnitCapacity_History.UnitCapacity_History_ID " & _
            "FROM tbUnitCapacity_History " & _
            "WHERE (((tbUnitCapacity_History.CapacityDate)=#" & Me.cboDate & "#) " & _
                "AND ((tbUnitCapacity_History.Copy=True));"
        
        Set db = CurrentDb()
        Set rsQ = db.OpenRecordset(strSQL1, dbOpenDynaset)
        
    [COLOR=SeaGreen]'Save any edits first[/COLOR]
        If Me.Dirty Then
            Me.Dirty = False
        End If
    
    [COLOR=SeaGreen]'If a record isn't selected, prompt user to select a record.  Otherwise, copy.[/COLOR]
        If rsQ.RecordCount < 1 Then
            MsgBox "Plese select a Unit Capacity to copy."
            Exit Sub
        Else
            strSQL2 = "INSERT INTO tbUnitCapacity_History ( DCOUnit_ID, CapacityDate, Capactiy ) " & _
                "SELECT DCOUnit_ID, " & """" & Me.cboNewToDay & """, Capacity " & _
                "FROM tbUnitCapacity_History " & _
                "WHERE (((tbUnitCapacity_History.CapacityDate)=#" & Me.cboDate & "#) " & _
                    "AND ((tbUnitCapacity_History.Copy)=True));"
            
            DBEngine(0)(0).Execute strSQL2, dbFailOnError
        End If
         
    [COLOR=SeaGreen]'Set the form to the new TO Day that the capacities were copied to[/COLOR]
        Me.cboDate = Me.cboNewToDay
        Me.Requery
        
Exit_Sub:
        Set db = Nothing
        Set rsQ = Nothing
        db.Close
        rsQ.Close
        Exit Sub
        
Err_Handler:
        If Err.Number = 3022 Then [COLOR=SeaGreen]'Duplicate Primary Key will be created[/COLOR]
            MsgBox "A unit's capacity already exists for TO Day " & _
                Me.cboNewToDay.Column(0) & ".  When the page reloads, verify " & _
                "all capacity information is correct."
            Resume Next
        End If

The Err_Handler handles duplicate primary keys (a unit having multiple records for the same day) the would be created and is the only way I've been able to give the user a "friendly" way of indicating that the copy will not work. There are 2 problems with this method:

1. The Insert Query (strSQL2) will stop and not copy ANY UnitCapacities if a duplicate primary key would be created. I would like it to copy the remaining records that are fine, and just not do anything for the records that would have created duplicate primary keys.
2. It doesn't capture which Unit(s) already have a Capacity specified for the new date and would create duplicate primary keys. This leaves the user guessing which Unit(s) is causing the problem. The only way to do that is to switch back and forth between the two days to see who's already there and who isn't.

I hope I haven't lost your interest already or confused you too much! What options do I have for the 2 problems above? Thanks so much for your help!

 

[pbaldy]

Try dropping the part in red:

DBEngine(0)(0).Execute strSQL2, dbFailOnError

I use that in a similar situation. Without dbFailOnError, the query should run and simply not add the records that would create duplicates.

 

[vbaInet]

1. Why not eliminate those entries that will create a duplicate in the first place?
2. If you know how to do (1) you'll be able to display those duplicate units.