Solved

Reference form in different databases

Posted on 2011-03-11
14
192 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
  • 7
  • 7
14 Comments
 
LVL 14

Expert Comment

by:pteranodon72
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:maximyshka
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:maximyshka
Comment Utility
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
Comment Utility
I normal operation I should not get parameter box.

0
 
LVL 14

Expert Comment

by:pteranodon72
Comment Utility
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
Comment Utility
Control box is LoanNo

See attached screen shot
Doc3.doc
0
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

763 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

12 Experts available now in Live!

Get 1:1 Help Now