A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

OptionGroup Loop



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2004, 11:51 AM
Pedro
external usenet poster
 
Posts: n/a
Default OptionGroup Loop

Hi, I have several option groups on a form. I want to loop through them and determine their value and performan an action based upon the value. Now the code that determines there value and performs the action is simple enough. However what I am struggling with is the actual looping. No matter how I try (collection, array) it always comes back with an error that doesn't accept optiongroup as a valid object type for looping.

i am using Access 97. Can anyone help?
  #2  
Old May 28th, 2004, 02:53 AM
Ken Snell
external usenet poster
 
Posts: n/a
Default OptionGroup Loop

You can loop through your controls and identify which are option groups this
way:

Dim ctl As Control
For Each ctl in Me.Controls
If ctl.ControlType = acOptionGroup Then
Debug.Print "The value of this option group is " & ctl.Value
End If
Next ctl

--
Ken Snell
MS ACCESS MVP

"Pedro" wrote in message
...
Hi, I have several option groups on a form. I want to loop through them

and determine their value and performan an action based upon the value. Now
the code that determines there value and performs the action is simple
enough. However what I am struggling with is the actual looping. No matter
how I try (collection, array) it always comes back with an error that
doesn't accept optiongroup as a valid object type for looping.

i am using Access 97. Can anyone help?



  #3  
Old May 28th, 2004, 12:21 PM
Pedro
external usenet poster
 
Posts: n/a
Default Help Again?

Hi Ken,

thank you very much. I got that working thank you.

now another problem has arisen. I loop through the optionGroupVariable.controls.item(index) to extract the lable.caption and get the OptionGroupVariable.value and perform the relevant insert via recordset. this all works fine but on several option groups within my form the controls.item(index) value changes so the label moves from 0 to 4. Any ideas why?
  #4  
Old May 28th, 2004, 02:41 PM
Ken Snell
external usenet poster
 
Posts: n/a
Default Help Again?

I'm sorry...I'm not understanding what you're doing here, and what is
happening? Can you provide specific examples of what your setup is, what
you're trying to find, and what you're seeing? Use real names/captions/etc.

--
Ken Snell
MS ACCESS MVP

"Pedro" wrote in message
news
Hi Ken,

thank you very much. I got that working thank you.

now another problem has arisen. I loop through the
optionGroupVariable.controls.item(index) to extract the lable.caption and
get the OptionGroupVariable.value and perform the relevant insert via
recordset. this all works fine but on several option groups within my form
the controls.item(index) value changes so the label moves from 0 to 4. Any
ideas why?


  #5  
Old May 28th, 2004, 03:06 PM
Pedro
external usenet poster
 
Posts: n/a
Default Help Again?

I have a form that is not bound to a table. it basically a check list. The users need to choose one of 4 options for each item on the list. The option group label contains the list item name. I need to retrieve this to enter it into a table field along with the option they have chosen. There is a submit button so that once they have entered the choices for each list item I am then using a recordset to do the insert into the table. So I loop through each option group extracting the label caption (list item) and the option group value (choice for that specific list item). The problem occurs (inconsistently) when the label capyion changes from item(0) to item(4) and vice versa? This gives me a data type mismatch error. Here is my code:

Private Sub cmdSubmit_Click()
Dim lblCaption As Label
Dim objOptGrp As OptionGroup
Dim strlabelVal As String
Dim chkOne, chkTwo, chkThree, chkFour As CheckBox
Dim rsTicklist As Recordset
Dim dbsLHSS As Database
Dim ctl As Control

'Set dbsLHSS = CurrentDb
'Set rsTicklist = dbsLHSS.OpenRecordset("tbl_tickList", dbOpenDynaset, dbAppendOnly)

For Each ctl In Me.Controls
If (ctl.ControlType = acOptionGroup) Then
Set objOptGrp = ctl
Set lblCaption = objOptGrp.Controls.Item(0)
strlabelVal = lblCaption.Caption
Debug.Print strlabelVal
Set chkOne = objOptGrp.Controls.Item(1)
Debug.Print "check 1 " & chkOne.Name '***Just me trying to find out which list items are causing trouble.
Set chkTwo = objOptGrp.Controls.Item(2)
Debug.Print "CHECK 2 " & chkTwo.Name
Set chkThree = objOptGrp.Controls.Item(3)
Debug.Print "CHECK 3 " & chkThree.Name
Set chkFour = objOptGrp.Controls.Item(4)
Debug.Print "CHECK 4 " & chkFour.Name

