Linking option group value to a command button query

[obheron]

Hello all,
New to the tek-tips site and Access, but looks pretty useful. Here is what I am trying to do at the moment....

I want to create a form with essentially 2 option groups and a command button. The choice of one option group will determine which table to use, and the choice of option group 2 will be used to apply a filter.

I want the user to pick his/her two options and click the "submit" button. I then want to read the values from the option groups and open up the table or form pertaining to the options. Any suggestions?

Thanks for your help.

 

[jebry]

Hi!

Well, not knowing your exact application here is, in general, how you would see which box of an option group is checked:

Select Case YourOptionGroup.Value
Case 1
strTable/FormName = appropriatetableorform
Case 2
etc.
End Select

It would seem to be most appropriate to have forms set up to display the table information because it would be easy to filter:

Select Case YourSecondGroup.Value
Case 1
strWhere = "YourFieldInTable = SomeNumericValue"
Case 2
strWhere = "YourFieldInTable = 'SomeStringValue'"
Case 3
etc.
End Select

DoCmd.OpenForm strFormName, , ,strWhere

hth


Jeff Bridgham
bridgham@purdue.edu

 

[obheron]

Let me try to summarize what I am trying to do so that it's better understood.

Lets say Option group 1 is a list of different types of fruit.
Option group 2 is information choices on the type of fruit you choose in group 1.

Ex. I choose Apple from group 1 and varieties from group 2.
When I hit the "submit" command button, I want a filter to access a large table that has all the information for the fruits in it and only pick out the varities for apples.

I wish for the button macro to open and filter the table instead of display it in the form, due to the large size of the table.

It appears that i cannot do this with the built in macros and will have to accomplish it in the editor build.

hth

Obh

 

[Eupher]

Hi, obheron,

Do you know how to create event procedures? Something like this in the command button's On Click event should work:

Dim strDocName As String
Dim strWhere As String

Select Case [COLOR=blue]optMyOptionGrp1[/color]
    Case 1
        strDocName = "[COLOR=blue]frmMyForm1[/color]"
    Case 2
        strDocName = "[COLOR=blue]frmMyForm2[/color]"
End Select

Select Case [COLOR=blue]optMyOptionGrp2[/color]
    Case 1
        strWhere = "[COLOR=blue]fldMyField[/color] = '[COLOR=blue]somevalue1[/color]'"
    Case 2
        strWhere = "[COLOR=blue]fldMyField[/color] = '[COLOR=blue]somevalue2[/color]'"
End Select

DoCmd.OpenForm strDocName, , , strWhere

This example assumes you have two forms, each bound to a different table, and you will substitute YOUR object names and values for the generic names in blue.

HTH,

Ken S.

 

[Eupher]

oops, sorry, I type too slowly!

Ken S.

 

[obheron]

Ok, was just looking at it a little more.

Both Option groups and command button are in the same form.
The options in group 2 will point to different tables.
The options in group 1 are the filters for the table.

 

[TheAceMan1]

How are ya obheron . . . . .
[ol][li]First [blue]if you intend to use a single form for this, forget it![/blue] Just too many differences from table to table (names, field count, field type & all). You can try . . . but I promise you'll come back to Tek-Tips speaking another language! ;-) So your gonna [blue]make a form for each table.[/blue][/li]
[li]You need to give more thought as to wether [blue]all filters will work on all forms![/blue] . . . and what ya gonna do if they dont![/li][/ol]
If you can accomplish the above, below is an example of code that would reside in the [blue]Click[/blue] event of the [blue]Command Button[/blue]:

[blue]   Dim frmName As String, Criteria As String
   
   frmName = Choose(Me!OptGrp1Name, "FormName1", FormName2, "FormName3")
   Criteria = Choose(Me!OptGrp2Name, "Filter1", "Filter2", "Filter3")
   
   DoCmd.OpenForm frmName, acNormal, , Criteria[/blue]

See Ya! . . . . . .

 

[obheron]

I tried something a bit different. I have 2 forms now. One with one option group, the second with the other option group and command button. The current code for the command button is:

Private Sub Command0_Click()

Dim strUnit As String
Dim strOption As String

Select Case frmForm1
Case 1
strUnit = "A"
Case 2
strUnit = "B"
Case 3
strUnit = "C"
Case 4
strUnit = "D"
End Select

Select Case frmForm2
Case 1
strOption = "Dog"
Case 2
strOption = "Cat"
End Select

DoCmd.OpenTable

End Sub


The strOption is the name of the table I want to open in the DoCmd.Opentable command. How do I state it so that it reads the table name from strOption?

Another question: is there an easier way to read the value from option group 1? there will be a lot more than 4 values for this group and it's a bit cumbersome to use the
Case #
strOption =

statement for all the values.

Also could I use subforms to have the option groups and command button on the same form? Thanks.