Modifying multiple selection from subform 1

[englundn]

I searched about my problem but didn't find a solution for it, so that's why a post.
I have a subform with many tests and a status of the tests. I want to select a bunch of the tests ie. 1-3 and change their status to True with VB.

Test 1 True
Test 2 False
Test 3 False
Test 4 True

I don't know how i should get the selection and how to build the "For each.....next" I could do it with SQL also, but when i can't get the selection. I can get the CurrentRecord, but how to get many records.
Thank you for any post

 

[Remou]

I would say that the best bet is to build an Update query string. If you use unbound check boxes, you can take advantage of the Triple State property to set the check boxes that you do not want updated to Null.

Very roughly:

For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If Not IsNull(ctl) Then
            strSQL = strSQL & ", [" & ctl.Name & "] = " & Format(ctl, "True/False")
        End If
    End If
Next
strSQL = "Update tblTable Set " & Mid(strSQL, 2)
MsgBox strSQL

 

[englundn]

When i wrote

For Each ctl In Me.Child6.Controls
MsgBox "test"
Next

It didn't matter how many records were selected, it just ran twice. So here is also the problem when i have the subform. I'm not completely sure of what you meant with the unbound checkboxes and how that would help me. The datasheet i'm using is much larger than what i wrote. I have approximately 20 fields and a few hundred records. The thing is that i just want to update max. 10 records and only the "test state" field in each record.

 

[Remou]

Ok. What you want is something completely different from what I thought. Have you a means of identifying the records to be updated? That is, the batch with such-and-such a date, or such-and-such an identifier? You will need something. You will also need a means of choosing which controls are to be updated and what they are to be update to. List boxes can be useful as can filters.

 

[englundn]

About the identification, i don't know what you are after but i answer both of the options that i'm thinking about. Of course the records have a unique key. But how to identify which record is selected, that i don't know how to do. I was thinking of doing it the same way as you can delete records from a datasheet (by "painting" them). But the tests that have to be updated don't usually have anything in common. So there is no date in one record that is similar to the other.
Otherwise it would be easy to say that UV-test1 starting now and ending tomorrow should be put as finished. That is the easy solution.

But when the database isn't updated daily and the test are not the same so the records don't have anything in common with each other. One thing that i have been able to do is to select current record from the datasheet, but access doesn't have, to my knowledge any easy key to select multiple. So that's why i ask from here.

 

[englundn]

I'm still fighting with the problem i described earlier. Here is some more information on how i want it to work. Maybe it's little too difficult programming in VB, but i'm sure it should work.
Beneath is a pic of the front-end where i want to update the sub-form. The selection is "painted", should i make a shortcut key to keep the focus on the subform? Or what is your solution.

http://koti.mbnet.fi/elvisp/modify.jpg

 

[Remou]

How about this:

Function UpdateTest()
   Dim i As Long
   Dim F As Form
   Dim rs As DAO.Recordset

   ' Get the form and its recordset.
   Set F = Forms!FormName![Subform Control Name].Form
   Set rs = F.RecordsetClone

   ' Move to the first record in the recordset.
   rs.MoveFirst

   ' Move to the first selected record.
   rs.Move F.SelTop - 1

   For i = 1 To F.SelHeight
     rs.Edit
     rs!Test = True
     rs.Update
     rs.MoveNext
   Next i

End Function

Modified from:

http://support.microsoft.com/kb/294202

 

[englundn]

Thank you. This was totally awesome.