Getting Label from OptionGroup in Qry?

[d9pierce1]

HI all,


I have a qry that has the option number in it from a form option group and i cant figure out how i get the label from it?
Typical option
1 Income
2 Asset
3 Liability
4 Expense

Is this something I can get in some type of expression or ? on a query?
The qry is for a different form then the one using to create a new form that needs to display the lable, not the number

Thanks
Dave

[Micron]

You've posted in forms, your title suggests this is a query issue but your post doesn't mention a query. I'll assume you want to do this in a query.
Since the value will be dynamic and you can't (AFAIK) directly refer to the result of a choice with a query expression, I'd say you will have to use a function. If that function could decipher a 'link' between the chosen control and a property of it you could get at the label caption. That link could be the control name property where for example, the last character in the name is its value, such as btnIncome1. Since the frame is what receives the value of the chosen control, then by retrieving the frame value of 1, you could relate that to the name of the chosen control and thereby get it's caption. When I put forms!form1.option2.controls(0).caption I can get at the label caption "Income".

If anyone knows of a better way, I'm sure they'll post it. I looked to see if there was a kind of "SelectedItem" property for a frame or option group but didn't find anything. So I'm assuming a round-about method will be required. Hope that's not too confusing.

EDIT 2
Forgot to say that for that to work, the option button labels must be attached to their controls.

[Micron]

maybe a short example is worth a thousand words. I have a form with 2 options in a frame and a button whose click event is

Private Sub Command7_Click()
MsgBox getOptionCaption(Me.Name, Frame0)
End Sub

It calls this function

Function getOptionCaption(strFormName As String, lngOption As Long) As String
 getOptionCaption = Forms(strFormName).Controls("option" & lngOption).Controls(0).Caption
End Function

and the message is either "Income" or "Asset"
The red part is where the frame value and control name are concatenated, resulting in the option control name, thus I can get at its caption. Obviously some error trapping would be required. I kept it simple just to illustrate the point.

[d9pierce1]

Thanks Micron
I will work on this today. I was at work with this and left my thumb drive at home with the db on it so will have to go get it.
Is is possible to just call this on current as i dont use a button for it. I will play with that as I have a text box on form for the
actual number value of it but want the lable. It should be set to just be there when form opens as the actual number value is on the form all ready
Thanks

[Micron]

Yes you can call a function from form current event. From what you describe it doesn't sound like you need the frame value because you have the value in a textbox, so the function I wrote would need to be modified for that. The main thing is to drive down to the label caption by way of the option button, however you do that. If your option was named optIncome1 and 1 was its value, then it should work as long as the label is attached to the option button.

The function doesn't have to be in a standard module but that's how I wrote it. You could simply write the same main parts in the current event and use Me instead of the Forms! reference.

[accesstos]

An alternative way:

In a standard code module:

Option Compare Database
Option Explicit

Public Enum CashFlows
    cfNone = 0
    cfIncome
    cfAsset
    cfLiability
    cfExpense
End Enum

Function CurrentFlow() As Long
    On Error Resume Next
    CurrentFlow = [Forms]![Form1]![fraFlows]
End Function

Function CashFlowToString(lngType As CashFlows) As String
    CashFlowToString = Choose(lngType + 1, "None", "Income", "Asset", "Liability", "Expense")
End Function

Function CurrentFlowToString() As String
    CurrentFlowToString = CashFlowToString(CurrentFlow())
End Function

Function CashFlowIncome() As Long
    CashFlowIncome = cfIncome
End Function

Function CashFlowExpense() As Long
    CashFlowExpense = cfExpense
End Function

Examples of usage:

Textbox

Control Source: =CurrentFlowToString()

Conditional Formatting

Expression Is: CurrentFlow() = CashFlowIncome()

Form2’s code

Option Compare Database
Option Explicit

Private Sub Form_Load()
    Me.Caption = "Cash Flows (" & CurrentFlowToString() & ")"
End Sub

Private Sub Form_Open(Cancel As Integer)
    If CurrentFlow() = cfNone Then
        MsgBox "Please, choose a cash flow type first!", vbCritical, "Cash flows"
        Cancel = -1
    End If
End Sub

Query

SELECT fDate, Sum(fAmount) AS FlowAmount, CurrentFlowToString() AS CashFlow 
FROM tblFlows
WHERE fType = CurrentFlow()
GROUP BY fDate;

OR

SELECT fDate, Sum(fAmount) AS Income
FROM tblFlows
WHERE fType = CashFlowIncome()
GROUP BY fDate;

[CJ_London]

the problem with referencing the label is the target form needs to be open - which may or may not be the case

you could use the choose function in your form recordsource

select *, choose(optionNum, "Income","Asset","Liability","Expense")
from myTable

if you want to use vba, it might be an idea in the table design to include the option 'labels' as a comma separated string which you can the reference using something like

CurrentDb.TableDefs("myTable").Fields("myOptionGro up").Properties("Description")

function might be

Function getOptLabel(tName as String, fName as string, Opt as integer) as String
 
    'arrays start from 0, option groups usually start from 1, hence Opt-1
    getOptLabel=Split(CurrentDb.TableDefs(tName).Fields(fName).Properties("Description"),",")(Opt-1)

End Function

Or have a lookup table that you can reference in your query as required