For not recognizing subform on load

[hondaman2003]

I have 2 databases. I simply want to use database 1 to open database 2. Database 2 has a startup form that has subforms. The parent form attempts to set several fields in each of the subforms to null on load to 'reset' the forms. When I open database 2 directly it will open normally. When I use database 1 to open database 2 it will give an error #2455, 'You entered an expression that has an invalid reference to the property Form/Report.'

This error brings me to the first line in my load event code the attempts to set a field to null on a subform. Again, when I open database 2 directly, there is no problem, when I open it by either of the two following options it will give the above error:

Dim appAccess As Access.Application
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase PathFile

OR

Application.FollowHyperlink PathFile, , True

 

[Hap007]

I have worked with opening forms and modules from a parent database (Database1) to a child database(Database2).

If memory serves me correctly, the child database will behave differently when opened from another db than if you open it directly.

Some differences.
1) Macros and Function keys in Database 2 will possibly look to database 1. (Depending on how you open the db2.
2) AutoExec and Startup do not work in database 2 when being called form another db. They do not run automatically.

I would suggest creating a Public Function in Database 2. In this function, I would handle all the startup modules and form loading.

Then, in Database 1, call the function in database 2.

Just remember, Database 2 may inherit some of the characteristics of Database 1, so be cautious about codeDB and currentDB settings.

Another approach might be to Include Database 2 in the reference section of a code module in Database 1.
This will expose all of the public code, forms and reports of Database 2 and you do not need to use the CreatObject. Instead you can call functions and the like in Database 2.

Hope This Helps,
Hap...



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[hondaman2003]

Thank you for responding!

The only extent of the interaction between the two databases will be purely opening database 2 from database 1. They will not interact beyond that point. Database 1 should continue running like normal after database 2 is open and there should be no other "connection" between them. The same as if I opened two word documents.

Database 2 is setup to automatically open a form at start up. It's as if the subforms are not loading before the load event code starts running. I have added a 'doevents' command with no success.

 

[Hap007]

I am guessing that the sub form has no data when you open it remotely. Perhaps the record source is different.


If there is no interaction between database 1 and database 2, then why not just open database 2 with a shell command in database 1.
That way, the s
tartup procedure will work as normal.

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[hondaman2003]

Thank you for your reply!

This database is actually being used more as an application than for a database. People type things on the forms to check for certain things and to format comments that will go into another application.

I will try the shell commands. However, I thought the following command would work in the same way:

Application.FollowHyperlink PathFile, , True

What do you think?

 

[PHV]

What about this ?
CreateObject("WScript.Shell").Run PathFile

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hondaman2003]

Thank you PHV

ERROR:

#-2147024894
Method 'Run' of object 'IWshShell3' failed

 

[hondaman2003]

I figured out the problem with that error message. There were spaces in my path to the file so I had to include space characters in the path to my file.

I am still having the same issue in that the second database opens but that the load event gets an error because it doesn't recognize that the subform is open (yet) so it cannot set a field on the subform to null. If I click debug, then immediately resume execution, the code successfully continues.

 

[MajP]

forms open from the inside out, so the subform should load before the parent form. However, it could be a timing issue.

I would add DoEvents prior to trying to set the values.
Or you could simply trap the error, and resume next.
If the simple doevents does not work you could try to pause and give some time.

Public Sub PauseThenMessage()
Const PauseTime = 2 'set pause time
Dim start As Long
start = Timer ' Set start time.
Do While Timer < start + PauseTime
DoEvents ' Yield to other processes.
Loop
End Sub

 

[hondaman2003]

I already tried 'doevents' and a pause sub for 10 seconds and nothing works. I can literally allow the error to happen and immediately resume the code and it works.

If I do the resume next, then it will not execute the first command because it had an error an move to the next (assuming it works at all)

 

[TheAceMan1]

hondaman2003 . . .

To the best of my knowledge [blue]you need to open another instance of access with the db in question[/blue]. There is no other way to simulate or otherwise open a db directly. I myself have pondered this question for many years ... [blue]Just what is the diference in opening a db thru object creation compared to opening it directly![/blue] Well ... years of queries to microsoft and searches have provided scanty info at best. In this I've left it up to my testing to provide the answers.

So the best I can say is that [blue]you need to open the child db in an additional/another instance of access[/blue]. The only problem with this method is that in a multiple user envrionment the location of the access executable may not be static!

The above can be done by using the [blue]Shell[/blue] function. You set the [blue]PathName[/blue] arguement to that of the access executable with additional arguement of the db you desire to open. Example:

[blue]   Dim Rtn, Pth As String
   
   Pth = "C:\Program Files (x86)\Microsoft Office\Office11\MSACCESS.exe " & _
         "C:\Database\CapOne\CapOne.mdb"
   Rtn = Shell(Pth, vbMaximizedFocus)[/blue]

CapOne.mdb has a login form and other code that is executed on open of the db thru an [blue]autoexec[/blue] macro. All execute smoothly.

So give the [blue]shell function[/blue] a shot. I expect it'll be productive.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

CreateObject("WScript.Shell").Run Chr(34) & PathFile & Char(34)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hondaman2003]

PHV, thank you for your reply...however that tactic didn't work in a previous attempt.

TheAceMan1, when I tried your method I got the message "The command line you used to start Microsoft Access contains an option that Microsoft Access doesn't recognize."

I used the following commands:

Dim Rtn, Pth As String

Pth = "C:\Program Files\Microsoft Access 2002\Office10\MSACCESS.exe " & PathAndFileName

Rtn = Shell(Pth, vbMaximizedFocus)

 

[TheAceMan1]

hondaman2003 . . .

There's probably at least one space in [blue]PathAndFileName[/blue]. To be sure the paths need to be embedded in quotes, so try the following:

[blue]   Dim Rtn, Pth As String, DQ As String
   
   DQ = """"
   
   Pth = DQ & "C:\Program Files\Microsoft Access 2002\Office10\MSACCESS.exe" & DQ & " " & _
         DQ & PathAndFileName & DQ
   Rtn = Shell(Pth, vbMaximizedFocus)[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]