473,320 Members | 1,612 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 7608
NeoPa
32,556 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
NeoPa
32,556 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
NeoPa
32,556 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
NeoPa
32,556 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
NeoPa
32,556 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
NeoPa
32,556 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
NeoPa
32,556 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

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

Similar topics

25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
4
by: Gerry Abbott | last post by:
Hi all, A subForm control, txtTotalSubForm contains a total from one of the fields on this subForm . The mainForm contains a combo, myCombo which filters, and updates the details on the subForm. I...
13
by: bitsnbytes64 | last post by:
Hi, I have a form which contains a subform. Both are were creetd using the form wizard and are bound by the column IXO_NR (on two different tables), which is the control source for a textbox on...
7
by: dscarbor | last post by:
I have a simple form with 4 fields, and a subform that retrieves records that are potential matches based on a query. The user will use the ID from the subform record and enter it into one of the...
6
by: solom190 | last post by:
Okay this is the situation I have two forms and they have a 1:M relationship. I don't have enough space screen-wise to do a traditional "drag form to form" to create a subform so what I did was...
4
Haitashi
by: Haitashi | last post by:
Snippet: <form> <select name="secCode" id="secCode"> <cfloop query="Request.qSecCodes"> <option value="#Request.qSecCodes.org_name#" <cfif (Request.qSecCodes.org_code EQ...
5
by: suexicano | last post by:
Hi - I call a method (method is within the parent form) in the child form right before I close the child form. looks something like this: m_parent.GetOrderDetailsFunc(); ...
1
Jerry Maiapu
by: Jerry Maiapu | last post by:
Hi Please help. How can I delete i single record in a datsheet subform from main form using a delete button on the parent form.? i will be more grateful for your help... jm
5
by: hbaf208 | last post by:
I have a combobox on a subform that is based on an SQL that uses a listbox on the unbound parent form as the criteria. When the form is first loaded, it works perfectly, limiting the dropdowns to...
2
by: Parmenides | last post by:
I get the "this record has been changed by another user since you started editing it" message when I first make an edit in a subform then click on anything in the parent area. It won't give me the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.