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
LVL 1
agwalshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
I don't understand your idea: on your form you can select only one set of orders for each workcentre. If it is correct, look at sample.
Workorder_database_EE_vers_03.accdb
PatHartmanCommented:
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.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
attached: Workorder_database_EE_vers_03_strive4peace.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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
agwalshAuthor Commented:
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 :-)
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the form that opens is called Frm_workcentre.  Its recordsource is tbl_WorkCentres

1. there is a subform called Frm_workcentre_WO_sub with recordsource that gets everything from tbl_Work and just one field from tbl_WorkOrders -- WorkCentre so it can be linked on a field it doesn;t actually contain.

LinkMasterFields and LinkChildfields for the subform control are WorkCentre, which is named the same wherever it is used.

2. the entries can be counted on the AfterUpdate event of Frm_workcentre_WO_sub.  If 5 records have been added, the user can be notified.

on 3, the combobox selections can be filtered.

Please take time to read the notes I gave you and understand what has been set up ~

thank you and you're welcome

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agwalshAuthor Commented:
Thanks for the help.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.