Saving a macro

[kdbailey]

I have a macro form to update tables. What I want is simple. The button goes through a list of delete and append queries, and when it is done, I want it to display Now() on a label on the form to just show the last time the update was ran. I've tried a few different ways to save the file, but every time I reopen the macro, the label displays the original text. How do I save this macro form?? It is titled "Update"

[JoeM]

I assume this "Form" you have is unbound?
I would recommend creating a one-record table with a field like "LastUpdated", and then use this table as the Control Source of this Form.
Then, you can add VBA code to your button to populate the value of this field to "Now()" at the end, and you can return its value in a Text Box on your Form too.

[kdbailey]

I guess I'm more just wondering why the label is not saving its caption?

[JoeM]

I have never tried to do it that way, but I would guess that if you have your code updating the caption of the Label directly, you will need to save the Form after you do that, or else those changes will be lost and it will revert back to how it was when you first opened it.

[kdbailey]

That's what I thought, what would be the code to save the form? Again, it is named "Update"
I believe I was trying DoCmd.save Acform, "Update"

[JoeM]

Can you post all your code for this Form?

Note, the way you are going about it isn't really the recommended way of doing it. It is generally better to the variables of the Form update from Tables/Queries on the back-end than to try to have your code physically modify the Form and save it.

Let's say that you wanted to use this date in another query or something like that. If it is stored in a Table field, you can do that. If it is stored as a label on a Form, you cannot (at least not easily).

[kdbailey]

The thing is, I do not want this date to appear anywhere but the form. Its only purpose is to let others know that the form has or has not been updated today already. Showing up in a table is not the direction I want to go.

[JoeM]

The thing is, I do not want this date to appear anywhere but the form. Its only purpose is to let others know that the form has or has not been updated today already. Showing up in a table is not the direction I want to go.

Pardon me for asking, but what is the big aversion to storing in a table? It is simply a single record, single field table that no one has to see. And it really isn't any harder to do than what you are trying to do.

I just don't like the idea of VBA code physically changing the structure if the Form. It is really not good Form design. But if you really want to do it that way, please post all your code from you Form, like I asked in the previous post.

[JoeM]

Just so you can see that this isn't just a personal opinion of mine, I found a thread that discusses the same problem that you describe, and they are given the same advice. The last post shows you how you can actually accomplish what you are trying to do, but the solution seems like more effort than its worth (definitely harder than just using the table method): http://www.access-programmers.co.uk/...d.php?t=211109

[kdbailey]

I ended up going through a table as you suggested, but I am still curious as to how you would change and save a form. Anywhere I could find information regarding this?

[JoeM]

Look at the last post in the link I sent in my previous post.

It looks like you would have to use a different Form to open the Form you want to update (in Design View), make the changes, then Save the Form.

[kdbailey]

Oh ok, this will help a lot. I will stick with the code through a table, but now I know how to adjust forms. Thanks a bunch!