Display date field from tbl when all records have the same value

[geotrouvetout67]

Hello,

I'm trying to display in a form a date/time that represents when the data was last updated.

My data is in tblFORECASTS, which contains a RefreshDate field which assigns the exact same date to each record every time the table is updated

I want to display that date in the form but getting an error.

In a text box I tried =[Forecasts]![RefreshDate], also =[qryRefreshDate]![FirstOfRefreshDate] and this does not work.

The following somewhat worked, adding "SELECT First(Forecasts.RefreshDate) AS RefreshDate FROM Forecasts;" or qryRefreshDate as Record Source of the form, this worked as of displaying the date/time but it's locking the table so when I click on the "refresh db" button I get an error message that the table is already locked.

I also tried putting that date in itself on a 1 record table and it does not work either, getting #Name?



it's probably very simple, What am I missing?

thanks

[Bob Fitz]

Perhaps you just need to use RefreshDate as the Control Source of your textbox

[geotrouvetout67]

This works if I assign a table or a query to the form but it locks the table where the record is which prevents me then from refreshing that table.
I even tried adding "close window" at the beginning of the refresh macro and it still gives me that table is locked by process error.

However if the form is closed, the macro in itself runs fine, it just bugs when the form is already open despite having a close window command at the top of the events chain.

[Bob Fitz]

This works if I assign a table or a query to the form but it locks the table where the record is which prevents me then from refreshing that table.
I even tried adding "close window" at the beginning of the refresh macro and it still gives me that table is locked by process error.

However if the form is closed, the macro in itself runs fine, it just bugs when the form is already open despite having a close window command at the top of the events chain.

I didn't see any mention of a macro in your original post and like most serious users I do not use them. I prefer to use VBA code. I think you will need to post a copy of your DB for me to be able to offer anything more in the way of help.

[geotrouvetout67]

I didn't see any mention of a macro in your original post and like most serious users I do not use them. I prefer to use VBA code. I think you will need to post a copy of your DB for me to be able to offer anything more in the way of help.

I use both VBA and macros, sometimes macros are easier for me as I'm beginning with VBA coding.

I think this is bugging because I have lots of VBA in that form that is locking the table.

I just fix it though, it's not pretty but simple and works. I built a query that is dumping the date in another table and I call that table record from a List box, that removed the conflict with the main table. Thanks offering your help.

[geotrouvetout67]

I've been trying to make this "prettier" with a function that runs on load but I'm doing something wrong.

I set a temp table tblLastRefreshDate with a field [RefreshDateTime]

The idea is at each load, delete the temp table, select the first date record in the main table "Forecasts" and set that value as variable LastRefresh then insert that variable back into the temporary table

If I set LastRefresh AS either String or Date, I get the error "Compile Error: Object required with "LastRefresh" highlighted on the "Set" line. If I don't declare the variable as anything, I get a "Type missmatch" error in the last line.

Private Sub RefreshTime()'save last refresh date

DoCmd.SetWarnings False

Dim LastRefresh



CurrentDb.Execute "DELETE * FROM tblLastRefreshTime"


Set LastRefresh = CurrentDb.OpenRecordset("SELECT First([RefreshDate]) FROM Forecasts")


CurrentDb.Execute "INSERT INTO tblLastRefreshDate(RefreshDateTime) VALUES ('" & LastRefresh & "')"


End Sub