Prev: AutoNumber, messed up sequence, duplicate values.
Next: Add totals of record type to a form footer.
From: RayMilhon via AccessMonster.com on 2 Jun 2010 19:13
I have the following code in a form:

Private Sub cmdQuarterly_Click()
Dim strsql As String, oldipa As String, itm As Variant
Dim rs As New ADODB.Recordset, rsgp As New ADODB.Recordset
strsql = "Select ipa from tbl_mailing_groups group by ipa"
If Not IsNull(Me.txtGroupid) Then oldipa = Me.txtGroupid
rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly
Do Until rs.EOF
Me.txtGroupid = rs!ipa
Me.Combo10.Requery
strsql = "Select group from tbl_mailing_groups " & _
"Where ipa = """ & rs!ipa & """ Group By group"
rsgp.Open strsql, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly
Do Until rsgp.EOF
for each item in me.combo10
If Forms!frmJOMReports.Combo10.ItemData(itm) = rsgp!group
Then
Forms!frmJOMReports.Combo10.ItemData(itm).Selected = True
End If
Next itm
rsgp.MoveNext
Loop
Call cmdPreviewScoreReport_Click
rs.MoveNext
Loop


End Sub

The form is normally used to generate 5 reports based on the group and
subgroups. The code addes is to automate the report quarterly for several
groups. I have a table that contains the groups and subgroups for each
quarterly report and I need to go through those to set the controls on the
form and generate the reports. The problem I'm having is in errors on the
for each routine. I need to iterate through each item in the listbox and if
the item is in the recordset for the grouop set the selected property to true.


I've tried everything I can think of and it errors on that line everytime.
I've tried:
for each item in me.combo10
for each item in me.combo10.listitems
for each item in me.combo10.itemdata

What am I missing?????

--
Message posted via http://www.accessmonster.com

From: PieterLinden via AccessMonster.com on 2 Jun 2010 22:10
Is it a combobox or a ListBox?

Dim varItem as Variant
For Each varItem in me.lbxTest.ItemsSelected
Debug.print me.lbxtest.ItemData(varItem)
Next varItem

RayMilhon wrote:
>I have the following code in a form:
>
>Private Sub cmdQuarterly_Click()
> Dim strsql As String, oldipa As String, itm As Variant
> Dim rs As New ADODB.Recordset, rsgp As New ADODB.Recordset
> strsql = "Select ipa from tbl_mailing_groups group by ipa"
> If Not IsNull(Me.txtGroupid) Then oldipa = Me.txtGroupid
> rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly,
>adLockReadOnly
> Do Until rs.EOF
> Me.txtGroupid = rs!ipa
> Me.Combo10.Requery
> strsql = "Select group from tbl_mailing_groups " & _
> "Where ipa = """ & rs!ipa & """ Group By group"
> rsgp.Open strsql, CurrentProject.Connection, adOpenForwardOnly,
>adLockReadOnly
> Do Until rsgp.EOF
> for each item in me.combo10 '<--- Did you notice the misspelling??? should be "itm"
> If Forms!frmJOMReports.Combo10.ItemData(itm) = rsgp!group
>Then
> Forms!frmJOMReports.Combo10.ItemData(itm).Selected = True
> End If
> Next itm
> rsgp.MoveNext
> Loop
> Call cmdPreviewScoreReport_Click
> rs.MoveNext
> Loop
>
>
>End Sub
>
>The form is normally used to generate 5 reports based on the group and
>subgroups. The code addes is to automate the report quarterly for several
>groups. I have a table that contains the groups and subgroups for each
>quarterly report and I need to go through those to set the controls on the
>form and generate the reports. The problem I'm having is in errors on the
>for each routine. I need to iterate through each item in the listbox and if
>the item is in the recordset for the grouop set the selected property to true.
>
>I've tried everything I can think of and it errors on that line everytime.
>I've tried:
>for each item in me.combo10
>for each item in me.combo10.listitems
>for each item in me.combo10.itemdata
>
>What am I missing?????

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1

From: RayMilhon via AccessMonster.com on 3 Jun 2010 17:26
It started as a combobox but requirements changed where multiple items were
possible so I changed it to a listbox. However due to all of the code that
had been written behind the form I left the name as it was.

PieterLinden wrote:
>Is it a combobox or a ListBox?
>
>Dim varItem as Variant
>For Each varItem in me.lbxTest.ItemsSelected
> Debug.print me.lbxtest.ItemData(varItem)
>Next varItem
>
>>I have the following code in a form:
>>
>[quoted text clipped - 43 lines]
>>
>>What am I missing?????

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1

 | 
Pages: 1
Prev: AutoNumber, messed up sequence, duplicate values.
Next: Add totals of record type to a form footer.