Time Stamp after clicking a Command Button

[Mr. Southern]

Hello,

I have a form with a command button that runs a few macros. How can I put a text box next to that button that displays the last time the button was clicked? The macro updates some tables and I want to display the last time it was updated.

Any Ideas?

 

[theDBguy]

Hi. Is the form bound? You'll have to store the date last updated in a table and you can bind your textbox to that. Just don't forget to update it when the user clicks the button.

 

[Mr. Southern]

Hi. Is the form bound? You'll have to store the date last updated in a table and you can bind your textbox to that. Just don't forget to update it when the user clicks the button.

The form is unbound.

 

[theDBguy]

The form is unbound.

Good. So like I said, you can bind it to the table were you will store the date and add the date field Textbox to the form.

 

[Pat Hartman]

You can never get back data that you don't explicitly save. So, theDBGuy gave one solution that will work. If you don't like that one, we can provide others but they will be more complex since I'm pretty sure that you are not logging change dates in the various tables.

Also, the fact that you are running update queries like this seems to indicate a design flaw since calculated data should rarely be stored.

 

[Mr. Southern]

For what im trying to use this for it will be fine. I was able to run a macro to add the current Day/Time to the table and linked the text box to that table. just got to make sure everything is working tomorrow. Thanks for the help/idea.

 

[Mr. Southern]

It appears everything is working except the text box will only update with the new time after the form is closed and opened again.

 

[Insane_ai]

re-query the field after updating the table.

 

[theDBguy]

It appears everything is working except the text box will only update with the new time after the form is closed and opened again.

Hi. How exactly are you updating the textbox? If it's bound, you can simply update the textbox and Access will automatically save it into the table for you.

 

[Mr. Southern]

Hi. How exactly are you updating the textbox? If it's bound, you can simply update the textbox and Access will automatically save it into the table for you.

In the command button I used a On Click Macro that RunsSQL UPDATE tblTime SET tblTime.Time=Now().

The text box has a control source that does =DLookup("Time", "tblTime")

 

[theDBguy]

In the command button I used a On Click Macro that RunsSQL UPDATE tblTime SET tblTime.Time=Now().

The text box has a control source that does =DLookup("Time", "tblTime")

Using DLookup() means its unbound. If so, try adding Recalc at the end of your macro.

 

[Mr. Southern]

Using DLookup() means its unbound. If so, try adding Recalc at the end of your macro.

What would the Control Name be?

 

[theDBguy]

What would the Control Name be?

Try using the name of your textbox.

 

[Mr. Southern]

Try using the name of your textbox.

It shows an error: The command or action 'Requery' isnt available now.

 

[theDBguy]

It shows an error: The command oraction 'Requery' isnt available now.

Hi. I didn't say Requery. Have you tried Recalc? I can't verify it right now, sorry.

Sent from phone...

 

[Mr. Southern]

Hi. I didn't say Requery. Have you tried Recalc? I can't verify it right now, sorry.

Sent from phone...

I do not see a macro for Recalc but I put in VBA for Recalc and it didnt work. I used

Sub Txtbox_AfterUpdate() 
Me.Recalc
End Sub

 

[theDBguy]

I do not see a macro for Recalc but I put in VBA for Recalc and it didnt work. I used

Sub Txtbox_AfterUpdate() 
Me.Recalc
End Sub

You're probably using the wrong event. You'll have to recalc after the data is changed in the table. What's wrong with binding the form and the textbox to the table. You wouldn't have spent this much time troubleshooting an unbound textbox if you simply bound it.

 

[Mr. Southern]

You're probably using the wrong event. You'll have to recalc after the data is changed in the table. What's wrong with binding the form and the textbox to the table. You wouldn't have spent this much time troubleshooting an unbound textbox if you simply bound it.

You are correct. I just bound the form and linked the textbox to the table. We are all good here.

Thanks

 

[theDBguy]

You are correct. I just bound the form and linked the textbox to the table. We are all good here.

Thanks

Hi. Glad to hear you got it to work. Good luck with your project.

 

[Pat Hartman]

Bound forms are soooooo much easier to use