Open second form based on field value in first form

[laavista]

I need help. This seems like it should be so simple, but …

I'm using Access 2007.

I have two forms, f_MilBase and f_Contact. Both have the field “BaseCodeâ€

On form f_MilBase, I have a command button. On Click, I wish to have form
f_Contact open with just the records for the BaseCode populated in f_MilBase

BaseCode is a text field, 5 char
Examples of BaseCodes in the db: 5234E or Beale

====
1stTry

DoCmd.OpenForm "f_contact", _
WhereCondition:="BaseCode=" & Me.BaseCode

In this case, I get error “Syntax error in number in query expression
‘BaseCode=5234’. (The last digit is left off of the basecode in form1, which
is 5234E)
====

2ndTry

Dim strCode as String
DoCmd.OpenForm “f_Contactâ€,,,â€[BaseCode]=†& strCode

I get the same error as 1stTry

===
3rd try
If I try a different record in f_milBase, e.g., with a BaseCode of “Bealeâ€,
for both 1st & 2nd try, I get error “Enter Parameter Value (for BEALE)â€
==

4th try

I’ve also tried this with f_Contact having a sub_form, with f_MiliBase’s
“BaseCode†being on the main form and f_Contact being the subform. No luck.


Any suggestions would be GREATLY appreciated.

 

[laavista]

I'M USING ACCESS 2003 (2007 was a typo. Wish I WAS using 2007!)

Laavista

 

[John Spencer MVP]

Since the base code is a string, you must use string delimiters.

There are several ways to add the necessary string delimiters - here are three.

DoCmd.OpenForm "f_contact", _
WhereCondition:="BaseCode=""" & Me.BaseCode & """"

Or

DoCmd.OpenForm "f_contact", _
WhereCondition:="BaseCode='" & Me.BaseCode & "'"

Or
DoCmd.OpenForm "f_contact", _
WhereCondition:="BaseCode="& Chr(34) & Me.BaseCode & Chr(34)



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[laavista]

John, THANK YOU so much for taking the time to provide this solution. It
worked!!!
I spent 3 hours trying everything I could think of to make it work with no
success.

This forum is great, and I really appreciate the experts, like you, who
share their knowledge.

Thanks again!

Laavista

Since the base code is a string, you must use string delimiters.

There are several ways to add the necessary string delimiters - here are three.

DoCmd.OpenForm "f_contact", _
WhereCondition:="BaseCode=""" & Me.BaseCode & """"

Or

DoCmd.OpenForm "f_contact", _
WhereCondition:="BaseCode='" & Me.BaseCode & "'"

Or
DoCmd.OpenForm "f_contact", _
WhereCondition:="BaseCode="& Chr(34) & Me.BaseCode & Chr(34)



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I need help. This seems like it should be so simple, but …

I'm using Access 2007.

I have two forms, f_MilBase and f_Contact. Both have the field “BaseCodeâ€

On form f_MilBase, I have a command button. On Click, I wish to have form
f_Contact open with just the records for the BaseCode populated in f_MilBase

BaseCode is a text field, 5 char
Examples of BaseCodes in the db: 5234E or Beale

====
1stTry

DoCmd.OpenForm "f_contact", _
WhereCondition:="BaseCode=" & Me.BaseCode

In this case, I get error “Syntax error in number in query expression
‘BaseCode=5234’. (The last digit is left off of the basecode in form1, which
is 5234E)
====

2ndTry

Dim strCode as String
DoCmd.OpenForm “f_Contactâ€,,,â€[BaseCode]=†& strCode

I get the same error as 1stTry

===
3rd try
If I try a different record in f_milBase, e.g., with a BaseCode of “Bealeâ€,
for both 1st & 2nd try, I get error “Enter Parameter Value (for BEALE)â€
==

4th try

I’ve also tried this with f_Contact having a sub_form, with f_MiliBase’s
“BaseCode†being on the main form and f_Contact being the subform. No luck.


Any suggestions would be GREATLY appreciated.