Combobox selection to set Textbox control source

[williamson1979]

Hello,

I have a form that has a 3 columns with 10 materials currently, this could grow and the materials used change daily.The first column subtracts inventory, the second simply calculates the percentage of each material and the 3rd adds inventory.

What I'd like to do is; Create multiple comboboxes to select the materials used that day which is easy enough but I dont know how to get the related textboxes to save the values to the correct table fields so the query can update each materials inventory correctly.


So basically cmb1 select material 3, txt1a I enter amount removed, txt1b I enter the amount added but it saves to material3Removed and material3added.

[twinnyfo]

A little more informaiton might be helpful here.

First, we kinda need to know all the table field names you will be dealing with.

Second, you say that cmb1 and txt1a/txt1b saves a value to material3Removed and material3added--so obviously you "doing something" to make that happen. What is it that you are doing to make that happen and we can trouble shoot that aspect of your form.

Remember, on the forum, assume that we know nothing about what you are doing. Some posters will say, "I have a form. It doesn't work. Fix it!" But we have no place to start. I know that is not what you are asking, but this is a good principle to start with. We've been back and forth for a while on some of your other posts, so we are glad to help. We just need more information.

Thanks.

[williamson1979]

Sure thing. I’ll get more info. Maybe best I upload. It’s in the aggregate system.

[twinnyfo]

We don't need an upload. But we do need to see any code you are using so we can troubleshoot it.

[williamson1979]

I've attached images. I only have calculations, no coding here. Honestly I do not know where to start. But you will notice Ive just used labels, Id like to replace those labels on the left with comboboxes that contain all materials but I need calculate each one for inventories. Can the textboxes be updated to match the selection. If I select Agg1 can it update the adjacent text boxes to agg1used, agg1percent, agg1received etc..

[twinnyfo]

Just as an aside note, you can take screen shots (like, actual screen shots with your computer) rather than taking a snapshot of your PC screen with your phone. It makes things much easier. Just highlight the window you want to take a screen shot of and click Alt-PrtScrn. That will save an image of that window to your clip board. You can then save it into an image application or even directly into Word.

[twinnyfo]

wiliamson1979:
I only have calculations, no coding here.

I see an aggregate query, but no calculations.

wiliamson1979:
But you will notice Ive just used labels

I'm not sure what you are talking about.

wiliamson1979:
Id like to replace those labels on the left with comboboxes that contain all materials but I need calculate each one for inventories.

Absolutely no idea....

wiliamson1979:
Can the textboxes be updated to match the selection. If I select Agg1 can it update the adjacent text boxes to agg1used, agg1percent, agg1received etc..

I think I might possibly understand kinda what you are talking about here. But, again, your response is very confusing. You are asking too many things at one time and it seems unrelated to the thread.

wiliamson1979:
Honestly I do not know where to start.

Honesty is refreshing. We will try to hepp.


==================================================

So, in response to your follow up post.... I am totally clueless. I have no idea what you are trying to describe. And your images add no assistance whatsoever, and I am not sure how any of this relates to any specific question for troubleshooting.

Let's start with baby steps--because we've always got to start somewhere, and I get the impression you sincerely want to try to do things correctly. And you are certainly exercising a great amount of patience with our inability to grasp your question. You deserve the respect of some decent patience reflected back toward you.

Here's the first baby step (from the opening post):

wiliamson1979:
So basically cmb1 select material 3, txt1a I enter amount removed, txt1b I enter the amount added but it saves to material3Removed and material3added.

I'm starting here for several reasons:

1. It appears to be a problem because you indicate that you want something to happen but (you actually use this wonderful adversative conjunction "but") something else happens.
2. It's specific.
3. There's a good chance we can actually find some sort of solution here.

So, my questions are several:

1. What is the exact sequence of events you want to happen (with this, very, small, little transaction--not the entire form, not the project, just this one minor transaction)?
2. Exactly, what is happening? What is not happening? What is happening incorrectly?
3. Is there any code anywhere (VBA or macros) that is triggered when you change the values on the form? If so, this is the code we need to see.
4. What is the naure of the data on the form? Is it a bound form? What fields are bound to which controls? What fields do you want to update?

I'm trying to hepp here, but you really need to slow down, think about your responses, address the specific questions you are asked and try to work things one step at a time.

Remember, although it might seem a total pain, I would rather have you begin 100 threads with very detailed, specific questions that can be addressed individually than have one large, scattered, unanswerable thread.

I am also inclined to believe that there are additional structural challenges with your DB, just based upon the small amount that you have shared so far in your several threads. In the long term, you will definitely want to fix those structural changes, otherwise, you will have increased headaches with any changes you make to your DB. However, let's stick with baby steps for right now.