'With rsTicklist
'.AddNew
'!School = strSchoolName
'!Theme = strlabelVal
'!DateOfAssessment = dtAssessDate
Select Case objOptGrp.Value
Case Is = 1
'!GoodUpToDate = True
MsgBox strlabelVal & " 1st" *****This is just so I can check the correct options are being submitted
Case Is = 2
'!PartiallyInPlaceWorkPlanned = True
MsgBox strlabelVal & " 2nd"
Case Is = 3
'!DevAreaWorkPlanned = True
MsgBox strlabelVal & " 3rd"
Case Is = 4
'!DevAreaWorkNOTplanned = True
MsgBox strlabelVal & " 4th"
End Select
'End With
End If
Next ctl

'rsTicklist.Close
'dbsLHSS.Close
End Sub

any help will be greatly appreciated.
  #6  
Old May 28th, 2004, 03:24 PM
Ken Snell
external usenet poster
 
Posts: n/a
Default Help Again?

One thing that jumps out at me is that you're setting the strlabelVal
variable to be the caption of the first option button/checkbox in the option
group, but you never reset it to the caption of the label for the second,
third, or fourth option button/checkbox? (If I am understanding that you
want this to change, that is.) So, in your last code block, you will always
see the same label caption -- namely, the first item's.


--
Ken Snell
MS ACCESS MVP

"Pedro" wrote in message
...
I have a form that is not bound to a table. it basically a check list. The

users need to choose one of 4 options for each item on the list. The option
group label contains the list item name. I need to retrieve this to enter it
into a table field along with the option they have chosen. There is a submit
button so that once they have entered the choices for each list item I am
then using a recordset to do the insert into the table. So I loop through
each option group extracting the label caption (list item) and the option
group value (choice for that specific list item). The problem occurs
(inconsistently) when the label capyion changes from item(0) to item(4) and
vice versa? This gives me a data type mismatch error. Here is my code:

Private Sub cmdSubmit_Click()
Dim lblCaption As Label
Dim objOptGrp As OptionGroup
Dim strlabelVal As String
Dim chkOne, chkTwo, chkThree, chkFour As CheckBox
Dim rsTicklist As Recordset
Dim dbsLHSS As Database
Dim ctl As Control

'Set dbsLHSS = CurrentDb
'Set rsTicklist = dbsLHSS.OpenRecordset("tbl_tickList", dbOpenDynaset,

dbAppendOnly)

For Each ctl In Me.Controls
If (ctl.ControlType = acOptionGroup) Then
Set objOptGrp = ctl
Set lblCaption = objOptGrp.Controls.Item(0)
strlabelVal = lblCaption.Caption
Debug.Print strlabelVal
Set chkOne = objOptGrp.Controls.Item(1)
Debug.Print "check 1 " & chkOne.Name '***Just me trying to find

out which list items are causing trouble.
Set chkTwo = objOptGrp.Controls.Item(2)
Debug.Print "CHECK 2 " & chkTwo.Name
Set chkThree = objOptGrp.Controls.Item(3)
Debug.Print "CHECK 3 " & chkThree.Name
Set chkFour = objOptGrp.Controls.Item(4)
Debug.Print "CHECK 4 " & chkFour.Name

'With rsTicklist
'.AddNew
'!School = strSchoolName
'!Theme = strlabelVal
'!DateOfAssessment = dtAssessDate
Select Case objOptGrp.Value
Case Is = 1
'!GoodUpToDate = True
MsgBox strlabelVal & " 1st" *****This is just so I can

check the correct options are being submitted
Case Is = 2
'!PartiallyInPlaceWorkPlanned = True
MsgBox strlabelVal & " 2nd"
Case Is = 3
'!DevAreaWorkPlanned = True
MsgBox strlabelVal & " 3rd"
Case Is = 4
'!DevAreaWorkNOTplanned = True
MsgBox strlabelVal & " 4th"
End Select
'End With
End If
Next ctl

'rsTicklist.Close
'dbsLHSS.Close
End Sub

any help will be greatly appreciated.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.