Solved

MS Access filter Form from one database in another database

Posted on 2016-09-30
8
39 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 35

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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
 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office 365 home questions 7 65
Passing variables to a function 6 34
VBA Access 2016 syntax 6 41
MS access 2010. Trouble on exporting to excel 2010 13 8
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

770 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