reference from one form to another on record

Posted on 2011-02-12
Last Modified: 2012-05-11
Hi, I have 2 forms in 2 databases:

1) db1-2, form: "loan", text field: Loan Number
2) db2-2, form: "loan2", text field: Loan Number

Form "loan" has button "Form"

Using button "OnClick" event I ask you to do the code similar to
the following:
db1-2![forms]![Loan]![Loan Number]= db2-2![forms]![Loan2]![Loan Number]

Note: please don't be confused that provided sample databases are simple.  I will need to integrate provided code to more complicated databases:

Note 2: Please do not provide answer based on linking tables only.  I can do it myself.  You can link tables.  However, I need code which references record from one form to another in different databases.

For simplicity db1 and d2 have identical data
Question by:maximyshka
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 77

Expert Comment

ID: 34878965
I think the Q that most readers will have is  given that you can do this easily using linked tables why you are choosing not to.
 But crucially you do say that if you used linked table you could do this, so that means the data you are trying to get is already saved in a table.  Would you confirm this.

So if you just opened the other database in code to get the data is that acceptable - even that assumes that the other database is not opened exclusivly, of course.
LVL 31

Expert Comment

ID: 34879491
Referencing a value on a form in another database might not work, because you don't know what record the form is on.  Or the form might not be open.  Why not just work with a recordset of data in the other database, and go to the appropriate record using an ID field?

Author Comment

ID: 34884079
Hellen and Peter:

Please see provided version of sample databases:

1. deals with loan number, renewal date, maturity, balance etc
2) deals with customer property

I changed data in 2 databases to reflect that each database has little difference in data.  This happened very often.

So: I have 2 different forms.

However, forms located in different databases.  


Sometimes, your suggestion reflect that I should copy form to one database and work with the data I want.

However, Task which I have is dynamically based on ID or Loan Number (up to you) open form in second database

You can consider that first database and form have main records, second database have follow-up property information.

Table linkage is necessary. That is out of questions.

Task which I have is open second form in second database based on ID or Loan Number.  Using Button in the first form.

I understand that it is incorrect design, both forms should be copy to one database etc.  Question to you can you provide code which allow to do that?

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 24

Accepted Solution

Bitsqueezer earned 500 total points
ID: 34925467


the attached demo shows how you can access a form on another database.

The trick: You cannot only add DLLs as reference in VBA, you can also add other database files as reference. In db2 I changed the name of the project in VBA because otherwise you get a name conflict. Then I added a simple module which tests if the form "Loan2" is open or not, if not, it opens it first. In the end the function returns the current value of the "Loan Number" field in the second database.

In database 1 you can now call the function of the module in database 2 which opens the form and returns the number. As I saw no real sense in setting the number of the field "Loan Number" in the form of db1 to the value of the form in db2 (this would overwrite the record's number in db1 and moreover it is not possible with your query) I alternatively added a code to search the same number of db2 in the form of db1. So whenever you change the record in the open form of db2 and then click the "form" button in the form of db1 it jumps to the same number if it exists.

So you see it is possible to access forms of other databases, it is of course possible to access anything else of the other database but as Helen & Peter said above: This is no really good idea.

If you work with databases you work with tables, with data, not with forms. Forms should only be used to display data in tables, not to display data and then access data on the forms. Data should always be in one database only to hold the data for all kind of frontends. If you need to use distributed databases for use in external offices with no live connection then you should use an algorithm to mix this data back into the main database if the external database comes back to the main office - but not use dirty tricks like in this demo to access forms and then access their data - really bad method!
The method shown here normally is used to create methods which should be used in more than one database as a library.

If the data is mixed back into the main database then you can work again with one data structure - that's the way a database should be used.



Author Comment

ID: 35101616
Hi.  This is message for Chris.  Chris I tried your approach it is not working.  It is working with your databases.  It is not working with mine.  When I click on button I'm going to the first record of the form -- not what I want.  Chris, I understand about bad methods.  Nothing can be done. Please help if you can
LVL 24

Expert Comment

ID: 35106544

difficult to help you without knowing what you've done.

First step for linking databases is to set the reference to the other database in the database where you want to access a remote database. So if "DB1" is the database which want to call a method of an external database you most open the VBA editor, open "Tools"-"References" and browse to the remote database. Don't forget to set the checkbox for the chosen database to activate the reference in the current one.

In the remote database there must be a function/sub which MUST be set to "Public" inside a standard module, otherwise you don't get access to it. You can use as many functions/subs as you want, but all must be set to "Public" if you want to use it from the other database.

As these Public functions/subs have of course access to the local ressources (forms, tables, reports...) of the own database you can here use commands to open forms and so on and in case of functions to return a value back to the calling database (like in the Public Function CurrentLoanNumber returning a string to the calling database).

If you look into the demo above it will only open a form in the remote database without anything else, so of course also here it will display the first record of the remote form. If you want the remote form to jump to a special record, change the function "CurrentLoanNumber". Add for example an ID parameter which identifies the wanted record uniquely in the remote database or use a search string parameter and then you can use the "FindFirst" method of the remote form's recordset to set the record bookmark to the right record. You could also return an error value in the return string so that the calling form can display to the user that the record was not found on the other database.

An extended version could use a class object to transport informations between the two databases and the remote functions. The advantage is that you don't need to use long parameter definitions, and you can get the object's data back which can be as complex as you want (a function only allows one return value and a standard datatype is most often not enough). I attached a demo which uses a class instead. This is also a very simple demo, the class object can of course be more complex than that, can contain own functions and subs and so on.



Author Comment

ID: 35112743
Hi.  This message for Chris and other experts, Peter, Helen

Answer for this question was pretty simple

It is listed in the question

That's exactly what I need it.  I'm sorry Chris, however I don't understand why previous experts were not able to solve this problem and brough any kind of comments that database was not correct.

Thank you Chris for your help.  I will not reverse points.  At least you tried comparing to others
LVL 24

Expert Comment

ID: 35113527

as you know, there are always more than one possible solutions for the most problems, same here. If you want to get an answer to a problem in future you maybe shouldn't say to all who tried to help you (and which spent their private time to work on your problem) that they are all wrong because the only right answer is xyz and how could it be possible that they didn't think of the answer xyz...

Of course, this is another solution to your problem, but not even better. This uses Office automation but this has the disadvantage that it opens another Access process and so the form you want to open is in a new Access window outside of the one you are currently using. The solution I wrote above uses only the current Access process and has a clean interface between both - of course with the disadvantage of a "hard link" between both. So any solution have their advantages and disadvantages, there is never "the" solution.

And to come back to the "database not corrent": Maybe the thing with experts is that I guess the most experts would not come to the idea to open a form in a remote Access database to pass data between two databases - as described above, it is a lot easier to open a connection to the other database in a recordset or table link and access the data on this way, with easy, fast SQL commands, that's the way experts design databases. I also said above that I wanted to show you a possible solution but that it is not a good way. I personally never have used the solution I posted above nor the solution of your link, never needed such things for professional databases.



Author Comment

ID: 35132507
Hi Chris.  The main goal was "To open second database and find loan there".  This was listed in the main question.  Please let me know if question was unclear (for future references).


Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

734 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