Move to index in combo box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been using a combo box to jump to a specific record in a form. But,
even with the comb box set at 15 lines long, with many hundreds of records in
the form the vertical slider on the combo box is a little small and can be
jumpy (moving 1/32" jumps 10 names, etc.). The combo box is formed from:
SELECT [cust num], [last name], [first name]
FROM [this table]
ORDER BY [last name], [first name];

I'd like to put in some boxes that will jump the combo box to a certain set
of names. For instance, put in a box with a label on it that says "M".
Pressing this button would jump the combo box to the first [last name] that
starts with the letter M. For instance, if I have records for:
Lupold
Macaroni
Martin
Then it would jump to Macaroni. But, if Macaroni is deleted, then Martin
would be the first name that begins with M and pressing the "M" box should
jump the combo box to Martin.
 
Banaticus

The combo box has a property you can set that will "auto-complete" when you
begin typing. No need to set up additional controls (i.e., boxes) to filter
the combo box.

Open the properties of the combo box in design mode and look for
"AutoExpand". Check Access HELP for more information on this property.

Regards

Jeff Boyce
<Office/Access MVP>
 
Yes, I use that auto-complete when I'm using it, but this is a feature
requested by completely computer illiterate people who, even if it takes 10
times longer, would rather navigate solely by mouse without ever touching the
keyboard.
The combo box has a property you can set that will "auto-complete" when you
begin typing. No need to set up additional controls (i.e., boxes) to filter
the combo box.
Click to expand...

The combo box itself is already set to auto-complete and limit to list.
 
Banaticus

I don't know if I'd call folks who are so mouse-centric "completely computer
illiterate" <g>...

Are you sure you (and your customers) really want to take up all the "real
estate" on the screen to put buttons with "A", "B", "C", ... "Z" on them?
You won't have much room left for actual meaningful data!

Oh well, whatever! Give this a try...

Add a command button with "A" on it. In the Click event for that button,
modify the SQL statement your combo box uses to list names. For example, if
your combo box uses something like:

"SELECT * FROM YourTable"

you would modify that to something like:

"SELECT * FROM YourTable WHERE YourNameField Like 'A' & '*'"

You might need to mess with the single/double quotes to get it to work the
way you want.

Then, still in the Click event, you'd set the RowSource property of the
combo box to this new SQL statement, with something like:

Me!YourComboBox.RowSource = ... (your new SQL string)

Good luck!

Jeff Boyce
<Office/Access MVP>
 
Thanks for your help.

I now have a command button where the Click (I keep wanting to type onClick)
command is:
Code: 
Private Sub Command_Button_A_Click()
Me![Names Combo Box].RowSource = "Long SQL statement"
End Sub
This changes the rowsource to all last names that begin with
an A (actually, an A or a B) but it doesn't change the name of the person in
the combo box. To the user, who's clicking the "A-B" command button,
apparently nothing is happening. I tried adding:
Me.Names_Combo_Box.Requery
Which resets the combo box, but still leaves the "top area", the area that
the user sees without clicking anywhere, blank.

I also have this same problem when the form loads, the combo box's top line
is blank. I guess if I put in an OnLoad function that calls the first
possible arguement in the combo box's list then both of these problems would
be solved.

I'm looking for some way to change the Combo Box to the top item in its list
 
I suppose one approach could be to use the SQL statement you dynamically
alter based on which button was clicked, and use that to get the first row
in the recordset. You'd do that in your code, then set the value of the
combobox to that first row's ID, after resetting the Row Source.

Just one person's opinion...

Regards

Jeff Boyce
<Office/Access MVP>

Banaticus said:
Thanks for your help.

I now have a command button where the Click (I keep wanting to type
onClick)
command is:
Code: 
Private Sub Command_Button_A_Click()
Me![Names Combo Box].RowSource = "Long SQL statement"
End Sub
This changes the rowsource to all last names that begin with
an A (actually, an A or a B) but it doesn't change the name of the person
in
the combo box. To the user, who's clicking the "A-B" command button,
apparently nothing is happening. I tried adding:
Me.Names_Combo_Box.Requery
Which resets the combo box, but still leaves the "top area", the area that
the user sees without clicking anywhere, blank.

I also have this same problem when the form loads, the combo box's top
line
is blank. I guess if I put in an OnLoad function that calls the first
possible arguement in the combo box's list then both of these problems
would
be solved.

I'm looking for some way to change the Combo Box to the top item in its
list

Jeff Boyce said:
Banaticus

I don't know if I'd call folks who are so mouse-centric "completely
computer
illiterate" <g>...

Are you sure you (and your customers) really want to take up all the
"real
estate" on the screen to put buttons with "A", "B", "C", ... "Z" on them?
You won't have much room left for actual meaningful data!

Oh well, whatever! Give this a try...

Add a command button with "A" on it. In the Click event for that button,
modify the SQL statement your combo box uses to list names. For example,
if
your combo box uses something like:

"SELECT * FROM YourTable"

you would modify that to something like:

"SELECT * FROM YourTable WHERE YourNameField Like 'A' & '*'"

You might need to mess with the single/double quotes to get it to work
the
way you want.

Then, still in the Click event, you'd set the RowSource property of the
combo box to this new SQL statement, with something like:

Me!YourComboBox.RowSource = ... (your new SQL string)

Good luck!

Jeff Boyce
<Office/Access MVP>
Click to expand...
Click to expand...
 
Back
Top