Open form based on value in a list box

[Fish521]

I have a list box on a form in which it's value is based on an option group. I would like to open a form based on that value. For example option 2=long evaluation, option 3=short evaluation, so if the value in the list box is 2 I want to open frmLongEvaluation when I double click on that record in the list box. I kind of got some help regarding this and here is the code I have so far:

Private Sub ListEvaluation_DblClick(Cancel As Integer)

Select Case Me.Evaluation
Case 2 'Long Evaluation
DoCmd.OpenForm "frmEvalLong", , , , , , , Me.SessionID
Case 3 'Short Evaluation
DoCmd.OpenForm "frmEvalShort", , , , , , , Me.SessionID
End Select

End Sub

I get an error that states it can't finde Me.Evaluation(option group)

Any ideas would be great

Thanks,

 

[Remou]

When you type Me. you should get a list of controls from intellisense. Is Evaluation one of them?

 

[Fish521]

No I don't. It just gives me the option of the list box itself. Is there a better way to set up the coding on this to reference that specific field in the list box?

 

[fneily]

Are you sure the NAME of your option group is Evaluation? In design view, make sure you click on the option frame and check the properties.
Have you tried just [Evaluation]?
Did you check the Option Values of your radio buttons? Are you sure they're 2 and 3?

 

[Fish521]

The name is correct and the values are correct. I am not listing a 1 in there due to the query that populates the list box does not include that value.

I need the code to reference the information contained in the list box such as the row selected (SessionID) and the column (Evaluation).

 

[Remou]

If you wish to refer to the column evaluation, you need:

Me.ListEvaluation.Column(1)

Column numbering starts from zero.

 

[Fish521]

That seemed to work, except now it is telling me on the DoCmd.OpenForm portion:

Wrong number of arguments or invalid property assignment

DoCmd.OpenForm "frmEvalLong", , , , , , , Me.ListEval.Column(0)

When opening the form I need to reference the first column in the list box

 

[Remou]

That should be:

DoCmd.OpenForm "frmEvalLong", , , , , , , "SomeNumericFieldInTheReport=" & Me.ListEval.Column(0)

 

[Remou]

I am wrong. You seem to have too many commas.

If you wish to open the form to a particular record, a where statement may be best.

 

[Fish521]

That worked. Now I only have one more problem. When opening frmLongEvaluation would I put in an argument on the form itself when opening it and if so how would I code that?

Or would it be better to reference it in the query that it is based on under the criteria? I don't know how this would work with having to reference the column in the list box.

 

[Remou]

What do you wish to achieve?

 

[Fish521]

I need this form to open up and reference that Session from the list box.

When it references that SessionID it will populate the remaining fields with the information listed under that SessionID

This also has a subform on it so that multiple entries can be entered for that specific SessionID.

 

[Remou]

Did you use the Where statement?

[tt]DoCmd.OpenForm "frmEvalLong", , , "SessionID=" & Me.ListEval.Column(0)[/tt]

Assuming that SessionID is numeric and is contained in Me.ListEval.Column(0).

 

[Fish521]

Works perfect. I had the Where statement in there wrong I put the = sign outside the "

Thank you so much for your help with this, I only get to work with access about once every 6 months so your help is greatly appreciated.