Button to reset an hourly production log

[bryanm694]

Hello,

I currently have a continuous form in my database that has 24 records in it. every record is blank except for a time (I will post a screenshot)

Our employees are instructed to enter various information every hour in this form. I am looking for help in coding a button that, when clicked, will create 24 new records in the table that this form is based on, insert times in those 24 records, and then display those 24 records in the form. It would have the illusion that the end user it "resetting" the form at the end of his shift and preparing a clean form for the next person.


In the spreadsheet that we currently use, there is a button that, when pressed, saves the workbook with the date in its file name and then clears all of the end-user's data so that the next shift has a clean spreadsheet to use. I would like to mimic this button as closely as possible in MS Access

I understand that this database design is not exactly ideal in the world of database engineering; however, I was told to make this a perfectly seamless transition from the excel spreadsheet we currently use (there is a folder with 900+ workbooks from over the years. This obviously makes the information useless - we need all of the information in one file - this database)

 

[Uncle Gizmo]

The following set of videos show how to do this with a set of checklists, you should be able to modify it to your needs.

Automatically add a Checklist to MS Access: https://www.youtube.com/playlist?list=PLhf4YcS5AjdqzkQ5egOuO1myweo6hNgG2

 

[plog]

First, we take great exception at people who think Access is Excel But Better for some reason they can't explain. If you want to use a database, use it properly, layout your tables correctly and you will reap the benefits of an actual database as opposed to a spreadsheet but better for some reason that you can't explain.

With that said, I don't think I see anything majorly wrong with your database table. I do question how you are going to differentiate these new set of 24 records from any other. Is there a date somewhere in there? On August 1st, how will you know which set of records are from July 30th, July 31st and August 1st? Also, you mentioned different shifts. I see where they can enter Line and Operator, is Shift a different concept than one of those pieces of data?

That differention is key to what you want to do. The database needs a logical way to know which records are "new" and which ones are "old". Most likely you will need a 'BatchID' which groups each 24 records together. Then the form can show all the records that belong to a batch and determine which is the "newest".

Ultimately, you will need a button. The user clicks the button and it runs VBA to run an APPEND statement to put your new 24 records into your table. Here's some specific functions you will need:

