PDA

View Full Version : Using ADO with Excel



Stefano
03-08-2016, 02:54 AM
Goodmorning everyone,
my first post on this awesome forum, and yet a question for you experts :banghead:
First of all, I'm not a programmer nor a coder, of any type... but I'm playing with VBA from some months.

So, the problem: in our company we need a practical way to manage components database, and thus to compile data sheets with them.
All the job has been manually done for years, one excel sheet per component (~ 600), and data sheets compiled manually row by row, component by component.

Now I decided to optimise this tedious and unsafe method:

I put all the components in ONE file organised like a database (from now let's call it DATA_SOURCE)
I created a model of data sheet (from now let's call it COMPILED) to be populated with data from DATA_SOURCE

Obviously every data taken from DATA_SOURCE have to be refreshable, in order to keep always everything up to date.

And here the problems.... as not a programmer, I probably miss the basis of everything about it!

What I have so far:

DATA_SOURCE, an xlsm file with 20 sheets (one for each component type), and every sheet has a table and a named range (to sort from)
COMPILED, another xlsm file, with a custom form to choose components from


Now, in COMPILED I've set up an user form with some combo box to select components
User form connect to DATA_SOURCE via this string:

cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & matWb & ";" & _
"Extended Properties=" & Chr(34) & "Excel 12.0;IMEX=1;HDR=YES;ReadOnly=1;" & Chr(34)

and with recordsets and some other code enables other combo boxes etc....
It works quite fine... but is this the right way??

Then, when I've selected my component I will press the "OK" button to write data on my sheet, but I cannot do this with previos connection, and I have to set another one with this code:

Private Sub BTN_ok_Click()
closeRS
closeRS2
sConn = "ODBC;DSN=Excel Files;DBQ=" & matWb & ";"
Set oQt = ActiveSheet.QueryTables.Add(sConn, ActiveCell, strSQL)
With oQt
.FieldNames = False
.HasAutoFormat = False
.AdjustColumnWidth = False
.RefreshOnFileOpen = falso
.BackgroundQuery = False
End With
oQt.Refresh
End Sub

Another time... is this the right way to do this??
Selected data populate the file.. every time I insert a component excel creates a new connection (and I think it is right, because each component point to a different sheet/row in DATA_SOURCE)
But when I refresh connections.... a lot of data disapper! :crying:


All the code is taken from various sites and adapted to my needs, but I'm not sure I've done everything well... (in fact I'm sure something is wrong!)

Can you please help me?




I do not know if I explained it well... in any case I will attach a pair of pictures, but if somebody will take care of it I will attach the entire code and example files too.



15586
15587

Thanks!

Stefano
03-10-2016, 12:24 AM
so, has anybody any idea how to solve this problem?

:help

Aussiebear
03-10-2016, 01:08 AM
Actually, its hard to test any code just by looking at the pictures. You no doubt mean well in presenting pictures, but I'm sure most people would rather a sample workbook to play around with. Are youable to provide one?

Stefano
03-10-2016, 01:26 AM
I'm preparing them!
Thanks!

Stefano
03-10-2016, 02:21 AM
Ok, here attached the 2 files to test, DATA_SOURCE and COMPILER (put them in same folder).
15602
15603


If you need additional information ask me, and if someone will find a solution he will become my new hero!


Tahnks!

Stefano
03-13-2016, 04:06 PM
No ideas?
:beg::beg:

SamT
03-13-2016, 10:54 PM
But when I refresh connections.... a lot of data disappear!
What is supposed to happen?

To help us get a better idea of the feel of the Project, speaking as an office manager to a new employee, tell us why, when and how to use the Form.

Please don't speak programmer to programmer, just boss to worker.

lookin over the code in Compiled I see that you are addressing ActiveCell

Private Sub BTN_RECSET_Click()
closeRS
closeRS2
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Do While Not rs.EOF
ActiveCell.Select
Selection = rs.Fields(0)
ActiveCell.Offset(0, 1).Select
Selection = rs.Fields(1)
rs.MoveNext
Loop
LabVerClear
End Sub

I think you must use a different way if you want to append data to the table

Private Sub BTN_RECSET_Click()
Dim NextRecord As Range

closeRS
closeRS2
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

If Not rs Is Nothing Then
Set NextRecord = Sheets("Foglio1").Cells(Rows.Count. "C").End(xlUp).Offset(1, 0)

Do While Not rs.EOF
NextRecord = rs.Fields(0)
NextRecordOffset(0, 1). = rs.Fields(1)
Set NextRecord = NextRecord.Offset(1, 0)
rs.MoveNext
Loop
Else
MsgBox "The Recordset " & strSQL & " was not opened."
End If
LabVerClear
End Sub


This code has a VBA spelling error

Private Sub BTN_ODBC_Click()
closeRS
closeRS2
sConn = "ODBC;DSN=Excel Files;DBQ=" & matWb & ";"
Set oQt = ActiveSheet.QueryTables.Add(sConn, ActiveCell, strSQL)
With oQt
.FieldNames = False
.HasAutoFormat = False
.AdjustColumnWidth = False
.RefreshOnFileOpen = falso '<------------------ "False"
.BackgroundQuery = False
End With
oQt.Refresh
End SubYou can avoid such errors by placing "Option Explicit" at the top of code pages. However once you do that, you will have to make many other corrections to the code. Use Debug >> Compile to find all the required corrections without running the Forms.

There are some things I can't help with because I am using Excel 2002 and they are not available to me.

Stefano
03-14-2016, 02:01 AM
Hi SamT, and thankyou for your answer.
Probably everything is so clear in my head that I've not been able to explain well :)

Step by step:

now in the office we have an only file for components, and we can use it like a database (yes, we know Excel is not a database, but we already know we can use it in this way for small projects)
we need to build, or maybe "compile", technical data sheets for our products
so we open our COMPILER file, and we discover it has a FORM inside
now with this form we should be able to fill the data sheet, component by component
so, we start searching for the first component, selecting field by field in the USERFORM, and when we find what we need, we click on OK button (pay attention that different components will have a specific position and row number on our sheet, that's why I used "Activecell": I want to be able to tell the form exactly where to put data)
component data are automatically inserted in the selected row of our data sheet
now I can go on inserting other components. I notice that the form is useful because I don't have to re-open it for every component, and it is also non-modal, letting me select the cell where I want data to be entered.


Ok, this is the process to compile data sheet; every data sheet will be compiled this way.

Now, suppose something changes in our database, maybe component description or price... I will do all the changes, and then resave my file.

And here the donkey falls (as we say in Italy): I open one of my compiled data sheet, and I want it with updated values from my database file.
So I go to DATA menu to refresh all connection.
And ideally all data will be refreshed.... but in fact the content of some rows disappear.
And I don't know why...

Here I attach a picture of a compiled data sheet, so you can see what the final result will be

15618

SamT
03-14-2016, 05:31 AM
OK

So you have a database workbook organized with one component Type per sheet.

You have a product line and you want to create or Compile, a data sheet showing all the components used in each product.

You want the user to Pick a Row on the Product Data Sheet, Choose a component, and Insert the data into the next row,

For example, to insert a new Ponticello, select a Cell in Row 20, Use the form to choose a component, then the code will insert a row below row 20 and add the Component data to the new row 21.

If the User Chooses another Component without first Picking a new Row, then assume to insert the new Component Data into the next Row down

You want to refresh all the component's data on all the product sheets when you change any data in the database Workbook.

I am goning to change the thraed Title to "Using ADO with Excel."

Stefano
03-14-2016, 06:34 AM
OK
You want the user to Pick a Row on the Product Data Sheet, Choose a component, and Insert the data into the next row,
For example, to insert a new Ponticello, select a Cell in Row 20, Use the form to choose a component, then the code will insert a row below row 20 and add the Component data to the new row 21.
If the User Chooses another Component without first Picking a new Row, then assume to insert the new Component Data into the next Row down


Sam,
it is all right, except quoted part.

The number of rows is fixed, so if I want to insert a Ponticello on Row 20, I will pick cell B20 (on A20 there will be numbering) and use the form to insert data, that will be inserted on B20, C20, D20 etc.
If I choose another component without changing cell selection I will simply overwrite existing data.

This is the main part, then there will be other little things to fix, but I think we can think about them when this part is done.

SamT
03-14-2016, 02:18 PM
This is the main part, then there will be other little things to fix, but I think we can think about them when this part is done.
That is the easy part, it is something we do every day.

The Product data sheet shown in the picture in post #8 is very customized and is different from the sample Product product Data sheet you have in Foglio1 in the Compiler.xlsm workbook you uploaded.

The best way to do things really depends on the final form of what you want. The best way to create a sheet like Foglio1 is not the best way to create a sheet like the picture in Post #8. There are at least 99 ways to accomplish anything with VBA, but only half of those are the best ways.

Tell us what the final resulting Product Data Sheet(s) should be. I am thinking that you may be wanting each Product to have a custom formed Data Sheet. This will take a bit more engineering and forethought. I my guess is correct, please show us two or three samples of different Product Data sheets so that we can understand what we may have to think about.





Please note that at this time, I am trying to gather information for all those who may be watching this thread. I see at least 6 ADO speakers in the list below as well as our resident Function Expert. I do not speak ADO and would not use SQL to accomplish what you need. I would only use VBA for Excel. You should understand that there are many ways to do anything with VBA. If ADO will work, it can be very elegant code.

Are you convinced that you want to use ADO?

Stefano
03-14-2016, 04:11 PM
Ok, first of all thank you again for your patience.
Then, tomorrow morning I will be in office and I will post some data sheets, so you can understand well what we need to achieve.
In fact the file I posted was only the beginning, I thought to customize it later... but maybe it is better to start the right way.

I'm sure there are a lot of ways to do everything just in VBA, but as I said I'm quite a newbie, and I don't know all of them, the only thing I can do is search, think and try :)
Instead I'm not sure about ADO and SQL (and I don't speak ADO too), I only found them as a quick way to obtain my goal (thinking about DATA_SOURCE as a database, where entries may increase or decrease).
If we find a way to do everything in VBA it is ok anyway, the important thing is that the final product works fine!

Stefano
03-15-2016, 03:20 AM
Here I am, with 4 data sheets of 4 different products, and 2 images that will explain exhaustively (I hope) every aspect of the final product.
It is not so simple... but if you take a pair of minutes the process will be clear, I think.

Now the question... what is, from the 99 possibilities, the right way to accomplish this?


1564115642

15643156441564515646

SamT
03-15-2016, 08:53 AM
Please! Just upload the sheets. Pictures are impossible to properly interpret.

IS it that you have a limited line of products that all Product Data Sheets have the identical layout for POS 1 to POS 16?

I can see that this will require 7 lists of Range Addresses and each bit of Data must be individually assigned to a Range Address. This is not difficult, merely cumbersome.





You should understand that it is difficult for VBA to deal with Merged Cells on a worksheet. However, if you will Format the Cells using Horizontal Alignment = Center Across Selection, then VBA can handle the issue with great ease and the Worksheet appears identical to one that has Merged Cells.

Stefano
03-15-2016, 10:04 AM
Please! Just upload the sheets. Pictures are impossible to properly interpret.
:rotlaugh:

If I upload only sheets I think is quite difficult to understand what part of layout is fixed and what not.

Anyway, we have about 500 products, and thus 500 data sheets.
Components may vary, they can be present or not, but every data sheet have identical layour from pos 1 to 16.
Then in fact there are 8 list of range Addresses (1-4 5-7 8-12 13 14 15 16 17-32).

Merged cells are not a problem... I've been a graphic designe for 10 years, in general everything must be in his place and I don't want to see useless cell, but I think I can manage it!

SamT
03-15-2016, 01:13 PM
Fixed Fields can be merged or formatted any way the Graphic Designer wishes.

Dynamic data should not be in a Merged Cell. Center Across Selection can make several adjacent Cells in a Row appear to be merged.

The creative use of white Fill, white Borders, and light grey Borders can complete the illusion of Merged Cells.

After the Graphic Designer in you has finished the final formatting, please Fill all the Dynamic Cells that VBA must deal with with a color. I think that we should write the Addresses list. After we choose one of the 99 ways to list them. :D

Stefano
03-15-2016, 03:10 PM
Hi Sam,
graphic designer inside me is suffering... :crying:

Anyway, here attached you will find an empty model of DATA SHEET with 3 sheets (plus settings) and a lot of colors :giggle
I think everything should be quite clear now, and we can start thinking about the right way to manage this!

To write down the Addresses list I need the DATA_SOURCE complete file, that is in my pc in the office. I will post it as soon as I arrive there tomorrow morning.

15658

SamT
03-16-2016, 06:56 AM
This will make your little Graphic heart happy

15665

Briefly, the green cells are the ones we need VBA to refer to.

I will be using that to ponder various referencing schema.

The only problem is... It is in English and the Data Source is not. I will be looking at your pictures above to correspond the two.

Stefano
03-16-2016, 07:38 AM
Perfext, I'm very happy :thumb

In fact we can also use the DATA_SOURCE I've already uploaded to do the work, it miss only MICA, IONIZERS and some others component types (because the final and complete file is not yet finished).
Once the present components are referenced I can do the tedious part to duplicate for the other, I think.

So here the referncing schema:



POS

DATA SHEET

DATA_SOURCE



1-4

MICA

missing



5-7

WIRE

_FILO_



8-12

CABLE

_CAVI_



13

DIODE

_DIODI_



14

THERMOSTAT

_TERMOSTATI_



15

IONIZER

missing



16

THERMOFUSE

_TERMOFUSIBILI_



17-32

ALL THE REST

_CAPOCORDA_, _PONTICELLI_ , _SUPPORTI_ , _VARIE_ and some others...

SamT
03-16-2016, 07:50 AM
I am sorry, but the Sheet formatting in Graphically Designed.xls has broken the formulas in the other sheets. :(

I have more to say, but Home Work is calling me.

Stefano
03-16-2016, 08:30 AM
Don't worry about other sheets, I can fix them at the end :)

SamT
03-17-2016, 08:31 PM
I have started the "Address" lists for the Tech sheet and the Price sheet. They are in the Module TableDescriptors. Note the patterns in the lists. Word patterns are an important mnemonic for programmers. Since the two sheets are identical, wioth the exception of the price column, I offered an alternative which is what I would use, even though the Programmer must keep a little bit more awareness in re which sheet he is working on.

If you would complete those lists, and work on the Customer sheet if you wish...

I am now going to take a hard look at the code in the Data Source.

SamT
03-17-2016, 09:22 PM
I forgot to upload the workbook, until I closed Excel and it asked if I wanted to save. :banghead:

15690

Stefano
03-18-2016, 07:50 AM
Wonderful :)
Because Tech sheet and Price sheet are almost identical, I tought to address all the values on the first one (maybe also the price, in an hidden column), and link the cells of the second to the first. No?

SamT
03-20-2016, 01:25 PM
Here's what I have been up to: 15710

There are a lot of omissions, noted by commented out code, that need a Graphic Designer, (who happens to have an excellent VBA code style,) to finish un-merging some data cells and assign the appropriate values to some Address Constants. See Code Comments.

I must now wait on you so that I can continue. In the meantime, I will work on the Data Source Code.

I intend to finish the MICANITE Trail of code to its completion and let you copy it for all the other Component Types.

Stefano
03-20-2016, 03:50 PM
Hey Sam,




thanks for the compliments to my VBA code, but I'm just a newbie :)

I had a look at your file and I would have a lot of questions for you, but it's nearly midnight and as a newbie it's not the right time to code!
Tomorrow morning, after a double coffee, I will try to manage it!

snb
03-20-2016, 04:41 PM
Stadio primo:
I think we'd better start integrating all sheets in datasource.xlsm into 1 sheet "Tutti".
Then we add a column 'type' in which we can put the type of component (the sheetnames you use now)
So the result will be 1 Table that contains all components.

Stadio secondo:
We'll devise a userform to compose an order, existing of several components.
That userform will have all the elements your 'compiler.xlsm' contains (but without named ranges and without merged cells and without links between different workbooks).
Userinput will only be done by this userform.
So storage of the database and the userinput will be integrated in 1 file.

Stadio terzio:
The output of the userform can be:
- adapting the stock data of components
- an inventory of prices
- a customer 'form' , whatever.
- so all sheets in 'compiler.xlsm' will be part of the only file that is required.

SamT
03-20-2016, 06:07 PM
I am leading him to two Master books (plus ~200 Product Books,) one with the Product Tech Data templates and one for Type Component Sheets. Note that each Product has four sheets. They could be consolidated into one sheet with well defined Page Breaks.

I am designing the interface between the two books so that it can be very easily ported to a single sheet table or a single Database table. Since the Data Source book is also a macro loaded xlsm file, I intend to have the Product book merely ask for a record by type and Codice. I intend to have the Data Source retrieve the record.

I am under the impression that the Component sheets are much more volatile than the Product Data sheets.

I do know that Price data is volatile across the board, and that occasionally the Component data on a particular Product can change.

I am thinking of creating a Change History sheet in the Data Source, recording only Date, Type and Codice, and using the Time stamp on the Product data Books to search this history for relevant changes whenever the Product book is opened. My thoughts are that unless the Product book is needed, thereby opened, there is no reason to update it.

My idea on this is the have the product book ask the Data Source for an array of the Change history since a given date, then request only those updates particular to that product. To this end, I am designing the Code base to be easily ported to using a separate Product Book Template so that it only needs to contain the updating code. Then again, I may keep that code in the "Master" Product book. I'm just not that far along yet.

Another issue I see is that whenever a component on a product is updated, it de facto becomes a new Product, but the Data for the old Product must be archived. I don't yet see what effect this will have on the Prices in the old Product Book. If the only updates that do not trigger a de facto new product is the Prices, that would make the updating much simpler, merely add the new price to the History list in the Data Source and I would keep that in the Product Book Template since it only affects one column on one Sheet/Table

Stefano
03-21-2016, 01:36 AM
Hi snb, and thank you for joining us in this crazy project!


Stadio primo:
I think we'd better start integrating all sheets in datasource.xlsm into 1 sheet "Tutti".
Then we add a column 'type' in which we can put the type of component (the sheetnames you use now)
So the result will be 1 Table that contains all components.


I also thought to integrate all products in one sheet, and I'm sure this is the best solution to have only one table to choose from, but in fact it wolud be better to keep different component on different sheets, even because I will not be the only one to work on it, and I'm not sure other people here would be happy to have all the components in an only list (but if the benefits to have an only sheet will be so great, I think thay can manage it :) ).


Stadio secondo:
We'll devise a userform to compose an order, existing of several components.
That userform will have all the elements your 'compiler.xlsm' contains (but without named ranges and without merged cells and without links between different workbooks).
Userinput will only be done by this userform.
So storage of the database and the userinput will be integrated in 1 file.

This part is not so clear to me.... are you talking about an userform to add components to the database?


Stadio terzio:
The output of the userform can be:
- adapting the stock data of components
- an inventory of prices
- a customer 'form' , whatever.
- so all sheets in 'compiler.xlsm' will be part of the only file that is required.

Same as above...


I am leading him to two Master books (plus ~200 Product Books,) one with the Product Tech Data templates and one for Type Component Sheets. Note that each Product has four sheets. They could be consolidated into one sheet with well defined Page Breaks.

Are you sure this is a good idea to integrate all sheets into one?
As above other people will work with it, and habits die hard... now they use even 3 different books for tech, price and customer sheets!
And in the end tech and customer sheets will be saved in pdf in 2 separate files for distribution.


I am designing the interface between the two books so that it can be very easily ported to a single sheet table or a single Database table. Since the Data Source book is also a macro loaded xlsm file, I intend to have the Product book merely ask for a record by type and Codice. I intend to have the Data Source retrieve the record.

I do not know if I understand correctly... but we do not know by heart all the 600 codes, so I think the userform will be more fliendly if designed as I did, where you can "build" your component step by step (obviosly depending on what is in components database).


I am under the impression that the Component sheets are much more volatile than the Product Data sheets.

I do know that Price data is volatile across the board, and that occasionally the Component data on a particular Product can change.

I am thinking of creating a Change History sheet in the Data Source, recording only Date, Type and Codice, and using the Time stamp on the Product data Books to search this history for relevant changes whenever the Product book is opened. My thoughts are that unless the Product book is needed, thereby opened, there is no reason to update it.

My idea on this is the have the product book ask the Data Source for an array of the Change history since a given date, then request only those updates particular to that product. To this end, I am designing the Code base to be easily ported to using a separate Product Book Template so that it only needs to contain the updating code. Then again, I may keep that code in the "Master" Product book. I'm just not that far along yet.

Here I did not understand too much... but we don't actually need a change history for price.
If you look at Data source, you can see that the price is in fact a weighted average price.
If you press the "+" button you can add a quantity of product specifying the total price, and weighted average price will change. Almost every day we have to add products, and thus prices will change a lot of times.
And in fact when I open a previously saved product sheet I should be able to update components price with the weighted average price, and not the last price we bought the component.


Another issue I see is that whenever a component on a product is updated, it de facto becomes a new Product, but the Data for the old Product must be archived. I don't yet see what effect this will have on the Prices in the old Product Book. If the only updates that do not trigger a de facto new product is the Prices, that would make the updating much simpler, merely add the new price to the History list in the Data Source and I would keep that in the Product Book Template since it only affects one column on one Sheet/Table

For every product we need an only file, and not "old versions" of the same, it wolud be very confusing.
And updates are surely about prices, maybe description (only for components from pos. 17 to 32).
And some times we need to modify, add or delete a component from a product sheet, but also in this case we will overwrite the file, without creating a new version of it.



And in the end I don't really know why I launched this venture!

http://www.vbaexpress.com/forum/images/smilies/120.gif

snb
03-21-2016, 03:28 AM
In the attachment:

- I integrated all component sheets into sheet 'tutti'
- I designed a simple userform in which you can select
- the type
- the code
- the quantity

The three comboboxes are dependent of each other (form left to right)

Stefano
03-21-2016, 06:22 AM
Yes, but the complete DATA_SOURCE file will have ~600 entries, and as I said is not possible to remember every single code.

Did you see the original DATA_SOURCE I uploaded?
I think it's easier to simply select the row where we want to add the product, and then run the userform, which automatically will works on that row, without the need to choose what to add.

Or maybe your form could be perfect if we could select: TYPE -> VAL_1 -> VAL_2 -> VAL_3 -> VAL_4 (if they are present)

snb
03-21-2016, 06:38 AM
Instead of codes we can show descriptions in combobox2.

Stefano
03-21-2016, 09:45 AM
In facts this could be a very good solution! :yes

Can you please comment your code a little bit?
It is very nice, but quite cryptic for me!

... so if I need a further modification I can do it by myself!

SamT
03-21-2016, 09:48 AM
so I think the userform will be more friendly if designed as I did, where you can "build" your component step by step (obviously depending on what is in components database).

The Worksheet Form that you have built is perfect It was created by the best possible person , a Graphic Designer with intimate knowledge of the Domain. My Domain is VBA, yours is the shop that you work in. I will never know as much about your shop and the way things are done as you. If I had made that form. it would have been much easier for a VBA person to work with, but much harder for your Users to use.

In my conversation with snb, I was talking about the "Grand Plan" which is only a cloud of Post It Notes now.
At this time I am only working on the back side of the project, the part that the User will never see. Please do not pay much attention to the conversation between snb and I. Those details will be asked of you in their time.

You can think of the work I am starting with as the Circuit board of your products. It can be used for many products. The Back Side code I have shown you to date is designed to be easy to add to at some future time. For example I spoke with snb about the interface between the two books, If some time in the future, you decide that you must move to Access, only the interface code will need a few changes and your Worksheet Form will work as usual with no changes. The Users would see no difference.





This conversation gives the opportunity to ask about your Domain and the actual process by which the Users "Build" a new product.

I imagine that the Form Users first select Val_1, then select Val_2, Etc.and then only select a Codice if there are more than one with all the correct Vals. In order to make the Form work in the pattern that is best for the User, I should understand the step by step process they use.

What is the purpose of the Customer sheet?

snb
03-21-2016, 10:28 AM
Next step: (as you can see without ADO)

Stefano
03-21-2016, 12:27 PM
Ok, this is a good explanation :)
Customer sheet is a "simplified" version of technical sheet, that we give to the customer

Stefano
03-21-2016, 04:03 PM
I was looking at snb last file, in this way userform is very good :)
Very useful the idea to add a listbox where to put all the components to add, so I can do it once for all!

