Open a form in another database to a specific record using VBA

[Danick]

I have two databases.
I would like to create a text box in the first database that users can type in the record Id of a record in the second database.

Then I would like the user to be able to double click that field which will launch the second database and open a form to that specific record id.

Does anyone have any suggestions on how to do that?

Thanks

 

[pr2-eugin]

When you say two Database, do you mean two Tables on a single Access File or two seperate tables in two Access Files?

 

[Danick]

When you say two Database, do you mean two Tables on a single Access File or two seperate tables in two Access Files?

These are two separate databases each having their own set of tables and forms. And they are not really related to one another, so I don't want to bring all the forms and tables into either one of them.

Except that I found that sometimes, when I'm in the first database, I would make a reference to a record id on the second database. Then I would open the second database, open a specific form and go to that record. But I thought it would much better to be able to just double click that text box to open another instance of Access and open that specific form and to that specific record id.

 

[Danick]

I did some searching on the internet and found a thread on another forum that discusses what I'd like to do. Unfortunately, the thread is from 2009 so I doubt I would get any response. The code there goes like this:

Private Sub Button_OpenRemoteForm_Click()

Dim strMDBFile as String
Dim strFormName as String

strMDBFile = "C:\Documents and Settings\SinnDHo\My documents\Access\dbforum\CascadingSubforms.mdb"
strFormName = "Main_Form"
OpenRemoteForm strMDBFile, strFormName

End Sub


But when I try this, I get an error "Invalid use of Property"
And it is also missing some code as to how to open the form to the specific record.

Then someone responded with the a comment that would do exactly what I am after. But he doesn't explain how to do it. The user wrote:

------------------
I personally would just use the arguements parameter when opening the mdb file. You can supply a "value" of a variable when opening an mdb using the appropriate parameter.

Then I'd have code in the mdb which reads that parameter (it's 1 or 2 lines of code in your startup sequence) and open the appropriate form depending on the arguement value.

I used to do this all the time when I wanted to open another mdb file and have it immediately go to a customerID. I'd pass the CustomerID as an arguement when opening the 2nd mdb file. It's only a couple lines of fairly easy coding (again, just using the arguement parameter.)
----------------

Does anyone know how to do what this user is describing?

 

[ChrisO]

As written, both files in the A2003 attachment need to be in the same directory.

Chris.

 

[Danick]

As written, both files in the A2003 attachment need to be in the same directory.

Chris.

This looks almost like I could use it the way it is.
Thanks for your help.

On question - Is there any way to get this to work without having to add the module in the target database? The reason being that the target database is working very well on it's own and I would hate to add something that may break it.

Thanks again. It's exactly what I was looking for.

 

[ChrisO]

I can’t think of a single way to show the Form without modifying the target database in some way.

If you only want to get the data from a table using the ID then that might be possible.

Chris.

 

[wezhogan]

I found this thread as a result of looking for information on how to open a form in database B using a button on a form in database A. It worked for a little while but now I am getting errors or database B opens and then closes right away.

Here is the code for the form in database A:

Private Sub bnClose_Click()
Dim strDatabasePathAndName As String
Dim strFormToFloatName As String

strDatabasePathAndName = "C:\Users\New Beginnings\Desktop\Louise Drafts\ClientInformation.accdb"
strFormToFloatName = "frmForms"

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase strDatabasePathAndName
appAccess.Run "InvokeCommand", "OpenForm", strFormToFloatName
appAccess.Visible = True

DoCmd.Close acForm, "frmSummaryReport", acSaveYes
End Sub

Here is the code for the module in database B:

' Called from the external Application.
Public Sub InvokeCommand(ByVal strCommand As String, _
ByVal strArgument As String)

Select Case strCommand
Case "OpenForm"
DoCmd.OpenForm FormName:=strArgument


Case Else
MsgBox "Invalid Command Passed to InvokeCommand"

End Select
End Sub

When I get the errors it says:

Runtime error 3734
"The database has been placed in a state by user "ADMIN" on machine "PC" which prevents it from being openned or locked."

