Displaying Last modified Date in unbound text box

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
226
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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")
 
Upvote 0
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.
 
Upvote 0
So i added a little VBA in On Enter event for the text box

VBA Code:
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
So i added a little VBA in On Enter event for the text box

VBA Code:
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?
1680348359024.png
 
Upvote 0
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()?
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top