Dynamically Building an Option Group from a Query

[chammock]

I need to programmatically construct an option group (radio buttons or
listbox, whichever is easier) that pulls in values from a query. The
number of values ranges from 2 - 4. So sometimes I want the option
group to show two options and sometimes 3, or 4.

I wrote a function like this to return a string of the values for each
layer.

Function GetLayerInfo(FastenerID As Integer) As String

'Returns the fastener name for a given fastener ID
Dim intFastenerID As Integer
Dim strLayerInfo As String
Dim db As Database
Dim rs As Recordset

intFastenerID = FastenerID

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from qryFastenerLayers where" & _
" FastenerID = " & intFastenerID & "")
rs.MoveFirst
GetLayerInfo = rs![Layer1] & "~" & rs![Layer2] & "~" & rs![Layer3] &
"^" & rs![Layer4]
rs.Close
Set rs = Nothing
db.Close

End Function

Now I am reading this in and trying to parse it out, but my skills are
a little weak on string parsing and I am having issues.

strLayerInfo = GetLayerInfo(intFastenerID)
strLayer1 = Left(strLayerInfo, InStr(strLayerInfo, "~") - 1)
strLayer2 = Mid(strLayerInfo, InStr(strLayerInfo, "~") + 1)
strLayer3 = Mid(strLayerInfo, InStr(strLayerInfo, "^") - 1)
strLayer4 = Mid(strLayerInfo, InStr(strLayerInfo, "^") + 1)

Does anyone have some parsing code to separate out the 4 items? Would
it be better to try to store them in an Array returned by the Function
instead of a String? If so, how would that work.

If there is a better way to start with, then let me know.

Once I get the values, would it be best to just setup the four options
and only make those visible that have data, or should I try to
construct the option group in code? How would that work.

Thanks for any ideas you have.

 

[TC]

Instead of the function returning the options in a single string, it
could return them in a collection, or an array. Then you'd simply
iterate through the collection (for each v in colBlah), or loop through
the elements of the array (for n = 1 to whatever).

HTH,
TC (MVP Access)
http://tc2.atspace.com