Parts Quantity and Orders

[adamquestion]

Hello All,

I am new to this forum and learning Access .
The Database
Tables:
Employees
Products
ExpectedQuantity
PartQTY
Parts

Most of the products are similar so they have the same parts for the majority. However they do not all have the same parts. So I created a parts table that contains all the parts needed for any product. Now each Product also can contain multiples of one part (such as wheels) where I would want to list the Product and the parts list and quantity needed of those individual parts for that product. Hence the PartQTY table.

Now what I want to be able to do, is go into a form where each record is one of the products. A subform (datasheet view) for that product that shows all the parts and the quantity of each part that make up the product. Such as 4
wheels, 5 screws, etc for Product A.

I would like there to be an ExpectedQauntity field in the main form which once you found the product you can enter in the number of that product expected. Then this Qauntity would be multiplied by the qauntity of each part required (in the datasheet subform a column with =[expectedQTY]*[PartQTY]) and list next to each part the number needed to order. So if for product A the expected quantity is 50 I would have to order 200 wheels and 250 screws for that product.

A report would Sum each part qauntity after the expectedqauntity has been entered for each product. So I could print a report that tells me how many of each part we need to order based on the expected qauntity of all the products.

I think I am complicating things and would like a fresh mind to assist me in which direction to go.

Thank You,
Adam

 

[neileg]

You have a many to many relationship between Products and Parts. Your parts QTY table needs to hold the primary key from the Products table and the primary key from the Parts table as well as the number of parts needed for that product. A table used in this way is known as a junction table. Do a search in these forums for more information.

Sounds like you are on the right lines.

 

[adamquestion]

I have the many to many relationship and I looked up information that told me to make the FK's from the parts and products tables and make them both PK's in the PartQTY table. I guess my main problem is to be able to enter in an ExpectedQTY into the text box related to the ExpextedQTY table on the main Product Form and for this to enter into my calculation. I would like that ExpectedQTY to be stored in the ExpectedQTY table and when I reopen the form for the next months expectedQTY it would be blank.

My Form has the Products as records (The products list will not change) and an ExpectedQTY textbox.

Subform is a Datasheet of the Parts that make up that Product along with a column that multiplies [ExpectedQTY] by [PartsQTY]

I just want that value to be stored into the ExpectedQTY table with an autoID and then upon re-entry of the DB it is blank and any new ExpectedQTY would generate a new ID and store. The problem now is that it stores the number but when I enter in a new value for the ExpectedQTY it replaces the old ExpectedQTY.

 

[neileg]

What mode are you opening your form in? If you set the Data entry property to Yes, it will open with a new blank record.

 

[adamquestion]

Well I would like for the main form to always be composed of the products and the parts list for that product. So if I have 15 products each with a list of parts, I want them to be visible when using the record selector. I just want the one field, the ExpectedQTY field to be blank each time. I guess I do not have to have a table containing the expectedQTY information so is there a way to have that textbox unbound and still do the calculation in the subform?

 

[neileg]

Yes you can have an unbound text box. I think you will want to set the ExpectedQTY to zero using the On Current property, otherwise the entry in the text box will carry forward to the next record you look at.

 

[adamquestion]

Thanks, that would work nicely. Do you have more information on what code I would enter into the event procedure so that the box corresponds to the record I have selected?

Thanks,
Adam

 

[neileg]

Thanks, that would work nicely. Do you have more information on what code I would enter into the event procedure so that the box corresponds to the record I have selected?

Don't understand. I thought you wanted an unbound text box so I would expect you to be setting this to zero. Do you want to store this value instead so it can be retrieved?

 

[adamquestion]

I apologize, I have been trying so many things that I am confusing myself. This database is so basic. I am taking a forecast for 10-15 models. So I want the models to be listed as records because the models dont change. Each of those models is comprised of parts (majority are the same). I want to be able to enter in the forecasted number for each model and that number multiplies by each individual part giving me a breakdown of parts to order per model. It is essentially a calculator but it makes my life easier.

I have the form set up to give me everything I want but what is annoying me is that i cannot figure out a way to assign a value of 0 to the expectedQTY textbox upon each re-entry. I have tried OnClose and I get a 2448 error, and i tried OnLoad which just sets the first record to 0. The reason I would like to be able to retain the values(and why I have had it bound), while the form is open, is for my report. I have a group section of the parts that group all the same parts and then a calulation that sums the total of each part that I will need to make every model. The report tells me overall how many of each part I will need. That is the main importance of this database, to tell me how many of each part I need based on the forecast.

 

[neileg]

If you want to calculate the parts useage across all models, an unbound box won't do, you're going to have to store the values, even if this is temporary. You can add a field into your products table, you don't need a separate table. I would write an update query to set the values in this field to zero, when you want to reset them. Resetting this value each time you view the record (would be the On Current event) I think will be unhelpful.