Only two things:
- now "quantità" picks as maximum value the number of actual component present in stock (say "X"). I should be able instead to write down a free quantity (say "Y"), because it is not a random number between 1 and X, but it may vary every time and is always a bigger number.
- in addition I need another field where to write down the total cost of Y. I need it to calculate weighted average price in the precise moment I add Y (but if you llok at my form is quite clear)

snb
03-22-2016, 12:41 AM
How can you use more components than you have in stock ?
Why do you need the price calculation ? Will it influence the input in the userform ?

Does a set always have
4 elements of mica ?
3 elements of wire
5 elements of cable
1 diode
1 thermostat ?

Stefano
03-22-2016, 01:36 AM
Probably we did a little of confusion... I now understand some of the things you said in previous messages!


I intend to have DATA_SOURCE with his userform, used only to update stock quantity when we purchase products.
That's why I must be able to insert a free quantity (that could go from 10 to 500.000) and his relative cost.
On DATA_SHEET I need another userform (the one I made with ADO) used to fill product sheet.
But I really need this userform, because products' data sheets sometimes change, and I must be able to run my userform again to modify or add components.

Your userform in DATA_SOURCE is intended to fill the DATA_SHEET instead (now I understand well the listbox, too).
The idea is very good... I open DATA_SOURCE and from here I can compile my DATA_SHEET and save with his name.
But when I need to modify it?

