Search Form Question

I have a search form that allows to search through 4 categories each category has a check box that when checked opens up search options for it and then displays the results in a listbox... my problem is that I have the on double click event for that listbox setup to open a form and display the record that is double clicked well that works for one category but i need to be able to open up a form for each category so if category 2 is checked on double click of the listbox it would open up category2 form and display the record and so on.. i need help coming up with some kind of if statement of something to say if checkbox1 is checked then on doubleclick of listbox open form1, or if checkbox2 is checked on listbox doubleclick open form2.. something like that for all 4 of them.. any ideas?
arcilesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BillPowellCommented:
If your check boxes are part of an option group frame then:

Select Case Me.MyOptionGroup.Value
  Case 1
    DoCmd.OpenForm "MyForm1", acNormal, , MyField = Me.MyListBox
  Case 2
    DoCmd.OpenForm "MyForm2", acNormal, , MyField = Me.MyListBox
  Case 3
    DoCmd.OpenForm "MyForm3", acNormal, , MyField = Me.MyListBox
  Case 4
    DoCmd.OpenForm "MyForm4", acNormal, , MyField = Me.MyListBox
End Select

You can find the option value of each checkbox under the Data tab (assuming of course they are in an option group).  If they are not in an option group, that means that you can select more than one of them so would need to do much more conditional testing.
0
arcilesAuthor Commented:
they are in an option group because you can only select one at a time.. i pasted that in and it looked something like this:
Private Sub lbxResults_Click()
Select Case inCTL.Name
  Case "chkTbl1" 'Name1
    DoCmd.OpenForm "MyForm1", acNormal, , MyField = Me.MyListBox
  Case "chkTbl1" 'Name2
    DoCmd.OpenForm "MyForm2", acNormal, , MyField = Me.MyListBox
  Case "chkTbl1" 'Name3
    DoCmd.OpenForm "MyForm3", acNormal, , MyField = Me.MyListBox
  Case "chkTbl1" 'Name4
    DoCmd.OpenForm "MyForm4", acNormal, , MyField = Me.MyListBox
End Select

End Sub

just to see what happens and it gives a variable not defined highlights the Private Sub lbxResults_Click() in yellow and selects the inCTL..  am i trying to put this in the wrong spot or did I miss something?
0
arcilesAuthor Commented:
oops i just pasted that in the chkTbl numbers are 1,2,3,4 i dont have them as all 1 in the code..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

arcilesAuthor Commented:
hmm on second glance under the data tab i didnt find anything about the options group, the boxes can all be selected but only 1 at a time...but I dont see anything in the data tab of any of the checkboxes either
0
BillPowellCommented:
Do you wish for them all to be able to be selected.  I suspect not, but it appears as if your current controls are not in an option group.  I suggest you create an option group with new controls and then delete the old checkboxes.  That way only one can be selected at a time.  You can use the wizard to help you with this.  Just make sure the magic wand is depressed in the cortrol toolbox.  I would do this first and we'll go from there.

Is there only one listbox on your form?

0
harfangCommented:
When you write:

  Select Case inCTL.Name
    Case "chkTbl1" 'Name1

You assume that inCTL (the option group) somehow returns the *name* of the currently selected option button in the .Name property.
This is not so. inCTL.Name will always return "inCTL"...
Go back to the original suggestion

  Select Case Me.MyOptionGroup.Value
    Case 1

The option group MyOptionGroup returns as .Value the .OptionValue of the currently selected option button, or Null if none is selected.

hope this helps
0
arcilesAuthor Commented:
here is what i am working with but it isn't doing anything when i double click the listbox..any ideas?

Private Sub lbxResults_DblClick(Cancel As Integer)
    Dim strFormName As String, strCriteria As String
    Select Case inCTL.Name
        Case "chkTbl1" 'Name1
            strFormName = "Name1_Search_Results"
            strCriteria = "[Name1]='" & Me!lbxResults.Column(0) & "'"
        Case "chkTbl2" 'Name2
            strFormName = "Name2_Search_Results"
            strCriteria = "[Name2]='" & Me!lbxResults.Column(0) & "'"
        Case "chkTbl3" 'Cows
            strFormName = "Name3_Search_Results"
            strCriteria = "[Name3]='" & Me!lbxResults.Column(0) & "'"
        Case "chkTbl4" 'Name4
            strFormName = "Name4_Search_Results"
            strCriteria = "[Name4]='" & Me!lbxResults.Column(0) & "'"
        Case Else
            Exit Sub
    End Select
 
    DoCmd.OpenForm strFormName, , , strCriteria
End Sub
0
harfangCommented:
Reread my comment http:#12692922 :)
Again inCTL.Name always returns "inCTL"... which is treated in "case else"...
0
arcilesAuthor Commented:
i must be in over my head on it i know little of programming.. i tried to create an option group like suggested but it foobar'd the whole search when i did that so that code was suggested by friend to try to get it to work but no go. i re-read the comment you made earlier about the inCTL.Name but seems like i could only get that to work if i could some how recreate it all as a group.. i am not sure though even in a group it wont jack up the rest of the search and i probably couldn't fix the code to it because the checkboxes control what is displayed so if checkbox1 is checked certain options appear and if checkbox2 is checked a different set of options appear and so on.
0
BillPowellCommented:
In your situation, the option group is the best and cleanest way, by far.  In order to perform actions on the other controls (enabling disabling, changing listbox contents etc), just test for the option group value in the after update event for the option group.  Your on the right track, but you need to forget about this whole inctl.name thing and stick with the option group.  Create a copy of your database to try this, so you can try different things without worry of screwing it up.
0
harfangCommented:
Let me add a few lines to your function:


Private Sub lbxResults_DblClick(Cancel As Integer)
    Dim strFormName As String, strCriteria As String

    MsgBox "inCTL has the name: " & intCTL.Name & " and the value: " & inCTL.Value

    Select Case inCTL.Name
        Case "chkTbl1" 'Name1
            strFormName = "Name1_Search_Results"
            strCriteria = "[Name1]='" & Me!lbxResults.Column(0) & "'"
        Case "chkTbl2" 'Name2
            strFormName = "Name2_Search_Results"
            strCriteria = "[Name2]='" & Me!lbxResults.Column(0) & "'"
        Case "chkTbl3" 'Cows
            strFormName = "Name3_Search_Results"
            strCriteria = "[Name3]='" & Me!lbxResults.Column(0) & "'"
        Case "chkTbl4" 'Name4
            strFormName = "Name4_Search_Results"
            strCriteria = "[Name4]='" & Me!lbxResults.Column(0) & "'"
        Case Else

            MsgBox "No case statement matched"

            Exit Sub
    End Select

    MsgBox "Now trying to open form: " & strFormName & vbCrLf _
        & "with criteria: " & strCriteria
 
    DoCmd.OpenForm strFormName, , , strCriteria
End Sub

This should give you an idea of what's actually happening.

When programming, it is always best to concentrate on one thing at a time. Do not worry yet about the side effects. Just get one thing running: the management of the double-click event. Worry later about the criteria and such.

Good Luck!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.