Multiple Record Select

[Tom]

I have a form which displays data in a spreadsheet format.

Whenever someone selects a line, I store the "Key" data field in a hidden
textbox.
Then when someone hit's delete, I use the form property "on Delete" to
delete records in another unassociated Table using the "Key" data field.
Works great.

However, what can I do if someone selects multiple lines? (my simple "on
Delete" function crashes)
- Can I block this function?
- Can I step through each delete like I do for a single line delete?
- Can I tell which ones have been selected?


Regards
Tom

 

[Dirk Goldgar]

I have a form which displays data in a spreadsheet format.

Whenever someone selects a line, I store the "Key" data field in a hidden
textbox.
Then when someone hit's delete, I use the form property "on Delete" to
delete records in another unassociated Table using the "Key" data field.
Works great.

However, what can I do if someone selects multiple lines? (my simple "on
Delete" function crashes)
- Can I block this function?
- Can I step through each delete like I do for a single line delete?
- Can I tell which ones have been selected?

You may be doing more work than you need to. The form's Delete event fires
for each record deleted. During that event (in the Form_Delete event
procedure) the record that is current on the form is the one being deleted,
so you can get the Key value from the form itself. For example,

Private Sub Form_Delete(Cancel As Integer)

Dim varKey As Variant

varKey = Me.Key

CurrentDb.Execute _
"DELETE FROM SomeTable WHERE Key=" & varKey _
dbFailOnError

End Sub

Be aware, though, that if the user cancels the delete via the normal
delete-confirmation dialog or your own code in the BeforeDelConfirm event,
the original records from your form will be restored -- but the ones you
deleted from the other table in the Delete event will *not* be restored. So
I would only do what you are trying if I were completely controlling the
delete process.

 

[Tom]

Works GREAT!
Thanks a bunch

You may be doing more work than you need to. The form's Delete event
fires for each record deleted. During that event (in the Form_Delete
event procedure) the record that is current on the form is the one being
deleted, so you can get the Key value from the form itself. For example,

Private Sub Form_Delete(Cancel As Integer)

Dim varKey As Variant

varKey = Me.Key

CurrentDb.Execute _
"DELETE FROM SomeTable WHERE Key=" & varKey _
dbFailOnError

End Sub

Be aware, though, that if the user cancels the delete via the normal
delete-confirmation dialog or your own code in the BeforeDelConfirm event,
the original records from your form will be restored -- but the ones you
deleted from the other table in the Delete event will *not* be restored.
So I would only do what you are trying if I were completely controlling
the delete process.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Tom]

Spoke too soon :~(
I am really a VB novice... and I am quite lost.... So I thought I would make
a simple Delete Query with runs everytime the "on-Delete" event occurs...
but for some reason it hangs. so back to VB,

table highlighted as delete table which needs deletion
------------------------- ---------------------------
Partnumber Partnumber
Product Product
Etc... Etc....


Dim VarPN as Variant
VarPN = me.partnumber ' this would be the link from the form-
right?'

Dim VarProduct as Variant
VarProduct = me.product ' this would be the second variable to
make sure I delete the right stuff''

Set mydb = currentdb()
set mytable =mydb.opentable(orderlines) ' identify the correct table'

Delete from mytable WHERE partnumber = VarPN and WHERE Product = VarProduct

BTW: will this delete two records if they exsist?

 

[Dirk Goldgar]

Spoke too soon :~(
I am really a VB novice... and I am quite lost.... So I thought I would
make a simple Delete Query with runs everytime the "on-Delete" event
occurs... but for some reason it hangs. so back to VB,

table highlighted as delete table which needs deletion
------------------------- ---------------------------
Partnumber Partnumber
Product Product
Etc... Etc....


Dim VarPN as Variant
VarPN = me.partnumber ' this would be the link from the form-
right?'

Dim VarProduct as Variant
VarProduct = me.product ' this would be the second variable to
make sure I delete the right stuff''

Set mydb = currentdb()
set mytable =mydb.opentable(orderlines) ' identify the correct table'

Delete from mytable WHERE partnumber = VarPN and WHERE Product =
VarProduct

BTW: will this delete two records if they exsist?

Your code isn't quite right, and it's more complicated than you need. I'd
recommend adapting the code I originally posted to match the more complete
information you've provided. Unfortunately, I still don't have quite enough
information; I need to know whether each of these fields, PartNumber and
Product, is a Text field or a Number field. It makes a difference in the
way the SQL string is formatted.

Suppose that both of these fields are text; then your code would look like
this:

Private Sub Form_Delete(Cancel As Integer)

Dim strSQL As String

strSQL = _
"DELETE FROM OrderLines " & _
"WHERE Product ='" & Me.Product & "'" & _
" AND PartNumber = '" & Me.PartNumber & "'"

CurrentDb.Execute strSQL, dbFailOnError_

End Sub

If both the fields are numbers instead, then we must change the assignment
to strSQL to remove the single-quotes around the values, like this:

strSQL = _
"DELETE FROM OrderLines " & _
"WHERE Product =" & Me.Product & _
" AND PartNumber = " & Me.PartNumber

If one is text and the other is numeric, then the appropriate adjustments
must be made.

Note, though, that the "text" version of the code assumes that neither field
will ever contain the single-quyote character itself ('). If either might,
we must use an alternate, more complicated version. So let me know if that
is the case.

And yes, this will delete two or more records if they match the Product and
PartNumber in question.

 

[Tom]

Your code isn't quite right, and it's more complicated than you need. I'd
recommend adapting the code I originally posted to match the more complete
information you've provided. Unfortunately, I still don't have quite
enough information; I need to know whether each of these fields,
PartNumber and Product, is a Text field or a Number field. It makes a
difference in the way the SQL string is formatted.

Suppose that both of these fields are text; then your code would look like
this:

Private Sub Form_Delete(Cancel As Integer)

Dim strSQL As String

strSQL = _
"DELETE FROM OrderLines " & _
"WHERE Product ='" & Me.Product & "'" & _
" AND PartNumber = '" & Me.PartNumber & "'"

CurrentDb.Execute strSQL, dbFailOnError_

End Sub

If both the fields are numbers instead, then we must change the assignment
to strSQL to remove the single-quotes around the values, like this:

strSQL = _
"DELETE FROM OrderLines " & _
"WHERE Product =" & Me.Product & _
" AND PartNumber = " & Me.PartNumber

If one is text and the other is numeric, then the appropriate adjustments
must be made.

Note, though, that the "text" version of the code assumes that neither
field will ever contain the single-quyote character itself ('). If either
might, we must use an alternate, more complicated version. So let me know
if that is the case.

And yes, this will delete two or more records if they match the Product
and PartNumber in question.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi dirk,
1st off, thank you so much for your help...
2nd, still having troubles, but not with your code. :~)

I created a simple version of the DB for testing and it appears to work ok
when called within a simple form.

However within the real DB and Form I end up with the following error;

Operation not supported in transactions. (Error 3246)
You tried to perform an operation that is not valid within a transaction. A
transaction can be one you create using a BeginTrans statements, or an
internal transaction created by the Microsoft Jet database engine, such as
when you delete multiple records in a single operation.

To trouble shoot the issue; I brought my "Simple" Form over and it still
works fine, Until I open the "Real" Form.
Once again using the simple form to perform the delete, the Access Hangs

- Could this be because I am using Tab's which are all tied to the same
table with a different sort criteria?
- Could it be because there are multiple SubForms of which one of them is
displaying the data being deleted?

 

[Dirk Goldgar]

1st off, thank you so much for your help...

You're welcome.

2nd, still having troubles, but not with your code. :~)
Drat!

I created a simple version of the DB for testing and it appears to work ok
when called within a simple form.

However within the real DB and Form I end up with the following error;

Operation not supported in transactions. (Error 3246)
You tried to perform an operation that is not valid within a transaction.
A transaction can be one you create using a BeginTrans statements, or an
internal transaction created by the Microsoft Jet database engine, such as
when you delete multiple records in a single operation.

To trouble shoot the issue; I brought my "Simple" Form over and it still
works fine, Until I open the "Real" Form.
Once again using the simple form to perform the delete, the Access Hangs

- Could this be because I am using Tab's which are all tied to the same
table with a different sort criteria?
- Could it be because there are multiple SubForms of which one of them is
displaying the data being deleted?

I'm not sure, because I don't have enough information about your real form.
But I wonder if you have defined a relationship between the main table and
the OrderLines table, with referential integrity enforced. If so, Access
won't let you delete records from the parent table unless either you have
Cascade Deletes specified for the relationship, or else you first delete all
related child records. Or maybe it could be something else, because I don't
have a clear picture of your setup.

Maybe the best answer for you would in fact to use Cascade Deletes to have
the database engine automatically delete the child records when you delete
the parent records. Then you wouldn't need any code at all to make it
happen. But maybe you ought to explain the background of what you're doing,
how your tables are set up and related to each other, and how the form and
subforms are set up. Then the best approach might be clearer.