Listbox Question

[Marinus]

Hi Guys,

Sorry to pop this question but I don't seem to be able to find the answer on-line. Lack of knowledge..

On my form I have a listbox125 that has a list of materials, it works well and contains different materials that are collected from table materials which contains material, buyprice, saleprice and category.

I would like to have this listbox list the category and when selected fold out to show the materials in that category and write the selected value to my table docket.

example;

Copper opens Element
Pipe
Tank

Steel opens Heavy
Light
Mixed

etc..

I looked at the cascading listboxes but could not make any sense of it in my situation, any help as always much appreciated..

 

[hk1]

You could do this with cascading combos. But what you are really looking for is the treeview control which is not native to access, the way I understand. I think this is an ActiveX control that comes with Visual Basic 6. If I err not, your best bet to obtain it legally is to install someone's demo software that includes this control.

http://mymsaccessblog.blogspot.com/2008/02/using-treeview-control-as-record.html

 

[hk1]

BTW, if you want to try cascading combo boxes, take a look here:
http://mymsaccessblog.blogspot.com/2009/04/access-2007-sample-database.html

 

[Marinus]

BTW, if you want to try cascading combo boxes, take a look here:
http://mymsaccessblog.blogspot.com/2009/04/access-2007-sample-database.html

Thanks HK1,

My problem is the fact that I work with a Touchscreen and combo boxes are not easily selected on this screen, as there are no mouse and keyboard attached it is harder to scroll through the List/Combo boxes.

Maybe my way to go is to have two list boxes, the first with the category and the second with the materials. When the category is selected, make category listbox invisible and then make the a new listbox with the materials in that category visible. Puzzle time so to speak..

With thanks to all who have helped me here I do attach a copy of the application to show it's progress and maybe someone can find something in it to use...

Needless to say, I need to clean it up a bit..

 

[hk1]

I recommend you use only buttons for input on a touchscreen. If you can make something else work for you, more power to you. I just don't think anything but buttons are good for touch screens.

I designed a timeclock system where the user selects a work order and then all of the "work types" (different categories of work) are displayed for that work order using the same set of 20 buttons. It's quite code intensive and the code is pretty hard to read but I think this type of design is the best solution for what you are trying to do. If you need hundreds of top level categories/items and then hundreds of subcategories/items it won't work. In my case twenty work orders and twenty work types for each work order was enough.

 

[Marinus]

I recommend you use only buttons for input on a touchscreen. If you can make something else work for you, more power to you. I just don't think anything but buttons are good for touch screens.

I designed a timeclock system where the user selects a work order and then all of the "work types" (different categories of work) are displayed for that work order using the same set of 20 buttons. It's quite code intensive and the code is pretty hard to read but I think this type of design is the best solution for what you are trying to do. If you need hundreds of top level categories/items and then hundreds of subcategories/items it won't work. In my case twenty work orders and twenty work types for each work order was enough.

Thanks for your advise HK1, it is worth considering, could you show me a sample of what you made?? That would certainly help me...

 

[DCrake]

If you have a material that has more than one option you could get it to open a popup form with a series of buttons on it, let's say 20 and populate the captions of the buttons with the options for that material. When you run out of options make the remaining buttons invisible. Don't forget to have one for Cancel.

When the user clicks on the button it responds to the caption on the button to decide the options chosen. At the same time as you are populating the buton captions you can also populate the button tag property with the FK of the option.

It's a bit like whn you go shopping and the cashier clicks on Cream Cake from the menu and a popup appears with pictures of cream cakes. Thne they simply click on the desired cake and it is sent to the EPOS.

 

[hk1]

The best I can offer right now is a screenshot. It would probably take me a full 8 hour day of work to strip this thing down to a database that could be posted here as an example. Sorry I can't offer more. Maybe someone else has a database/code example how to do this.

 

[Marinus]

If you have a material that has more than one option you could get it to open a popup form with a series of buttons on it, let's say 20 and populate the captions of the buttons with the options for that material. When you run out of options make the remaining buttons invisible. Don't forget to have one for Cancel.

When the user clicks on the button it responds to the caption on the button to decide the options chosen. At the same time as you are populating the buton captions you can also populate the button tag property with the FK of the option.

It's a bit like whn you go shopping and the cashier clicks on Cream Cake from the menu and a popup appears with pictures of cream cakes. Thne they simply click on the desired cake and it is sent to the EPOS.

Thanks David, I know the principle, in the supermaket every day but wouldn't have a clue how to program that, will try to find more on that on this forum..

 

[Marinus]

The best I can offer right now is a screenshot. It would probably take me a full 8 hour day of work to strip this thing down to a database that could be posted here as an example. Sorry I can't offer more. Maybe someone else has a database/code example how to do this.

Thanks HK1, will have a look around and see, there maybe something out there..

 

[DCrake]

Take a look at this very simple demo of what you need.

 

[Marinus]

Take a look at this very simple demo of what you need.

Great David, With this I should be able to make a meal for the app, have to feed the kid first but start trying tonight.. Keep you posted..

 

[Marinus]

Take a look at this very simple demo of what you need.

Had a great time moving this in my app, it works to the point where the data has to be written in the table, I can't figure out how to get the Value TmpStr in my table Docket!Materials.

Private Sub SelectMe(Ctl As String)
Dim TmpStr As String
TmpStr = Me(Ctl).Caption
Forms("Touchscreen")("TxtType") = TmpStr
DoCmd.Close
End Sub

This is when on the form Touchscreen a category is selected and then a material from frmMaterial

 

[DCrake]

Do you have a field in your table that txtType is saved to?

 

[Marinus]

Do you have a field in your table that txtType is saved to?

I have a table docket which contains the field materials, I want to count stock based on category so I think I have to change the original table not to contain materials, instead I am thinking I have to add CatID and MatID in the docket table and set up relationship between the 3 tables.. I have to admit I am a little at loss here.. still plenty to learn..

 

[DCrake]

If you are intending to have more than one matierial type per docket then you are going to nd to go down that route. However if there is only going to be one then simply ad the two id's to the docket table.

 

[Marinus]

If you are intending to have more than one matierial type per docket then you are going to nd to go down that route. However if there is only going to be one then simply ad the two id's to the docket table.

As my Grandmother said, why make life easy if you can make it complicated. 95% of the dockets only have one material, however there are exceptions possible, with more materials per load. So I guess I better take the more difficult route.. I already put a MatID and CatID in the Docket table and established relationship Docket!MatID on to many to TblMaterials!ID also Docket!CatID one to many to TblCategories!CatID

As my programming skills are minimal, a hint where to go next would be most welcome..

 

[DCrake]

The one big problem you are going to have is when you have more than one matierial. As the popup only works on one at a time selection.

If you change your txtType control into a combo box, which in effect is the simulation of the child docket table, each time you select an item from the popup is uses the .AddNew to the combo. Then when you save the record you update the table accordingly.

 

[Marinus]

The one big problem you are going to have is when you have more than one matierial. As the popup only works on one at a time selection.

If you change your txtType control into a combo box, which in effect is the simulation of the child docket table, each time you select an item from the popup is uses the .AddNew to the combo. Then when you save the record you update the table accordingly.

David your solution works perfectly for me, as I will only choose one material at the time. My multi material problem I have to sort on docket level as each time a new load is taken in or out, there is also going to be a weight in and out..

For now I messed up completely as nothing works any more.. so have to step back a bit to older version..

 

[DCrake]

Just as well I have modified my previous sample. This takes into account multiple selections. Take a look at it, notice I have changed the structure of the TblMaterials table.

Choose Docket No 83 when you open up form 2