How to delete a record when a combo box value is Null?

StagIraq
StagIraq used Ask the Experts™
on
Hey, first post on this site.

I am building an Access db that encompasses scheduling of students, syllabus events, and media (simulator, classroom, etc…).  I have a functioning form built to input a daily schedule.  This form draws its input fields from a query derived from two tables (StudentResume and SimulatorLog). To schedule the simulator event, the company scheduler will open the form (opens to blank fields), and when he selects a student then a NewRecord is created in both the StudentResume and SimulatorLog tables.  This works fine, and the scheduler can change students and other event items as required.  
Where I run into a problem is this: once the new record is created, if the scheduler changes a student in this period, and he deletes the student’s name, then I need to delete the record from the StudentResume…but I do not want to delete the record from the SimulatorLog table.  
I tried using the AfterUpdate event for the studentName field on the form, and making all of the input controls that were tied to the StudentResume table blank:
      If textStudentName = Null then
            Me.textStartDate = Null
            Me.textStudentResumeID = Null

…and such, but that did not work.

I added a button to delete the record, but it of course deleted the recordset created by the query the form was based upon.

After searching this forum, I tried various combinations derived from other posts such as:
If cboStudent = Null Then
      DoCmd.RunCommand acCmdDeleteRecord

If cboStudent = Null Then
      Me.RecordsetClone.Delete

And I even tried a poorly tweaked sql line of:
DELETE from "studentresume" WHERE ID = me.txtStudResumeId;statement.executeUpdate(sql);
[as I am not familiar with sql this was a very futile effort on my part]

I would like to know a way to delete the record from the StudentResume table based upon the StudentResumeID (primary Key), and this would be visible on the form by the input fields that apply to that table would go back to being blank (or show “new record” like the field does when the form loads) displayed in the textStudentResumeID input text box.

This form creates the scheduled use of a period for a training device.  If the student falls out, then the device is still tracked as available, so that is why I cannot simply use the delete record button that I mentioned above.
I look forward to your ideas/solutions.

Thanks,
Stag
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I can't really follow your description, but here's some code to delte one row fromt he studentresume table and clear the text box:

docmd.runsql ("Delete from studentresume where StudentResumeID=" & me.txtStudResumeId)
me.txtStudResumeID = NULL

Author

Commented:
dqmq, thanks for the quick response.  I placed your suggestion into the "after update" event.  It did not delete the record from the table, nor did it blank out the text bos showing the StudResumeID?

Private Sub cboStudent_AfterUpdate()
   
    If cboStudent = Null Then
      DoCmd.RunSQL ("Delete from studentresume where StudentResumeID=" & Me.txtStudResumeId)
      Me.txtStudResumeId = Null
      GoTo 1
      End If
    ' the following is the normal updates to populate the fields for the new record    
    Me.CboStudEvent = Null
    Me.CboStudEvent.Requery
    Me.CboStudEvent = Me.CboStudEvent.ItemData(0)
    Me.TextStatus = Null
    Me.textMemberID = cboStudent.Value
    Me.textStudEventDate = Date
    Me.SimDate = Date
    Me.txtSyllEventID = Null
    Me.txtSyllEventID = CboStudEvent.Value
    Me.TextStatus = "S"
    'Me.FrameSimAvail = 1 (trying to get button showing sim avail to be "on"  
1
End Sub

Sorry if I misunderstood where your code should have been place to be effective?

Thanks,
Stag
Commented:
This condition is NEVER true:

If cboStudent = Null Then  ...


If  "X" = Null   is false
If  "X" <> Null is false
If Null = Null is false

Null cannot be compared to anything.   You need to do like this:

If cboStudent is Null Then  ...

or like this:

If isnull(cboStudent) then




Author

Commented:
I tried your advice and the version that worked was:

If isnull(cboStudent) then


Thank you!
Stag

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial