Code to Update table

[BRANDON99]

Hello

I have not used Access for a few years and have never used much code in the past. I am creating a db to calculate the cost of building materials for a house restoration project. I have three tables, BOM, Specification and Room Size. As an example, I want to update the BOM table with the quantity of floor boards required. I have a form with check boxes, if the room need floor boards I check the box, Replace_Floor_Boards. I want to update the BOM, field Floor Boards table with the data. In this example this would be the width of the room (From RoomSize tbl) / width of Floor Board from Specification Tbl * Room Length, from the room size tbl. I know I could achieve this with a query, but as there are many different materials, I think it would be much better run from code.

Can anyone please point me in the right direction, I am sure that with a little help I can get going again

Many thanks

Stephen

 

[fneily]

Please post your table structures (field names, primary key). The reason I'm asking is that you don't store calculated values in Access tables. They're calculated on the fly for presentation in forms or reports. So it seems you need a junction table but I can't be sure without seeing the table structures. Also, please use the proper "order of operations" convention in your algebraic statement, it's kinda confusing.

 

[BRANDON99]

Hi

Thank you for your reply.
These are the tables
I have only shown basic fields as the rest are just for different materials etc.
RoomSize
Room Text Primary
Length Double
Width Double
Height Double
Specification

ID Autonumber Primary
FloorBoardWidth Double

BOM

Room Text Primary
FloorBoards yes/no
FloorBoardsqty Double

I have a form based on the BOM table, each material has a check box . When the box is checked I want to get the result of the room width divided by the Floorboardwidth multiplied by the room length to give the total amount of floor boards required.
There is no relationship between the Specification tbl and the other two

I have a rough idea of how to achieve this
If Floorboards = True then
Floorboardqty = (roomwith/floorboardwidth)* roomlength
I have just totally forgotten the correct way to name the fields etc correctly
Can you please help with this?

Thanks

Stephen

 

[fneily]

Some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You'll see you don't store, nor do you need to in this case, calculations.

Based on normalization, you'll have your main two tables:
tblRoomSize and tblSpecification. They look like:

tblRoomSize (Same as yours)
RoomName (primary key)
Length
Width
Height

tblSpecification(Same as yours)
SpecID (primary key)
FloorBoardWidth

Now a room could have many specifications. A specification can be used for many rooms. This is a many-to-many relationship that relational database don't like. So you'll make a junction table. At minimum, this table will have the primary keys of the other two tables, and any COMMON fields.
So that's your BOM.
tblBOM
BOMID (primary key)
RoomID
SpecID

The tables are connected in a query: tblRoomsize to tblBOM to tblSpecification through the keys. You can then base a form or report off of this to see your Floorboardqty. Let's say for a form, then you'd show your fields roomname, roomwidth, roomlength, floorboardwidth. Then you'd have an unbound textbox (name it floorboardqty). On the Oncurrent event of the form, you'd have:
Me![floorboardqty] = (Me![roomwith]/Me![floorboardwidth])* Me![roomlength]
Now as you go from record to record it'll calculate what you want.

Notice you don't need a yes/no indicator because the tblBOM only contains rooms that need floorboards.

 

[BRANDON99]

Hi

Thanks for the help, I've changed my tables and realise I was approaching this incorrectly (think it out first). I have taken your advice and this gives what I wanted but much simpler (easyier)
Works a treat!!
Thanks

kind regards

 

[BRANDON99]

hI

Just one more thing - the first row works great, it show second row as zero's - I think it needs to loop through the table - can you help with this?

thanks