[williamson1979]

First question. I want to create a Combobox that will show materials. Upon selecting a choice in the Combobox I would like it to update 3 text boxes to to the fields on my table that relate to that material.

[williamson1979]

So can the textboxes have a code and I’m just guessing here to help you understand me.

If combo1 “agg3” then text1 “agg3used” , text2 “agg3percent”, text3 “agg3addition” and just repeat for all materials and Comboboxes.

Simply I want to not have to continue to create textboxes for every material as it takes up firm space and gets cluttered. But I need the textboxes to know what field they are controlled by to save the record correctly and so my report can pull correct inventory.

[twinnyfo]

I will try to make as many allowances for your unfamiliarity with terms and general DB processes. I'm not trying to be unkind. We often speak in geek-speak or tech-speak (or DB-speak) and our "general" assumption is that all involved know what's going on. This is not always the case.

I would like it to update 3 text boxes to to the fields on my table that relate to that material.

I think you understand the principle, and I think I understand what you want to do--again baby steps.

Let me try to understand first, your table structure:
I "think" you have a bunch of fields in a particular table:
Agg1Used, Agg3Percent, Agg3Addition, ... Agg10Used, Agg10Percent, Agg10Addition. Correct?

You have a Form. On that form you have a Combo Box. From that Cobo Box, the User can select the number of the Aggregate (1-10). Depending upon the selection of the user, you want to update the appropriate number of Aggregate fields in the table (let's not think about "Text boxes updating tables" right now). Correct?

First, you would unbind the form from the Table. That is, you would remove anything from the Form's RecordSource. There may be ways to do this by binding a form, but because you are planning on switching which fields to update, I find it easier to tell VBA which fields to update, rather than constantly changing a text boxes Control Source--but either way is possible, you just might have more confusion issues with your text boxes.

The table updates must all be done in VBA. It is a relatively easy procedure. But we need to know how to update these records.

Are all of these updates on new records? Existing records? Is it overwriting previous information? How do you know which record to update? Again--there are more questions, but I think that these issues are more of a result of you being unfamiliar with what to do, what to ask for, how to do it, and a general lack of familiarity with DB design and execution. These all take time.

As mentioned in one of your previous threads, I say again, that I think having these ten sets of fields in your table is a mistake, and it is waiting for things to become more complicated. However, I understand that you "think" that there is a "need" to maintain this structure. We will continue with that structure for now, but over time, it may be a hurdle you want to cut down to size before too much of your project relies upon a faulty structure, and such changes become major overhauls. But, keep in mind that there MAY be a good reason for maintaining this structure--it is just not quite apparent to me yet.

Again, we'll go through baby steps. Please take time and care to respond and look over your response for good grammar, too! This helps us understand what you are trying to say.

[williamson1979]

"Let me try to understand first, your table structure:
I "think" you have a bunch of fields in a particular table:
Agg1Used, Agg3Percent, Agg3Addition, ... Agg10Used, Agg10Percent, Agg10Addition. Correct?"

Correct, I have 1 table "tblProduction" that currently has 101 Fields, though I'll remove roughly 10 that are not useful.
-------------------------------------
"You have a Form. On that form you have a Combo Box. From that Cobo Box, the User can select the number of the Aggregate (1-10). Depending upon the selection of the user, you want to update the appropriate number of Aggregate fields in the table (let's not think about "Text boxes updating tables" right now). Correct?"

Not currently although that's my goal, (Currently I basically have 10 rows x 3 columns on my form ordered "1-10" which works now but if anything new is added it will become very cluttered as is.). But exactly if the user selects Aggregate (1-10) then 3 fields will be updated from that selection to store in the appropriate table fields.
-------------------------------------
"However, I understand that you "think" that there is a "need" to maintain this structure. We will continue with that structure for now, but over time, it may be a hurdle you want to cut down to size before too much of your project relies upon a faulty structure, and such changes become major overhauls. But, keep in mind that there MAY be a good reason for maintaining this structure--it is just not quite apparent to me yet."

I do need to store the records. Reason being if the product fails its usually months after production so this way its very easy to look back at conditions and calculations from the date of production

[williamson1979]

I'm no DB guy so what I think might be way off base so thanks for bearing with me. Ultimitly though what Ive done and what Im trying to do is move what was a 1 page excel report to Access to have easy access to past production records, track inventory easier, fuel usage tracking and it just looks cleaner.

I know you and others have mentioned the 1 table for records not being correct. I did try to break it up into smaller tables but began to have issues with relationships and my form becoming blank so I reverted back to the 1 record table. That being said it will not expand very much from where it is now outside of new aggregates and everything in the table is related from the first field to the last field.

[twinnyfo]

I’ll give a more detailed response a bit later, but the answer is NOT multiple tables, but a more sound table structure. Very seldom should you have “wide” tables. Typical structures have “tall” tables. I’ll discuss that in a bit.

[twinnyfo]

So, just thinking about 30 of the rows in your table (which still leaves 71!), let's approach this from a different perspective. That is the DB perspective. Their are two primary enemies of the DBA: 1) duplicated data and 2) frequently unused fields. This is extrapolated from other principles which guide a "normalized" database (see this article).

Now, if you have 30 fields (which is really a reduplication of three fields ten times), unless each and every time you have a record, each and every one of those 30 fields has data in them, you have frequently unused fields. However, you may say that you never know which fields may or may not be used for any given record, correct? Yes, BUT, each time any of the fields are used (I have inferred this from your posts) they are used in triads: xUsed, xPercent, xRecieved. So, why not have a table that uses that triad, and every time a record is added that triad of fields has data in them.

But, you will say that some records have use of four of these triads (or even ten!). However, if you have a record (And I am still totally clues as to exactly what this database is keeping track of, so this also hampers my ability to recommend how to structure your tables), and this is record number 1234. I don't know what record 1234 represents at this point. However, on 14 Aug 18 you perform some sort of action with respect to record 1234. Those actions include using Aggregates 1, 6, 7, 9 and 10. We're just talking hypotheticals right now, so try to stay with me.

In your Aggregate table you would add the following data:

• You will add five records
• Each record will contain a reference to the Item/action/product/whatever-it-is with the record 1234.
• Each record will contain the date: 14 Aug 18
• Each record will list which Aggregate this action refers to: 1, 6, 7, 9 or 10
• Each record will list Used, Percent and Received for the specified Aggregate

But you will say that you need a historical record of these activities. I will say that you still have it. When proerly design, you build a query that seeks record 1234 and date 14 Aug 18 and BAM! You have your historical record.

Now, back to your 71 fields. There may be 71 bits of information that relate to each and every record in your table. For example, if you had a table with descriptors of a person, and these descriptors applied to each and every person in your db, you could easily come up with a bunch: First Name, Last Name, Middle Name, Maiden Name, Height, Weight, Birth date, Skin tone, hair color, eye color, hair length, fingernail length, facial hair, ear lobe size, body mass index, toe length, etc. You could probably come up with 71 different descriptors. Although this would be a strange table, such a table would be correct, because all items of data apply to all records all the time.

So, your entire table may not be incorrect. But I would argue that your 30 fields concerning aggregates is not properly structured.

A detailed discussion of your table structure would be a new thread--and there are some really smart guys on the forum who really do well at scrutinizing table strucutre.

The long story shortened: While it is possible for us to guide you down the road of creating a combo box that selects and aggregate (for whatever an aggregate is used for whatever your database does), and then select a set of three fields in a table and save data there (which we can do for you), it is not recommended.

The basics of that solution is in my Post #10.

[williamson1979]

So basically it’s a production report. The information entered is date/times, plant/product/ambient temps, weather conditions, finished product info total/waste/rate, then we need to know what materials were used/percent of product/received. So if it were broken down it would be a Production summary, environmental conditions, mineral fillers used, bitumen used, fuel used and aggregate used.

So back to the aggregate; to track a running total of each aggregate inventory I need the used and received fields. I need the percent field to compare to the products blend design. The mineral filler and bitumen are the same principle just far fewer possible ingredients so it wasn’t a issue until I started on aggregates.

[twinnyfo]

Could you send a screenshot (probably several screenshots) showing your table in design view, so I can see all the fields and their data types. If there is some "production-speak" that might not be clear to a DB guy, please describe what some of the fields might mean. A good explanation ofyour table might go a long way in helping you with your overall project.....

[williamson1979]

Ok I’ll do that. Might be later on.

[twinnyfo]

Sounds great! Thanks for that.

[williamson1979]

Hey, I just attached the db. That might be more helpful. If nothing else easier to see what Im trying to say.

[twinnyfo]

I'll try to take a look at it tomorrow.

[williamson1979]

Thank you. I appreciate the help.

[twinnyfo]

Before I comment on other things, I want you to get something corrected first.

In your Tables, Field names should be brief, but understandable. Preferably you should not have any spaces in these names (as it makes it easier for future reference throughout the DB). Field names DO NOT need to have a prefix--although some people prefer to have them, but I recommend against it. For example, if you have a text field in your Table tblPeople named "LastName", this is very self-explanatory, because you know it is a person's last name.

On your Forms, when you drag and drop fields from a table into a Form, the Controls automatically take on the name of the field (I think this is a grave mistake by Microsoft, and we should have the option to declare what type of prefix we want to affix). However, EVERY TIME YOU ADD A CONTROL TO A FORM, you should rename it with a prefix to that control so that in your code, you know that you are referring to a control and not to a field (and vice versa). It is possible to refer to the fields directly underlying a form. However, if you have a Field named "LastName" and a similarly named Text Box named "LastName" this can cause some ambiguity in the DB--it doesn't always cause problems, but it can. However, if you rename that Text Box to "txtLastName", then whenever you refer to the Control Me.txtLastName, the DB knows exactly what you want to refer to.

So.............. I am going to ask that you rename all of your table Field names first.

[twinnyfo]

The DB will also tell you that "Date" is not an acceptable name for a field, because it is a reserved word (this is a strong hint from MS). Rename it to ProdDate--self-explanatory again: this is the production date.

[twinnyfo]

And......

Just to show you that I am always learning, too, you can teach me something about your Production form.

I have never seen controls placed on a form as "Cells". How did you do that? I'm not sure I have a need for it, but I would like to know about it.

Also, because of how we are doing things, that may or may not be necessary--but for right now, most of your form looks like it works well using that method.

For this thread, we are focusing on the Aggregate Page of your Tab Control and very much of this page will change.

[williamson1979]

When I added the controls I just used the tabular option to align everything. Highlight the controls, right click and layout.

[twinnyfo]

OK - what might seem to be major overhauls to your DB, these were really very small, but, in the long run, hopefully you will be able to see the advantages.

1 - I created a table tblAggTypes. This gives each of the Aggregate types a numerical value, keeping in line with good DB design, and also allows you to select the Aggregate type from other places in your DB.

2 - I created a table tblAggregate. This table logs all the values for your aggregates during production. I removed the "P" or Percent field, because it is a calculated field. Notice the Fields: ProdID, AggType (foreign key to tblAggTypes!), AggU and AggR. These last two fields could be "AggUsed" and "AggRcvd" to make things more clear, but I'll leave that up to you.

3 - I created a very simple query qryAggregateReport. Because your aggregate totals rely on the total tons (from tblProduction), we need to bring that data into the mix (pun intended). Notice that there is no need for your TotalTons field, nor is there any longer a need for your Agg1-10 fields.

4 - I created a super-duper easy form fsubAggregate. All this form does is consolidate all the data that deals with your aggregate values. Its record source is qryAggregateReport. This sub-form has been placed into frmPlantReport on a new page of your tab control. Notice the calculations. Notice its simplicity and elegance. Notice also its absence of overcomplexification with complicated calculations.

This should cover all your needs.

Although, technically, this response does not "answer" your desire for a combo box to set the control source of other text boxes, it is, in my opinion, the answer that you are looking for. Not only this, but this brings your tables into a more standardized, normalized structure that will easily allow for exapansion and future development.

I did not look at all of the information in your Production table, but based upon a cursory glance, it appears that much of what is in that table all pertains to each particular production run. So, you may have a table that requires a considerably greater "width" than most. But, it is not poorly designed simply becuase of that fact.

Let me know i fyou need anything else.

Hope this hepps!

[twinnyfo]

Well, I learned something today about the layout. I guess I just build my forms custom, so I don't even consider trying to lay them out in such a way. Thanks for the tip!

[williamson1979]

Thank you too. I’ll look at what you have done soon as I have access to my computer.

[williamson1979]

Hey Twinny,
I was able to glance at what you did. The aggregates 2 tab you setup with the sub looks like what I was trying to do. I'm interested in looking at it deeper once I get a moment to do so. The drop downs and the ability to add aggregates is great. So just at a glance my aggregate tab would now be obsolete correct?

[twinnyfo]

You are correct.

One change: in the VBA module for the Aggreagate sub-form, add this:

Option Compare Database
Option Explicit
 
Private Sub txtAggU_AfterUpdate()
    Me.Refresh
    Me.txtTotAggP.Requery
End Sub

This will make sure that the total percent is calculated immediately.

[williamson1979]

Awesome. Thanks very much. Like I say it looks like what I wanted to describe to you now I just need to go through it so I can understand how it works.

[twinnyfo]

I'm glad I could get you moving down the track.

Let us know if you have any other questions, comments, moans, groans, gripes, complaints or words of wit....