Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


reference from one form to another on record

Posted on 2011-02-12
Medium Priority
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

by:Helen Feddema
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?

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

LVL 24

Accepted Solution

Bitsqueezer earned 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
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…

636 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