agwalsh
asked on
Updating combo boxes and using an append query
Hi Folks
I have some very specific questions about the attached database:
1. in the Frm_workcentre_only...the user can pick a workcentre and then the workorder numbers will populate each of the 5 dropdown combo boxes. However, I notice that the selections in the combo boxes (work order 1...5) are not changing when I move onto the next order..how can I fix that so that the choices given in the dropdown reflect the work centre number chosen e.g. if I choose 641040044 in the work centre number I get the correct values in the dropdowns BUT if I go to another workcentre, I still have the 641040044 values in my dropdown.
2. How can I append the values that are selected in this form into a table. I have created an append query called QryAppend_to_Heat_Treat and then applied this to the button on the frm_workcentre_only (Update Records) but it's not appending any records to the Heat Treat table. I've referenced the form controls in this query.
3.
Workorder_database_EE_vers_02.accdb
I have some very specific questions about the attached database:
1. in the Frm_workcentre_only...the user can pick a workcentre and then the workorder numbers will populate each of the 5 dropdown combo boxes. However, I notice that the selections in the combo boxes (work order 1...5) are not changing when I move onto the next order..how can I fix that so that the choices given in the dropdown reflect the work centre number chosen e.g. if I choose 641040044 in the work centre number I get the correct values in the dropdowns BUT if I go to another workcentre, I still have the 641040044 values in my dropdown.
2. How can I append the values that are selected in this form into a table. I have created an append query called QryAppend_to_Heat_Treat and then applied this to the button on the frm_workcentre_only (Update Records) but it's not appending any records to the Heat Treat table. I've referenced the form controls in this query.
3.
Workorder_database_EE_vers_02.accdb
1. The reason that the work orders don't change is because the controls are unbound.
2. The "append" query is actually a "select" query so it doesn't do anything.
3. Consistency in naming will save you many hours over the life of a project. In some cases you use prefixes and in others you use suffixes. I would avoid using spaces in ANY object names and I would NEVER use other special characters except the underscore "_".
Aside from the fact that the design is not normalized (it looks like a spreadsheet rather than a relational database), why are you using an append query rather than a bound form?
Do some reading on normalization to see if you can understand why having 5 workorder numbers in each row is wrong. The Workorders should each be a separate row in a different, related table. You will find that queries, forms, etc will be much simpler to use.
2. The "append" query is actually a "select" query so it doesn't do anything.
3. Consistency in naming will save you many hours over the life of a project. In some cases you use prefixes and in others you use suffixes. I would avoid using spaces in ANY object names and I would NEVER use other special characters except the underscore "_".
Aside from the fact that the design is not normalized (it looks like a spreadsheet rather than a relational database), why are you using an append query rather than a bound form?
Do some reading on normalization to see if you can understand why having 5 workorder numbers in each row is wrong. The Workorders should each be a separate row in a different, related table. You will find that queries, forms, etc will be much simpler to use.
attached: Workorder_database_EE_vers
the data type for WorkCenter is double precision -- this is NOT AN ACCURATE Data Type and should not be used for exact comparisons
I changed this to a Long Integer (actual data type is detmined in the SIZE property in the lower pane), and made it a PK (Primary Key)
Also changed data type in WorkOrders table
For the same reason, I changed data type of WorkOrder to Long Integer.
Removed spaces from names and renamed your tables
Renamed ID in WorkOrders to WOID so it is not ambiguous.
Added records to WorkCentres that were being used in WorkOrders so that referential integrity could be enforced on your relationships. 14 records got added but they look like they might be mistakes because the WorkCenter is not in the same format.
Also added tracking fields to each table for date record was added and edited (this will have to be updated on form BeforeUpdate events).
Created a table called tbl_WorkType and a record for Heat Treat.
Renamed Heat Treat table to Work table. Separated your 5 values across each record into 5 records.
Combined DateModified and TimeModified into one field and put those values into dtmEdit. Actually, there weren't any values -- but that is where they should go. Date and time both track the same thing ... passing of time, just in different units.
Work Table has WOID, which defines Work Centre and Work Order
in looking at the tbl_workorders_workcentre subform, I see you did not include all your tables here ....
created Frm_workcentre_WO and put it on Frm_workcentre (renamed from Frm_workcentre_only)
Well, I did a bunch more too ... made frm_WorkCentre the startup form when the database opens so you can see it first. I also created some fake records.
Hopefully, this will get your project off to a better start.
Workorder_database_EE_vers_03_strive.zip
the data type for WorkCenter is double precision -- this is NOT AN ACCURATE Data Type and should not be used for exact comparisons
I changed this to a Long Integer (actual data type is detmined in the SIZE property in the lower pane), and made it a PK (Primary Key)
Also changed data type in WorkOrders table
For the same reason, I changed data type of WorkOrder to Long Integer.
Removed spaces from names and renamed your tables
Renamed ID in WorkOrders to WOID so it is not ambiguous.
Added records to WorkCentres that were being used in WorkOrders so that referential integrity could be enforced on your relationships. 14 records got added but they look like they might be mistakes because the WorkCenter is not in the same format.
Also added tracking fields to each table for date record was added and edited (this will have to be updated on form BeforeUpdate events).
Created a table called tbl_WorkType and a record for Heat Treat.
Renamed Heat Treat table to Work table. Separated your 5 values across each record into 5 records.
Combined DateModified and TimeModified into one field and put those values into dtmEdit. Actually, there weren't any values -- but that is where they should go. Date and time both track the same thing ... passing of time, just in different units.
Work Table has WOID, which defines Work Centre and Work Order
in looking at the tbl_workorders_workcentre subform, I see you did not include all your tables here ....
created Frm_workcentre_WO and put it on Frm_workcentre (renamed from Frm_workcentre_only)
Well, I did a bunch more too ... made frm_WorkCentre the startup form when the database opens so you can see it first. I also created some fake records.
Hopefully, this will get your project off to a better start.
Workorder_database_EE_vers_03_strive.zip
Hi,
I am assuming since you haven't come back that Access isn't very fun for you ... it gets better!
watch the videos on this playlist:
http://www.youtube.com/playlist?p=PL1B2705CCB40CA4CA
and read this free 100-page book -- lots of screen-shots! so not so many pages ;)
Access Basics
http://www.AccessMVP.com/strive4peace
if you really want to go the extra mile ...
Microsoft Access Basics
https://www.goskills.com/Course/Access-Basics
40 lessons to empower you to build an application with Access. This course is geared to a beginner and includes many advanced topics as the need arises such as importing information from other sources and running action queries to update data in tables. It is designed to put you on the road to becoming a master with Access.
I am assuming since you haven't come back that Access isn't very fun for you ... it gets better!
watch the videos on this playlist:
http://www.youtube.com/playlist?p=PL1B2705CCB40CA4CA
and read this free 100-page book -- lots of screen-shots! so not so many pages ;)
Access Basics
http://www.AccessMVP.com/strive4peace
if you really want to go the extra mile ...
Microsoft Access Basics
https://www.goskills.com/Course/Access-Basics
40 lessons to empower you to build an application with Access. This course is geared to a beginner and includes many advanced topics as the need arises such as importing information from other sources and running action queries to update data in tables. It is designed to put you on the road to becoming a master with Access.
ASKER
Hi @striveforpeace.. thank you very much for all that work. :-) Much appreciated. Now a couple of questions..
1. What table/query shows a list of the workcentre numbers along with the corresponding worknumber orders (and date/time) as chosen on a particular form? That's what the user really wants...a list of the 5 work order numbers chosen for a particular work centre at a particular date/time. That's why the 5 was in it because he reckons there will never be more than 5..
2. Is there some way to add a button to this form (WorkCentre) so the user knows that an entry has been completed?
3. Is there some way to filter the records so that when the first workorder is chosen for a particular workcentre (as per your form WorkCentre), that is excluded from the workorder numbers available?
I know the 5 thing breaks normalisation rules but it is what he wants and I did ask if he ever thought there would be more than 5 and he reckons no. Thanks again :-)
1. What table/query shows a list of the workcentre numbers along with the corresponding worknumber orders (and date/time) as chosen on a particular form? That's what the user really wants...a list of the 5 work order numbers chosen for a particular work centre at a particular date/time. That's why the 5 was in it because he reckons there will never be more than 5..
2. Is there some way to add a button to this form (WorkCentre) so the user knows that an entry has been completed?
3. Is there some way to filter the records so that when the first workorder is chosen for a particular workcentre (as per your form WorkCentre), that is excluded from the workorder numbers available?
I know the 5 thing breaks normalisation rules but it is what he wants and I did ask if he ever thought there would be more than 5 and he reckons no. Thanks again :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help.
you're welcome ~ happy to help
Workorder_database_EE_vers_03.accdb