Few listbox problems...

[morglum007]

Few listbox problems... [1st issue solved]

Good day,

First of all I'd like to sorry for my english speech. I am not english native, so english is not my native language.

Then, lets introduce my problem, and lets see if someone here can point me out with some guide/help or whatever.

First, lets say I have read almost everything about data base normalization, etc etc, and I have tried to create a big (and easy one) for the company I work for. To this point, I am not IT engineer, but nuclear one, so if I miss something, please, consider I am not especialized in this.

To general knowledge, data base is 41 data table large (I already know Access limitation to 25 "one to all" table relationship limitation) and some of them got about 190+ fields. (I tell this because someone could say here it is too large and normalization issues...) In any case, some of this tables correspond to fabrication components, costs, materials and technical characteristics, so there is no way to further normalization in the time given to this project.

At this point I am finishing a search form. I have three combo boxes, first of all select table, second one field in selected table, and third one discriminates value in such field to look for. Results are shown in a listbox, and to this point is where I got in troubles.

The problem I have got are two here:

1.- As you may suppose, listboxes columns are limited up to 20 in listbox, so even selecting correct field count, most fields doesnt appears. I have tried to insert an horizontal scroll bar, but with no luck. In any case this is not my main problem, I think I can deal with it. The problem is that some tables are not shown (even having less than 20 fields) and I dont really know why this happens. Some tables appears with no problem, some doesnt.

2.- Regarding to 1.- I think it is a column width problem, and access bug related issue, so I am looking for some code in vba to do this job. I have already looked at http://www.lebans.com/autocolumnwidth.htm with no luck.

