MS Access filter Form from one database in another database

I have two databased files.  
CurrentDb and HistoryDB  in MS Access 2003.
 From a form in HistoryDB on  command button click event I need next:
 I want to open two bound forms in CurrentDB with particular record in
 then I want to close HistoryDB and have CurrentDB form frmMainEdit stay  opened and frmMainOutstand should be hidden.

 I started with some code as bellow but can not figure out how to Filter those forms.

   
 ********************************************

 Private Sub CmdOpen_Form_In_CurrentDatabase_Click()


 Dim strDB As String
 strDB = "D:\Databases\CurrentdB.mdb"
   
 Dim appAccess As Access.Application
 Set appAccess = New Access.Application

 Dim strFilter1 as string
 strFilter1 = "Some text xxxxxx"


 appAccess.OpenCurrentDatabase strDB
 appAccess.Visible = True

 appAccess.UserControl = True





 stDocName = "frmMainOutStand"
 appAccess.DoCmd.OpenForm stDocName

 Forms!frmMainOutstand!sfrmMainOutStandCurr.Form.Filter = strFilter1
 Forms!frmMainOutstand!sfrmMainOutStandCurr.Form.FilterOn = True


 Forms!frmMainOutstand!sfrmMainOutStandHis.Form.Filter = strFilter1
 Forms!frmMainOutstand!sfrmMainOutStandHis.Form.FilterOn = True






 stDocName = "frmMainEdit"
 appAccess.DoCmd.OpenForm stDocName

 Forms!frmMainEdit.Form.Filter = strFilter1
 Forms!frmMainEdit.Form.FilterOn = True

 DoCmd.Quit

 End Sub
TarasAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
I agree with Pat - move those forms into the same database, and link the tables needed. That's the simplest way, at least in my view.

If you simply must do this, then you must preface any call to the "other" database with your automated instance. For example, if "frmMainOutstand" is in the automated database you'd do this:

appAccess.Forms!frmMainOutstand!sfrmMainOutStandCurr.Form.Filter = strFilter1
appAccess.Forms!frmMainOutstand!sfrmMainOutStandCurr.Form.FilterOn = True

But - again - you would be much, much better off putting everything into the same container.
0
 
PatHartmanConnect With a Mentor Commented:
In your current procedure, I don't see where you are setting a value for strFilter

Why not link the HistoryDB to the CurrentDB so you can work within one database and not worry about using OLE animation to control one database from another?
0
 
TarasAuthor Commented:
I just wanted to simplify it but strFilter1 was set something like this.


Private Sub CmdOpen_Form_In_CurrentDatabase_Click()


  Dim strDB As String
  strDB = "D:\Databases\CurrentdB.mdb"
     
  Dim appAccess As Access.Application
  Set appAccess = New Access.Application

  Dim strFilter1 as string

Dim lngServiceID  As Long
Dim strCAllBackID As String
Dim strTerr As String
Dim strContrID As String


lngServiceID = Me.ServiceID
If Nz(Me.ServiceID, 0) = 0 Or Me.ServiceID = "" Then
    MsgBox "Please Check Service ID number"
    Exit Sub
End If

strCAllBackID = Me.CallBackID
If Nz(Me.CallBackID, "") = "" Or Me.CallBackID = "" Then
    MsgBox "Please Check Callback ID number"
    Exit Sub
End If

strTerr = Me.TerritoryID
If Nz(Me.TerritoryID, "") = "" Or Me.TerritoryID = "" Then
    MsgBox "Please check Territory ID number"
    Exit Sub
End If
strContrID = Me.ContractorID
If Nz(Me.ContractorID, "") = "" Or Me.ContractorID = "" Then
    MsgBox "Please check Contractor ID "
    Exit Sub
End If

StrFilter1 = " [TerritoryID]='" & strTerr & "' And [CallBackID] = '" & strCAllBackID & " '  And [ServiceID] = " & lngServiceID & " And [ContractorID] ='" & strContrID & "' "





  appAccess.OpenCurrentDatabase strDB
  appAccess.Visible = True

  appAccess.UserControl = True





  stDocName = "frmMainOutStand"
  appAccess.DoCmd.OpenForm stDocName

  Forms!frmMainOutstand!sfrmMainOutStandCurr.Form.Filter = strFilter1
  Forms!frmMainOutstand!sfrmMainOutStandCurr.Form.FilterOn = True


  Forms!frmMainOutstand!sfrmMainOutStandHis.Form.Filter = strFilter1
  Forms!frmMainOutstand!sfrmMainOutStandHis.Form.FilterOn = True






  stDocName = "frmMainEdit"
  appAccess.DoCmd.OpenForm stDocName

  Forms!frmMainEdit.Form.Filter = strFilter1
  Forms!frmMainEdit.Form.FilterOn = True

  DoCmd.Quit

  End Sub
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
TarasAuthor Commented:
User wants to have some setup of current and archive- history database of services that hi perform for customers.
In History database  he would like to store data from previous  years and he want to use current database just for current year records.
From time to time he wants to go to HistoryDB find old record bring it I current database make some changes and make it new record.
So they ask me to create this scenario make copy of current database and name it HistoryDB , then empty some tables in HistoryDB. Then create separate form in CurrentDB with   command button and one  field (text box) for entering a year  of export. Then by click on button export all records from CurrentDb  for particular year in HistoryDB.
From time to time they go back to HistoryDB and look for particular record(service)
In History database they need all features (search,reports,… as current database have)and extra option they want to pick up particular record from HistoryDB and bring it in CurrentDB  and making it new record(new ID and so on)
Scenario is unusual and open for complication, but that is what I have to deal with.
I was thinking about referencing CurrentDb from HistoryDB. Then create  in Currentdb in a module public function: Public Function OpenFormInCurrentWithPassedParametersFromHistory(my filter field values).
 So I can call  from HistoryDB that Function in CurrentDB  and fire those form open procedure with passed filters.
Or I can create in this function SQL select statements that pull records from CurrentDB and make forms record source to these SQL statements and open them.

What do you think about this option referencing CurrentDB in HistoryDB?
0
 
TarasAuthor Commented:
Is referencing CurrentDB in HistoryDB that what Pat refered when she said link HistoryDB to CurrentDB?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Creating historical data, and linking it back to your current "live" data, is fairly commonplace. Linking the historical tables to the "live" database would be the simplest and easiest method, by far. If you have Form in the history db that need to be used, move them to the "live" db and call them from there, and use the linked tables in those forms when needed.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Creating historical data, and linking it back to your current "live" data, is fairly commonplace. Linking the historical tables to the "live" database would be the simplest and easiest method, by far. If you have Form in the history db that need to be used, move them to the "live" db and call them from there, and use the linked tables in those forms when needed.
0
 
TarasAuthor Commented:
Thank You a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.