Subform - which records are selected...

Hi
Is there any way of finding out the records (rows) highlighted in a
sub-form?

For instance say the user highlights 3 records in the subform and then hits
the DELETE key, is there any way of seeing which records will be deleted?

I am asking because I would like to delete each of the records manually. Or
perhaps there another approach to achieve the same result?

thanks
daryl

Use SelHeight and SelTop to see which records are highlighted.

The easiest solution might be to use a custom button on the toolbar to
handle the deletion if you want to do it manually. It cannot be a command
button on the form, since clicking that loses the multi-record selection.

If you wish to work with the built-in events, the form's Delete event will
fire once for each record in the selection, and the values are available so
you could record them into an array or temp table, or perform whatever other
actions you need to perform. Then the form's BeforeDelConfirm event fires,
and the user can cancel the deletion. Then the AfterDelConfirm event fires,
and you can read the Status argument to see if the delete really occurred.
The actual values being deleted are not available in BeforeDelConfirm or
AfterDelConfirm.

Could your issue be solved simply by creating relations with cascading
deletes?

Another possibility in JET 4 (Access 2000 and later) is to use
Cascade-to-Null relations. The related records are not deleted, but the
foreign key field is set to Null when the main record is deleted.
Cascade-to-Null relations can only be created programmatically, but it's not
difficult. Post back if you want more details.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daryl" wrote in message
...

Is there any way of finding out the records (rows) highlighted in a
sub-form?

For instance say the user highlights 3 records in the subform and then
hits
the DELETE key, is there any way of seeing which records will be deleted?

I am asking because I would like to delete each of the records manually.
Or
perhaps there another approach to achieve the same result?

Thanks for the reply.

My problem is with using an adp. It appears as the events associated with a
delete are rearranged to limit the transactions with SQL Server. It appears
to me that the OnDelete event fires before the actual records are deleted.
Therefore I am looking for a manual way of doing it.

Are you familiar with MS access data projects???

thanks
daryl

"Allen Browne" wrote in message
...
Use SelHeight and SelTop to see which records are highlighted.

The easiest solution might be to use a custom button on the toolbar to
handle the deletion if you want to do it manually. It cannot be a command
button on the form, since clicking that loses the multi-record selection.

If you wish to work with the built-in events, the form's Delete event will
fire once for each record in the selection, and the values are available
so
you could record them into an array or temp table, or perform whatever
other
actions you need to perform. Then the form's BeforeDelConfirm event fires,
and the user can cancel the deletion. Then the AfterDelConfirm event
fires,
and you can read the Status argument to see if the delete really occurred.
The actual values being deleted are not available in BeforeDelConfirm or
AfterDelConfirm.

Could your issue be solved simply by creating relations with cascading
deletes?

Another possibility in JET 4 (Access 2000 and later) is to use
Cascade-to-Null relations. The related records are not deleted, but the
foreign key field is set to Null when the main record is deleted.
Cascade-to-Null relations can only be created programmatically, but it's
not
difficult. Post back if you want more details.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daryl" wrote in message
...

Is there any way of finding out the records (rows) highlighted in a
sub-form?

For instance say the user highlights 3 records in the subform and then
hits
the DELETE key, is there any way of seeing which records will be
deleted?

I am asking because I would like to delete each of the records manually.
Or
perhaps there another approach to achieve the same result?

Yes, the order of events is different in an ADP. See:
Order of Form Delete Events Differs in ADPs and MDBs
at:
http://support.microsoft.com/?id=234866

If you really need to catch them and do it manually, I suspect the simplest
solution might be to set AllowDeletions to No so that the built-in deletion
is not available to the user, and run your code through a toolbar button.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daryl" wrote in message
...
Thanks for the reply.

My problem is with using an adp. It appears as the events associated with
a
delete are rearranged to limit the transactions with SQL Server. It
appears
to me that the OnDelete event fires before the actual records are deleted.
Therefore I am looking for a manual way of doing it.

Are you familiar with MS access data projects???

thanks
daryl

"Allen Browne" wrote in message
...
Use SelHeight and SelTop to see which records are highlighted.

The easiest solution might be to use a custom button on the toolbar to
handle the deletion if you want to do it manually. It cannot be a command
button on the form, since clicking that loses the multi-record selection.

If you wish to work with the built-in events, the form's Delete event
will
fire once for each record in the selection, and the values are available
so
you could record them into an array or temp table, or perform whatever
other
actions you need to perform. Then the form's BeforeDelConfirm event
fires,
and the user can cancel the deletion. Then the AfterDelConfirm event
fires,
and you can read the Status argument to see if the delete really
occurred.
The actual values being deleted are not available in BeforeDelConfirm or
AfterDelConfirm.

Could your issue be solved simply by creating relations with cascading
deletes?

Another possibility in JET 4 (Access 2000 and later) is to use
Cascade-to-Null relations. The related records are not deleted, but the
foreign key field is set to Null when the main record is deleted.
Cascade-to-Null relations can only be created programmatically, but it's
not
difficult. Post back if you want more details.


"Daryl" wrote in message
...

