Solved

MS Access filter Form from one database in another database

Posted on 2016-09-30
8
30 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:Taras
Comment Utility
Is referencing CurrentDB in HistoryDB that what Pat refered when she said link HistoryDB to CurrentDB?
0
 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
Thank You a lot
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

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…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 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

6 Experts available now in Live!

Get 1:1 Help Now