Expression doesn’t work (Access XP)

Topic

New Reply

I have an expression with subtracts Quantity Taken from Quantity Received to = Units on Hand as follows Expr1: Sum([QuantityIn]+[QuantityOut]) This expression does what I want it to do if I enter the values directly into my Inventory Transaction Table, however, if I enter the transactions using the form I’ve created the expression doesn’t work (eg. values from the form will give a result as follows QuantityIn = 10, QuantityOut = -5 Units on Hand = 10 where as if I enter the same values (10, -5) directly into the Inventory transaction table, the Units in Hand will be right (5). What do I need to change on the form?

Thanks in advance for any help.

Christa

Reply | Quote

Replies

There is not enough detail to determine what is wrong.
Where do you have the calculation? Do you refresh the form after the calc?

Reply | Quote

The calculation is in a query and since I don’t know what you mean by “refresh the form”…I probably don’t. How would I do that? (Sorry, I’m a real newby at Access).

Thanks,

Reply | Quote

I don’t understand what you are doing.
You will have to describe a lot more about the form, what is the source of the form? Do you use the query you mentioned as the source of the form?
Do you store the calculated value in the table that is part of the source behind the form?

Failing all this, why don’t you post a zipped version of your database, taking out the sensitive bits, so we can all assess what you are doing.

Reply | Quote

I don’t understand what you are doing.
You will have to describe a lot more about the form, what is the source of the form? Do you use the query you mentioned as the source of the form?
Do you store the calculated value in the table that is part of the source behind the form?

Failing all this, why don’t you post a zipped version of your database, taking out the sensitive bits, so we can all assess what you are doing.

Reply | Quote

Here is a zipped version of my database.

Thanks,

Christa

Reply | Quote

Christa,

I modify your query Units on Hand with Nz function to get the right quantity on hand.
In the form Categories and products, I add a textbox Quantity on hand with a DLoopUp function to get the number.
Hope this is what you want.

Reply | Quote

Thank you very much!!!

A couple more questions…is there any way to force the entry of a negative number into the “Quantity Taken” box? And how can I make the Categories and Products form go to the last record (or a new blank record) when the form is opened?

Thanks again,

Christa

Reply | Quote

In the design of the table select the quantity taken field.
In the Validation rule enter :
<0
In the Validation text enter:
Please enter a negative number
or what ever text you want to appear in the message box.

For the New blank record, set the Data Entry property of the form to Yes.
Or if you want to be able to browse the existing records, then you could enter the following code in the on load event of the form:
Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, "Categories and Products", acNewRec
End Sub

Reply | Quote

Thank you!

Reply | Quote

Thank you!

Reply | Quote

I added the code from the last post to my form and it worked great.
Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, “Categories and Products”, acNewRec
End Sub
Now I’ve changed the form and added two more tables “Inventory Transactions” and “Location”. For some reason the code advancing the records to a new one no longer works. Is there a limit to the amount of tables one can use together with this code. The error I get is Run-time error ‘2105’

Reply | Quote

Error 2105 means that Access can’t go to the specified record. Perhaps the addition of the two tables has made the form non-editable. Can you go to a new record manually?

Reply | Quote

You are right…it’s uneditable…I can’t go to a new record manually. Is there anything I can do about that (and still keep the 2 additional tables)?

Thanks,

PS. I’ve tried to delete some records from the database in order to make it small enought to attach but I can only get it down to 117KB.

Reply | Quote

If you have a query based on multiple tables, and you want the query to be updateable, there must be a unique index on the field on the “one” side of the join between two tables.

See post 411702 for instructions on how to attach a database.

Reply | Quote

I’m trying to join 4 tables…I’ve tried to do 2 separate queries and then join the queries…but I still can’t get it to work. I’ve attached a copy of the database. The form in question is called “Inventory”.

Thanks in advance for any help.

Christa

Reply | Quote

The relationships between your tables are not quite correct.

The Inventory Transactions table does not need a CategoryID field, since CategoryID follows from ProductID. Having CategoryID in Inventory Transactions led to a circular relationship in the record source of the form.

Some other points:
– The primary key in the Location table should be on Location Number, and the corresponding field in Inventory Transactions should therefore be numeric. The relationship between these tables should be on Location Number. You don’t need the Location table in the record source of the form. It’ll serve as row source of the Location combo box.
– You should use ProductID from Inventory Transactions in the record source, not from Inventory.
– The controls bound to fields from Inventory and Categories should be locked. You only should modify fields from Inventory Transactions in this form.

See attached.

Reply | Quote

Hans…thanks for your suggestions. Unfortunately I have another form (which I had deleted from the previous attachment to compress the size of the database) which needs the Category ID field in the Inventory transactions table. Ultimately what I’m trying to accomplish is to use the Inventory form to record additions to the Inventory and use the Inventory transactions form to record ‘subtractions’ or ‘inventory sold’. Is there a better way to accomplish this?

I also have another question. I have a report based on the Units on Hand query. It works fine as long as every field in the query contains text or numbers (is not left blank). This is not a problem for any of the fields except the Note field which is not required with every record. However, I’m finding that if the notes field is left blank when, for instance, I record a receipt of a product (Quantity Received) and filled in when I record the use of that product (Quantity Taken), the total expression in the units on hand query does not work and I get 2 lines appearing on the report for that product instead of just the one that I want. (In the attachment, I’ve got “Type Note Here” in all the notes fields…but that’s not a good solution). Is there any way to ‘hide’ a character in the Notes field to trick the query into thinking something is there and still having the note appear blank?

I know I’m asking a lot of questions…this is a learning work in progress for me. I apologize. I am taking some courses but just haven’t gotten to the stage in them where these questions are answered.

Thanks,

Christa

Reply | Quote

A) You still don’t need CategoryID in the Inventory Transactions table, since it is superfluous – it follows from ProductID. One of the basic rules of relational database design is that you should avoid storing the same information twice. You can create a query based on Inventory Transactions and Inventory to retrieve the category belonging to each product.