---

A product is made by:
(fixed components)
- 2/4 elements of mica, n° 1 for each type
- 2/3 different type of wire, quantity in kg
- 3/5 different cables (in fact they would be 6, but this is the last of our problems), quantity in m
- SOMETIMES 1 diode
- SOMETIMES 1 thermostat
- SOMETIMES 1 ionizer
- SOMETIMES 1 thrmofuse
(variable components)
- all the other components (rivetti, ponticelli, supporti...) in variable quantity, say from 1 to 8

snb
03-22-2016, 02:09 AM
The ordering userform is so simple, that I keep that for later.
At the moment I'm only interested in creating the data sheets instead.
Like I showed there is no need to create a separate file from Data source to do so.

Since there are no fixed components, nor fixed amounts of components I won't take that into account.
The user may choose as desired.
I will sort the list in combobox1 according to your list (mica/wire/cable/etc.)
NB. data for wire and cable are lacking.

You indicated that for 'MICA' some manual input has to be done : 'pos' (what does it mean and what is valid input).

The same applies to Wire : Omega_toll_omega/m_øV (what is valid input?)

And for cable: 'strip" (what is valid input)

Stefano
03-22-2016, 03:45 AM
Ok, I hope to provide the final DATA_SHEET file this afternoon.

Manual inputs:

