Creating a form with a list (1 Viewer)

[JudyHNM]

This seems like it should be simple, but I can't figure out how to do it. I have a database with a table that contains Id numbers of threads and the inventory on hand of each thread. I want to create a form that allows me to enter an id number and have it look up and display the inventory amount. I would need to add multiple id numbers and have a list on the screen. How do I do this?

 

[mike60smart]

Can you upload your database?

 

[Mike Krailo]

I would need to add multiple id numbers and have a list on the screen. How do I do this?

Could you create a listbox with a row source of the list of threads in your table? From there, you would click on whatever threads you want to include in your form data and have a button that would process the actively selected listbox with a little VBA. Hard to make exact recommendations without the tables and example output of what you wanted to see in the form after clicking the button. The VBA could generate a query that would end up being the rowsource for a continuous subform listing your required info on the threads.

 

[JudyHNM]

No, I can't upload the whole database because of other business info it contains. However, I have attach a database that contains the table that hs the data. Will that help?

 

[moke123]

Heres some stuff from Allen Browne http://allenbrowne.com/AppInventory.html

 

[JudyHNM]

Could you create a listbox with a row source of the list of threads in your table? From there, you would click on whatever threads you want to include in your form data and have a button that would process the actively selected listbox with a little VBA. Hard to make exact recommendations without the tables and example output of what you wanted to see in the form after clicking the button. The VBA could generate a query that would end up being the rowsource for a continuous subform listing your required info on the threads.

There are over 500 entries in the table that contains the threads. They are stored as text because not all of the id's are numbers, i.e., some have letters. I think it would be too cumbersome to go through that lengthy of a list to find an assortment of id's. The form I had in mind is multi column (sort of like an Excel table). Column is where I would enter the ID. Then, I would want the thread name in column 2 and the inventory amount in column 3. It would have to allow for multiple rows, as I would be searching for all the threads for a given project which could be anywhere from 10 to 75+.

 

[moke123]

heres some data models. look at the inventory section
http://www.databaseanswers.org/data_models/

They are stored as text because not all of the id's are numbers, i.e., some have letters.

That doesn't mean the primary key can't be numerical.

Sounds like you want a combination of cascading comboboxes and multiselect listboxes to wittle down your data.

Perhaps post you relationship diagram which may assists us in giving a more targeted solution.

 

[Mike Krailo]

It would have to allow for multiple rows, as I would be searching for all the threads for a given project which could be anywhere from 10 to 75+.

That suggests that the threads have already been entered under a certain project. In that case, no searching necessary as you should have a ProjID associated with all the thread in that project. It's fun playing the guessing game isn't it.

 

[JudyHNM]

heres some data models. look at the inventory section
http://www.databaseanswers.org/data_models/


That doesn't mean the primary key can't be numerical.

Sounds like you want a combination of cascading comboboxes and multiselect listboxes to wittle down your data.

Perhaps post you relationship diagram which may assists us in giving a more targeted solution.

Thanks, that's a wonderful source. There's no relationship diagram that applies for my database. This is one table that has the ID number, the thread name, and the inventory. This application is not as involved as a true inventory control database.

 

[JudyHNM]

That suggests that the threads have already been entered under a certain project. In that case, no searching necessary as you should have a ProjID associated with all the thread in that project. It's fun playing the guessing game isn't it.

Sorry, if I misled with my original question. I have one existing table that will be used that has the ID, the thread name, and inventory count and is loaded with data. The form I want is not going to update the database in anyway -- it's just pulling existing data of how much of each thread I have on hand for a project to see if I need to purchase more. So, I just want to enter the id and have "something" return the inventory amount and color name that's already stored.

 

[Mike Krailo]

Sorry, if I misled with my original question. I have one existing table that will be used that has the ID, the thread name, and inventory count and is loaded with data. The form I want is not going to update the database in anyway -- it's just pulling existing data of how much of each thread I have on hand for a project to see if I need to purchase more. So, I just want to enter the id and have "something" return the inventory amount and color name that's already stored.

