Using 2 Option Groups 1

[doctorswamp]

Hi

Have a large number of items in a form's source and an option group to pick any of the 10 groups they belong to. The AfterUpdate procedure also limits the list box selector to the current group.

Some groups are still large and it would help to have another option group with letters A to Z to get the selector to home in more quickly than scrolling it.

Any thoughts on getting the two option groups to work together? One over-rides the other in my attempt.

 

[IGPCS]

yes you can create a second option group with letter from a - z and on the after update limit your records filtered by optionGroup1 and By optionGroup2

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[doctorswamp]

Thanks IGPCS

I'm using AfterUpdate procedures for each option group, with Select Case to apply the filters like this

Select Case
.
.
.
Case 10
Me.Filter = "qryStock.Group = 'Rose'"
Me.FilterOn = True

End Select

Dim strSQL As String
strSQL = "SELECT qryStock.StockListID, qryStock.Genus, qryStock.Variety, qryStock.Group"
If Me.FilterOn Then strSQL = strSQL & " WHERE " & Me.Filter

If Case 10, say, I can't then see how to work in the criteria from the second option group, since the letter I want is returned by its AfterUpate.

Are you saying to use the form's AfterUpdate instead, if so how do you reference the current settings of the option groups?

Thanks for your time.

 

[TheAceMan1]

Hoew are ya doctorswamp . . .

Of course you'll need a [blue]second control to filter on![/blue]

[blue]Copy/paste[/blue] the following code to the code module of the form. For your reference the followings items go together per option group.

[purple]OptGrpName1[/purple], [purple]CtlName1[/purple], Sel1
[purple]OptGrpName2[/purple], [purple]CtlName2[/purple], Sel2

Don't forget [blue]you![/blue] substitute proper names/values in [purple]purple[/purple] . . .

[blue]Public Sub DoFilter()
   Dim [b]Sel1[/b] As String, [b]Sel2[/b] As String, idx As Integer
   
   If IsNull(Me![purple][b][i]OptGrpName1[/i][/b][/purple]) Then
      idx = [purple][b][i]text count of Sel1[/i][/b][/purple]
   Else
      idx = Me![purple][b][i]OptGrpName1[/i][/b][/purple]
   End If
   
   [b]Sel1[/b] = Choose(idx, "[purple][b][i]Txt1[/i][/b][/purple]", "[purple][b][i]Txt2[/i][/b][/purple]", "[purple][b][i]Txt3[/i][/b][/purple]", [purple][b][i]...[/i][/b][/purple] , [purple][b][i]LastTxt[/i][/b][/purple] , "")
   
   If IsNull(Me![purple][b][i]OptGrpName2[/i][/b][/purple]) Then
      idx = 27
   Else
      idx = Me![purple][b][i]OptGrpName2[/i][/b][/purple]
   End If
   
   [b]Sel2[/b] = Choose(idx, "A", "B", "C", "D", "E", "F", "G", "H", _
                      "I", "J", "K", "L", "M", "N", "O", "P", _
                      "Q", "R", "S", "T", "U", "V", "W", "X", _
                      "Y", "Z", "")
   
   If [b]Sel1[/b] = "" And [b]Sel2[/b] = "" Then
      Me.FilterOn = False
   ElseIf [b]Sel1[/b] = "" And [b]Sel2[/b] <> "" Then
      Me.Filter = "[[purple][b][i]CtlName2[/i][/b][/purple]] Like '" & [b]Sel2[/b] & "*'"
      Me.FilterOn = True
   ElseIf [b]Sel1[/b] <> "" And [b]Sel2[/b] = "" Then
      Me.Filter = "[[purple][b][i]CtlName1[/i][/b][/purple]] = '" & [b]Sel1[/b] & "'"
      Me.FilterOn = True
   Else
      Me.Filter = "[[purple][b][i]CtlName1[/i][/b][/purple]] = '" & [b]Sel1[/b] & "' AND " & _
                  "[[purple][b][i]CtlName2[/i][/b][/purple]] Like '" & [b]Sel2[/b] & "*'"
      Me.FilterOn = True
   End If

End Sub[/blue]

In the [blue]AfterUpdate[/blue] event of the option groups just call the code:

