Issue with appending

[MkIIISupra]

Need a little help resolving this issue.

I have a database that I am using to schedule classes. Simple enough really but here is the issue I am encountering, when I need to delete a student that is scheduled and re-assign the quota to the main schedule it is creating a duplicate in the wrong date. Now that I have confoozeled you....

Here is an example of what I have going on.

tbl_MASTER_Schedule
mbrSsn
mbrLastName
mbrFirstName
mbrMi
mbrRate
mbrShopId
mbrEmail
courseRequested
courseLevel
classDate
confNumber
sima

tblClassBuild_Tool
courseRequested
courseLevel
classDate

This is really simple, the tblClassBuild_Tool which is accessed by the frmClassBuild_Tool, is used to create the schedule for each Quarter. So each class scheduled will appear at a minimum 3 times per quarter.

Here is an example of what it looks like

Access Introduction 06/24/02
Access Intermediate 06/25/02
Word Introduction 06/26/02
Word Intermediate 06/27/02
Access Introduction 07/01/02
Access Intermediate 07/02/02

This is the initial build for the schedule, these classes are appended to tblAccess and tblWord etc... I use a do loop for that here is the code ---

Private Sub btnBuildClassSchedule_Click()
On Error GoTo Err_btnBuildClassSchedule_Click
Dim counter As Integer

' THIS WILL CLEAR THE OLD SCHEDULE OUT SO THAT THERE IS NO OVERLAP OR DUPLICATE _
  ENTRIES.
    stDocName = "qry_DELETE_ALL_Access"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    stDocName = "qry_DELETE_ALL_Excel"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    stDocName = "qry_DELETE_ALL_Outlook"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    stDocName = "qry_DELETE_ALL_PowerPoint"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    stDocName = "qry_DELETE_ALL_Word"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

' THIS WILL BUILD THE REQUIRED NUMBER OF SEATS
Do
' THIS QUERY TAKES THE INPUT _
  TO tbl_MASTER_CourseScheduling TABLE AND BUILDS THE tblClassRequest _
  FOR USE BY THE FRONT DESK PERSONEL TO SCHEDULE CLASSES.
    stDocName = "qryCreateAccessSchedule"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    stDocName = "qryCreateWordSchedule"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    stDocName = "qryCreateExcelSchedule"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    stDocName = "qryCreateOutlookSchedule"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    stDocName = "qryCreatePowerPointSchedule"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
'    stDocName = "qryCreate_MASTER_ClassSchedule"
'    DoCmd.OpenQuery stDocName, acNormal, acEdit
    counter = counter + 1
Loop Until counter = 20
    
    DoCmd.Close
    
'    stDocName = "frm_SWITCHBOARD_ONE"
'    DoCmd.OpenForm stDocName, , , stLinkCriteria
'    stDocName = "qry_DELETE_ClassSchedule"
'    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_btnBuildClassSchedule_Click:
    Exit Sub

Err_btnBuildClassSchedule_Click:
    MsgBox Err.Description
    Resume Exit_btnBuildClassSchedule_Click

End Sub

Now be low is the delete code I created to append a student and delete them from the tbl_MASTER_Schedule.

Private Sub btnDeleteStudent_Click()
On Error GoTo Err_btnDeleteStudent_Click
Dim count As Integer
count = 0

' THIS COMMAND IS USED TO REMOVE A STUDENT FROM THE ROSTER AND REPLACE THE _
  QUOTA BACK INTO THE MASTER TABLE FOR RE-ISSUE. _
  1 IN THE MsgBox IS vbOKCancel

If MsgBox("ARE YOU SURE ABOUT THIS? THERE IS NO UNDO...", vbOKCancel, "DELETE STUDENT") = vbOK Then
    
    Do
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        count = count + 1
    Loop Until count = 2
      
        stDocName = "qryCreateAccessSchedule"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
        btnDeleteStudent.Enabled = False
        DoCmd.Close
        stDocName = "frmAccessScheduledLookup"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        
Else
        
        MsgBox "NO RECORDS WILL BE DELETED", vbOKOnly, "CANCEL RECORD DELETE."
    
End If

Exit_btnDeleteStudent_Click:
    Exit Sub

Err_btnDeleteStudent_Click:
    MsgBox Err.Description
    Resume Exit_btnDeleteStudent_Click
    
End Sub

The issue is when I run the delete command I wrote above it appends all records not just the selected ones. How do I get the query to select only the ones I have. Keep in mind the dates are different.

If a student signs up for the 17th for an introduction class then he/she is assigned to an intermediate class on the 18th as well and if a student drops and we delete them from the specific schedule and we want to append the dropped class back into the master schedule we need just the intro and interm dates of the selected student.

If you need more info or want a copy of what I am doing let me know... Thanks Again!

V/R GSM1(SW)McDonald USN One by one the penguins steal my sanity!

 

[MkIIISupra]

Bump.... One by one the penguins steal my sanity!