I don’t understand your second question. In fact, I don’t understand the Units on Hand query. Why is it a Totals query? It returns as many records as there are in Inventory Transactions, so nothing is summarized.

Reply | Quote

Thanks…

A) So the best thing would be to redesign my form called “Categories and Products” based on a query instead of two tables? (If I take the Category ID field out of the inventory transactions table my drop down box for Category on the Categories and Products form no longer works.)

Sorry about that. In order to consolidate the database to make it small enough to attach, I deleted the records that show how the Units on Hand query and the report work (or don’t work). I’ve added some records into this attachment to illustrate what I was talking about. If you look at the query and the report you will see that the Black Koskin Briefcase (under other merchandise – category 5) shows up twice….once as units received and once as units taken because the notes field in the units taken “record” is blank. I would like these to add together so that the report shows that there is a total of 5 units on hand. If I put something in the blank notes field…this works…that’s why I was wondering if I could “hide” text in there.

Thanks,

Reply | Quote

A) In fact, the form can still be based on the table alone. You can make the Categories combo box unbound, and write some code to keep it up to date as the user moves from record to record:

Private Sub Form_Current()
If IsNull(Me.ProductID) Then
Me.Categories = Null
Else
Me.Categories = DLookup(“CategoryID”, “Inventory”, “ProductID = ‘” & Me.ProductID & “‘”)
End If
Me.Products.Requery
End Sub

Do you really need the Note field in the Units on Hand query and report? If you leave it out, you’ll be rid of the problems.

Reply | Quote

A) In fact, the form can still be based on the table alone. You can make the Categories combo box unbound, and write some code to keep it up to date as the user moves from record to record:

Private Sub Form_Current()
If IsNull(Me.ProductID) Then
Me.Categories = Null
Else
Me.Categories = DLookup(“CategoryID”, “Inventory”, “ProductID = ‘” & Me.ProductID & “‘”)
End If
Me.Products.Requery
End Sub

Do you really need the Note field in the Units on Hand query and report? If you leave it out, you’ll be rid of the problems.

Reply | Quote

Thanks…

A) So the best thing would be to redesign my form called “Categories and Products” based on a query instead of two tables? (If I take the Category ID field out of the inventory transactions table my drop down box for Category on the Categories and Products form no longer works.)

Sorry about that. In order to consolidate the database to make it small enough to attach, I deleted the records that show how the Units on Hand query and the report work (or don’t work). I’ve added some records into this attachment to illustrate what I was talking about. If you look at the query and the report you will see that the Black Koskin Briefcase (under other merchandise – category 5) shows up twice….once as units received and once as units taken because the notes field in the units taken “record” is blank. I would like these to add together so that the report shows that there is a total of 5 units on hand. If I put something in the blank notes field…this works…that’s why I was wondering if I could “hide” text in there.

Thanks,

Reply | Quote

A) You still don’t need CategoryID in the Inventory Transactions table, since it is superfluous – it follows from ProductID. One of the basic rules of relational database design is that you should avoid storing the same information twice. You can create a query based on Inventory Transactions and Inventory to retrieve the category belonging to each product.

