Expresion on Text Box returning wrong value

[jmonica]

HI,
I'm stuck. I have a form used for taking inventory. One table to lookup items, one for writing values. Tables linked by an ID #. I created a text box on my form to lookup a value called PARQTY in the lookup table. I need to be able to subtract that value from the QTY that is entered into the Qty_Counted box on teh same form when they take inventory.

I created a new Text box and in the control source for it, I entered an expresion that basically says subtract The PARQTY from the QTY_COUNTED it reads = [QTY_COUNTED] - [PARQTY]

The box is subtracting the QTY_COUNTED from the ID # that is linking my two tables and displaying it. Its not supposed to be subtracting the QTY_COUNTED at all. It should be subtracting the PARQTY value that is displayed from the QTY_COUNTED. The PARQTY box does display the right value from the lookup table so I can't figure out why it's pulling that ID number or where from. The rest of the form / program works and we've been using it for a few years. I also created that, but like now, I had to muddle through each and every little thing I did. I usually can sort things out and get them to work, but this is going on for 2 days now to get one simple function added, and I really need help. I have an inventory tomorrow and need this to work for it.

I really hope this all makes sense. Thank You for your help!

 

[missinglinq]

Are any of the Fields involved defined as Lookup Fields at the Table level? If so, have a look at this:

The Evils of Lookup Fields in Tables

Linq ;0)>

 

[jmonica]

Thanks Very much for your reply. Its the only one I received and was very hopeful that it would be the answer to my problem. This seems like a simple task, but obviously I don't know what I'm doing and I seem to be going about thisd the wrong way. In terms of your question as to whether or not it's a lookup field, I'm not sure which one you are referring to. The ID field that links my two tables is not a lookuop field, however the text boxes that I am basing my control source expression are.

With that said, is there a better way (one that will work even if it's convoluted, I'm desperate) for me to accomplish this?
Just to summarize, here's what I;'m trying to do in a nutshell.

I have two tables, one is alookup table and one for writing values for an inventory.

Each item in teh lookup table has a number which is teh level that the item on teh shelf should be at.

I need the program to subtract that number from the number of items they count (enter into the QTY BOX on the form), and display the difference so they know how many to take away.

Doesn't this seem like something that is doable?

Thank You again very much for your help. You can take solice in the fact that you are helping a very desparate person and someone who really needs the help badly. My job depends on getting this to work. Thanks again!

 

[Solo712]

HI,
I'm stuck. I have a form used for taking inventory. One table to lookup items, one for writing values. Tables linked by an ID #. I created a text box on my form to lookup a value called PARQTY in the lookup table. I need to be able to subtract that value from the QTY that is entered into the Qty_Counted box on teh same form when they take inventory.

I created a new Text box and in the control source for it, I entered an expresion that basically says subtract The PARQTY from the QTY_COUNTED it reads = [QTY_COUNTED] - [PARQTY]

The box is subtracting the QTY_COUNTED from the ID # that is linking my two tables and displaying it. Its not supposed to be subtracting the QTY_COUNTED at all. It should be subtracting the PARQTY value that is displayed from the QTY_COUNTED. The PARQTY box does display the right value from the lookup table so I can't figure out why it's pulling that ID number or where from. The rest of the form / program works and we've been using it for a few years. I also created that, but like now, I had to muddle through each and every little thing I did. I usually can sort things out and get them to work, but this is going on for 2 days now to get one simple function added, and I really need help. I have an inventory tomorrow and need this to work for it.

I really hope this all makes sense. Thank You for your help!

How many columns are there in each of the lookup textboxes ? If the values you are looking for are not the bound columns (and it appears they are not) then you need to specify the column number (0 based) in which the value is stored, i.e. sth like

[QTY_COUNTED.Column(x)] - [PARQTY.Column(y)]

Sub the right column values for x and y.

Best,
Jiri

 

[jmonica]

Hello and THANK YOU for your reply. UNfortunately, no joy. I think with this, the problem may be that there are two tables involved so entering the column numbers doesn't work.

I have one table that I call the lookup table. It has the item name, the description and the par level. When an item number is typed in, it populates the form with all oif this data. They then enter a QTY_COUNTED which gets written to a separate table. This table is linked by an ID number to the lookup table. That's the table that the QTY_COUNTED value gets stored.

What I was trying to do is I have the Par_Level appear in a text box on the form and there's a QTY_COUNTED text box on the form where the person enters the Quantity that they count. Once they enter the QTY, I just need to subtract the Par level number (which is appearing in a text box on the form and it works) from the value that they enter in the QTY_COUNTED text box and have it display the difference. Instead its subtracting the qty counted from the ID number of the item and displaying that. GD!!! Why is this not working? I'm SO screwed. I'm not getting many replies on this so maybe it is impossible. I'm thinking maybe it is. Thanks everyone for trying any way. I'm off to the unemployment office!

 

