Solved Showing the Date when I Append Records to a Table

[Weekleyba]

I have a small database with one table.
I append the table weekly.
I would like to automatically show on the main form, the date of the last update to the table.

How can I do this?

 

[theDBguy]

Hi. You could try creating a custom function to read the "last modified" property of the table (although I don't know how accurate that could be), or you could simply add the date you imported the data (or store it in another table), so you can easily get it back. Just some thoughts...

 

[Pat Hartman]

Add a column to the table and call it CreateDT. Set its default to Date() if you want only the date stored or use Now() if you want both date and time. On the form, use a DMax() to get the highest value for CreateDT. You will need to run an update query initially to populate existing records with a dummy CreateDT value or you could leave the column blank.

 

[Weekleyba]

Thanks. I’ll work on that tomorrow.
Really appreciate the help!!

 

[Pat Hartman]

You're welcome.

 

[Weekleyba]

Pat,
I've been working on this today and find myself stuck on multiple fronts.

Three things:
1. Update Query - I really want to learn how to do this. Attached is my attempt in creating an update query to change the table field CreateDT to the current date. Right now, if the field is blank, nothing happens. If I have a date in it, the query will change it to "12:00:00AM" Can you look at my update query and tell me where I'm going wrong?
2. Updating the Table - I thought Appending the table was what I needed but that was wrong. Appending adds the records to the table, doubling up the records. I want to replace all the records with new. Is it best to just overwrite the existing table with the same name? Of course when I do that, my CreateDT field disappears. And I'll need to close the Main_F first since it has the table as the record source. Unless there's a way around that?
3. Even when I had the CreateDT field in the table and the textbox on the form, Main_F, with the Control Source (=DMax("[CreateDT]","IHS - Allowance Status by Locat"), I couldn't get it to work. So I'm still stuck on that to.

Spent the last two hours searching the web but coming up short on all these.

Could you take a look at my db and help me out?
Thanks.
Brad

 

[theDBguy]

Hi. All I had to do was change the UPDATE query to this and the form worked.

UPDATE [IHS - Allowance Status by Locat] SET [IHS - Allowance Status by Locat].CreateDT = Date();

 

[Weekleyba]

Thanks DBguy, that solves problem 1. Nice!
Any thoughts on 2 & 3?

 

[theDBguy]

Thanks DBguy, that solves problem 1. Nice!
Any thoughts on 2 & 3?

Hi. The image above should cover #3, right?

 

[Weekleyba]

#3 is about setting the CreateDT when I update the Table.
But how can I update the table?
Appending: It will not let me append, most likely since it has the existing table has the extra field CreateDT.
Importing: This deletes the existing CreateDT field and leaves at a blank field.

So, I just I need help with how to update the table while keeping track of date the table with last updated.
I guess I'm back to my original problem still.

 

[theDBguy]

#3 is about setting the CreateDT when I update the Table.
But how can I update the table?
Appending: It will not let me append, most likely since it has the existing table has the extra field CreateDT.
Importing: This deletes the existing CreateDT field and leaves at a blank field.

So, I just I need help with how to update the table while keeping track of date the table with last updated.
I guess I'm back to my original problem still.

Okay, let's go back to the beginning. Where does the data come from?

 

[Weekleyba]

The data for table "IHS - Allowance Status by Locat" comes from online program that I use.
I export the data to an Excel file and then import into my db so I can manipulate it into some user friendly reports.
I'll need to import this everyday, so I want to make it as easy a process as possible.

 

[theDBguy]

The data for table "IHS - Allowance Status by Locat" comes from online program that I use.
I export the data to an Excel file and then import into my db so I can manipulate it into some user friendly reports.
I'll need to import this everyday, so I want to make it as easy a process as possible.

Okay, next question, do you want to keep the previous day's import or do you just replace it with today's import? Also, when you said you wanted to show the date of the last "update" to the table, can we assume you mean the last time the data was "imported?"

 

[Weekleyba]

No, I do not need to keep the previous day's import.
Yes. The last update does mean the last time the data was imported into Access.

 

[theDBguy]

No, I do not need to keep the previous day's import.
Yes. The last update does mean the last time the data was imported into Access.

Okay, in that case, to make it simpler, I would just create a new table with only one column and one record in it to store the date when you import the data. So, when you import the data, make sure you update this table to show the new date. In your main form, point the DLookup() expression to that new table. If you're doing the import manually, just update the table manually too. If you're using code to import the data, then add some code to update the table with the new date as well. Hope that helps...

 

[Weekleyba]

So, just do it manually?
I was looking for more automated way.

Is there not a way to do this automatically when the table is imported?
That way, when I had this over to Admin, there's no way for them to forget putting in the Last Update date.
It really does makes a difference on when the data was last imported to those using the db.

Any other suggestions?

 

[Pat Hartman]

Did you set the default for the CreateDT field to Date()? That will set the value for all appended records. If it doesn't, then include the column in your append query and use Date() as the source.

 

[theDBguy]

So, just do it manually?
I was looking for more automated way.

Is there not a way to do this automatically when the table is imported?
That way, when I had this over to Admin, there's no way for them to forget putting in the Last Update date.
It really does makes a difference on when the data was last imported to those using the db.

Any other suggestions?

Hi. Like I said, it depends on how you were doing the import. If you were doing it manually, then you can update the date in a separate table manually. But if you were doing it using automation, then you should also be able to automate updating the date.

So, how exactly are you or your users importing the data from Excel into Access? If by code, maybe you could post it here, so we can show you how to add the part to update the date.

 

[Weekleyba]

Pat - You are correct. It was not working for me because I had set a filter on the Excel file that I was trying to use to update. My bad.
It does work now, if I don't mess with the Excel file.

One last thing...
After I append the table, how can I tell the Last Date text box to refresh? Automatically I mean.
Would it be on the forms Current Event?
The Main_F is not bound.

 

[Pat Hartman]

I'm assuming you used a dMax() as the controlSource for the circled control. The form totally refreshes when you move to a new record because it runs the Current event. Clicking a button to import something does not affect the bound recordsource. You would need to force the unbound control to update yourself. If you have this form open and do the import on another form, same thing. What you see on a form is what was read from the table when the current record became "current". Access does refresh data in the background so if the current record were changed by some other user, you will eventually see the change reflected on the open form but it might take a while.

In the click event of the button, after you run the import, refresh the control.

Me.txtLastUpdate.Refresh