I don’t understand your second question. In fact, I don’t understand the Units on Hand query. Why is it a Totals query? It returns as many records as there are in Inventory Transactions, so nothing is summarized.

Reply | Quote

Hans…thanks for your suggestions. Unfortunately I have another form (which I had deleted from the previous attachment to compress the size of the database) which needs the Category ID field in the Inventory transactions table. Ultimately what I’m trying to accomplish is to use the Inventory form to record additions to the Inventory and use the Inventory transactions form to record ‘subtractions’ or ‘inventory sold’. Is there a better way to accomplish this?

I also have another question. I have a report based on the Units on Hand query. It works fine as long as every field in the query contains text or numbers (is not left blank). This is not a problem for any of the fields except the Note field which is not required with every record. However, I’m finding that if the notes field is left blank when, for instance, I record a receipt of a product (Quantity Received) and filled in when I record the use of that product (Quantity Taken), the total expression in the units on hand query does not work and I get 2 lines appearing on the report for that product instead of just the one that I want. (In the attachment, I’ve got “Type Note Here” in all the notes fields…but that’s not a good solution). Is there any way to ‘hide’ a character in the Notes field to trick the query into thinking something is there and still having the note appear blank?

I know I’m asking a lot of questions…this is a learning work in progress for me. I apologize. I am taking some courses but just haven’t gotten to the stage in them where these questions are answered.

Thanks,

Christa

Reply | Quote

The relationships between your tables are not quite correct.

The Inventory Transactions table does not need a CategoryID field, since CategoryID follows from ProductID. Having CategoryID in Inventory Transactions led to a circular relationship in the record source of the form.

Some other points:
– The primary key in the Location table should be on Location Number, and the corresponding field in Inventory Transactions should therefore be numeric. The relationship between these tables should be on Location Number. You don’t need the Location table in the record source of the form. It’ll serve as row source of the Location combo box.
– You should use ProductID from Inventory Transactions in the record source, not from Inventory.
– The controls bound to fields from Inventory and Categories should be locked. You only should modify fields from Inventory Transactions in this form.

See attached.

Reply | Quote

I’m trying to join 4 tables…I’ve tried to do 2 separate queries and then join the queries…but I still can’t get it to work. I’ve attached a copy of the database. The form in question is called “Inventory”.

Thanks in advance for any help.

Christa

Reply | Quote

If you have a query based on multiple tables, and you want the query to be updateable, there must be a unique index on the field on the “one” side of the join between two tables.

See post 411702 for instructions on how to attach a database.

Reply | Quote

You are right…it’s uneditable…I can’t go to a new record manually. Is there anything I can do about that (and still keep the 2 additional tables)?

Thanks,

PS. I’ve tried to delete some records from the database in order to make it small enought to attach but I can only get it down to 117KB.

Reply | Quote

Error 2105 means that Access can’t go to the specified record. Perhaps the addition of the two tables has made the form non-editable. Can you go to a new record manually?

Reply | Quote

I added the code from the last post to my form and it worked great.
Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, “Categories and Products”, acNewRec
End Sub
Now I’ve changed the form and added two more tables “Inventory Transactions” and “Location”. For some reason the code advancing the records to a new one no longer works. Is there a limit to the amount of tables one can use together with this code. The error I get is Run-time error ‘2105’

Reply | Quote

In the design of the table select the quantity taken field.
In the Validation rule enter :
<0
In the Validation text enter:
Please enter a negative number
or what ever text you want to appear in the message box.

For the New blank record, set the Data Entry property of the form to Yes.
Or if you want to be able to browse the existing records, then you could enter the following code in the on load event of the form:
Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, "Categories and Products", acNewRec
End Sub

Reply | Quote

Thank you very much!!!

A couple more questions…is there any way to force the entry of a negative number into the “Quantity Taken” box? And how can I make the Categories and Products form go to the last record (or a new blank record) when the form is opened?

Thanks again,

Christa

Reply | Quote

Christa,

I modify your query Units on Hand with Nz function to get the right quantity on hand.
In the form Categories and products, I add a textbox Quantity on hand with a DLoopUp function to get the number.
Hope this is what you want.

Reply | Quote

Here is a zipped version of my database.

Thanks,

Christa

Reply | Quote

The calculation is in a query and since I don’t know what you mean by “refresh the form”…I probably don’t. How would I do that? (Sorry, I’m a real newby at Access).

Thanks,

Reply | Quote

There is not enough detail to determine what is wrong.
Where do you have the calculation? Do you refresh the form after the calc?

Reply | Quote