Variable between form subs

[fsjavan]

New user, first post, and love the site its been very helpful so far. I haven't found the answer yet so I am posting

I have a form with 2 combos and a txt box that is ultimately performs an update query when submitted.

The form is what sets which table needs to be updated based on the combo box selection.

I can make this work without passing variables but it would require me to do another select case in the submit sub. Seems like it would be best to just have to do that once. then fill my sql statement with the variables I set previously.

I am just setting glob variables and being done with it.

 

[MarkK]

You have data hard coded into your VBA, and that must mean there is a mistake somewhere. For instance, "Chevy" is data, which you should be able to add and delete, but if you add "Honda", then you'll need to rewrite your code, adding it to your Select Case list. That is not a workable solution.

So you've modeled the problem incorrectly somehow. What tables do you have? I would expect to see tblMake, tblModel, and tblInventory. Then Ford, Chevy and Honda be data in tblMake. You want to create tables that are an abstraction of your data types.

 

[fsjavan]

Your right. the select case is hard coded. but that would be due to my lack of knowledge on how to determine the select case as a variable.

Part of the code that I did not include was on form load I am doing an open recordset to populate the combo box values for vehicle make. So yes it would be possible to not hard code the select case. I just don't know how to do that... yet. For the purpose of what I am doing that "flaw" is a minor consequence. The data I have for the types I am doing the select case hasn't changed in 20 years. The type (vehicle makes) in this example is pretty much a constant. Maybe I shouldn't have used vehicles as an easier to relate piece of data.

The tables I have is a main table of finished parts with a one-many relationship to the sub components which are in their own tables. subcompents tables have its part number, type of part, on hand, max, and min.

I think my table and data organization is ok. or will work for what I am trying to do. Passing a variable between subs is my issue. Unless I am missing something here.

Unless you are saying, the table I have that populates the cbo box needs to have my table names in it also so I can set a variable from that. Not sure how I can do that though.

End result. I need a way to allow a user to choose from 12 different subcomponent types. (CBO1) each subcompoent type has various different part numbers (CBO2). change the numeric value for quantity (txtbox1)

 

[MarkK]

OK, let's go after this one problem.

I need a way to allow a user to choose from 12 different subcomponent types.

Where is this data now? How is it stored? How is it structured?

I would expect to find a table like . . .

tSubCompType
SubCompTypeID (Primary Key)
SubCompTypeName (user friendly name)

I would expect to be able to write SQL like . . .

SELECT SubCompTypeID, SubCompTypeName FROM tSubCompType

. . . as the RowSource of my combo. Set the combo ColumnCount and ColumnWidths properties and, done. But what problem are you running into getting this data into a combo?

 

[fsjavan]

Sorry Mark I must not be explaining well. I think you broke that down a bit too far, I can populate the comboboxs without any issues. I can even submit the data.. I just didn't want to run the select case in the twice. I thought it would be poor coding to duplicate it. When it could be ran once and a variable set.


To answer your question.

Where is this data now? How is it stored? How is it structured?

tbllowerdie
LowerDieNumber (<-one to many->) tblDieSets)
LowerDieDescription (Lookup to tblDieType)
LowerDieInventory
LowerDieMax
LowerDieMin

Rinse and repeat 11 more times for the different components that make up a die.

The database is attached with data in the tables. Yes, there will be some testing structure in there as it is a work in progress.

 

[MarkK]

You have misunderstood how a relational database system works. LowerDie is data. It should be a row in a table, not a table unto itself.

Let's say you are making a database for a school. You should have a table called Student, but you are making a table for all the kids. You have tables called tblBilly, tblSuzie, tblStephen, and tblLarry, all with exactly the same structure. Each student in your system gets his/her own table, and that is a mistake. The table needs to be an abstraction, like Student is an abstraction, like a student is the type of thing that Billy is. Each row in a table represents an instance of the type, so IN the Student table you have Billy, Suzie, and so on.

In your case, LowerDie is the instance not the abstraction, LowerDie is Billy. What table does Billy belong in? Not tblBilly!

In respect to the system you posted, the table design problem is critical. Any other problem in that system should wait until the data is correctly structured.

Hope this helps,

 

[fsjavan]

Maybe i have... But to use your example. tblLowerDie is not the same as tblBilly. It would be more like tblEightGrade. The way im seeing it is, tblLowerDie.dienumber field is what is holds billy, suzie etc.

Each of these 11 subcomponent tables have a unique part number (student name in your example). tblDieSets hold the finished part number along with the subcomponent that are required to make the finished part. (done through a lookup to the individual subcomponent tables) Not every finished part number requires all 11 subcomponents.

I will not sit here and tell you im an expert in relational databases. But this table organization is nothing like me trying to make a table for each student. It seems to me that you are making assumptions and do not understand what the data is doing.. or your students example is WAY off.

However it goes why are we scrutinizing my data when that wasnt this question. How to pass a stupid variable between subs without using global variable is what I was asking for help on.:banghead: