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

two listboxes

100+
P: 140
hi all


i ve 2 listboxes. listbox1 has to get value from tasklist. listbox 2 has to get value from personlist.

when i select listbox1 value it should show the coressponding value. to the listbox 2

list1 has tasknames from tasklist.
list2 has to display values from personlist.

for eg( clicking(list1) office it should show (list2) employee name, etc)

help me
Nov 11 '06 #1
Share this Question
Share on Google+
17 Replies


NeoPa
Expert Mod 15k+
P: 31,186
In the AfterUpdate event of listbox1 enter some code to set the listbox2.ControSource to some SQL with the WHERE clause, selecting only the data that matches the value in listbox1.
Nov 11 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
In the AfterUpdate event of listbox1 enter some code to set the listbox2.ControSource to some SQL with the WHERE clause, selecting only the data that matches the value in listbox1.
Slight correction.

It's not the listbox2.ControlSource that has to be reset. This should be empty unless you want the value stored in a field.

It's the listbox2.RowSource that has to be set to a sql statement with criteria based on the selection in listbox1.

Adrian

You made a mistake. How unlike you. <grin>

Mary
Nov 12 '06 #3

100+
P: 140
Slight correction.

It's not the listbox2.ControlSource that has to be reset. This should be empty unless you want the value stored in a field.

It's the listbox2.RowSource that has to be set to a sql statement with criteria based on the selection in listbox1.
its working but see when i click the listbox1 value only the list2 has to display the value.

under each taskid atleast person involved in it. list1 has 4taskid. when I click that taskid it should display 4 person details in list2.
Nov 13 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
its working but see when i click the listbox1 value only the list2 has to display the value.

under each taskid atleast person involved in it. list1 has 4taskid. when I click that taskid it should display 4 person details in list2.
I don't really understand what you're saying. Can you post the sql statement in the Row Source property in list2 so I can see what it's doing.
Nov 13 '06 #5

100+
P: 140
I don't really understand what you're saying. Can you post the sql statement in the Row Source property in list2 so I can see what it's doing.

listbox2.. rowsource.
SELECT person.person_id, person.Person_name, person.task, person.taskspec, person.status, person.startdate, person.endate, tasklist.task_id FROM tasklist INNER JOIN person ON tasklist.task_id=person.task_id;

list1
SELECT DISTINCT tasklist.task_id, person.person_id FROM tasklist INNER JOIN person ON tasklist.task_id=person.task_id;
Nov 14 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Change listbox2 rowsource to this:


SELECT person_id, Person_name, task, taskspec, status, startdate, endate, task_id FROM person
WHERE task_id=[Forms]![FormName]![List1];
Nov 14 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Change listbox2 rowsource to this:


SELECT person_id, Person_name, task, taskspec, status, startdate, endate, task_id FROM person
WHERE task_id=[Forms]![FormName]![List1];
You will also have to put the following in the After Update event of list1:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub list1_AfterUpdate()
  3.  
  4.   Me.list2.Requery
  5.  
  6. End Sub
  7.  
  8.  
Nov 14 '06 #8

NeoPa
Expert Mod 15k+
P: 31,186
Slight correction.

It's not the listbox2.ControlSource that has to be reset. This should be empty unless you want the value stored in a field.

It's the listbox2.RowSource that has to be set to a sql statement with criteria based on the selection in listbox1.
Adrian

You made a mistake. How unlike you. <grin>

Mary
As usual - quite correct Mary.
You will notice that my Form work on here is not at the same level as my Query work (hopefuly).
I usually have to go and look it up in the help system (or try it out on a test form) before I give a form or control type response.
I'm learning from this myself, and also from your posts. I see form manipulation as one of your (many) strength areas.
I think many (not all) of our members would benefit greatly themselves if they just looked around a little first, tried help etc.
I'm always happy to be corrected though, saves me remembering the wrong stuff.

-Adrian.
Nov 14 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
As usual - quite correct Mary.
You will notice that my Form work on here is not at the same level as my Query work (hopefuly).
I usually have to go and look it up in the help system (or try it out on a test form) before I give a form or control type response.
I'm learning from this myself, and also from your posts. I see form manipulation as one of your (many) strength areas.
I think many (not all) of our members would benefit greatly themselves if they just looked around a little first, tried help etc.
I'm always happy to be corrected though, saves me remembering the wrong stuff.

-Adrian.
Most of what I know I learned through trial and error and of course looking for examples of code online. Unfortunately, you aren't always guaranteed that the code is correct so you often have to play around with it. The help files in Access and the VB Editor are good for the basic rules. I still have to check sometimes if I haven't used a function in a while.