[blue] Call DoFilter[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[doctorswamp]

AceMan

Wow, that's really impressive but please try to get some rest!

Small thing to resolve is that if there isn't a record satisfying the filter request the form goes totally blank with no error message. How can I catch this?

Bucketful of Guinness waiting if you're ever near Cambridge.

 

[TheAceMan1]

doctorswamp . . .

[blue]AceMan always takes time out for #1! . . .[/blue] Besides what I posted is a trimmed for your need common filtering routine in my library. Maybe 10 minutes all total! . . . if that much!

So . . . are you sure you really need the prompt? . . . After all if they can't see they got nothing then why are they there?

In any case if you want to persue this post back the [blue]Table Name[/blue] and the [blue]PrimaryKeyName[/blue] of the table the recordSource of the form is based on . . .

See Ya! . . . . . .

 

[doctorswamp]

Thanks AceMan

Last shot before Santa comes.

Just to be clear about the problem, after selecting an item from Option Group 1 I use my selector listbox to find an item. The second option group allows me to jump down the list to items starting with any letter of the alphabet, to speed up the process.

What happens is that if there isn't an item starting with a particular letter the whole form vanishes when this letter is clicked, apart from an outline. The form then has to be restarted.

The data source of the form is a query derived from two other queries, but is equivalent to a stock list with unique items defined by a compound of five fields. Because of the way stock items are built there isn't a primary key but the listbox links using the compound field.

Learning VBA is a slow process for me so thanks for all your help and sorry to be slow off the mark at times.

 

[TheAceMan1]

doctorswamp . . .

Post The RowSource of the form! . . .

See Ya! . . . . . .

 

[doctorswamp]

Gulp, sorry AceMan, here goes.

Main form Rowsource : qrySaleableSock

qrySaleableStock made from qryItems based on Items table with autonumber ItemID as primary key, and table Batches. Query has an outer join so it includes all listed Items (7500) and those made into batches.

This is the SQL version of the query.

SELECT IIf((IsNull(qryBatches.size) Or IsNull(qryBatches.Location)),qryItems.StockID,qryBatches.StockID) AS StockListID, qryItems.Genus, qryItems.Variety, qryItems.Group, qryBatches.Size, qryBatches.Location, qryBatches.BatchDate, qryBatches.BatchType AS Type, qryBatches.Quantity, qryBatches.CurrentStock, qryBatches.CatalogueItem, qryItems.Letter, qryItems.TotalSaleable, qryItems.TotalPrickedOut, qryItems.TotalGrowing
FROM qryItems LEFT JOIN qryBatches ON qryItems.ItemID = qryBatches.ItemID
ORDER BY qryItems.Genus, qryItems.Variety;

Items has three fields that define unique properties of garden plants. Batches then add two more properties that make unique batch items. The BatchID is a composite of Items and the extra two properteis. and is used as the link filed to a subform that records movements of items from stock.

 

[TheAceMan1]

doctorswamp . . .

New Code:

[blue]Public Sub DoFilter()
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim SQL As String, Fltr As String
   Dim Sel1 As String, Sel2 As String, idx As Integer
   
   If IsNull(Me![purple][b][i]OptGrpName1[/i][/b][/purple]) Then
      idx = [purple][b][i]text count of Sel1[/i]
   Else
      idx = Me![purple][b][i]OptGrpName1[/i][/b][/purple]
   End If
   
   Sel1 = Choose(idx, "[purple][b][i]Txt1[/i][/b][/purple]", "[purple][b][i]Txt2[/i][/b][/purple]", "[purple][b][i]Txt3[/i][/b][/purple]", "[purple][b][i]...[/i][/b][/purple]", [purple][b][i]LastTxt[/i][/b][/purple], "")
   
   If IsNull(Me![purple][b][i]OptGrpName2[/i][/b][/purple]) Then
      idx = 27
   Else
      idx = Me![purple][b][i]OptGrpName2[/i][/b][/purple]
   End If
   
   Sel2 = Choose(idx, "A", "B", "C", "D", "E", "F", "G", "H", _
                      "I", "J", "K", "L", "M", "N", "O", "P", _
                      "Q", "R", "S", "T", "U", "V", "W", "X", _
                      "Y", "Z", "")
   
   If Sel1 = "" And Sel2 <> "" Then
      Fltr = "[[purple][b][i]CtlName2[/i][/b][/purple]] Like '" & [Sel2] & "*'"
   ElseIf Sel1 <> "" And Sel2 = "" Then
      Fltr = "[[purple][b][i]CtlName1[/i][/b][/purple]] = '" & [Sel1] & "'"
   ElseIf Sel1 <> "" And Sel2 <> "" Then
      Fltr = "[[purple][b][i]CtlName1[/i][/b][/purple]] = '" & [Sel1] & "' AND " & _
             "[[purple][b][i]CtlName2[/i][/b][/purple]] Like '" & [Sel2] & "*'"
   End If
   
   SQL = "SELECT TOP 1 qryItems.ItemID, qryBatches.ItemID " & _
         "FROM qryItems " & _
         "LEFT JOIN qryBatches ON qryItems.ItemID = qryBatches.ItemID"
   
   If Fltr = "" Then
      SQL = SQL & ";"
   Else
      SQL = SQL & " WHERE (" & Fltr & ");"
   End If
   
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      Msg = "No records found for the combination you selected!"
      Style = vbInformation + vbOKOnly
      Title = "Search Criteria Failed! . . ."
      MsgBox Msg, Style, Title
   ElseIf Fltr = "" Then
      Me.FilterOn = False
   Else
      Me.Filter = Fltr
      Me.FilterOn = True
   End If
   
   Set rst = Nothing
   Set db = Nothing

End Sub[/blue]

BTW: In the forms [blue]OnLoad[/blue] event [blue]add a call to DoFilter![/blue]

See Ya! . . . . . .

 

[doctorswamp]

AceMan

Fantastic, end of journey!

Had a few error messages along the way in setting values for your [CtrlName1] and [CtrlName2]. Using things like[qryStockItems.Genus] etc gave 'Invalid bracketing of name', then 'Too few parameters...'. Eventually just [Genus] etc worked. Oh, also added this along the way

db=CurrentDb()
Very many thanks forall your help. Also from Hilary who says perhaps life can return to normal now!