I haven't made an access database for the best part of 20 years and I'm struggling, I'm hoping you guys can help me! The information I'm working with has been gradually migrated from paper to excel and now I'm wanting to make it a bit more joined up so that we can do searches, create reports etc. Basically automate tasks which people currently go and look stuff up to do then type it.
So I have a table with a list of moulds that the company owns, some we only have 1off, some we have multiple off, so lets for example say that table might be;
A1 - Mould number 1 to make part A
A2 - Mould number 2 to make part A
A3 - Mould number 3 to make part A
B1 - Mould number 1 to make part B
B2 - Mould number 2 to make part B
etc etc. The table has other data relating to the moulds but that's not relevant
Then I have a table of products, this has the products code, description, drawing number etc etc then a drop down box with tick boxes so when you add a product you can select which moulds can make that part. So;
X123 - Part X 123 - A1, A2, A3
Y123 - Part Y 123 - B1, B2
Then I have a third table where operators (will?!?) record the daily quality control tests they do on a product. So there is a drop down box where they can select the product code which is pulled from the product table, the fields to record the results of the tests and a field for the mould which was used to make the part. So;
X123 - test 1 - test 2 - test 3 - mould used
Y123 - test 1 - test 2 - test 3 - mould used
Now here is where I am struggling. I want the mould used field to be a drop down options box. We know from the product table which moulds can be used to make that part so that is what I want the drop down options to be. How can I make access populate that drop down box with the ticked items from the product table based on the product code which they have selected?
I'm trying to do it in the table but if there is no easy way maybe I will have to do it in a form? I want to use forms for data entry when I get that far to make it easier for people to use but I was hoping this could be done in the table?
So I have a table with a list of moulds that the company owns, some we only have 1off, some we have multiple off, so lets for example say that table might be;
A1 - Mould number 1 to make part A
A2 - Mould number 2 to make part A
A3 - Mould number 3 to make part A
B1 - Mould number 1 to make part B
B2 - Mould number 2 to make part B
etc etc. The table has other data relating to the moulds but that's not relevant
Then I have a table of products, this has the products code, description, drawing number etc etc then a drop down box with tick boxes so when you add a product you can select which moulds can make that part. So;
X123 - Part X 123 - A1, A2, A3
Y123 - Part Y 123 - B1, B2
Then I have a third table where operators (will?!?) record the daily quality control tests they do on a product. So there is a drop down box where they can select the product code which is pulled from the product table, the fields to record the results of the tests and a field for the mould which was used to make the part. So;
X123 - test 1 - test 2 - test 3 - mould used
Y123 - test 1 - test 2 - test 3 - mould used
Now here is where I am struggling. I want the mould used field to be a drop down options box. We know from the product table which moulds can be used to make that part so that is what I want the drop down options to be. How can I make access populate that drop down box with the ticked items from the product table based on the product code which they have selected?
I'm trying to do it in the table but if there is no easy way maybe I will have to do it in a form? I want to use forms for data entry when I get that far to make it easier for people to use but I was hoping this could be done in the table?