469,306 Members | 2,477 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

To populate listbox in subform from value selected in parent form

How can I populate the list box (list 2) in subform from a value selected in a list box (list 1) in parent form?

I can put in the row source of list 2 a query to match a table's field to the list 1.value, but I don't know how to pass this value from parent form to subform and where I should populate the row source of list 2.

Thanks for any tips.
Aug 25 '11 #1
12 7000
32,173 Expert Mod 16PB
See Referring to Items on a Sub-Form for that bit and then consider including more information in the question for more detailed help. I'm not even sure from your question what you're after exactly.
Aug 25 '11 #2
Say I have the parent form: FormA that has a listbox: lst1 and it has a few rows say displaying people's names.
When a row in this list is selected, it should populate a listbox: lst2 on the subform: FormB, with relevant data, say the selected person's children.
I put the subform on a page of FormA actually, say PageA1.

I suppose I should populate lst2.rowsource with query that selects the children where parent = lst1.value.

I suppose the syntax of the query in lst2.rowsource is:
Expand|Select|Wrap|Line Numbers
  1. SELECT child_id, child_name FROM child_table
  2. WHERE parent_id=Forms!FormA.lst1

And to refresh the content of lst2, I should put the requery on the parent form's current event:
Me.PageA1.Form.lst2.Requery ?

If PageA1 is not the subform control name, where can I find this?

Thanks in advance.
Aug 25 '11 #3
32,173 Expert Mod 16PB
That's a much clearer job :-)

My problem now is that in attempting to explain you have found correct answers to pretty well everything you asked. Good again.

There is an alternative approach that you may wish to be aware of though. In the SQL used for the RowSource of lst2 you could use a string literal built within your code in the AfterUpdate event procedure of FormA.lst1. Then, if the Value of lst1 were "Murphy", you would end up with something like :

Expand|Select|Wrap|Line Numbers
  1. SELECT child_id, child_name FROM child_table
  2. WHERE parent_id='Murphy'
The name of the subform control is found by going into design mode of the main form and clicking in the subform area. At this point the Properties pane (Alt-Enter if not already visible) will show the Name of the control.
Aug 25 '11 #4
NeoPa, unfortunately it didn't work :(
I used ! instead of . and still didn't work.
Aug 26 '11 #5
32,173 Expert Mod 16PB
Again with the minimal information. I can't help you unless you share details with me. What didn't work? What happened exactly?

Don't forget I'm working blind on this. I have no access to your database or your computer. I have to rely 100% on what you tell me and my own experience. If you tell me the least possible then my ability to help you is severely restricted.

At the very least you need to show what you tried and explain the results (It never worked; It worked the first time but never updated; etc).
Aug 26 '11 #6
I found the answer!

So I populated the rowsource of lst2 on OnClick event of lst1.
And apparently what I should write is:
Expand|Select|Wrap|Line Numbers
  1. Forms![ParentFormName]!PageA1.Controls!SubFormName.Controls!lst2.RowSource = "Query1"
Then I put:
Expand|Select|Wrap|Line Numbers
  1. [ParentFormName]!PageA1.Controls!SubFormName.Controls!lst2.Requery
so that when another row is selected on lst1, lst2 would be populated with the children of the selected person on lst1.

Oh man that took me like 2 days to figure that out.
Thank you for the tips!
Aug 26 '11 #7
32,173 Expert Mod 16PB
With that extra information I can suggest you try :
Expand|Select|Wrap|Line Numbers
  1. Forms![ParentFormName].PageA1.SubFormName!lst2.RowSource = "Query1"
... and :
Expand|Select|Wrap|Line Numbers
  1. [ParentFormName]!PageA1.SubFormName.lst2.Requery
These are just less complex versions of the same references to be fair, but why not use less complex if you can right.
Aug 27 '11 #8
Now I have a problem to synchronise parent and sub forms.

Say in parent form I have a combo box that when a value: a person is selected, a list box on the same form will be populated with the bank names where this person holds account and address of the branch.
I have to use query to populate the list box also cos the bank name and address are held in 2 different tables.
A person can be connected to more than 1 banks and 1 bank can be connected to more than 1 persons.

When a row in list box is selected, the sub form initially should display the details of the account held in that branch that has a flag "main" selected. But when left/right arrow button is pushed, the sub form can display the details of other accounts held in the same branch.

On the recordsource of the parent form, I put the name of the table that connects person ID to account ID.
And on the recordsource of the subform, I put the name of the table that has details of each account ID.
A person can have more than 1 accounts, but only 1 account that is marked as "main".
So the main and child field is the account ID on both tables.

My question is how to synchronise the display of the parent and sub form. Somebody suggested using recordsetclone and bookmark, but I'm not familiar with this concept, so any tips are much appreciated.
Aug 29 '11 #9
32,173 Expert Mod 16PB
How can I follow what you mean if you don't even respond to what I post? It should be simple enough.

As I don't have enough information to follow to follow this tangent, I'll leave it to you to decide if this is a separate question or not. If so it needs to be moved into a new thread (which I'll happily do for you if required). One way or another though, I'm going to need you to respond to what I post if I'm to continue helping you.
Aug 29 '11 #10
What did you ask NeoPa? I thought I've responded to what you posted.
Actually this question is related cos say in my previous question, lst2 is on subform and lst1 is on the parent form.

So besides lst1, there's a combo box on parent form.
And besides lst2, there are text boxes on subform to display the details of a bank account based on what is selected on lst1.
Aug 29 '11 #11
32,173 Expert Mod 16PB
A response is not necessarily an answer to a question. You posted nothing to post #8 so I'm left guessing whether your new post is a clarification question of a follow-on question. If you'd posted to say whether or not you'd tried the suggestions and how well (or not) they worked, I'd have more clues available to determine what you're talking about in the new post.

Having taken the trouble now to decipher post #9 it seems clear it is the latter, so does indeed need to be moved to another thread. I will do this for you, but please try to understand the difference between a clarification of a single question and a follow-on question related to the same database as our rules make it quite clear that the latter is not allowed in the thread of the former (as they're different questions).
Aug 29 '11 #12
32,173 Expert Mod 16PB
New question moved across to its own thread (How to Synchronise Parent and Sub Forms).

Please reply here to confirm you've read all posts up to here then I will remove from #9 through #12 as they just make a mess of an otherwise usable thread. I will also remove that post when I do that as well as this part of this post (Leaving just the link through to the other thread).
Aug 29 '11 #13

Post your reply

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

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.