Deleting Record Selected in Subform

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

I have a main form with a subform datasheet listing all employees.

how can I have the user select a record in the subform datasheet, then click
a button on the main form which will then delete that subform record?

So, they would click on the row next to the record they want to delete,
which would highlight the entire row, then press the DELETE button on the
main form which would delete the record.

Thanks!

Joe
 
Once you click on the main form or one of its controls, the focus moves back
to the main form, but the selected record in the subform remains the active
record for the subform. Have the button run a Delete query to delete the
record, then requery the subform.

Example:
strSQL = "Delete * From tblMyTable Where [UniqueIDField]=" &
Me.ctlNameOfSubformControl.Form.txtUniqueID
CurrentDb.Execute strSQL, dbFailOnError
Me.ctlNameOfSubformControl.Form.Requery

The strSQL line assumes the value is a number, if not, adjust the quotes to
handle text.
strSQL = "Delete * From tblMyTable Where [UniqueIDField]=""" &
Me.ctlNameOfSubformControl.Form.txtUniqueID & """"
 
Joe,

Something like this will do it...

CurrentDb.Execute "DELETE * FROM Subform'sTable WHERE IdField=" &
Me.SubformName!IdField
Me.SubformName.Requery

You wouldn't need to highlight the whole row, this will apply to
whatever is the current record in the subform.
 
:
Joe,
Something like this will do it...

CurrentDb.Execute "DELETE * FROM Subform'sTable WHERE IdField=" &
Me.SubformName!IdField
Me.SubformName.Requery

You wouldn't need to highlight the whole row, this will apply to
whatever is the current record in the subform.
Click to expand...

Hi, this thread seems to answer my question, but I just need a little more
guidance. My subform datasheet is on one of the pages of a Tab Control,
instead of on the main form --but I suppose this wouldn't make any difference
to Joe's case.

I have amended the code above to suit my case (as shown below), but error
message returns for 1st command line "Expected: expression" and for 2nd
command line "Expected: ="

NameOfDatabase.Execute "DELETE * FROM NameOfSubformRecordSource WHERE
NameOfIDField=" &
Me.NameOfSubform!NameOfIdField
Me.NameOfSubform.Requery

Much appreciated!
 
Sam,

I am not sure what you are referring to with "NameOfDatabase" in the
first line of your code, but the CurrentDb I used in my example to Joe
is a function which specifies an instance of your database, so I suggest
you try yours with this.

Also, it seems that you may heve been affected by the wordwrap in your
newsreader of the earlier post. The code you posted is supposed to only
be 2 lines altogether. The whole string from "CurrentDb" to
"Me.SubformName!IdField" is supposed to be all one line.
 
Thanks Steve. because I'm a newbie to Access coding :P
Is there any reading source you'd recommend for a beginner like me?
 
Back
Top