Full Version: List Box updating a subform?
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
omicron
Hello, I'm looking to have a list box that stays in the same place on a form. When I choose an item in the list box I'd like it to update the sub form that I have. Basically for each item in the list I have made a form for it. When I choose the item I'd like it to update with the corresponding sub form.

I hope you get what I mean. If have a form with a window in it, when I choose what tool I want to look at, I would like that tool to pop up in the sub form. The tool has a heading and a table in it for people who checked out a tool.

Any help would be greatly appreciated, thank you very much.
loonytick11
I think by "window" you mean subform?

Use the OnUpdate event of the listbox to change the ControlSource of the subform like this:
Select Case MyListBox
Case "Tool1"
me!subform.controlsource="sbfTool1"
Case "Tool2"
me!subform.controlsource="sbfTool2"
Else
msgbox "Unknown Error"
End Select
omicron
The listbox is named...

Combo2

The unbound subform is...

subform

So if the text in the list box is...

8" Grinder
Ladders

and the subforms for each are named...
8in_grindersub
ladderssub

I would then name it...

Select Case MyListBox
Case "8" Grinder"
me!subform.controlsource="8in_grindersub"
Case "Ladders"
me!subform.controlsource="ladderssub"
Else
msgbox "Unknown Error"
End Select


I did try that, I couldn't get the 8" Grinder to work, I get a compile error every time I go to the next line.

Also I got an error about the else msgbox "unknown error" not having an IF statement.

Thanks for your help thus far.

Edited by: omicron on Wed Jul 19 14:16:51 EDT 2006.

Edited by: omicron on Wed Jul 19 14:17:54 EDT 2006.
omicron
I don't like to re-post on this, but I'm kinda losing placement in the forum, getting buried. I am still having a little trouble grasping what is going on with this. I have tried many different things with no luck as of yet. I am considering giving up on Access and moving on to excel though, lol.
omicron
Hey guys still having the same issues, can someone please help me understand the codes? Thank you.
jmcwk
Omnicron,

You say:

QUOTE
Hello, I'm looking to have a list box that stays in the same place on a form. When I choose an item in the list box I'd like it to update the sub form that I have. Basically for each item in the list I have made a form for it. When I choose the item I'd like it to update with the corresponding sub form.


Are you saying that for example you have 10 items in your listbox you have 10 forms for those items?

Can you post zipped copy of your db no larger than 500000kb?
omicron
Here's an example...

There's 2 tables
-------------------
110_welder
2wheel_carts

5 Forms
-----------
frm110_welder
frm2wheel_carts
MainForm
subform110_welder
subform2wheel_carts

What i'm looking to do is when I choose "welder" from the listbox, i'd like it to show the "frm110_welder" form. When I choose "2 wheel carts" i'd like it to show "frm2wheel_carts."
jmcwk
Why do you have 2 pair of the same form/s? Your DB is not structured properly and will only give you problems if not corrected now. If you want to seperate the Carts from the Welders you should have only one table

tblItems
ItemID
ItemName


Then build your form linking the ItemID Jobnumber

tblJobnumber
jobnumberID (Pk) Autonumber
Job


tblItems
ItemID (Pk) Autonumber
ItemName

TblJobDetail
DetailID (Pk) Autonumber
JobnumberID (Fk)
ItemID (Fk)
DateOut
DateIn
etc.
omicron
Alright, Thanks for the help. I had another thought, what if I were to use a setup like the one attached?

What I did, is I child and master linked the subform to the main Tool field in the only table on there. In the "All Tools" tab, it just gives a window to the main table. When you go to "Search" the subform is child&master linked to the "tool" field. If you choose a tool from the dropdown list, it updates that table and only shows the records with that specific tool shown.

What I wonder now, is can the top menu's other than just the Tool alone be used to look up the parts by typing the value in? Can it act like a live query, where when you enter in information it shows just that part. If I link the subform with any more fields than just tools, i'd have to have both of them filled in before it shows any info.

I am looking to making each one of them have the ability to show in the subform just what I put into the box. ex: I just want to see one person, and what tools he has, so I type in his name and it shows only his records.

I really do appreciate the help, if I can be of any assistance in return I won't hesitate to help.

Thank you.
jmcwk
I have not opened your attachment yet have some meetings coming up that will take most of my day but based on your post have you tried using the 3rd option in the Combo Box wizard based on the persons table?

I will check back in when I get the chance.
omicron
Hey,

I'm still here with the same problem, trust me I have been trying. This is what I have so far, you may get the just of it. Basicly, at the top of the form is a line of fields, I would like to input info into there and have it automaticly update the datasheet shown in the "all tools" tab. That data is coming from a query that just sorts the info.

In the "search" tab, the data shown there is coming right from the Inventory table. When you select a tool from the list it automaticly shows just the tools selected. I would like to be able to do that with the other criteria as well.

I could really use the help.

Thank you much,
Tim
jmcwk
Matt,

Surely your attachment is not ALL of the structure you are using in your Database. While I have not tried to build one I have read various posts in the past that an Inventory Type Database is very difficult to structure properly. Have you done any searches here in UA on Inventory type Databases? IF in fact that is the only table you plan to use you will have problems. Employees Table,Tools Table, at the very least will be needed I see no master child link/s in your forms.
omicron
Well, when it comes to proper table structure and relationship status, that's where I fail.

If you go into the form "inventory" and go to the tab in the footer called "search" and find the properties of the subform named "Inventorysubform" you will see that it has a child/master link to "tool."

Access comes with a standardized "inventory" database, but that it was beyond what is needed. I will keep searching elsewhere, but what you see is what I have so far. Granted I am only putting in maybe an hour each day into the creation of this, if not less.
jmcwk
Matt,

I am not critisizing just pointing out that IF you want your DB to function properly and be useful to you and the users you will need to first get the Structure Corrected THEN proceed from that and build you forms, queries etc. in it's current state none of the above is feasible. While it will not be easy it will be well worth your effort. You said Access comes with a standardized inventory Database if it is to complex or envolved to meet your needs take what you can from it (structure etc.) and build from there. Sorry I can not help you more than the advice I have given.

Good Luck With IT! sad.gif
jmcwk
Matt,

Here is a Model on Inventory that may help you.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.