Runtime error 440
"Method 'Run' of object_'Application' failed.

Then other times it just opens the database and closes it right away.
"

 

[boblarson]

Chris is on the right track but it is way easier than he is doing. You don't need the code in the second database if you do it this way:

Function OpenRemoteForm(strDbFile As String, strFormName As String, strKeyFieldName As String, lngIDToFind As Long)
    Dim objAcc As Object
    Dim accFrm As Object
    Dim rst As DAO.Recordset
    DoCmd.RunCommand acCmdAppMinimize
    Dim strBookmark As String
    Set objAcc = CreateObject("Access.Application")
    objAcc.OpenCurrentDatabase (strDbFile)
    objAcc.DoCmd.OpenForm strFormName
    With objAcc.Forms(strFormName)
        Set rst = .RecordsetClone
        rst.FindFirst strKeyFieldName & "=" & lngIDToFind
        If rst.NoMatch Then
            MsgBox "No Match"
        Else
            .Bookmark = rst.Bookmark
        End If
    End With
 
    objAcc.UserControl = True
End Function

To use this, the code in the click event (of the sample Chris did) would be:
OpenRemoteForm "C:\Test\TargetDatabase_A2003.mdb", "frmShowRecords", "CompanyID", Me.txtID

See the attached revised version of his sample.

 

[wezhogan]

Thank-you so much. That fixed my problem completely.

 

[wezhogan]

Well I spoke too soon. Everything is working in my .accdb file of the database. I am now getting a runtime error when trying to run the .accdr file on another computer that just has Access Runtime loaded on it. Any ideas on this?

 

[boblarson]

Well I spoke too soon. Everything is working in my .accdb file of the database. I am now getting a runtime error when trying to run the .accdr file on another computer that just has Access Runtime loaded on it. Any ideas on this?

Yes, the Access Runtime can't use CreateObject or New Access.Database. So, if you are forced to use the Access Runtime, I think you are going to be needing to use a modified version of ChrisO's method which will need code in BOTH databases to make it work since there will be no ability to control the second through the first as it can't connect to the application object. So you would need to call the first one via the Shell method and then you probably are going to need to have the first save a text file somewhere with the record ID you want and then when the second opens it looks for that text file and gets the value.

 

[wezhogan]

Any idea where I can find the code I would need to open database B from the form in database A without using the createobject function? I don't need to open database B to a specific record, just have to open it to a specific form. I can set up database B to just automatically open that form in the options so all I really need is code to open database B when a button is clicked on the form in database A.

 

[boblarson]

Something like this:

Shell "C:\Program Files\Microsoft Office\Office11\MSAccess.exe C:\Temp\RedBarkMap.mdb",1

 

[Soniaski]

Hi Bob...

I was doing a search on the forum regarding modifying a label on a form in another database and I came across this post. I used the sample logic you have shown above to actually open the other database. Now I'm at a point where I can't find anything that will allow me to change the name of the label on my form. Do you have any ideas on how to do this?

The premise behind this is that I use one database to run weekly updates in other databases. The other databases each contain a label on the main form showing "Last Updated Date". Right now we have to open up each database and manually change this date. It's a little frustrating as everything else is automated.

I suppose a fix could be to use a file containg the update date and show that on the form/label... First though I would like to attempt to update it using VBAas opposed to going out and modifying each database that I touch.

Thoughts? Also, I apologize if I shouldn't have posted this here. I will be happy to copy and paste this into a new thread. I'm not sure of the protocol that is used on this site.

Thank you...

 

[boblarson]

You really need to modify this other database to get rid of the label, Create a table in the database that the label currently exists on and set a TEXT BOX to display it with a DLookup. Then you just need to modify the date in the table with simple SQL.

As for it looking different, you can modify the text box to look like a label and thereby nobody will know the difference as far as the way it currently works.

 

[Soniaski]

Thank you for the reply Bob. I was afraid you were going to say something like that. I guess I will update each of the databases I touch. In the long run it will save me time.

Have a great day!