?
Solved

Search Form Question

Posted on 2004-11-28
11
Medium Priority
?
193 Views
Last Modified: 2006-11-17
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?
0
Comment
Question by:arciles
  • 5
  • 3
  • 3
11 Comments
 
LVL 11

Expert Comment

by:BillPowell
ID: 12691493
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
 

Author Comment

by:arciles
ID: 12691641
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
 

Author Comment

by:arciles
ID: 12691649
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:arciles
ID: 12691724
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
 
LVL 11

Expert Comment

by:BillPowell
ID: 12692511
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
 
LVL 58

Expert Comment

by:harfang
ID: 12692922
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
 

Author Comment

by:arciles
ID: 12699318
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
 
LVL 58

Expert Comment

by:harfang
ID: 12700494
Reread my comment http:#12692922 :)
Again inCTL.Name always returns "inCTL"... which is treated in "case else"...
0
 

Author Comment

by:arciles
ID: 12700974
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
 
LVL 11

Expert Comment

by:BillPowell
ID: 12702015
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
 
LVL 58

Accepted Solution

by:
harfang earned 300 total points
ID: 12702041
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question