MICA -> POS stands for POSITION, it could be 1,2,3 or 0
WIRE -> Ω stands for OHMIC VALUE (~"5" to ~"200"), TOLL for TOLERANCE (~ "±2" or "+2-1"), Ω/m for OHM/meter (~"2" to ~"200"), ØV for SPIRAL DIAMETER (from ~ "1.50" to ~ "5.00")
CABLE -> SPEL stands for PEELING (~"3 mm" to ~"15 mm"), and then there is an empty field for notes

DIODE
THERMOSTAT
IONIZER
THERMOFUSE -> for these 4 components we have an empty field for notes

snb
03-23-2016, 08:06 AM
I'm waiting....

Stefano
03-23-2016, 12:05 PM
Yes, I know, and I'm very sorry, but another guy was working to complete that file.
He just finished this evening. Tomorrow morning I will check it, and if everything is ok (as I hope), I'll upload it

snb
03-23-2016, 01:50 PM
In the meantime you can test:

Stefano
03-24-2016, 01:13 AM
Ok... you are a real guru :yes
The most amazing thing is how the code is compact to achieve such a result... if I did something like this I will probably spent some months and 1000s rows of code...

Beside this the form start to works wery well, some fixes: I think manual inputs must remain really manual, because for ex. wire ohmic value (as I told before ~"5" to ~"200") could be 68.3.
For MICA POSITION now we have a column in the respective sheet where it is specified, so I think it could be automatic.


Now I'm working on final DATA_SOURCE (finished, but no completed, if you know what I mean...), as soon as I finish I will upload it!