• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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