make combo auto go to item

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

Guest

Hi all,

Was honestly trying to find anything which would be similar to my problem
but couldnt.

I have 2 combos. When i have selected a value in 1st (client number), and
start entering data into the other combo (project number) i want
automatically to go to the set of items which starts with the same number as
selected client.

example: client no.426 has 4 projects: 426-01, 426-02, 426-03 & 426-04

i dont want to filter out only those 4 projects out of others (sometimes for
this client i need to select a project other than the one starting with 426),
i just want TO GO TO the items in project list which start with 426

i want to get the same effect as if i start typing 426 in the project combo
WITHOUT having to type.

is there a way to accomplish this?
Any help would be apreciated!
Thank you.
Lana
 
Lana

I may be reading (too much) between the lines, ...

It sounds like your project number is actually comprised of more than one
"fact" (i.e., includes the client number). In a well-normalized table
structure, you would only have ONE fact per field.

If this is how your table is designed, split the data into two fields: 1) a
client number (this represents a foreign key, as it sounds like it is the
"many" side of a one-to-many relationship from client to project, and 2)
project number -- if this is supposed to be a unique ID, that's one thing.
If you are using this to "count" the number of projects, stop it! Use
queries to count.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Sorry Jeff,

I dont understand what you are talking about. - May be i am too stupid?
But I was asking for a code for my combo and you are telling me about tables
and querries.

Of cause i have different tables for Clients and different table for
Projects and i never intended to count them.

All i wanted was a code which would allow me to go to the needed item in the
2nd combo's list (instead of scrolling down) depending on the value of the
1st combo.

Lana
 
Lana

As I said, I may have read too much into your post... You did say "any
help..."

It sounded like you have a client number like "426" and project numbers like
"426-01", "426-02", ...

If the project numbers are actually, literally, "426-01" in the project
number field, your table structure is not well-normalized, and both you and
Access will have to work harder to do simple things. For example, to do
what you seem to consider a simple thing ... go to the part of the list that
starts with "426-..." <g>

So what I offered was a suggestion that would make this and future work with
your data easier for both you and Access.

That said, one approach to what you are trying to do might be to add code to
the AfterUpdate event of the first combo box. You could:
* set the focus on the second combo box
* use SendKeys to "type" the client number from the first combo box
* drop-down the second combo box.

Now the alternative, using a well-normalized structure...

Two combo boxes, with the query behind the second combo box being based on
(a selection criterion using) the selected value in the first. The
AfterUpdate event of the first combo box would have:
* Me!SecondComboBox.Requery

This would limit the second combo box to only those projects belonging to
the selected client. I'm not clear from your original description why you
would need/want to see ALL projects if you have a single specific client
selected to start with.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Hi Jeff,

what i wanted was exactly
* set the focus on the second combo box
* use SendKeys to "type" the client number from the first combo box
* drop-down the second combo box.
Click to expand...
but i have no idea how to do this. Can you please help me with the code?

The reason why i wanted to keep the list of projects full is that i can have
2-3 clients (various subcontractors) on the same project and i need to keep
reference to this project.
But in majority of cases the client and the project have corresponding
numbers that is why i wanted to automate some data input.

I know how to use Requery and i DO use it when i need to filter out the
contact persons from the same company, but the above case with projects is
completely different, - so it is not applicable here.

Thank you.
Lana
 
Lana

Create an event procedure for the first combo box.

Add something like (your actual syntax may vary-untested aircode):

Me!YourSecondComboBox.SetFocus
SendKeys Left(YourFirstComboBox,1)
SendKeys Mid(YourFirstComboBox,2,1)
SendKeys Mid(YourFirstComboBox,3,1)
Me!YourSecondComboBox.DropDown


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Hi Jeff

I tried to place the code into "onEnter" event of Combo Project1.

I tried the following, but nothing would work:
SendKeys Left(Client1.Column(2),1)
SendKeys Left(Me.Client1.Column(2),1)
SendKeys "Left(Me.Client1.Column(2),1)"
Tried to make a new text box on my form (Text70) equal to Client1.Column(2)
and tried following, but it also wouldnt work:
SendKeys Left(Me.Text70,1)
SendKeys "Left(Me.Text70,1)"
SendKeys Left(Text70, 1)

And when I tried Me.Project1 = Me.Text70
I would get wrong value in the Project1 Combo (as Text70 or
Client1.Column(2) corresponds with Column(1) of the Project1 Combo but not
its ID which is invisible)

Can anybody advise any solution please? I am out of thoughts who to solve it.
Lana
 
Lana

What happens if you "hard-code" the three numbers, as in:

SendKeys 4
SendKeys 2
SendKeys 6

Have you re-considered limiting the second combo to only those projects that
actually belong to customer number 426?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Back
Top