Update a Record with a matching Listbox value.

[Gicu]

Here you are, please try it now.



Cheers,
Vlad

[akeller]

Here you are, please try it now.

Cheers,
Vlad

Only one Work Order is populating the lstAvailable box and the Hold (Yes/No) field on tblIncomingJobJoin is not updating to place the jobs on hold. Also I noticed the AddAll and RemoveAll buttons only work if you select the orders. Can this be done by just clicking the button without any selections?

Thanks again!

[Gicu]

Sorry, I think I got it now, had to dig up some of your older posts to get the relationships. See how it works now and let us know.

Cheers,
Vlad

[akeller]

Sorry, I think I got it now, had to dig up some of your older posts to get the relationships. See how it works now and let us know.

Cheers,
Vlad

Awesome...So far that works great!
Thank you so much!

[Gicu]

Glad to help! Good luck with your project!

[akeller]

Glad to help! Good luck with your project!

Vlad,

I was hoping you could once again help me out.
I'm trying to update tblNCRs with frmNCRs_D. The form has 3 comboboxes and 2 textboxes all are bound to tblNCR. I copied your example and added two listboxes to this same form that populate the Work Orders available to select. They populate a temp folder like your original example.

The form creates the records in tblNCR with the bound controls, but I now need to update the empty WorkOrderID_FK fields in the newly created records that have a matching NCRID_FK.
I have tried a few different queries but am unable to gather the workorderids from the tmpIncomingJobNCRUpdater table and place them into tblNCR.

Attached is a stripped version.

Here are the tables, forms, and queries that I'm trying to use.
tblNCRs
tmpIncomingJobNCRUpdater
qryInWorkNCR_SelectedJob
qryAppend_to_tmpNCR
qryUpdateNCR
qryNCRWorkOrder
qryRejectNCR
frmNCRs_D

Again, after stripping the db of all data and entering a test data, the Inwork tab is not displaying the query results. I assume it has to do with not having a valid key field like you stated before.

Thanks!

qry

[Gicu]

Please have a look at the updated file, I have fixed some errors on the InWork tab of frmMain (one control on the subform had a non-existing control source + I updated the record source query to remove on of the links to the tblDepartment table). Have a look at my comments in the code behind the Save button on frmNCRs_D. You will need to provide more info regarding the intended structure, you can use some Excel mock-ups to show how the final data should look like.
You can send me a private message if you wish with that info.

Cheers,
Vlad

[Gicu]

Have a look at this one and see if it is closer to what you want.

Cheers,
Vlad

[akeller]

Not quite what I was expecting.
I forgot to mention that each NCR will default to "Set Disposition" status upon creation and then can change upon further review. I plan to have another form that allows for changes to the created NCRs and the layout and function that you displayed will work perfectly for that form.
As for this one, I'm hoping to have it as user friendly as possible with the least amount of selections.
Every NCR created on an order will have exactly the same information applied to it other than the comments.
That is why I was hoping to make the work order selections from a multi select listbox and then the other selections from combo and txtboxes.

[Gicu]

Try this one.

Cheers,
Vlad

[akeller]

Try this one.

Cheers,
Vlad

Great, that was working except it was not adding the WorkOrderID_FKs to the added records on tblNCR.

I worked on this some today and changed the frmNCRs_D form as unbound along with a new query(qryNCRWorkOrder) that pulls the WorkOrders from the tmpIncomingJobNCRUpdater table.
I then nested that query into an Append Query(qryAppend_ADD_NCR). This updates the tblNCRs perfectly(It appears that way to me, but I'm a newbie).
After the Append query is ran, but before frmNCRs_D is closed, I again run your qryUpdateNCR to set the appropriate columns in tblIncomingJobJoin.
This works great, but I need to modify qryUpdateNCR to populate the NCRID_FK on tblIncomingJobJoin as well.

Attached is my version.

Do you see any functionality issues or a possibility of any in the future with my version?

[Gicu]

Hard for me to follow your process, it seems a bit over-complicated but sometimes they are...Usually I would expect a "job" to have multiple items (=components) and to be able to be split (or combined) in multiple "work orders" (which would assume that all components of a job would be included in a multi-job work order, otherwise all tracking should be downgraded to the item or component level) and the rest would be tracked via a "status" attribute (NCR, OnHold, Claimed, etc.). But again it is your process and you probably have spent a lot more time thinking about these things.

Please have a look and see if the query works for you now.

Cheers,
Vlad

[akeller]

Hard for me to follow your process, it seems a bit over-complicated but sometimes they are...Usually I would expect a "job" to have multiple items (=components) and to be able to be split (or combined) in multiple "work orders" (which would assume that all components of a job would be included in a multi-job work order, otherwise all tracking should be downgraded to the item or component level) and the rest would be tracked via a "status" attribute (NCR, OnHold, Claimed, etc.). But again it is your process and you probably have spent a lot more time thinking about these things.

Please have a look and see if the query works for you now.

Cheers,
Vlad

Works perfectly! Thank you for all the help!

And yes, what I thought would be easy and it has turned into a major headache. After thoroughly dissecting our processes during this DB construction, I realized how much work it actually takes. Not to mention at the start of this project, I had no idea how to use Access, let alone understand VBA or SQL code. I have learned a lot and still have so much to learn but it has been fun.

Again thank you for everything!

[Gicu]

Glad to hear you got it working! I started working with Access 2.0 in 1995 to replace some paper-based processes in my (government) office and never looked back, it is fun and exciting and, done right, it is invaluable in every organization (even if some IT people will say otherwise)...

Good luck!
Vlad