Form>Button>Input Box>Append to existing records. Help please

[hightekkrednek]

Good afternoon. My first post here, thank you in advance for any help. I am new to Access and have a very small amount of experience tinkering around in c flavors.. laymans terms is much appreciated. I am trying to write a db to help my wife with her job.




db details..


DB name:blendSchedule
Form Name: frm_currentLoads
Form Source: qry_currentSchedule
qry_currentSchedule source is tbl_silo9


On the form, it has a combo box that is grabbing its rows from qry_currentSchedule(based on int_loadNumber) and only listing those that are not completed(bool_isComplete=false). When a record is selected in the combo box, it populates fields in the form. There is a button on the form called btn_completeLoad.


What I am wanting to do is.. while in a form, press btn_completeLoad that appends input box contents(and a couple other things) to the record selected in qry_currentSchedule.


It should go like this.. select record in form via the combo box.. click btn_completeLoad, input box pops up and requests truck number, enter info and confirm.. if info is entered(!NULL) to str_truckNumber, it should change a yes/no checkbox field(bool_isComplete) in the table to TRUE.. then automatically add the current date and time to another field(dtm_completedTimeStamp) for that record for the moment the load is marked as completed. I would then like it to refresh the qry data in the combobox and in the fields of that form to omit any shown records that are marked as complete.


Please let me know if I jumbled my request up into an incoherent mess or if you could help me with the best route to move forward here. I tried getting the button to run an append query that did this for me but i was never able to get it to associate the currently selected record in the form to the one i was trying to append data to, it kept telling me it was going to append 0 records.. otherwise it almost worked.. either my mistake or completely wrong way to go about it. Best i can tell this will require code and cant be done in macro or in an append qry.. but thats where my ignorance has gotten me so far thus here I am. Thank you for any help.

Dennis

[orange]

To me it's jumbled.
What is your wife's job that the database is intended to support?
What is the issue /problem/opportunity to be resolved with a database?

You telling us about a form to do something that isn't working as you want. The form and accoutrements are part of HOW to do something---we need to understand the something (WHAT) before looking for HOW options.

So simple terms and and overview please.

Welcome to the forum.

[kd2017]

Good afternoon. My first post here, thank you in advance for any help. I am new to Access and have a very small amount of experience tinkering around in c flavors.. laymans terms is much appreciated. I am trying to write a db to help my wife with her job.


db details..


DB name:blendSchedule
Form Name: frm_currentLoads
Form Source: qry_currentSchedule
qry_currentSchedule source is tbl_silo9


On the form, it has a combo box that is grabbing its rows from qry_currentSchedule(based on int_loadNumber) and only listing those that are not completed(bool_isComplete=false). When a record is selected in the combo box, it populates fields in the form. There is a button on the form called btn_completeLoad.

Instead of using vba to populate the controls on the form one would usually set the form's recordsource to the table/query in question and use the combobox to simply navigate to the appropriate row. To use a combobox to navigate a form I personally use the 'alternative' code in David-W-Fenton's answer here https://stackoverflow.com/questions/...obox-in-access

What I am wanting to do is.. while in a form, press btn_completeLoad that appends input box contents(and a couple other things) to the record selected in qry_currentSchedule.

Instead of using a button to run an append query, since your form and controls are bound to a recordset, all you simply need to do is check if the form is "dirty" and if it is then set dirty to false and the record will be saved.
Put this in your button's click event:

If Me.Dirty = True Then Me.Dirty = False

It should go like this.. select record in form via the combo box.. click btn_completeLoad, input box pops up and requests truck number, enter info and confirm.. if info is entered(!NULL) to str_truckNumber, it should change a yes/no checkbox field(bool_isComplete) in the table to TRUE.. then automatically add the current date and time to another field(dtm_completedTimeStamp) for that record for the moment the load is marked as completed. I would then like it to refresh the qry data in the combobox and in the fields of that form to omit any shown records that are marked as complete.

After Me.Dirty=False per above fires, it will fire the form's Before Update event. This is where you can validate any user input and add a time stamp, mark complete, etc. before committing the changes to the database.

Please let me know if I jumbled my request up into an incoherent mess or if you could help me with the best route to move forward here. I tried getting the button to run an append query that did this for me but i was never able to get it to associate the currently selected record in the form to the one i was trying to append data to, it kept telling me it was going to append 0 records.. otherwise it almost worked.. either my mistake or completely wrong way to go about it. Best i can tell this will require code and cant be done in macro or in an append qry.. but thats where my ignorance has gotten me so far thus here I am. Thank you for any help.

Dennis

You are welcome to zip up your database project and attach it for review.

[hightekkrednek]

