By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,496 Members | 1,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,496 IT Pros & Developers. It's quick & easy.

Selecting a range of records in a combobox with another combobox

P: n/a
I have a combobox which is used to select records, which is
satisfactory at the moment.

However, a second user is going to start using this database and there
will be 1600 records. This makes the combobox a little unfeasable as
searching through this many names would be difficult and
time-consuming.

I would like the user to be able to select a group of people from a
first combobox, and then when he goes to the second, only the names of
these people are displayed.

If anybody can point me in the right direction, I'd be grateful.
Thanks.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
How are they grouped?

Phil

"Pete" <pg*******@yahoo.co.uk> wrote in message
news:6f**************************@posting.google.c om...
I have a combobox which is used to select records, which is
satisfactory at the moment.

However, a second user is going to start using this database and there
will be 1600 records. This makes the combobox a little unfeasable as
searching through this many names would be difficult and
time-consuming.

I would like the user to be able to select a group of people from a
first combobox, and then when he goes to the second, only the names of
these people are displayed.

If anybody can point me in the right direction, I'd be grateful.
Thanks.

Nov 12 '05 #2

P: n/a
Basically, its for a school. The pupils are divided into forms, 7F1,
7F2, 8F1, 8F2, etc, with five years (the 7,8,9,10 and 11) and ten
forms in each year F1 to 10).

What I want to be able to do is select 7,8,9,10 or 11 from one
combobox and then in the second combobox see only the pupils in that
particular year group, if anyone can help...???

I've tried a few different methods but with no success so far - I'm
not so experienced in coding.

Thanks.
"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in message news:<3f***********************@mercury.nildram.ne t>...
How are they grouped?

Phil

"Pete" <pg*******@yahoo.co.uk> wrote in message
news:6f**************************@posting.google.c om...
I have a combobox which is used to select records, which is
satisfactory at the moment.

However, a second user is going to start using this database and there
will be 1600 records. This makes the combobox a little unfeasable as
searching through this many names would be difficult and
time-consuming.

I would like the user to be able to select a group of people from a
first combobox, and then when he goes to the second, only the names of
these people are displayed.

If anybody can point me in the right direction, I'd be grateful.
Thanks.

Nov 12 '05 #3

P: n/a
OK Pete

Lets assume you have done the thing logically and have 2 tables

Table Pupils
PupilID Autonumber Key
PupilName Text ' though you shold have surname and first name in
separate fields
.....
.....
FormID Long Number Required

and

Table Classes ' Don't use "Form" it will get
muddled with an Access Form
ClassID Autonumber Key
Class Text Indexed No Duplicates

Relationship between the 2 ClassIDs

You then have a form based on the Pupil Table (or query) with 2 combo boxes
for searching + 1 Combo box for changing the Pupils Class.
I assume it also has PupilID, PupilName and Class on it

The first ComboBox is for inputing the class for the pupil
Name ClassID
ControlSource ClassID
RowSource "SELECT Class, Class FROM Classes ORDER BY Class;"
ColumnCount 2
ColumnWidths 2cm;0cm
BoundColumn 2

The first ComboBox for finding the pupil, which class
Name ClassIDRelay
ControlSource
RowSource "SELECT Class, ClassFROM Classes ORDER BY Class;"
ColumnCount 2
ColumnWidths 2cm;0cm
BoundColumn 2

The Second ComboBox for finding the pupil having entereg the class in
ClassIDRelay
Name PupilRelay
ControlSource
RowSource "SELECT Pupils.PupilName, Classes.ClassID, Pupils.PupilID
FROM Pupils INNER JOIN Classes ON Pupils.ClassID = Classes.ClassID
WHERE (((Classes.ClassID)=[forms]![Pupils]![ClassIDRelay]))
ORDER BY Pupils.PupilName;;"
ColumnCount 3
ColumnWidths 4cm;2cm;0cm
BoundColumn 3

Ok I know that sound a bit complicated. Now unfortunately you need a few
lines of code.

After you change the ClassIDRelay, you need to get fresh information into
the PupilRelayCombo box.

So in the ClassIDRelay properties tab, under After Update just key in a [ -
yes a square bracket and it will change to [Event Procedure]
At the right hand side of the line will be ... (3 dots). Click on them and
you are in Visual basic
It should already say
Private Sub ClassIDRelay_AfterUpdate()

End Sub

Type in in between the 2 lines ClassIDRelay.Requery so that it looks like
bthis

Private Sub ClassIDRelay_AfterUpdate()

RupilRelay.Requery

End Sub

That will get your second combo box with the pupils from the selected class
showing in order

Nearly there. I could have written the whole thing faster than writing this
reply

Now type in a similar thing on the After update of the PupilIDRelay to get

Private Sub PupilIDRelay_AfterUpdate()

DoCmd.GoToControl "PupilID"
DoCmd.FindRecord PupilIDRelay

End Sub

Hope this helps

Phil
"Pete" <pg*******@yahoo.co.uk> wrote in message
news:6f**************************@posting.google.c om...
Basically, its for a school. The pupils are divided into forms, 7F1,
7F2, 8F1, 8F2, etc, with five years (the 7,8,9,10 and 11) and ten
forms in each year F1 to 10).

What I want to be able to do is select 7,8,9,10 or 11 from one
combobox and then in the second combobox see only the pupils in that
particular year group, if anyone can help...???

I've tried a few different methods but with no success so far - I'm
not so experienced in coding.

Thanks.
"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in message

news:<3f***********************@mercury.nildram.ne t>...
How are they grouped?

Phil

"Pete" <pg*******@yahoo.co.uk> wrote in message
news:6f**************************@posting.google.c om...
I have a combobox which is used to select records, which is
satisfactory at the moment.

However, a second user is going to start using this database and there
will be 1600 records. This makes the combobox a little unfeasable as
searching through this many names would be difficult and
time-consuming.

I would like the user to be able to select a group of people from a
first combobox, and then when he goes to the second, only the names of
these people are displayed.

If anybody can point me in the right direction, I'd be grateful.
Thanks.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.