Solved Combobox Row Source as the Form's Bound Table, Joined to Itself (1 Viewer)

[B99]

Hi all,

Apologies for the confusing title; I wasn't sure how to explain it succinctly. Here's what I want to do: I have a table with 3 (relevant) fields - Name, Number and ParentNumber, where ParentNumber is a value of Number on another row of the same table, and I want a form to show the values of the Name, Number and name of the parent. In order to do that, I need to join the table to itself to get the Name of the Parentumber from the child row. For example:

SELECT c.Name, c.Number, p.Name
FROM MyTable c LEFT JOIN MyTable p
ON c.ParentNumber = p.Number

I've seen this in various applications for a chart of accounts listing where you might have the following data:
Account Number, Account Name, Parent Number
100, Assets, <null>
110, Cash, 100
200, Liabilities, <null>
210, Accounts Payable, 200

This is simplified and only one child level but there could be many child levels. On the form, I want to display "110, Cash, Assets".

Right now I have a continuous form for data entry/update with text boxes for Number and Name and an unbound combobox for the Parent Name. I want the user to be able to enter a new Account Number, a new Account Name and select the parent (if applicable) from the combobox dropdown. That part works if I make the row source a query against the underlying table. However it doesn't pull back the existing values since it is unbound. If I bind it to the parent number field, it of course shows the parent number, but I want it to show the parent name.

I was hoping it would be along the lines of:

SELECT p.Name
FROM MyTable p
WHERE p.Number = [Forms]![MyForm]![cboParentNumber]

But that didn't work and even if it did, it wouldn't help for new records.

Also - I know there are other, arguably better, ways to design this using a separate join table but I'm curious how to make this work. Any ideas?

-Brian

 

[theDBguy]

Hi Brian. You should be able to bind the combobox to the parent field but make sure its bound column is set to the actual value for that field. For example, if the parent field is a number, make sure the combobox is bound to a number column. Then, just adjust its Column Widths property to hide the numeric column and display the name column.

 

[B99]

Hi theDBguy, I don't quite understand. If I bind the combobox to the ParentNumber field (a number), then it shows the number value of the parent. So, using the chart of accounts example, the form would display values of 110, Cash, 100 for Number, Name, ParentNumber. But I want it to show 110, Cash, Assets where "Assets" is the name of the parent, not the ID. Somehow it has to take the value of the ParentNumber field on the child row and lookup the Name from the same table for the parent row.

 

[Ranman256]

make a 'report' table to put the results in,
you need a vb loop to cycle thru the records until all parents are filled.
you need a query to see if there are any missing parents not in the rpt table: qsAnyMissing

1. add current record to tRpt
2. use VB to loop to add recs if parent<>null and not in tRpt table
while dCount(qsAnyMissing)>0
run append parents query: qaAddParents ,to add records to tRpt where not Null([parent])
wend

when no more parents , the append ends and user can view all related records

 

[B99]

Hi Ranman256, I think I understand what you are saying but I'm a little confused on the 'report' table concept. Would that also allow entry of new records?

 

[theDBguy]

Hi theDBguy, I don't quite understand. If I bind the combobox to the ParentNumber field (a number), then it shows the number value of the parent. So, using the chart of accounts example, the form would display values of 110, Cash, 100 for Number, Name, ParentNumber. But I want it to show 110, Cash, Assets where "Assets" is the name of the parent, not the ID. Somehow it has to take the value of the ParentNumber field on the child row and lookup the Name from the same table for the parent row.

Hi. Don't worry, this is really easy. Can you post a sample copy of your db?

 

[B99]

Thanks theDBguy; I'm hoping it is easy and I've just missed it somewhere. Attached is a quick example. In the form, I want to show the name of the parent, instead of the ID.

I have to step away for a while but will check this when I get back. Thanks for your help!

 

[theDBguy]

Thanks theDBguy; I'm hoping it is easy and I've just missed it somewhere. Attached is a quick example. In the form, I want to show the name of the parent, instead of the ID.

I have to step away for a while but will check this when I get back. Thanks for your help!

Hi. Thanks. Okay, check this out and let us know if this is what you mean.

 

[B99]

That's it! I made one small change to the row source to select the account number instead of the autonumber ID and it works exactly like I wanted. I thought I tried that but I must have been trying to make it more complex than it is.

Thanks theDBguy!!

 

[theDBguy]

That's it! I made one small change to the row source to select the account number instead of the autonumber ID and it works exactly like I wanted. I thought I tried that but I must have been trying to make it more complex than it is.

Thanks theDBguy!!

Hi. You're welcome. Glad to hear you got it sorted out. Good luck with your project.