[Solo712]

Hello and THANK YOU for your reply. UNfortunately, no joy. I think with this, the problem may be that there are two tables involved so entering the column numbers doesn't work.

I have one table that I call the lookup table. It has the item name, the description and the par level. When an item number is typed in, it populates the form with all oif this data. They then enter a QTY_COUNTED which gets written to a separate table. This table is linked by an ID number to the lookup table. That's the table that the QTY_COUNTED value gets stored.

What I was trying to do is I have the Par_Level appear in a text box on the form and there's a QTY_COUNTED text box on the form where the person enters the Quantity that they count. Once they enter the QTY, I just need to subtract the Par level number (which is appearing in a text box on the form and it works) from the value that they enter in the QTY_COUNTED text box and have it display the difference. Instead its subtracting the qty counted from the ID number of the item and displaying that. GD!!! Why is this not working? I'm SO screwed. I'm not getting many replies on this so maybe it is impossible. I'm thinking maybe it is. Thanks everyone for trying any way. I'm off to the unemployment office!

Let me assure it's not just 'possible' but you will get this done in a jiffy if you can forget the drama queen shticks ! The reason Access subtracts from the ID of the item is (most likely....95+%) because the text box is a lookup box which is bound by the ID. If you don't specify the value in QTY_COUNTED, Access assumes you want the value of the bound column.

You can ascertain that simply by highlighting the textbox and checking the "Bound Column" property on the Data tab of the Property sheet. Go to the Row Source above. There should be a SELECT statement there. Click on the three dots and find the name for the "ID" in the field. If it appears first - it's in column 0 (Bound Column 1), if second then in column 1 (Bound Column 2), etc.... Now, look for the field name with the "value" in the SELECT sequence of fields. If you find as first, it can be retrieved by writing [QTY_COUNTED.Column(0)] if as a second then [QTY_COUNTED.Column(1)] , ... Place that number in the expression like...

[QTY_COUNTED.Column(0,1, or whatever the column is)] - [PARQTY].

Let us know how you did at the unemployment office !

Best,
Jiri

 

[jmonica]

Thank You once again for your reply. Problem #1. When I check the Data Tab of the TEXT box that I have the expression on, I don't have a Bound To option. I have Control Source, Text Format etc. etc.

I did chuckle at your comment about my drama queen schtick, however, its really that serious at this point based on a few recent other failures and the consequences of me not getting this to work prior to the inevntory we have this Saturday. Nothing is working at this point. I've tried everything I can possibly try. I wish it would let me specify the values in teh form controls and do the calculations based on those instead of whats in the two tables because I'm convinced thats where my problem lies. I'm trying to do a calculation based on values from two separate tables via an expression in teh control source of a text box, so just specifying the column name and number is not going to tell it which table to look in for each value. :banghead: I also have the same column names in the lookup and data tables which probably isn't helping. I sincerely Thank You anyway for trying to help me. I really do appreciate it.

 

[Solo712]

Thank You once again for your reply. Problem #1. When I check the Data Tab of the TEXT box that I have the expression on, I don't have a Bound To option. I have Control Source, Text Format etc. etc.

But that's not the item I am interested in. So no problem # 1 exists as yet ! I have been trying to have you take a look at the QTY_COUNTED control properties. You want to do it ? If not, I am good. You would not be the first one who comes here with the mission of proving his/her problem cannot be solved.

Best,
Jiri

 

[jmonica]

Thanks. Of course I want to try to fix the problem and I'm not trying to prove it can't be fixed. I'm just trying ot get help but all of the replies I've received have not coincided with what I see on my screen, so I must not even be able to explain the problem or what I'm trying to accomplish correctly. I am looking at the Data Tab of the QTY_COUNTED Text Box right now. There's no "Bound to" property for it. Just a "Control Source". I thought only Combo and List boxes have a bound to property but not Text boxes? Thank You for your help.

 

[Solo712]

Thanks. Of course I want to try to fix the problem and I'm not trying to prove it can't be fixed. I'm just trying ot get help but all of the replies I've received have not coincided with what I see on my screen, so I must not even be able to explain the problem or what I'm trying to accomplish correctly. I am looking at the Data Tab of the QTY_COUNTED Text Box right now. There's no "Bound to" property for it. Just a "Control Source". I thought only Combo and List boxes have a bound to property but not Text boxes? Thank You for your help.

Hi jmonica,
some people here don't realize when they create a lookup field in a form they are creating a combo box. At any rate, if QTY_COUNTED is a plain textbox the next item to check is the PARQTY. Is it a lookup field with multiple columns. And if so, is the column involved in the calculation the bound column ?

Best,
Jiri