If someone have some ideas about (for example taking origin's table columns width and exporting it to listbox), I would be eternally thanked.

Some suggestions already wanted.

TIP: Please, I already know too this data base is too large for Access, and I should use SQL or better. As I began this project in Excel, consider that now I am working in access, so learn curve is big, but not so big to migrate now to SQL.

Thank you so much for reading at least that.

Best regards

 

[Galaxiom]

With 41 tables, some with 190 fields I can promise you that you have not structured your database correctly. Tell us about your project and we can suggest a better structure.

 

[morglum007]

Well, thank you for your reply,

Well, lets see, the project's target is to do a data base for every subcomponent in a fabrication process. This includes engines, turbines, pumps, valves, instruments, materials, and so on. Every previous "subcomponent" relies on another superior subcomponent, and all of them got more subcomponents, so it is not practical to build different tables for each subcomponent.

For example:

A pump have different drive engines, that could be electrical or just mechanical (electrical motor or just a turbine), and depending on pump's size, engine characteristics are completelly different to upper power pumps, so it is not practical to create a table with engines, because I should create at least 10 tables for drive engines depending on the main component they belongs to. Now, apply this to every component.
I have tried to short at the maximum the amount of data tables we got, and still 41, so imagine if I upgrade this to more (maybe more than 100 tables will be created). When doing searchs, coding will be awfull if I have to include all data tables relationships, not including the adding data subforms.

As you may figure out we are in a hurry, so I must do as quick as possible. It is not intended to create a magic data base, just an operating one.

I know 190+ fields is too much, but no way if I wanted to do this easier to end user, especially considering more ppl will have access to this and have little or no knowledge about access.

This is why I am looking for help in doing this with listbox (if possible).

Thanks again

 

[Galaxiom]

You have built a giant spreadsheet. Databases are different. You only need two tables.

Make an inventory table that lists every basic component. Include an ID field which will be used to refer to it from the other table. Add fields for any other property you need to record about the component.

Every component is part of another part. However a group of these parts that make a small part is no different from the large assemblies in the finished product.

Into the same table, add every subassembly that can be made, allocating them a part number just like the components. Include a field in the table to identify that these are constructed parts rather than basic components.

Make a Kit table. Include a field for the key from the inventory table. Each assembled part will have a record for each inventory item used to directly build it. A field will be included for the required quantity of parts. In turn, each subassembly will have its own set of kit table records.

To return the components required for any part, you query the kit table records by the inventory key of the finished part.

A report can easily be prepared to list every component in the finished product in the groups matching its assembly.

 

[morglum007]

Well, thanks again for the reply, it seems I havent explained myself at all.

Ehm.... lets see... I got 41 tables, ok, everyone with one component, i think this is clear. I will try to show you with little example:

Just one LITTLE table, named "Anodes", with 11 fields: JOB #, Position, ORDER #, Anodes Type, Anodes #, Anodes manufacturer, Cost, Cost per unit, Additional costs and Date

Another bigger table, named "Valves", with 89 fields: JOB #, Position, ORDER #, tag, Quantity, Activator, Activator model, Valve's function, Main unit, Main subcomponent, Secondary subcomponent, Subcomponent position, Design pressure, set pressure, Counterpressure, Cleaningless factor, Design maximum temp, Design nominal temp, Design maximum flow, Design flow, Discharge coefficient, Selected area, calculated area, Weight, relative position, Full vacuum capability, medium, Valve's type, Valve's size, valve's rating, Action, main chacrateristics, Fail position, ATEX, Electrical class, opening control signal, closing control signal, KVS, Water seal................

As you may suppose, every field have its own data for its specific valve, and here it is included all valve's types, normal, electropneumatic, safety.... All of them have common properties, but some of them are specific. I could create different data tables for each valve's type, but this will create duplication of many data in such tables, so relationship could be awful.

The same is applied to the rest of components.

What we want to do is, for example, search in valves all of those that are 4" in size, because I want to see its material for purchasing department. Everyone will do its search to look for different data, so thats why I need to show first preliminar results. Later on, everyone will search for another parameter, and thats why I need to be shown complete results in listbox

This is not an inventory as you says. Here every part have its own data/physical properties, and all of them must be searchable by some parameter.

Maybe I forgot to mention i am covering about 60 jobs, 1 million € or so each, in which even a nut has its properties and position, and have to be registered. If a nut costs less than 0.30 €... well, imagine the huge amount of data I am covering. It is definetelly a database, and lasted about 3 months to complete just the tables. Now I am in the coding process.

Therefore, imagine I cannot do substantial changes in database right now...

Thank you!

(Regarding the listbox issue: no way??)

 

[Galaxiom]

Thanks for the perspective. Essentially it is still an inventory database. It is just that the number of values required for each item is quite extraordinary. I would still recommend a different structure. Your database is a huge project and you need powerful structure to deal with it. Continuing on with so many tables and fields that are already a struggle will just lead you further into misery.

The problems you are having with the listbox are due to the structure. Also consider the problem if another property of an item needs to be added. You will be adding fields to the tables, controls to forms and reports and changing row sources on listboxes and combos.

Dealing with databases having large numbers of fields is always problematic. Their developers start looking for ways to code field and table names as variables and this leads to complex code. Databases work much better with large numbers of records than they do with large numbers of fields not only for processing queries but for building them.

Before you reiterate that it is already too late for this, please humor me for a while. The data is already held in a complete and highly structured state. This task is not wasted. With a handful of queries and some deft code this data could be entirely restructured in a way that will enormously simplify the demands of the interface you are creating to interact with it.

I understand the reasons for making tables based on particular components because they share common properties. Valves have pressures, anodes have other properties. But there is another way to deal with vast numbers of unrelated properties of unrelated objects.

With so many fields for different aspects you face a monumnetal task of maintaining multiple custom forms for different sections of the organisation. With a different structure you can easily build an interface that works everywhere yet has all the advantage of custom forms.

All the parts have some properties in common. PartName, Job#, Position etc. Move all this common information into one Inventory table with those fields. Include a field for inventory type which was previously the name of its table.

The left over field should be converted to a table with these property names as values in records rather than fields. Include a primary key. Also include a field with an index number for the type of value. For example all pressures should have the same secondary index number which would be the foreign key of an entry called Pressure in another table of property groups.

Include a field with the foreign key to the inventory table. Another field is the foreign key to the property table and a third is the value itself. So for example we know that the value is the Operating Pressure of a particular key in the inventory.

You can also include a fourth field which is an optional measurement unit. Once again this is recorded as a foreign key to a table of units.

On the form, not only the value is drawn from the tables but the name of the property and its units. The dynamic labels can be achieved by using textboxes as labels. When locked and set transparent background and border they look just like labels anyway but they get their caption from a table.

Or simply include the special properties in either a datasheet or continuous subform. Include a search function to find the record by property name.

To customise the form to a particular aspect of the project set up a link table between your table of custom form specifications and the special property type records.

With this system you can create new custom interfaces at a user level by designing the forms to allow them to select the property to be shown in a particular box on their interface. You allow interfaces to be saved copied and edited by authorised users.

The properties would be selected at each textbox from a series of comboboxes which work down the tree of secondary indexes as described above. You can split these down into groups as much as you wish. The row sources can also be set by nominating the department so they get their default set of properties automatically when designing a form.

Perhaps it is too late for now to change the structure. However I would recommend you live with the shorcomings of the original structure for now but don't put any more effort into it. Start work on restructuring for the next version so that you can build a more workable interface.

 

[morglum007]

Okay, thank you so much. I will try to do some job with large fields tables in order to avoid listbox problems.

The problem we got is that db is not an eternal one, because we are migrating to "SAP" (we have a time dead line for access db). We don't have lot of time to re-do the whole thing, so I will try to save some efforts by editing some tables.

As I see I have no choice but to capitulate with listbox. I will keep trying anyway with listbox.

TIP: most fields are "final" so no more "add-ins" are expected to be added.

Again, thank you for your point of view. I really appreciate that.

Best regards

 

[morglum007]

Hello there again

Finally I have solved the blank table showing by adding some brackets in SELECT statement. It was quite stupid, but sometimes stupid things makes things not to work correctly (Forgot that tables names are variables, and they got spaces in it, so if you want to show all, you need to use the [& whatever &])

At the end, got all tables correctly showing, but still got the horizontal scroll problem. Still on it.

So, to those who want to do specific searchs in listboxes, here is the code:

1.- Listbox showing results (as a dynamic table), formerly named "searchlist".

2.- Table select combobox (named in my case "elementscb")
Row type: Table/query
Row origin: SELECT MSysObjects.Name FROM MSysObjects WHERE (((Left([Name],4))<>"MSys") AND ((MSysObjects.Type)=1)) OR (((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~")) ORDER BY MSysObjects.Name;

VBA code:

Private Sub elementscb_AfterUpdate()
Me![fieldcb].RowSource = Me![elementscb]
columnnumber = CurrentDb.TableDefs(elementscb).fields.Count
columntb.Value = columnnumber
If columnnumber <= 20 Then
Searchlist.ColumnCount = columnnumber
Searchlist.ColumnWidths = 2
Else
Searchlist.ColumnCount = 20
Searchlist.ColumnWidths = 2
End If
Me.Searchlist.ColumnHeads = True
Me.Searchlist.RowSource = "SELECT " & elementscb & ".* FROM [" & elementscb & "]"
TempVars("elementscb").Value = elementscb.Text
If elementscb = "Users" Then
Searchlist.Visible = False
MsgBox "Sorry, this is a restricted area!"
Else
Searchlist.Visible = True
End If
End Sub

3.- Field select combobox (named in my case "fieldscb")
Row type: Field list
Row origin: VBA

VBA code:

Private Sub fieldcb_AfterUpdate()
Me.finalchoosecb.RowSource = "SELECT DISTINCT [" & fieldcb & "] FROM [" & elementscb & "]"
TempVars("fieldcb").Value = fieldcb.Text
End Sub

4.- Search parameter combobox (named in my case "finalchoosecb")
Row type: Table/query
Row origin: VBA

VBA code:

Private Sub finalchoosecb_AfterUpdate()
finalchoosecb = finalchoosecb.Text
Me.Searchlist.RowSource = "SELECT " & elementscb & ".* FROM [" & elementscb & "] WHERE [" & fieldcb & "] = finalchoosecb"
TempVars("finalchoosecb").Value = finalchoosecb.Text
End sub

This will do the job. Still on the listbox problem showing more than 20 fields.

Thank you again for your patience.

TIP: Table "Users" wont be shown in general. Thats something I m working on yet.
As you may see, I am also using form variables (for user & pass restriction beyond other purposes)

Best regards,

Morglum007