Solved

Go to specified row of a combo box with check boxes

Posted on 2011-03-22
9
364 Views
Last Modified: 2012-05-11
On MS Access 2007 my combo box allows multiple selections, providing check boxes for the selections.  How can I allow user to quickly go to a desired row of the combo box? --e.g., go to the rows of the field that begin with letter "P"? ( This is not a problem with a simple combo box that doesn't allow multiple sections; you put the curser in a row of the field, and enter the first letter of the first word in the field.) I have attached a Word picture of the opened combo box. GOTO-desired-row-of-a-combo-box.docx
0
Comment
Question by:Tucker22
  • 4
  • 3
  • 2
9 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 35192249
One option is to add a new first column to the combo box row source.  Make it the same as the current second column.  You'll have that column included twice.  Set the column width for the first column to 0.01" or something.  Make sure to adjust the bound column setting so it is still on the column you want.
OM Gang
0
 

Author Comment

by:Tucker22
ID: 35192340
This would not allow me to use the "Allow Multiple Values" property for the combo box (with check boxes to select the values), and move to the desired row by entering the first letter of that row's text value.
0
 
LVL 28

Expert Comment

by:omgang
ID: 35193766
Please post the row source for the combo box in question.
OM Gang
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 167 total points
ID: 35193845
This is what I would consider yet another drawback to using these "Multi Valued fields"
http://allenbrowne.com/Access2007.html#Mixed

Even though the Autoexpand property is set to yes, this does not seem to work because the checkbox seems to always be the first visible column (what the autoexpand property usually picks up on)

The theory being, if you are going to use multiselect fields, then you want to select more than 1 Value,
...hence, no need to "Jump" to a value like you can in a "Single" value filed" (standard combobox).


JeffCoachman
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Tucker22
ID: 35194420
To Omgang: Thank you for trying to help; maybe you will have a further suggestion.  Here is the row source:

SELECT [LkUpPart63MACTReq].[Part63_subpart_short_name], [LkUpPart63MACTReq].[MajorArea] FROM [LkUpPart63MACTReq];

I had already combined the original two fields--the field having "A" to "ZZZZ" values with the field that is the "short name" of the Subpart.  I did this because I wanted the combo box selections to update the associated table with both of these values, and selections from a combo box can populate only one field of a table--as far as I know.

 I might give up on allowing multiple selections from this particular  combo box (which has long rows of text, as it seems to adversely affect (1) selecting options , and (2) the readability of the values when they are placed in one field.  But maybe you will have another suggestion that works well.
0
 
LVL 28

Assisted Solution

by:omgang
omgang earned 333 total points
ID: 35198528
<<and selections from a combo box can populate only one field of a table--as far as I know.>>
Not so.  It's true that a combo box can have only one bound column that can be assigned to a field in the underlying table.  However, there is nothing to stop us from populating many other table fields (via code, macro, etc.) with the values from the other columns in the combo box.  In general we don't do this becuase the it goes against normalization:  if we can store one value in a table and lookup related values from other tables (or via calculation) then we should only store the single value in our table.
OM Gang
0
 
LVL 28

Assisted Solution

by:omgang
omgang earned 333 total points
ID: 35199294
Tucker22, I can't figure it out either.  I have never used multi valued fields and am thinking boag2000 is on to it for why this doesn't work.  Here's what I've tried and it functions as expected up to the point of trying to set focus onto an item in the list.  Each approach I attempt results in an error.


Option Compare Database
Option Explicit

Public blKeyPressed As Boolean

Private Sub Category1_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo Err_Category1_KeyDown

    Dim i As Integer
    Dim x As String
    Dim vVal As Variant
   
    blKeyPressed = False
   
        'check value of public key press variable an only proceed if it is False
    If blKeyPressed = True Then GoTo Exit_Category1_KeyDown
   
        'capture key press character
    x = Chr$(KeyCode)
   
    For i = 0 To Me.Category1.ListCount - 1
        If Left(Me.Category1.ItemData(i), 1) = x Then

            'Me.Category1.ItemData(i).Select
       
            'vVal = Me.Category1.ItemData(i).Column(0)
       
            'Me.Category1.ItemData(i).SetFocus
           
            'vVal = Me.Category1.ItemData(i)
            'Me.Category1.Value = vVal        
   
            Exit For
        End If
    Next

        'set public key press variable to true so we don't repeat this for every key press
    blKeyPressed = True
   
Exit_Category1_KeyDown:
    Exit Sub

Err_Category1_KeyDown:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub Category1_KeyDown of VBA Document Form_frmCategory"
    Resume Exit_Category1_KeyDown
End Sub
0
 

Author Closing Comment

by:Tucker22
ID: 35202807
Thanks to OM Gang & Jeff Coachman.  Your comments convinced me to create multiple combo boxes rather than allowing multiple entries to one combo box.  It's a less sophisticated method and takes up more space on the form, but it'll be easy for users to enter data and the design can be easily maintained.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35203538
To be fair, ...yes the Multiselect value/field/combobox is slick...

But remember, it has real flaws:
http://allenbrowne.com/Access2007.html#Mixed
http://support.microsoft.com/kb/926701

IMO, the biggest draw back is that it glosses over one the most crucial relationships in database design, ...The Many-to-Many relationship.

By doing this it shortchanges new Access developers of this critical skill.

Remember, if you rely to heavily on MVF's, and do not learn these basic concepts, you will be at a distinct disadvantage when it comes time to upgrade to SQL Server.
As of now, these MVF's *DO NOT* import into SQL Server.
So you may have to  redesign key parts of your application all over again from scratch.

Finally keep in mind that Database professionals have been successfully designing these types of relationships and interfaces for over 50 years, ...long before MVF's in MS Access.


;-)

JeffCoachman
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now