Solved

Reference form in different databases

Posted on 2011-03-11
14
233 Views
Last Modified: 2012-05-11
Hi I have 2 form in 2 databases:
1. Docs
2. Code

1st form has following control: [Forms]![InputForm]![LoanNos]  located in "Docs"

2nd form has following conrrol: [Forms]![InputForm-Main]![Loan Number]  located in "Code"

Using Code :

im objAccess As Access.Application
    Set objAccess = New Access.Application
    objAccess.Visible = True
    objAccess.OpenCurrentDatabase ("G:\Code.mdb")
    objAccess.DoCmd.OpenForm "InputForm-MAIN"

Using code above I was able to open form.  However, InputForm-Main going to the 1st record.  I need to be able to go the same record as it was in the form [Forms]![InputForm]![LoanNos] (located in db "Docs")

So, last line needs argument.  Would you please help me with this one

Note: please do not provide comments not relevant to this question (something like I should be using one database)


0
Comment
Question by:maximyshka
[X]
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
  • 7
  • 7
14 Comments
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35113214
With the OpenForm method, try using the WhereCondition parameter

objAccess.DoCmd.OpenForm FormName:="InputForm-MAIN",WhereCondition:="Loan Number=" & Forms![InputForm]![LoanNos]

This will filter the form in Code to show just records that match.

Hope this helps,
pT72
0
 

Author Comment

by:maximyshka
ID: 35113279
Run Time Error 3075. Syntax Error (Missing Operator) in query expression Loan Number=1111111111-111111

Please be advised that loan number is text 17 characters
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35113677
Ah, since the field is stored as text in the underlying tables, the value for the comparison must be surrounded by apostrophes:

objAccess.DoCmd.OpenForm FormName:="InputForm-MAIN",WhereCondition:="Loan Number=" & Chr(39) & Forms![InputForm]![LoanNos] & Chr(39)

HTH,
pT72
0
Technology Partners: 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!

 

Author Comment

by:maximyshka
ID: 35128550
I checked provided code.  Same error:

Run Time Error 3075. Syntax Error (Missing Operator) in query expression 'Loan Number='1111111111-111111''
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35128973
I think the problem now is the space in the field name.

objAccess.DoCmd.OpenForm FormName:="InputForm-MAIN",WhereCondition:="[Loan Number]=" & Chr(39) & Forms![InputForm]![LoanNos] & Chr(39)

HTH,
pT72


0
 

Author Comment

by:maximyshka
ID: 35129689
It is not going to the Loan Number chosed in the first database.  See attached file

Since parameter box was opened I have issue with sending loan number as a parameter.


Open-second-database.doc
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35129989
If the Parameter box opens through this code, but not normally, that means we mis-spelled the underlying field name. In normal operation do you get a parameter box?

If you look in the second database's form, what is the control source of the upper left textbox? It is labelled Loan Number, but the control source may be Loan_Number or LoanNumber or something completely different. Whatever the property box says for ControlSource is what we want as the first word in the WhereCondition argument. If it has a space or odd character, surround it in square brackets.

To clarify, try saving / showing the WhereCondition before it is used:
Dim strCrit As String
strCrit = "[Control Source from TextBox in second form]=" & Chr(39) & Forms![InputForm]![LoanNos] & Chr(39)
Debug.Print strCrit
objAccess.DoCmd.OpenForm FormName:="InputForm-MAIN",WhereCondition:=strCrit

Open in new window


0
 

Author Comment

by:maximyshka
ID: 35131207
I added code which you provided in last comment.  Same thing it is incorrectly show parameter.  Second form is going to the first record

See code used below

No more error messages.  It is not working
Set objAccess = New Access.Application
    objAccess.Visible = True
    objAccess.OpenCurrentDatabase ("G:\DATA\General\Access\Database\Sa-Code.mdb")
    Dim strCrit As String
strCrit = "[Control Source from TextBox in second form]=" & Chr(39) & Forms![InputForm]![LoanNos] & Chr(39)
Debug.Print strCrit
objAccess.DoCmd.OpenForm formname:="InputForm-MAIN", WhereCondition:=strCrit

Open in new window

Doc2.doc
0
 

Author Comment

by:maximyshka
ID: 35131241
I normal operation I should not get parameter box.

0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35131669
What is the control source of the textbox in the second database's form? We need to paste that between the first set of square brackets in line 5. Once it is spelled correctly, the parameter box will go away.

pT72
0
 

Author Comment

by:maximyshka
ID: 35131910
Control box is LoanNo

See attached screen shot
Doc3.doc
0
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 total points
ID: 35132160
Great. That's what we were missing.

Dim strCrit As String
strCrit = "[LoanNo]=" & Chr(39) & Forms![InputForm]![LoanNos] & Chr(39)
Debug.Print strCrit
objAccess.DoCmd.OpenForm FormName:="InputForm-MAIN",WhereCondition:=strCrit

Open in new window


This should filter correctly. If it doesn't, please report the the text that is printed on the Immediate Pane in the debugger (Press Ctrl-G to view it).

pT72
0
 

Author Comment

by:maximyshka
ID: 35132424
Thank you very much.  You did such excellent work.

As I understand "Debug.Print strCrit" is not needed.  Am I correct?
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35132516
Correct. That is for debugging purposes only and should be removed once you are satisfied with the code. It's a good way to see what Access actually sees when your results don't match expectations. I'm glad it is working now.
pT72
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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 …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

739 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