• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Reference form in different databases

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
maximyshka
Asked:
maximyshka
  • 7
  • 7
1 Solution
 
pteranodon72Commented:
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
 
maximyshkaAuthor Commented:
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
 
pteranodon72Commented:
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
Independent Software Vendors: 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!

 
maximyshkaAuthor Commented:
I checked provided code.  Same error:

Run Time Error 3075. Syntax Error (Missing Operator) in query expression 'Loan Number='1111111111-111111''
0
 
pteranodon72Commented:
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
 
maximyshkaAuthor Commented:
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
 
pteranodon72Commented:
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
 
maximyshkaAuthor Commented:
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
 
maximyshkaAuthor Commented:
I normal operation I should not get parameter box.

0
 
pteranodon72Commented:
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
 
maximyshkaAuthor Commented:
Control box is LoanNo

See attached screen shot
Doc3.doc
0
 
pteranodon72Commented:
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
 
maximyshkaAuthor Commented:
Thank you very much.  You did such excellent work.

As I understand "Debug.Print strCrit" is not needed.  Am I correct?
0
 
pteranodon72Commented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now