Enumerating Records in a Subform

[zooropa66]

I wondered if anyone could help as i've been at this for 2 days now:

The following article 294202 which can be found by Googling "MS ACCESS ENUMERATE RECORDS" (sorry but i don't have enough posts to give the link directly)


describes how to enumerate records in a form (which i've managed to get working) - so when you select several contiguous records then click a command button, a message box pops up and tells you which records you just selected.

At the end of the article it gives a hint about how to do the same with the command button on a main form and the records in a subform (rather than with the button and records all on the one form).

After 2 days perseverance I just can't get it to work.

Could anyone spare 10 mins to go through the article and try to get it working with the button on a main form and data in a subform? The code can just be copied and pasted directly from the article.

Thanks

 

[boblarson]

It would be helpful if you would post the code you are currently using, the name of your main form, and the name of the subform control (control on the main form which houses the subform).

 

[zooropa66]

Hi Bob,
Thanks for your reply. I thought i'd start by posting the code for the part i've got working - that is, the ability to enumerate selected records in a Continuous Form and update a certain field (my_field2 in this case) with a certain value ("999" in this case)

It might be useful to other beginners and will be a good reference point.

I'll post my Main Form / SubForm code which isn't working within the hour.

Table 1

my_id (Autonumber, Primary Key)
my_field1 (Text)
my_field2 (Text)

Form 1 (Continuous Form with RecordSource = Table1)

Textbox with name=my_id and control source = my_id
Textbox with name=my_field1 and control source = my_field1
Textbox with name=my_field2 and control source = my_field2

Command Button with name = Command1
On Mouse Down =SelRecord([Form],"Down")
On Mouse Move =SelRecord([Form],"Move")
On Mouse Up =SelRecord([Form],"Up")

[SIZE=3][FONT=Times New Roman]Private Sub Command1_Click()[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim X[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   ' Restore the lost selection.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   SelRestore[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]   ' Enumerate the list of selected company names.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   X = DisplaySelectedCompanyNames()[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

Module 1

[SIZE=3][FONT=Times New Roman][COLOR=black]Option Compare Database[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]Dim MySelTop As Long[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]Dim MySelHeight As Long[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]Dim MySelForm As Form[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]Dim fMouseDown As Integer[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=black]Function SelRecord(F As Form, MouseEvent As String)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]   Select Case MouseEvent[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]      Case "Move"[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]         [/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]         If fMouseDown = True Then Exit Function[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]         Set MySelForm = F[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]         MySelTop = F.SelTop[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]         MySelHeight = F.SelHeight[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]      Case "Down"[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]         fMouseDown = True[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]      Case "Up"[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]         fMouseDown = False[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]   End Select[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]End Function[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=black]Public Sub SelRestore()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]     Debug.Print "got into Restore"[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     MySelForm.SelTop = MySelTop[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     MySelForm.SelHeight = MySelHeight[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]End Sub[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]Function DisplaySelectedCompanyNames()[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]     Dim i As Long[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     Dim F As Form[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     Dim RS As Recordset[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]     Set F = Forms![Form1][/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     Set RS = F.RecordsetClone[/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black] [/COLOR][/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][COLOR=black]     RS.MoveFirst[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     RS.Move F.SelTop - 1[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     For i = 1 To F.SelHeight[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     MsgBox RS![my_id][/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     DoCmd.SetWarnings False[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     RS.Edit[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     RS![my_field2] = "999"[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     RS.Update[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     RS.MoveNext   [/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black]     Next i   [/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=black] [/COLOR][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=black]End Function[/COLOR][/SIZE][/FONT]
[COLOR=black]

[/COLOR]

 

[boblarson]

The problem that you are facing with using this on a subform is that the call you are using is just using [Form] and that doesn't refer to a subform. You would need to use [Forms]![YourMainForm]![YourSubformControlName]![Form] as the call (where YourSubformControlName is the name of the control on the parent form which houses the subform and not the subform name itself).

 

[zooropa66]

Thanks Bob,
Success! The previous code works when I change

Set F = Forms![Form1] to Set F = Forms![Form1]![frm_subform].Form

where frm_subform is the subform / report name

Thanks again,
Mark

 

[zooropa66]

sorry, and you also need to change the following button events

On Mouse Down =SelRecord(frm_subform.Form,"Down")
On Mouse Up =SelRecord(frm_subform.Form,"Up")
On Mouse Move =SelRecord(frm_subform.Form,"Move")