Entering returned value in table (1 Viewer)

LoveBard

New member
Local time
Today, 16:49
Joined
Nov 24, 2021
Messages
8
I have a form to assemble assets. It calculates the cost of goods line items and then a total COGS combo box tallies the individual line item amounts.

I want to update the Asset table's COGS and Consumables field with the calculated total COGS amount (when the form is saved).

I have the total COGS combo box and I have a summary page on the form for the asset that pulls the total COGS number over, so I have two locations I can add another command.

Can I combine two expressions? I cannot figure out how to do that without getting error messages.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:49
Joined
May 7, 2009
Messages
19,169
you can use an Update Query to update the [GOGS and Consumables] field:

Update [asset table] Set [GOGS and Consumables] = [Forms]![yourFormName]![theTextboxOnForm]
 

LoveBard

New member
Local time
Today, 16:49
Joined
Nov 24, 2021
Messages
8
I am not sure this is quite what I want. I want to open the form, enter the data and calculate the COGS, then save the form and have the calculated field enter into the table. I think running a query is a separate task - I do not know enough about setting it up, but each asset would need to have its own COGS.

As an example, if I assemble a dresser, I take the dresser, hardware, paint, and finish costs $500 and that number should then save to the COGS of the dresser in the Asset table (each asset has its own Inventory number.

I did try creating a query and, like I said, I don't know if it is set up correctly. I will play with it to see, but I would much rather have the calculated total in the form enter into the table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:49
Joined
Oct 29, 2018
Messages
21,358
Are you able to post a sample copy of your db with test data?
 

LoveBard

New member
Local time
Today, 16:49
Joined
Nov 24, 2021
Messages
8
The Assemble Asset form is the one I am working on. Ignore the popups, I have to remove them. They are part of the template.
 

Attachments

  • Assets and Inventory.accdb
    2.3 MB · Views: 249

jdraw

Super Moderator
Staff member
Local time
Today, 18:49
Joined
Jan 23, 2006
Messages
15,364
Is there a dresser in your sample database? It would be easier if we could follow along some theme eg assembling a dresser.... with your post. Your relationships window has no relationships.
Perhaps you could provide a description of the "business" you are trying to support with this database. It is difficult to "guess" what you are trying to do along with "guessing your posted database structure" vs the database required to support the business.
 

LoveBard

New member
Local time
Today, 16:49
Joined
Nov 24, 2021
Messages
8
Thanks, sorry, I was trying to keep the question specific.

I have a furniture refinishing business. I need to track inventory purchases such as paint, decor items (jewelry boxes I refinish, as an example), furniture (before it is painted), hardware, transfer (images that are added to the furniture), and other cost of goods items that I have included in the Category field of the Add Inventory form.

The items are purchased, then added to the Inventory with the Add Inventory Form. Then, once the furniture is complete, it is assembled using the inventory items and calculating how many of each item were used plus the cost of each item. That is calculated on the COGS calculations tab. This total pulls over onto the asset details tab.

Once the item is assembled, then it will be updated as it is posted and sold. That uses the Asset Details form. This will then be a report on the asset that can be printed and used to update my accounting program (Sage 50).

This is all quite basic so far. I am using a template and adapting it. My Access skills are rudimentary - I am watching videos and doing some training to try and improve my understanding. I do not have the relationships set up - I am just figuring out how they all should work.

I used a dresser as an example, but haven't added it. If you open the Assemble Asset form, it is the one that is not working for me. The tab COGS Calculations does work now, to add up the line items (I only have two sample inventory items added to that table). But the total does not post to the Inventory Table COGS and Consumables field - I have the calculated expression and want to also add to the combo box a command to save the information to that field. (the total is both on the COGS tab and the Asset Details tab).
 

mike60smart

Registered User.
Local time
Today, 22:49
Joined
Aug 6, 2017
Messages
1,899
The Assemble Asset form is the one I am working on. Ignore the popups, I have to remove them. They are part of the template.
Hi
I only took a look at the Assets table which has the following points:-

Primary Key is set on a Short text field
Autonumber is not being used as a Primary Key
Values are Hard Coded into fields which are set as Lookup Fields. - Lookup fields are a big No No in Access.
You are using Multiple Unbound DLookups to your Inventory table

You need to look at using a 1 to Many table setup so that when you select an Item in your Main Form you can then add Multiple Items in the Subform.
This way your fields are Bound to tables vice the Multiple DLookups.
 

LoveBard

New member
Local time
Today, 16:49
Joined
Nov 24, 2021
Messages
8
Yes, I am realizing my errors in the tables - I watched a 4 hour series today on setting up Access/beginner intro, which helped me get a better grasp of the database 'rules'. I think I will start from scratch, rather than the template, since it might help me set up only what I need. Thank you for your help on pointing those out.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:49
Joined
Jan 23, 2006
Messages
15,364
I am not sure what videos you watched. I recommend the series by Steve Bishop or
the series by Richard Rost. Links to these are included in the link in my signature Database Planning and Design.

My suggestion is that you start with the 30,000 ft overview of your business. Then work through 1 or 2 of the tutorials from RogersAccessLibrary (also identified in the Database Planning and Design link). He leads you through a process to identify your tables and relationships based on your business description. 45 minutes - 1 hour working through the tutorial will give you the experience you need to design your own database.
 

Users who are viewing this thread

Top Bottom