My apologies. She manages all incoming and outgoing material production and shipping of a large international medical supply manufacturer and relies heavily on Excel right now. The information that is being tracked in this case is relative to loads that are shipped out around the world each month for just a single production line. I am just setting her up to use this option rather than Excel, as it has been limiting her and formatting/formulas get messed up constantly in it. I picture this turning into a replacement tool for many of her other Excel worksheets as I gain experience in setting it up. I just tried to upload the db file but it is about 900k which seems to exceed the 500k limit for uploads. In Access she will be importing data into a table(tbl_silo9) from an excel file that is exported from another tool. The table has things like schedule date, load number, lbs ordered, how many pallets and to which country it is going. i have also added a yes/no field to show whether the load has been shipped yet(bool_isComplete), and another to show whether it is late. The late field doesnt really do anything in the table, but in qry_currentSchedule it does a [d_scheduledate]<date() so you see a check next to the unshipped late items.. otherwise that qry is just a field for field of tbl_silo9.

Essentially, the shipping folks open frm_currentLoads when they complete a load.. select the load they just completed.. press a "complete load" button on that form.. this is as far as i have gotten in setting it up.. this next part is all i need help with, having a hard time deciding the right method to use to accomplish this.. i need it to then ask for a truck number(input box?), validate that a truck number was entered(no formatting, just not null) and store it in str_truckNumber in tbl_silo9.. add a true statement to the bool_isComplete in tbl_silo9, and add a timestamp to dtm_completedTimeStamp again in tbl_silo9. I hope this clarified this.. they say one must thoroughly understand something to explain it well to others.. I'm pretty clueless.. lol.

[hightekkrednek]

attaching zipped file, thanks

blendscheduleSilo9public1.zip

[orange]

In Access she will be importing data into a table(tbl_silo9) from an excel file that is exported from another tool.

So are you saying "she's in the middle". Somewhere in the organization/factory things are done and a file (Excel) is sent to her for related processing. What I am asking about is the scope of her involvement and any future plans to reduce dependence on Excel etc.

You have primary key on most of your tables which is a good thing. It would seem that tbl_silo9 should also have a PK. I would suggest the Access autonumber data type rather than short text for your PKs, but I don't know any of the details that you may be constrained with.

The Pallets subject comes up from time to time with respect to "how many pallets are required for this load?"
Usually a number of boxes or containers of a certain size or weight need to be shipped. What i s the minimum number of pallets of size X are required? You can find some of these pallet-related threads if you search the forum. It may give you some insight- if determination of pallet size/number is relevant to your wife's involvement. It seems your wife gets the number of pallets supplied with the incoming info and product--is this correct?

I don't see a customer per se nor a specific product/part nor specific order details. That's why I'm asking about scope. Are you working on a piece of a larger "puzzle" or will it be some staged refinement of what you are undertaking at the moment.

Anyway enough general questions/thoughts for now.

As kd2017 suggested, the late David Fenton has left us many great tidbits of code and thinking.

Good luck with your project.

[hightekkrednek]

The tables, elements, qrys and forms that are in it are all that will be relative to this project.. no future considerations to worry about in this project. Even if the same db was to be used for other stuff they will not correlate in any way with anything discussed here. She has custom software already for most of her stuff, these tasks that I will be porting over form excel to access are the few menial tasks she must track and don't justify them hiring a programmer for a custom app. Hence the honey-do.

There is a pk in tbl_silo9, its int_loadNumber as it needs to be.

She knows her pallet calculations off the top of her head and does all of them, they aren't given to her. The pallet field is also used(and will continue to be) for bulk loads as well.. if its a bulk load, 1=1 truck, not 1 pallet.

These records do not require customer information or any other data. The process has been established and served its purpose as it is designed, we are just porting the exact same process over to Access as what the shipping department is used to in excel.. they(like so many) seem to fear change. The need here is very specific..

"...... press a "complete load" button on that form.. this is as far as i have gotten in setting it up.. this next part is all i need help with, having a hard time deciding the right method to use to accomplish this.. i need it to then ask for a truck number(input box?), validate that a truck number was entered(no formatting, just not null) and store it in str_truckNumber in tbl_silo9.. add a true statement to the bool_isComplete in tbl_silo9, and add a timestamp to dtm_completedTimeStamp again in tbl_silo9."

I appreciate the input, I hope my response helps in under complicating the request. Ill check out David Fenton

[hightekkrednek]

KD2017 Thank you, I will look over this and see if I can make it work on my end tonight. Very much appreciated!

[kd2017]

I've reviewed your db and I've made minimal changes necessary to achieve what you have asked for.
I created an update query, not an append query, that will update the truck number, time stamp, and isCompleted boolean of which ever int_loadNumber the form is currently on.

I would suggest you change the primary keys of your lookup tables to numbers. I wonder if the isCompleted boolean is necessary, couldn't you check if a load is incomplete by checking if the timestamp is null? Also, I would suggest you append any primary or foreign key fields with ID, or PK, or FK, so everyone can more quickly identify those key fields eg: LoadNumberId. Finally, make sure your date fields that you're searching on are indexed, i forgot to check??

[hightekkrednek]

kd2017.. That was perfect! exactly what i needed. i was messing up with which type of query i was running by the looks of it. Thank you for your time and suggestions. You made it look so simple.. between the qry and the 2 lines of code. thank you again.

[orange]

Glad you have the database issue resolved. Good luck with your project.