Display file size on form?

[Romper]

Hi all,
I have created a form that acts as the main / home screen for my database. Is it possible to add a text box or other control that will display the database file size so I can monitor my database growth without going through My Computer or Windows Explorer? If so, how?

 

[Isskint]

Put a textbox on the form and set its control source to = GetFileSize and then add this function to a normal module

Function GetFileSize() As String
    
    Dim fso As Object
    Dim f As Object
    Dim fsize As Double
    Dim strFilePathAndName as String
    strFilePathAndName =FullPathtoDatabase

    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Set f = fso.GetFile(strFilePathAndName)
    fsize = (f.Size / 1024) / 1024
    
    GetFileSize = Round(fsize, 2) & " Mb"
    Set fso = Nothing
End Function

You could change this

 

[RuralGuy]

If you are using ac2010 then you could use the FileLen() function.

 

[Romper]

Thanks to you both,
I'm a complete bonehead when it comes to creating code Isskint, how do I create a normal module? Is there a dumb-ass short tutorial you could link me to so I don't screw this up, or can you just tell me which icon I select in vba for a normal module. I'm using 2007.
Thanks so much.

 

[Romper]

Okay, I'm guessing you mean normal module as opposed to class module. Do I have to name the module so it links to the text box control source?
Outta my depth!

 

[Isskint]

No the module name is not important, only the function name. Some people just name the modules as Module1, Module2 etc. Others may have mduFunctions or mduProcedures etc. As i say the name is unimportant.

 

[RuralGuy]

No the module name is not important, only the function name. Some people just name the modules as Module1, Module2 etc. Others may have mduFunctions or mduProcedures etc. As i say the name is unimportant.

...as long as it is *not* the same as any function!

 

[Romper]

Thanks,
Just playing with it now. I'll be sure to let you know how bad I go!

 

[Romper]

I knew I'd muck it up!

My text box just displays #Name?

What did I do wrong?

 

[Isskint]

The textbox Control Source should be GetFileSize. If it is, have you provided the full file path for your database in the function?

 

[Romper]

The textbox Control Source should be GetFileSize. If it is, have you provided the full file path for your database in the function?

Haha, being the twit I am, I copied your code word for word. Do I only change :-

StrFilePathAndName = C:/Documents/Dave's Collection/New Library

Do I also have to add the file extension for the database?

 

[Isskint]

Yes and errr Yes

StrFilePathAndName = C:/Documents/Dave's Collection/New Library.mdb

 

[Romper]

Can't get it to work, it keeps highlighting that line in red when I enter the path. Could this be because the folder Dave's Collection contains an apostrophe and therefore it sees that as some sort of code?

 

[Isskint]

Try this with speech marks and \ not /

StrFilePathAndName = "C:\Documents\Dave's Collection\New Library.mdb"

 

[Romper]

Okay, the module now saves with no red lines. My form text box still just displays #Name?

I triple checked the module to ensure I've written it just as you suggested. What else could be the problem. I knew there was a reason code scares me!

That one line is the only one I should have edited huh?

 

[Isskint]

#Name would suggest you are supplying an invalid Control Source. Do you have an = sign in front of GetFileSize? Remember, GetFileSize is a function so the control = the function as opposed to a field name where only the name suffices.

 

[Romper]

I have tried both with and without =

If I enter the Control Source as =GetFileSize it automatically gets changed to :-
=[GetFileSize]

 

[Isskint]

I must be sleepy. I should have posted the full format earlier. The ControlSource should be =GetFileSize() :banghead:

I normally use the function with the file path supplied as an argument, but adapted it to your situation as you are only interested in the one file.

 

[Romper]

Woohoo!

Now it works beautifully! Just to be a pain in the rear end, at the moment it displays:-
18.2 MB
Can I edit the function so that it displays 2 decimal places?

Thanks so much for sticking with it
Very happy chappy.

 

[Isskint]

The function already does that.

GetFileSize = Round(fsize, 2) & " Mb".
If you wanted 2 digits (even if the 100ths is a zero) try GetFileSize = Format(Round(fsize, 2),"####.00") & " Mb"