Solved

Reference form in different databases

Posted on 2011-03-11
14
214 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
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 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