InfoPath Dev

I have created a load event within InfoPath using vbs. My issue is that the code executes fine when I open it in preview mode from the design window. However, I attempt to fill out the form, as the users will, the event does not occur. I don't know if it is worth noting, but when I preview the form I get a dialog box that says "An ActiveX control on this page might be unsafe to interact with other parts of the page. Do you want to allow this interaction?" I select yes and my code executes properly. Could this possibly be a full trust issue? For reference I included my code below. Any help appreciated.


Sub XDocument_OnLoad(eventObj)
' Write your code here
Dim fso
Dim strFilename
Dim context
Dim findFile
Dim lastModified
Dim date2

strFilename = "D:\Data\QRF\Mannual Database.mdb"

'This creates an instance of the MS Scripting Runtime FileSystemObject class
Set fso = CreateObject("Scripting.FileSystemObject")
Set findFile = fso.GetFile(strFileName)
lastModified = findFile.DateLastModified

Set fso = Nothing
date2 = Now

If DateDiff("q", lastModified, date2) > 0 Then
x = MsgBox("You have an outdated database stored on your computer. Please close the form and download the newest database.", 0, "Outdated Database")
'Application.ActiveWindow.Close()
End If

XDocument.DOM.selectSingleNode("/my:myFields/my:timestampDatabase").text = lastModified

End Sub

Anyone have any ideas?

I've discovered that it's because my form is not full trust. The only problem with that discovery is that it requires too much work to get the certificate to allow for full trust (you just have to believe me on that). Is there a way I can use the edit SQL in my data connection to have it display date last modified? It doesn't seem like that would work. However, this is a feature I would really prefer to have in the program and I'm stumped on what I could do to get around the full trust issue.

There is a DateCreate in the MSysObjects table - the entire DB file is being replaced / updated on a certain basis, do I remember your other questions correctly?

So, if the whole DB is being replaced, I wonder if something like this:

SELECT distinct CDate(Int(DateCreate)) AS CreateDate
FROM MSysObjects

Would work? I'm converting to Int then back to date to remove the time....

Yes Hilary you are referring to the correct one. Where should I put that SQL statement? Is this being done within access or infopath? I've never used MSysObjects so this is a bit unfamiliar territory. Since I am doing date modified I assume I'd change DateCreate to DateLastModified.

Well, I'd try the query in Access first - My thought is that if you are actually copying a new DB file onto a machine, the create date may be the correct one. Not sure though.

But then, you should be able to create a data connection in IP to Access, and select any table, and click the Edit SQL button, and replace with this.

I think I've pretty much figured it out so I will lay out the steps here.

1. I made the Access Query
SELECT MSysObjects.DateCreate, CDate(Int(DateUpdate)) AS UpdateDate, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects;
2. Created a data connected in InfoPath with the Date Last Modified (the query I created)

3. Inserted a text box to serve as a timestamp
4. Set the default value to @UpdateDate[@Name = "Date Last Modified"]
Now this all works except that it provides that date with a time of zero. It reads 08/08/2012T00:00:00. So I changed the default field value to the following function: translate(@UpdateDate[@Name = "Date Last Modified"], "T00:00:00", "") and that solves the issue.

5. I then change my load event to read the contents of the text box thus circumventing the full trust error.

Now the only issue I have is with my translate equation. For some reason instead of producing 08/08/2012 it is doing 212/8/8.


Edit: You have to change the permission settings to allow for reading of MSysObjects table

Solved I changed my equation to: substring-before(@UpdateDate[@Name = "Date Last Modified"], "T")