Solved

MS Access filter Form from one database in another database

Posted on 2016-09-30
8
36 Views
Last Modified: 2016-10-02
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
0
Comment
Question by:Taras
  • 4
  • 3
8 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 150 total points
ID: 41824224
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
 

Author Comment

by:Taras
ID: 41824265
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 350 total points
ID: 41824698
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
 

Author Comment

by:Taras
ID: 41825026
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Taras
ID: 41825061
Is referencing CurrentDB in HistoryDB that what Pat refered when she said link HistoryDB to CurrentDB?
0
 
LVL 84
ID: 41825526
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
 
LVL 84
ID: 41825528
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
 

Author Closing Comment

by:Taras
ID: 41825758
Thank You a lot
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now