Create Recordset from selected records (1 Viewer)

[gfultz]

Hi All,

I haven't been able to figure out how to call the selected records in an access continous form so I may create a new recordset from just those records. Here is what I'd like to do:

1. Have a user be able to select multiple records using the built in access record selector.
2. Create a button that creates a recordset of those records, then loops through to delete those records.

How do I make the recordset only those records that have been selected?

From there the code should be straight forward to find and delete the records based on the PK.

Thanks,
Garrett

 

[pbaldy]

The record selector will only let you select one record at a time, so I'm not sure it will work for you. You'd need a bound checkbox or something along those lines to select multiple records on a continuous form. Have you considered a multi-select listbox?

 

[gfultz]

I haven't considered that. So your suggestion is to create a multiselect listbox that contains each line number (A unique value for each record) and is bound to the LineID (PK). Then allow the user to select multiple and click the delete button. This button then deletes the records by using a SQL statement that deletes all records with a PK value that was selected. Is that correct? And what about populating this list box? Should I store it in the footer of my continuous sub-form? Or should it be in my main form?

 

[pbaldy]

Well, the presentation is up to you. I'd probably use the listbox in place of the subform, but it depends on if you need the data to be editable. It could have the same source as the subform, since I assume that would be the set of records you wanted to display. You'd have to use code to loop through the selected items, along the lines of:

Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.ListboxName
For Each varItem In ctl.ItemsSelected
  CurrentDb.Execute "DELETE * FROM TableName WHERE LineID = " & ctl.ItemData(varItem)
Next varItem

 

[gfultz]

Hi Paul,

I definitely need the data editable. The variant identifies if the record has been selected in the list box correct? And if nothing was selected, it would have nothing to delete. I assume I could run a check to see if this is null as part of the event to stop the procedure and tell the user they haven't selected any records for deletion. Similarly if a user wants to largely duplicate an item or items, I can set this up for them and have them be able to do that as well? Thanks for your help, I am thinking this might be the way to go!

Garrett

 

[Kryst51]

How about having a delete button and an edit record button in conjunction with the multiselect list box? The delete button can do just that, then the edit button could open a new form in datasheet view with the records available.

 

[gfultz]

The users will do far more editing than deleting. This is mainly a way for them to delete a line item created in error. It is extremely rare to actually delete a line item if it was legitimately created. Since the user will want the ability to edit multiple line items quickly, selecting the line item to edit would be a tedious process.

 

[Kryst51]

I googled what you wanted and came up with this, so I don't think the datasheet is going to work the way you want...

You may have to compromise with the list box way.

 

[pbaldy]

I assume I could run a check to see if this is null as part of the event to stop the procedure and tell the user they haven't selected any records for deletion.

Sure, like this:

If Me.lstEmployees.ItemsSelected.Count = 0 Then
  MsgBox "Must select at least 1 employee"
  Exit Sub
End If

You could also do the copy type thing you mentioned. Basically append instead of delete. Here's an example:

http://www.baldyweb.com/MultiselectAppend.htm

 

[LPurvis]

Just to mention that the whole "selection in a form" has been considered (and even debated) over time.
I, personally, think it's simple. A local "selection" table to hold your choices.
The server table (with user ID field) is another option (slightly less trivial - but not complex).
Doing the whole form source thing in code is another possibility - either using a function bound checkbox (see Albert Kallal's example) or my own recordset methods.

Once you have selections, then establishing a new recordset is as simple as concatenating the PK values of the selected rows into a WHERE clause (or Filter expression).

Example of selections found in my page linked to in my sig. (ListSelect demo - and then the final Recordset examples from articles at the bottom).

Examples (and healthy disagreements) found in a thread over the way here - even if the OP did eventually make the wrong decision. ;-)

Cheers