Ah, so you don't really have a database application then. That's what you probably need though. What you are trying to do would be dead simple if you managed all of your data in a relational database. One table is not going to do it unless it was the table that contained the linking ProjID and Thread used in that project type of table. You don't have anything like that. Good luck.

 

[moke123]

With your current table you are still going to have 561 choices in a list or combobox. I note, for instance, that you have Greys and Grays, which makes it harder to do cascading combos or lists.

If you categorize your colors you could do something like this example. Not sure how you would want to categorize colors such as "blue green" and "Black Brown"

 

[JudyHNM]

Ah, so you don't really have a database application then. That's what you probably need though. What you are trying to do would be dead simple if you managed all of your data in a relational database. One table is not going to do it unless it was the table that contained the linking ProjID and Thread used in that project type of table. You don't have anything like that. Good luck.

This is just one table from a relational database, but I was trying to do something simple instead of tracking an entire project and the threads. I can certainly create that and read the thread table from that. I just wanted a quick list by providing numbers and looking up the count. Thanks for your input.

 

[JudyHNM]

With your current table you are still going to have 561 choices in a list or combobox. I note, for instance, that you have Greys and Grays, which makes it harder to do cascading combos or lists.

If you categorize your colors you could do something like this example. Not sure how you would want to categorize colors such as "blue green" and "Black Brown"

Thanks for your input. That gives me something to think about.

 

[Mike Krailo]

I think the multi-select listbox is the best option for what you are attempting to do without an associated ProjID. Make your selections and then simply filter the main form based on your selections. 500 items in a listbox is not that hard to navigate if it is all in alphabetical or numerical order. Now if you have to select 75 items, then that would be a pain. That is why it makes more sense to create a new table from your relational database via query that contains the ProjID and the Thread data. Then that would simplify everything. Does that make sense?

I just looked at what Moke123 posted and that is essentially what you wanted except instead of color categories, it would be based off of Projects. So you simply select the project and all the threads in that project would be shown in the listbox or filtered form.

 

[CJ_London]

I want to create a form that allows me to enter an id number and have it look up and display the inventory amount

I just wanted a quick list by providing numbers and looking up the count.

if you know the numbers you can filter the form based on those numbers. enter them in a textbox, separated with a comma then apply a filter in the textbox exit event. see attachment

total at the bottom of the form. You can also use the forms filtering options. for example, highlight the word 'salmon', right click and select all records that being with salmon. Or whatever you want

 

[JudyHNM]

I think the multi-select listbox is the best option for what you are attempting to do without an associated ProjID. Make your selections and then simply filter the main form based on your selections. 500 items in a listbox is not that hard to navigate if it is all in alphabetical or numerical order. Now if you have to select 75 items, then that would be a pain. That is why it makes more sense to create a new table from your relational database via query that contains the ProjID and the Thread data. Then that would simplify everything. Does that make sense?

Yes, it makes total sense, and I am mapping out how I want to do that now. I had thought there might be something quick available like Dlookup, etc. that exists in Excel. I appreciate your help in sorting through all this.

 

[JudyHNM]

if you know the numbers you can filter the form based on those numbers. enter them in a textbox, separated with a comma then apply a filter in the textbox exit event. see attachment

total at the bottom of the form. You can also use the forms filtering options. for example, highlight the word 'salmon', right click and select all records that being with salmon. Or whatever you want

Very clever. That's exactly what I need! Thanks.

 

[CJ_London]

you might be interested in this link

multi select form

Responding to a thread where I suggest using a multiselect listbox, I thought it would be useful to upload this simple example where you can turn a form into a multi select form. It only does a 'simple' version but with more work could probably also be extended to mimic the extended form. The...

www.access-programmers.co.uk

it builds your text string by clicking on a record - much like a multi value combo.

 

[Mike Krailo]

Yes CJ's idea works great if you know the exact numbers you are looking for. I just used a button instead of the OnExit as it is a little confusing how pressing Enter does not exit the text box and run the filter. Just a few small changes to it so it works more intuitively. Have fun with it.