Link to home
Start Free TrialLog in
Avatar of maximyshka
maximyshka

asked on

reference from one form to another on record

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
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
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?
Avatar of maximyshka
maximyshka

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
Hi.  This message for Chris and other experts, Peter, Helen

Answer for this question was pretty simple

It is listed in the question
https://www.experts-exchange.com/questions/21732902/Open-MSaccess-for-in-a-different-database.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
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
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).