DoCmd.RunSQL (https://msdn.microsoft.com/en-us/library/office/Ff194626.aspx)
Form.Filter (https://msdn.microsoft.com/en-us/library/office/Ff194672.aspx)

 

[bryanm694]

Thank you, Gizmo. I will begin watching that playlist shortly.

Plog, I understand that Access is not Excel; however, we have a massive pile of separate files that we will never be able to easily compare/run reports on. This is a problem. We also need this standalone database done within the next 7 hours if possible, so time is a big factor. That's why I jumped on these forums.

The unique "batch identifier" will be the date. For the purposes of this particular floor log, a 24 hour period will be from 7AM one day to 6AM the next. I do apologize for using a screenshot that did not show the date textbox, but I can assure you that there will be one. Like I said, I threw this entire standalone database together in about 2 hours.

For the purposes of this floor log, only a third shift operator (whose final data entry is at 6AM) will be clicking this button. Then, the first shift operator (Whose first data entry is at 7AM) will have a blank form to enter data on.

I will take a look at those codes as soon as I have finished watching this youtube playlist. Thank you both for your help so far.

 

[bryanm694]

The following set of videos show how to do this with a set of checklists, you should be able to modify it to your needs.

Automatically add a Checklist to MS Access: https://www.youtube.com/playlist?list=PLhf4YcS5AjdqzkQ5egOuO1myweo6hNgG2

I believe that the 3rd video in that playlist (video 2) shows some code that is very close to what I want to do. However, I am pretty new to VBA and coding in general. It is very difficult for me to keep up with the video, especially when I can only see pieces of the code at a time. Is there a place that I could go to view this code in its entirety so that I can figure out what I need to change? Thanks.

 

[bryanm694]

First, we take great exception at people who think Access is Excel But Better for some reason they can't explain. If you want to use a database, use it properly, layout your tables correctly and you will reap the benefits of an actual database as opposed to a spreadsheet but better for some reason that you can't explain.

With that said, I don't think I see anything majorly wrong with your database table. I do question how you are going to differentiate these new set of 24 records from any other. Is there a date somewhere in there? On August 1st, how will you know which set of records are from July 30th, July 31st and August 1st? Also, you mentioned different shifts. I see where they can enter Line and Operator, is Shift a different concept than one of those pieces of data?

That differention is key to what you want to do. The database needs a logical way to know which records are "new" and which ones are "old". Most likely you will need a 'BatchID' which groups each 24 records together. Then the form can show all the records that belong to a batch and determine which is the "newest".

Ultimately, you will need a button. The user clicks the button and it runs VBA to run an APPEND statement to put your new 24 records into your table. Here's some specific functions you will need:

DoCmd.RunSQL (https://msdn.microsoft.com/en-us/library/office/Ff194626.aspx)
Form.Filter (https://msdn.microsoft.com/en-us/library/office/Ff194672.aspx)

I apologize if this is horribly wrong, but as I was telling Gizmo, my coding skills are definitely lacking, extensively.

I am fairly certain that I need an INSERT INTO statement in the DoCmd.RunSQL expression. However, I am a little unsure how to type the WHERE statement. Would it be "WHERE tblFloorRunData.Date = TODAY()"?

In plain English, I know it would be something like "Insert current floor run data into tblFloorRunData where the date displayed on the form is equal to today" but I do not know the syntax for that.

Using the Form.Filter expression seems redundant at that point but I am sure that I am missing something.

Thank you for your patience

 

[plog]

INSERT statements themselves don't have a WHERE clause. So, I don't totally get what you are after. If you want to set the value of a field to today's date, you do it in the VALUES section:

... VALUES (#" & Date() & "#, ...

To get around building the SQL dynamically, you might consider an actual query object that does the appending for you. Then in the VBA you just run that query.

Using the Form.Filter expression seems redundant at that point but I am sure that I am missing something

No, you will definitely have to filter the form in some manner. We are talking about 2 seperate actions--Inserting data and showing data, don't confuse the two.

 

[Gasman]

As a newbie as well, I would be creating the append query in design mode and saving it. then executing it in VBA.

I would have a static table of the blank records for the day.
Then the append query would use a date to populate your date field in the working table and use the empty fields from the static table.

That way you would insert 24 records with correct date, but with the rest of the fields empty.

I expect the filter then would filter on the required date.?

As the last worker at 0600 is going to create the new data, you could use the current date?

The experts here will probably have a more efficient way, but it's a start, if time is of the essence?

HTH

 

[bryanm694]

As a newbie as well, I would be creating the append query in design mode and saving it. then executing it in VBA.

I would have a static table of the blank records for the day.
Then the append query would use a date to populate your date field in the working table and use the empty fields from the static table.

That way you would insert 24 records with correct date, but with the rest of the fields empty.

I expect the filter then would filter on the required date.?

As the last worker at 0600 is going to create the new data, you could use the current date?

The experts here will probably have a more efficient way, but it's a start, if time is of the essence?

HTH

NOW WE'RE TALKING! I just made the append query and static table as you suggested. Everything is working as I want it to, the only problem is that when I ran the append query to test it, it made 600 new records instead of 24. It's as if it repeated itself 25 times and I definitely only clicked it once. Could you think of any reason why this would have happened? I will keep playing around with it in the meantime

EDIT: I just clicked it again, for fun, to see if it would make another 600 records. It ended up making another 14,400 records. So now I have 15,000 records of the same 24 things repeated 625 times. Odd, but it's still a step in the right direction

 

[plog]

You shouldn't have your main table in there, only the static table. Also, when having trouble with a query, post the SQL of that query so we can see what you have.

Right click on the query in Design View and click the 'SQL View' option. It will open to a bunch of text. Show us that text.

 

[Gasman]

paste the sql code from the query.

Are you *sure* you only have 24 records in the source table?

 

[bryanm694]

You shouldn't have your main table in there, only the static table.

This was the issue. My bad. I have never made an append query before. It only makes 24 records at a time now. It also inserts the day. My last issue with this entire thing is this: How do I get it to insert the hours in each of the 24 new records? Another append query? How would this look in design mode?

You guys have both been an immense help to me today, thank you very much

 

[plog]

You have 24 records in your static table, you need 24 records in your main table. Each record in the static table corresponds to a record that will be generated in your main table....so...

 

[Gasman]

When I said 'blank' records, I did not mean every field would be blank.

My bad, sorry.

 

[bryanm694]

You have 24 records in your static table, you need 24 records in your main table. Each record in the static table corresponds to a record that will be generated in your main table....so...

Wow, I didn't release how obvious that was. Thank you.

Alright, everything is working now.

What would be the best way to have the date be updated each day in the static table? I plan on making a form based on the table. The form would then have a macro that runs every time the button for the append query is clicked and updates the date fields in the static table to "TODAY()"

This is the way I would attempt to do things if I weren't already on the forums, but since I am here, I figured I would ask what your opinions were

EDIT: I am assuming an update query would be a better way to go

 

[plog]

Sorry, didn't see that Gas told you to make blank records. Generic might be a better term--one for each hour you want to report on, along with the value each record corresponds to. Then in the APPEND query you use that field to append to the corresponding hour field in your main field.

For the date, you don't do it in the table, you do it in the APPEND query. Make a calculated field like this:

RecordDate: Date()

Then underneath it, you make it append to the field you want the date in.

 

[plog]

runs every time the button for the append query is clicked and updates the date fields

I need to be pedantic here. APPEND and UPDATE are not interchangeable in the database world. They mean 2 distinct things.

APPEND adds new records to a database.
UPDATE changes existing records.

So your macro wouldn't UPDATE records, it would APPEND records to your table. The updating will be done by users via the form. I say this because there is such a thing as an UPDATE query (http://www.w3schools.com/sql/sql_update.asp)