One of my specialities is designing user friendly bespoke applications. For 'user friendly' read LOTS of functionality. My applications these days have so much code I feel like a proper programmer , HA HA.

Mary
Nov 14 '06 #10

100+
P: 140
Most of what I know I learned through trial and error and of course looking for examples of code online. Unfortunately, you aren't always guaranteed that the code is correct so you often have to play around with it. The help files in Access and the VB Editor are good for the basic rules. I still have to check sometimes if I haven't used a function in a while.

One of my specialities is designing user friendly bespoke applications. For 'user friendly' read LOTS of functionality. My applications these days have so much code I feel like a proper programmer , HA HA.

Mary
Hi,

its not working.

Form name: object_form

list1. i ve changed now to SELECT DISTINCT tasklist.task_id FROM tasklist ORDER BY tasklist.task_id;

list2. as u said. SELECT person.person_id, person.Person_name, person.task, person.taskspec, person.status, person.startdate, person.endate, person.task_id FROM person WHERE (((person.task_id)=Forms!object_form!List0)); I ve changed the form name to object_form.

afterupdate event

me.list2.requery thatz it.

but not working. its becos of the form name or something else.

help me out
Nov 15 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
list1

SELECT DISTINCT tasklist.task_id FROM tasklist ORDER BY tasklist.task_id;

list2

SELECT person.person_id, person.Person_name, person.task, person.taskspec, person.status, person.startdate, person.endate, person.task_id FROM person WHERE (((person.task_id)=[Forms]![object_form]![List1]));

You had the wrong listbox name in list2 it should be List1 on the object_form as above.

The after update event should be on list1 but the code is

Me.List2.Requery

Yes?
Nov 15 '06 #12

100+
P: 140
list1

SELECT DISTINCT tasklist.task_id FROM tasklist ORDER BY tasklist.task_id;

list2

SELECT person.person_id, person.Person_name, person.task, person.taskspec, person.status, person.startdate, person.endate, person.task_id FROM person WHERE (((person.task_id)=[Forms]![object_form]![List1]));

You had the wrong listbox name in list2 it should be List1 on the object_form as above.

The after update event should be on list1 but the code is

Me.List2.Requery

Yes?
still its not working. i don't know where am going wrong.


Private Sub List2_AfterUpdate()

Me.List1.Requery

End Sub

=[Forms]![object_form]![List1])); square bracket is not showing in rowsource property

is there anyother solution to execute this form.

both listboxes are in object_form

help me know
Nov 16 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
still its not working. i don't know where am going wrong.


Private Sub List2_AfterUpdate()

Me.List1.Requery

End Sub
This should be

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub List1_AfterUpdate()
  3.  
  4. Me.List2.Requery
  5.  
  6. End Sub
  7.  
  8.  
Then go to the properties of List1 and make sure that [Event Procedure] appears in the After Update property on the event tab.
Nov 16 '06 #14

100+
P: 140
This should be

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub List1_AfterUpdate()
  3.  
  4. Me.List2.Requery
  5.  
  6. End Sub
  7.  
  8.  
Then go to the properties of List1 and make sure that [Event Procedure] appears in the After Update property on the event tab.

hi

i ve tried that, its still not working
:(
Nov 16 '06 #15

100+
P: 140
This should be

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub List1_AfterUpdate()
  3.  
  4. Me.List2.Requery
  5.  
  6. End Sub
  7.  
  8.  
Then go to the properties of List1 and make sure that [Event Procedure] appears in the After Update property on the event tab.
hi mary

its not at all working. help me out in this.
pls
Nov 16 '06 #16

100+
P: 140
This should be

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub List1_AfterUpdate()
  3.  
  4. Me.List2.Requery
  5.  
  6. End Sub
  7.  
  8.  
Then go to the properties of List1 and make sure that [Event Procedure] appears in the After Update property on the event tab.
now i ve created new form and tried. its working pucca. thanks a lot for ur effort mary. really my head was spinning like anything today, thought of breaking this at last.
Nov 16 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
now i ve created new form and tried. its working pucca. thanks a lot for ur effort mary. really my head was spinning like anything today, thought of breaking this at last.
I'm glad everythings worked out.

It was a good idea to start from scratch with a new form as sometimes you've made changes you can't remember to properties and if you don't know what you're looking for it can be a bit of a minefield.

I must admit I couldn't understand why it wasn't working.

Good work and good initiative. Just what we like in posters.

Mary
Nov 16 '06 #18

Post your reply

Sign in to post your reply or Sign up for a free account.