Display fields after listbox selection

[laavista]

I have been working on this problem for 3 days, and can't figure out what to do. I'm using Access 2010.



I have two tables. Tier1 and Tier2.
Table: Tier1 has Tier1ID (autonumber) and Tier1Desc
Table: Tier2 has
-Tier2id (autonumber)
-Tier1ID (which is the link back to the Tier1 table),
-Tier2Num -- this number shows which tier2 records are associated with each other
- and about 6 fields with descriptive info, 2 of which are memo fields


I have a form that has 2 listboxes:
- Listbox 1 has the row source to Tier1 table, control source Tier1ID
- Listbox 2 is an unbound listbox with the row source to a query.
The query is all the fields from Tier2 with the criteria where Tier1ID in Tier2 table = Tier1ID in Tier1 table
This all works. When I select something from the Tier1 ListBox, it displays the associated tier2 items for the tier1 selection.

What I need help with:
When the selection is made in the 2nd listbox, I need the other 6 fields in tier2 table to be displayed based on the "Tier2Num". The listbox does return Tier2Num correctly.

I just need to display all the rest of the fields.

- I tried a popup form based on a query, but can't seem to pass the tier2Num to a form. (The query works if you enter the Tier2Num, which is the
[listitem] selection)
- I tried dlookup in a textbox
- I tried to add all the fields into the listbox, but could only get 3 to display (then I was going to do textboxes with the control(#) in it

Thanks in advance.

[June7]

Should be able to display more than 3 columns in a listbox. Why can't you? What are the ColumnCount and ColumnWidths properties set to?

Passing value to a form is possible - what did you try?

Expression in textbox can reference column index of combobox or listbox. Index begins with 0.


[listboxname].[Column](1)

[laavista]

ColumnCount = 9
ColumnWidths = 0, 1, 1, 1, 1, 1, 1, 1, 1
I also tried adding one more to the columnwidths as I read that somewhere: 0, 1, 1, 1, 1, 1, 1, 1, 1, 1
Also tried 1,2,2,2,2,2,2,2,2

Tried textbox: =[lstItem].column(4) (also tried all the numbers). 1st 3 worked; rest did not

Thanks for looking at this.

[June7]

That certainly is odd. If you want to provide db for analysis, follow instructions at bottom of my post.

[laavista]

I will prepare the database and attach it, but I will not be able to get it done until tomorrow (Thursday). Thanks again for taking the time to look at my post.

[laavista]

The intent of the database is to provide information for each tier.

Listbox #1 lists the top tier info

Listbox #2 is a ‘cascaded’ list.
When the user selects one of the records in listbox #1,
the records associated with the Tier1 record is listed in ListBox #2

When a record is selected in listbox #2, then I need all the fields in tblTier2 (except for the IDs) to be displayed.

I was thinking that textboxes would be the best way to go as they could be placed underneath the listboxes on the form.

Listbox #2 is not displaying all the fields—I’m not sure why.
I think this is the problem and why the textboxes I tried are not working.

To test:
On the "MainForm", click on "job submission" in the 1st listbox, then click "submission tracking history" on the 2 listbox.
The tier1id for this = 3
The tier2Num (linking all the tier2 records together) = 39

Hope this makes sense.
Anything you could do to help would be so very appreciated.

(This is my first time uploading a zipped database--would you let me know if you could view it?)

[laavista]

The more I look at this, the more I think it might be how the structure is layed out. I don't know how to fix this, though.....

[laavista]

I found the problem. It was an incorrect db structure. Thanks again for looking at this.