Looping through selected records of a continuous form (1 Viewer)

[SunWuKung]

Hi there,
I have been looking for some way to loop through the selected records of a continuous form. (I know it is easier with multiselect listbox but I didn't find a way to wrap the text there, which I need, since the display text is long and formatted.) Could somebody help me with this one?

Many thanks.
SWK

 

[David R]

Are you trying to loop through them with code or with an action query?

Also, how are you defining 'selected'; do you have a checkbox on each entry that you can tick off to make it Selected, or some other way?

A little more information will let us help you better. Please post back.

 

[SunWuKung]

Hi David,
thanks for the reply.

Either vb or an action query would be good for me.

I select the records by holding down the shift key and moving down with the arrow key or clicking on a record selector (this is a continuous form). This blackens a number of record selectors - this is what I call selected records. I could delete them togather so it must be stored somewhere that these records are selected.

I would like to take the IDs of the selected records and append them to an other table. It would be easy to do this one by one, but I am hoping that it is also possible for a whole selection.

Thanks for the help.
SWK

 

[Alexandre]

Did you give a try to
DoCmd.RunCommand acCmdCopy
and
DoCmd.RunCommand acCmdPasteAppend
?

 

[SunWuKung]

I haven't tried that yet, but I think this code would try to copy the selected records as a whole whereas I only need to insert the IDs of the selected records into a different table.

I am doing it one at a time now using an append query filtered with the current record of the form using the following code:

Set cn = CurrentProject.Connection
'Add selected interview SubQuestionIDs to Custom_Content table
SQLstring = "INSERT INTO Custom_Content ( CurrentContentID, ModelID, TableName )" & _
" SELECT DISTINCT IntSubQuestions.IntSubQuestAutoID, " & [Forms]![Main]![ModelName] & " AS ModelID, '1' AS TableName" & _
" FROM IntMain_withCompID INNER JOIN IntSubQuestions ON IntMain_withCompID.IntMainAutoID = IntSubQuestions.IntMainAutoID" & _
" WHERE (((IntSubQuestions.IntSubQuestAutoID)=" & [Forms]![Main]![Content_ChooseFrom].[Form]![IntSubQuestAutoID] & "));"
cn.Execute SQLstring

Could you let me know how could I use your code (or anything else) to have the same effect on all selected rows?

Many thanks.
SWK

 

[David R]

I'm not sure you're going to be able to do this your current way...

Because (as far as I know) Access doesn't "store" the fact that a record has been selected with the Record Selector (highlighted). So SQL would have no way of knowing how it is done. Since you're only copying the ID field, not the entire record, Alexandre's solution isn't what you want.

So what do you do? Easy; you cheat.

Add one field to your table; a checkbox called [Selected]. On your form, make it the very first field, an unobtrusive checkbox right at the edge. Remove it from the Tab Order if you like, to prevent accidental clicking.
Now go through and check the records you want to "Select". You can use this checkbox as the criteria for your INSERT INTO query. Make sure you have an additional action at the end of your button to uncheck all those boxes, unless they need to stay checked for whatever reason.

If you need to check a series of them you might consider developing a form that will ask for criteria and check all records that match them with an UPDATE query, but that's a separate task.

 

[SunWuKung]

Yes, that was what I thought I would do if the selection method failed. Selecting would be nicer though because I could do it from record X to Y and that could be 30 records with very little in common (they must be next to each other but its sorted by a hidden value, so there is no obvious filter criteria), whereas if I have to check them first and than add that is a lot of clicking (almost the same as adding them one by one).

But this is strange to me - Access knows that these records are selected, I could delete or copy them togather so this state must be stored somewhere ... in a far, far galaxy.

If anybody knows where it is could he/she beam me there?

Thanks.
SWK

 

[Rich]

Is this any help
Article ID: Q148393

 

[SunWuKung]

Thanks Rich,
that was it.

SWK