Displaying Last modified Date in unbound text box

[Kemidan2014]

On the front page of my front end I wanted to include an unbound text box that displayed the last date modified of a linked excel file.

Currently I am using "FileDateTime("File path here")" in the Default Value property of the unbound box and set it as a locked box but the result is "#Name?"
I guess this type function can not be used as a default value? where else could i put this function so that the box outputs the date? would i really need to set up a query just to get this piece of information?

 

[Micron]

IIRC, default value cannot be used for unbound controls. Try setting the textbox control source property to your function or a DLookup if you are storing the value in a table. Like
=FileDateTime("File path here")

 

[Kemidan2014]

I can confirm "Year(Date())" works in unbound boxes but maybe that is technically not a function? Furthur Googling suggesting that FileDateTime can only be used in VBA or an SQL query,

if i wanted utilize VBA in a simple way is "<subname>_Enter()" be what i would use so that when Access opens my Front Page form i could set up a simple macro to run that function and make that textbox equal to that value? i have mostly used _Click() type of macros.

 

[Kemidan2014]

So i added a little VBA in On Enter event for the text box

private Sub Text35_Enter()
Dim getmoddate As String

getmoddate = FileDateTime("O:\1_All Customers\Current Complaints\data.xlsx")

Me.Text35 = getmoddate


End Sub

It out puts the correct information but only when i click into the box itself. so what should i change _Enter() to in order to have this run when Form opens?

 

[Micron]

Year and Date are both functions. Whether or not they work as a Default Value property I cannot recall. Using a function as the controlsource property is quite common and does work with udf's (user defined functions) which is what I took FileDateTime to be. When you open a form, the function will run and return a value ( or not - it depends). You should not need an event if you implement my suggestion from my first post:

Try setting the textbox control source property to your function

 

[Kemidan2014]

Ah sorry i did actually try putting =FileDateTime("<My file path>") both with and with out "=" and with and with out quotations in the Control source property but all combinations equalled "#Name?" as a result. and i was not currently storing the value in any table so i did not try Dlookup but It cant hurt to just add a table to store this value.

 

[Micron]

Maybe try putting a breakpoint on your function and step through to ensure it not only runs, it returns a value. I've used udf calls as control source properties and the unbound textbox shows what I expect when the form opens.
EDIT - actually I'd place the breakpoint in the procedure that seems to be calling your function as another test.

 

[welshgasman]

So i added a little VBA in On Enter event for the text box

private Sub Text35_Enter()
Dim getmoddate As String

getmoddate = FileDateTime("O:\1_All Customers\Current Complaints\data.xlsx")

Me.Text35 = getmoddate


End Sub

It out puts the correct information but only when i click into the box itself. so what should i change _Enter() to in order to have this run when Form opens?

Is it ever meant to change?, as the Form_Load would be my choice if not.

Works for me?

 

[Kemidan2014]

yes its meant to change when we download data and i run a personal VBA excel macro to set up and save the file in its proper place

but i didnt think there was a form load option would that be Text35_Form_load()?