Combobox value transfer to another frm text box.

[november]

Well I'm creating a database at work for my teams for our yearly cost increases from our suppliers.

This database will have a field for Supplier #, Prod Mgr Code, Rolling 12 COGS, Proposed % Increase, Revised % and Accepted %.

I have created some fields in a query to produce the $ Impact Prop'd/Rev'd/Acct'd based on the ( * COGS), with a tabular form displaying all results.


On the main form the user will pick a team then PM(Product Manager) to view from Comboboxes. After choosing a PM they submit and it should load the 2nd form with the query to display the results. The Combo value of the PM needs to be displayed in a label on form2 and also populate the criteria for the query.

List of necessities:

• Get Cbo.value to display in text box on form2
• Header of form2 should display totals (total cogs for all suppliers listed, total $ impact for each(Prop/Rev/Acct).
• Guidance.

Please let me know if anything is unclear and I will try to explain again the best that I can. Thank you in advance for your help.

 

[John Big Booty]

..... The Combo value of the PM needs to be displayed in a label on form2 and also populate the criteria for the query.

When you say label on form2 I'm presuming you mean the caption ie. the name of the form in the very top left hand corner of the form frame. if this is the case, in the On Load event of your second form, place the following;

Me.Caption = Forms!Form1!Combo2.Column(x)

where;
Form1 is the name of your first form
ComboName is the name of the combo box on your first form that you wish to reference.
x of Column(x) is the column numbe in the combo box you wish to display. Remeber that the columns are numbered from 0 (zero) up and that the first column (clumn0) is often hidden.

For the result of the combo to become part of the criteria of your query simply place

Forms!Form1!Combo2

in the criteria of the field it is to be the criteria for.

 

[november]

Great, I'll try that when I get in.

As for making the summary at the top.

Should I just create a duplicate query and change how it ouputs? Making the values that it ouputs go into the textboxes or labels?

 

[John Big Booty]

........

Should I just create a duplicate query and change how it ouputs? Making the values that it ouputs go into the textboxes or labels?

If your query is returning a number of lines and you just want totals of a/some particular fields. Place a TextBox/es in the header and put the following in their control source;

=Sum(ControlName)

 

[november]

I think I'm in love with you.

Thank you for your help.

I will be back shortly

 

[John Big Booty]

Glad I could help out

 

[november]

I'm back again.

What is a way I can make the COGS update when I close out of and reopen a form? If I change the PM the COGS don't change from the initial load.

I tried putting the code in "gotfocus" but that didn't do anything.

I'm also trying to have the ability to edit fields, like the % of increase to change the $ impacts.

• Make Sums/totals in header update on "re focus" of form.
• Allow editing of fields on form to affect and update $ impacts.

 

[John Big Booty]

I suspect that you will need to force a requery of the underlying data source.

 

[november]

Man, this is great, the query is not working any longer.

It's returning blank results for some reason.

Bah, I hate debugging.

 

[John Big Booty]

Are you able to step back to a point where everything was working? Then step by step re-implement the changes until you isolate the one the caused the problem.

 

[november]

No, but I just created a new continuous form from the query again and copied over my objects. So it's back to working.

Damn computers.

I'm having trouble getting the "SUM" to work with COGS. It's coming up blank. I'll look into this one some more tomorrow.

 

[John Big Booty]

Good work, and good luck

 

[november]

=Sum(ControlName)

When doing so it spits out "#error".

Also, I want to edit data that's showing up in the continuous form. Will I need to make a subform and view as data sheet to enable this?

Please let me know if I need to clarify.

-Nic

 

[John Big Booty]

Is the field you are trying to sum one that is calculated on the form, or one that is produced by the query populating the form?

I've just been having a play with a small DB and found that if I try and sum a field produced by a query no problem. However if I insert a field that I calculate on the form, as soon as I try and sum that field, both sum fields produce a #error rather than a result.

 

[John Big Booty]

From here if you are trying to sum a calculated field.

You are trying to have the text box sum a calculated control in the form or the report. Because Microsoft Access does not store calculated values, Access cannot sum the calculated field.

If the filed you are summing is not calculated then there may be an error in the way you are referencing the field. Make sure you have the correct control name, and be careful with control names that contain embedded spaces.

 

[november]

When I get to work I'll post the formula that I have.

I think I know what to try when I get there tomorrow.

 

[november]

I figured out the reason for the error. I was trying to sum directly from the qry instead of using an item on the field list in expression builder.

I noticed something that bothers me. When you have a cell and it's formatted. When click it shows it's actual value (ie $1,004 can have an actual value of 1004.121543312451.

I don't want the users seeing this.

 

[John Big Booty]

.......

......... When click it shows it's actual value (ie $1,004 can have an actual value of 1004.121543312451.

I don't want the users seeing this.

If you are only interested in the $1,004 portion of the field and don't want the user to see all the decimal places when the click on the filed use

=Int([FieldName])

in the control source on the form

 

[november]

Wow, that makes me feel really stupid.

I really don't remember much from Programming a couple years back. But also, I'm not thinking of solutions as being so simple.

 

[november]

From here if you are trying to sum a calculated field.



If the filed you are summing is not calculated then there may be an error in the way you are referencing the field. Make sure you have the correct control name, and be careful with control names that contain embedded spaces.

So I got all that figured out.

I wanted to know, also. If I have multiple queries that will produce outputs to the same field names, can I use VBA to change the control source of the items?

IE, I want to use the same form the display different query information when a button is clicked.

I will have one for our main suppliers and then any suppliers with a parts program. I would prefer to not have to use 2 forms or have duplicate/overlapping labels or textbox fields for the continuous form.