Code

Re: Load Event Only Works in Preview

shep123 — Wed, 08 Aug 2012 18:38:12 GMT

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

Re: Load Event Only Works in Preview

shep123 — Wed, 08 Aug 2012 18:34:05 GMT

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

Re: Load Event Only Works in Preview

Hilary Stoupa — Wed, 08 Aug 2012 17:52:11 GMT

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.

Re: Load Event Only Works in Preview

shep123 — Wed, 08 Aug 2012 17:45:27 GMT

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.

Re: Load Event Only Works in Preview

Hilary Stoupa — Wed, 08 Aug 2012 17:05:51 GMT

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....

Re: Load Event Only Works in Preview

shep123 — Wed, 08 Aug 2012 16:52:28 GMT

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.

Re: Load Event Only Works in Preview

shep123 — Wed, 08 Aug 2012 12:09:41 GMT

Anyone have any ideas?

Load Event Only Works in Preview

shep123 — Mon, 06 Aug 2012 12:57:51 GMT

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