Class Roster entry and delete suggestion

[isaacski]

Hi All!,
I have a form that comes from one main table. The form will be used to make a class roster. There are choices of Trainer, Class Name, and Start Date. The only other thing on the main form is a text box for the New employee name. The user then clicks add student and the new "list" is displayed as a subform.

(It is set up so that the user doesn't have to keep choosing the class, trainer and date but these values are carried over to the next record and the list is compiled and shown below based on the selections in the boxes for these values)

This works great for our purpose however I need a way for the user to be able to delete a record if they make a mistake in the input. So, I would like the user to be able to click the record in the subform and delete it from there somehow.

I'm thinking the back-end process needs to be to click the subform record which will open the record in the parent form and then have a button to press to delete the record. I can't figure out how to click the record in the subform and have it open in the main form.

Let me know what you guys think!

Thanks

 

[Wiz47]

Private Sub CmdDelete_Click()

[COLOR="Green"]‘Create the message box to warn that deletion is permanent. [/COLOR]

If MsgBox("Deleting will permanently DELETE the Record" & vbCrLf & " Do You Really Want To Delete It?", vbInformation + vbYesNo, "DELETE RECORD!") = vbYes Then

[COLOR="Green"]‘Turn warnings off[/COLOR]

DoCmd.SetWarnings False

[COLOR="Green"]‘Delete the current record[/COLOR] 

DoCmd.RunSQL "DELETE * FROM main_table WHERE (ID = " & Me.ID & ")"

[COLOR="Green"]'Requery the form.[/COLOR]

Me.Requery 

[COLOR="Green"]‘Turn warnings back on[/COLOR] 

DoCmd.SetWarnings True

Else Exit Sub 
  End If 
    End Sub

VBA to delete the current record where Main_Table is the table, ID is the primary key and Me. is the current form.

 

[Wiz47]

Wiz, there is no reason to run a delete query. You can just delete the record with:

If Me.Dirty = True Then
    Me.Dirty = False
End If
DoCmd.RunCommand acCmdDeleteRecord

If you want to prompt to confirm a delete, put the code in the Form's BeforeDelConfirm event.

Also, running an action query that affects the current record can result in errors if the current record is dirty.

I run the delete query so I can specify the current record as the delete target with the where statement. But I agree, I should have checked for is dirty before proceeding.

 

[Wiz47]

There is no reason to run a delete query nor should you. Why make Access process a query when you already have a recordset open that you can work with? I work in environments with high transaction volumes so I am sensitive to wasteful techniques and this is one of them. I/O (input/output) is the most expensive operation you can perform and so you should learn to make it efficient. I'm not talking about going out of your way to optimize code. I'm only talking about the most obvious inefficiencies that you should automatically avoid. You are already sitting on the record you want to delete. There is no benefit and only downside to running a query to delete it rather than using the integral Access methods.

I suppose we're all animals of habit. It's how I was taught, and I guess what I'm comfortable with. That's not to say that you haven't made some excellent points. All of which I will incorporate in my new designs. Thanks again for the input.