Solved

reference from one form to another on record

Posted on 2011-02-12
11
472 Views
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
db1-2-.mdb
db2-2-.mdb
0
Comment
Question by:maximyshka
11 Comments
 
LVL 77

Expert Comment

by:peter57r
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.
0
 
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?
0
 

Author Comment

by:maximyshka
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?




db1-2.mdb
db2-2.mdb
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 34925467
Loan.zip

Hi,

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.

Cheers,

Christian
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:maximyshka
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
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35106544
Hi,

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.

Cheers,

Christian

Loan.zip
0
 

Author Comment

by:maximyshka
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
http://www.experts-exchange.com/Database/Miscellaneous/Q_21732902.html?sfQueryTermInfo=1+10+30+databas+differ+form+refer

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
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35113527
Hi,

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.

Cheers,

Christian
0
 

Author Comment

by:maximyshka
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).

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

708 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

15 Experts available now in Live!

Get 1:1 Help Now