Is there any way of finding out the records (rows) highlighted in a
sub-form?

For instance say the user highlights 3 records in the subform and then
hits
the DELETE key, is there any way of seeing which records will be
deleted?

I am asking because I would like to delete each of the records
manually.
Or
perhaps there another approach to achieve the same result?

Another idea: you've considered using a trigger in SQL Server?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daryl" wrote in message
...
Thanks for the reply.

My problem is with using an adp. It appears as the events associated with
a
delete are rearranged to limit the transactions with SQL Server. It
appears
to me that the OnDelete event fires before the actual records are deleted.
Therefore I am looking for a manual way of doing it.

Are you familiar with MS access data projects???

thanks
daryl

Allen
Am I correct in thinking that when the delete occurs it simply deletes all
the selected records in one transaction? It does not fire the OnDelete
event for each selected record.

I can easily check myself - just thought you might know.

BTW - I am on the East Coast of Australia...

Good to hear from another Australian.

daryl

"Allen Browne" wrote in message
...
Another idea: you've considered using a trigger in SQL Server?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daryl" wrote in message
...
Thanks for the reply.

My problem is with using an adp. It appears as the events associated
with
a
delete are rearranged to limit the transactions with SQL Server. It
appears
to me that the OnDelete event fires before the actual records are
deleted.
Therefore I am looking for a manual way of doing it.

Are you familiar with MS access data projects???

thanks
daryl

Easy enough for you to test, but I think you are correct.

After explaining that the order of events in an ADP changed to:
BeforeDelConfirm--AfterDelConfirm--Delete
the k.b. article explains:
Microsoft intentionally changed the order of deletion events
in an Access project in order to avoid opening unnecessary
transactions on Microsoft SQL Server.

Quite a few of us Aussies in the groups. :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daryl" wrote in message
...

Am I correct in thinking that when the delete occurs it simply deletes all
the selected records in one transaction? It does not fire the OnDelete
event for each selected record.

I can easily check myself - just thought you might know.

BTW - I am on the East Coast of Australia...

Good to hear from another Australian.

daryl

Allen
I tested it and it does call the OnEvent for each record deleted, so...

My solution, it works but I don't really like it - too complicated. Any
comments?

daryl

Private Sub Form_Delete(Cancel As Integer)
Dim cnn As New ADODB.Connection
Dim strSQL As String
Dim y As Integer
Static i As Integer

i = i + 1
Set cnn = Application.CurrentProject.Connection
strSQL = "DELETE FROM tblCostDetails WHERE [Date] = '" & _
Format(Me.Date, "mm/dd/yy") & "' AND " & _
"ServiceNO = '" & Me.ServiceNO & "' AND " & _
"CostCodeID = '" & Me.CostCodeID & "' AND " & _
"TaskID = " & Me.TaskID
cnn.Execute strSQL

' Used to requery my other controls - the need for me to go this way
If i = Me.SelHeight Then
Me.Requery
Me.Parent.lstAllocatedTasks.Requery
i = 0 ' Reset the statc variable for next time
End If

Set cnn = Nothing
' Cancel the delete action - Note: adp uses this to delete records
Cancel = True
End Sub

"Allen Browne" wrote in message
...
Easy enough for you to test, but I think you are correct.

After explaining that the order of events in an ADP changed to:
BeforeDelConfirm--AfterDelConfirm--Delete
the k.b. article explains:
Microsoft intentionally changed the order of deletion events
in an Access project in order to avoid opening unnecessary
transactions on Microsoft SQL Server.

Quite a few of us Aussies in the groups. :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daryl" wrote in message
...

Am I correct in thinking that when the delete occurs it simply deletes
all
the selected records in one transaction? It does not fire the OnDelete
event for each selected record.

I can easily check myself - just thought you might know.

BTW - I am on the East Coast of Australia...

Good to hear from another Australian.

daryl

Not sure what you needed to achieve, Daryl, so can't really comment on
whether it's a good approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Daryl" wrote in message
...
Allen
I tested it and it does call the OnEvent for each record deleted, so...

My solution, it works but I don't really like it - too complicated. Any
comments?

daryl

Allen
The problem I needed to overcome was the order of the events using an ADP
and the fact that if I used the OnDelete event to start the requery the
recordsource of a listbox that contains a calculation that indicated the
hours worked. I need this action because when the records were deleted the
calculation had to be done again to show the change in hours.

When I placed the command to requery the recordsource in the OnDelete event
it appeared that the recordset was requeried, but before the actual deletion
had been done. This was proved due to the fact that the second deletion
leading to the requery made the expected change to the hours worked from the
previous deletion.

So I decided that it would be best to do the task manually, hence my code.
In describing the problem do you feel the method I have described is the
best way?

Thanks for your previous comments and tips...

daryl

"Allen Browne" wrote in message
...
Not sure what you needed to achieve, Daryl, so can't really comment on
whether it's a good approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Daryl" wrote in message
...
Allen
I tested it and it does call the OnEvent for each record deleted, so...

My solution, it works but I don't really like it - too complicated. Any
comments?

daryl