OpenForm Criteria in Access 2007

[MIG]

In Access 2003 I was able to put an unbound text field on a form and
use whatever was entered the field as the criteria for opening a form.

So I might have one unbound text field and various buttons for "search
by name", "search by postcode" etc, so I could type what I was
matching by in the field and click the appropriate button.

eg

"[PostCode]=" & Me![Text1]

where [Text1] was my unbound field.

This doesn't work in 2007. Firstly, the wizard for linking to the
form being opened doesn't list Text1 as an available field. Secondly,
if I write the code myself, it takes [Text1] as a parameter and asks
for the search term over again.

How can I get 2007 to work like 2003?

(In fact, how can I get Microsoft to revert to Office 2003 anyway,
which seems to have been the last usable version, before they just
stopped everything working out of what seems to be pure spite?)

 

[MIG]

In Access 2003 I was able to put an unbound text field on a form and
use whatever was entered the field as the criteria for opening a form.

So I might have one unbound text field and various buttons for "search
by name", "search by postcode" etc, so I could type what I was
matching by in the field and click the appropriate button.

eg

"[PostCode]=" & Me![Text1]

where [Text1] was my unbound field.

This doesn't work in 2007.  Firstly, the wizard for linking to the
form being opened doesn't list Text1 as an available field.  Secondly,
if I write the code myself, it takes [Text1] as a parameter and asks
for the search term over again.

How can I get 2007 to work like 2003?

(In fact, how can I get Microsoft to revert to Office 2003 anyway,
which seems to have been the last usable version, before they just
stopped everything working out of what seems to be pure spite?)

And, more bizarre, if I abandon that idea and use

"Dim SearchTerm As String
Dim SearchCriteria As String

SearchTerm = InputBox("Enter a Surname")

SearchCriteria = "[Surname]=" & SearchTerm

DoCmd.OpenForm "Person Summary", , , SearchCriteria"

It still comes up with a parameter, named after whatever is typed into
the InputBox. How the hell does that end up being the name of a
parameter?

God I hate Office 2007 so f*cking much.

 

[Access Developer]

Unless my memory plays tricks on me, string criteria must be enclosed in
quotes, and quotes within a string must be doubled. Did you review the code
that actually worked in the older version or just rely on your memory?

Instead of what you show, give the following a try:

"[PostCode]=""" & Me![Text1] & """"
SearchCriteria = "[Surname]=""" & SearchTerm & """"

(note: that's three double-quotes in a
row after the = and four at the end,
in both cases)

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

In Access 2003 I was able to put an unbound text field on a form and
use whatever was entered the field as the criteria for opening a form.

So I might have one unbound text field and various buttons for "search
by name", "search by postcode" etc, so I could type what I was
matching by in the field and click the appropriate button.

eg

"[PostCode]=" & Me![Text1]

where [Text1] was my unbound field.

This doesn't work in 2007. Firstly, the wizard for linking to the
form being opened doesn't list Text1 as an available field. Secondly,
if I write the code myself, it takes [Text1] as a parameter and asks
for the search term over again.

How can I get 2007 to work like 2003?

(In fact, how can I get Microsoft to revert to Office 2003 anyway,
which seems to have been the last usable version, before they just
stopped everything working out of what seems to be pure spite?)

And, more bizarre, if I abandon that idea and use

"Dim SearchTerm As String
Dim SearchCriteria As String

SearchTerm = InputBox("Enter a Surname")

SearchCriteria = "[Surname]=" & SearchTerm

DoCmd.OpenForm "Person Summary", , , SearchCriteria"

It still comes up with a parameter, named after whatever is typed into
the InputBox. How the hell does that end up being the name of a
parameter?

God I hate Office 2007 so f*cking much.

 

[Gloops]

Access Developer wrote, on 18th March 2012 02:17 UTC + 1 :

Unless my memory plays tricks on me, string criteria must be enclosed in
quotes, and quotes within a string must be doubled. Did you review the code
that actually worked in the older version or just rely on your memory?

Instead of what you show, give the following a try:

"[PostCode]="""& Me![Text1]& """"
SearchCriteria = "[Surname]="""& SearchTerm& """"

Right.

I took the habit to write it like that :

SearchCriteria = "Surname='" + Replace(SearchTerm, "'", "''") + "'"

Perhaps you will find it more readable like that :

vbQuot = Chr$(39)

SearchCriteria = "Surname=" + vbQuot + _
Replace(SearchTerm, vbQuot, vbQuot + vbQuot) _
+ vbQuot


meaning that inside the value that is searched for the field, you double
the quote character that is used to delimit it.

It is also OK with vbQuot = Chr$(34), except when using Chr$(34) at the
upper level.

 

[Gloops]

MIG wrote, on 17th March 2012 20:56 UTC + 1 :

In Access 2003 I was able to put an unbound text field on a form and
use whatever was entered the field as the criteria for opening a form.

So I might have one unbound text field and various buttons for "search
by name", "search by postcode" etc, so I could type what I was
matching by in the field and click the appropriate button.

Hello,

It seems what you are looking for is named the assistants, that were
activated by default until Access 2003. On the Creation ribbon, you find
a "Use the control assistants" button (pay attention that I translated
that from the French version, it can be that the official translation is
a little differently expressed).

After you clicked on that, you find the assistants again.

You still have to familiar yourself with the ribbon instead of the
dropdown menus. Fortunately, the development interface has kept a
certain stability, which allows you to execute something via VB if you
do not find it in the ribbon. Of course, the user has to learn Access
with the ribbon, if he has Access with a ribbon. Supposing you are about
to be asked to teach the user, it is a good idea to teach yourself. The
development interface avoids you to be lost when mastering the user
interface is not absolutely necessary (and you master VB, of course).

 

[Gloops]

MIG wrote, on 17th March 2012 20:56 UTC + 1 :

(In fact, how can I get Microsoft to revert to Office 2003 anyway,
which seems to have been the last usable version, before they just
stopped everything working out of what seems to be pure spite?)

Microsoft is encouraging developers to move to .Net.

I do not know exactly how many young developers are motivated to learn
Access. I have some experience since version II, before version 95, so I
begin to have my bearings (I have to admit I helped myself with a
French/English dictionary for this, please tell me if another expression
was more appropriate ...)

I imagine that after beginning with version 2003, it can be a challenge
to adapt to version 2007.

From the user's point of view, I wonder in what extent Microsoft
realized that the basis of users who knew the previous Office interface
was a good advantage for them in front of OpenOffice's competition.

 

[MIG]

Access Developer wrote, on 18th March 2012 02:17 UTC + 1 :

Unless my memory plays tricks on me, string criteria must be enclosed in
quotes, and quotes within a string must be doubled.  Did you review the code
that actually worked  in the older version or just rely on your memory?

Instead of what you show, give the following a try:

    "[PostCode]="""&  Me![Text1]&  """"
    SearchCriteria = "[Surname]="""&  SearchTerm&  """"

Right.

I took the habit to write it like that :

SearchCriteria = "Surname='" + Replace(SearchTerm, "'", "''") + "'"

Perhaps you will find it more readable like that :

vbQuot = Chr$(39)

SearchCriteria = "Surname=" + vbQuot + _
        Replace(SearchTerm, vbQuot, vbQuot + vbQuot) _
        + vbQuot

meaning that inside the value that is searched for the field, you double
the quote character that is used to delimit it.

It is also OK with vbQuot = Chr$(34), except when using Chr$(34) at the
upper level.

Dear Both

Maybe there has been a change in string handling. In Access 2003 and
before, appending a specific string (in single double quotes, if you
see what I mean) to a string variable is interpreted as a text string,
so you don't need to worry about additional quotes around the string
variable.

Is this likely? I've never had to use triple quotes in this sort of
situation before.

 

[MIG]

MIG wrote, on 17th March 2012 20:56 UTC + 1 :



Hello,

It seems what you are looking for is named the assistants, that were
activated by default until Access 2003. On the Creation ribbon, you find
a "Use the control assistants" button (pay attention that I translated
that from the French version, it can be that the official translation is
a little differently expressed).

After you clicked on that, you find the assistants again.

You still have to familiar yourself with the ribbon instead of the
dropdown menus. Fortunately, the development interface has kept a
certain stability, which allows you to execute something via VB if you
do not find it in the ribbon. Of course, the user has to learn Access
with the ribbon, if he has Access with a ribbon. Supposing you are about
to be asked to teach the user, it is a good idea to teach yourself. The
development interface avoids you to be lost when mastering the user
interface is not absolutely necessary (and you master VB, of course).

In the meantime, I seem to have managed to get this to work by using
embedded macros, which seems to be Access 2007's way of doing things.

To get the macro right, I had to use the new button OpenForm wizard on
another form with a bound text box.

Although the wizard refuses to list Text1 (my unbound text box) as a
field I want to use in linking to the form I am opening, I can type it
into the OpenForm Where Condition in the macro grid.

In 2003, the new button OpenForm wizard would have listed Text1.
Maybe this is a new ... er ... feature.

 

[Gloops]

MIG wrote, on 18th March 2012 16:12 UTC + 1 :

Although the wizard refuses to list Text1 (my unbound text box) as a
field I want to use in linking to the form I am opening, I can type it
into the OpenForm Where Condition in the macro grid.

The assistant proposes you the existing tables and queries.
After finishing the assistant, in the Data tab of the properties, you
find the Control source property with a "..." button which opens the
expression generator. This is the good place to select the controls of
the opened forms.

In 2003, the new button OpenForm wizard would have listed Text1.
Maybe this is a new ... er ... feature.

Oh really ? I have to admit I do not remember.

 

[MIG]

MIG wrote, on 18th March 2012 16:12 UTC + 1 :


The assistant proposes you the existing tables and queries.
After finishing the assistant, in the Data tab of the properties, you
find the Control source property with a "..." button which opens the
expression generator. This is the good place to select the controls of
the opened forms.




Oh really ? I have to admit I do not remember.

My problem was that unless I can select a field from the form I am
designing, to link to a field in the form I want to open, I can't
continue with the wizard at all. In Access 2003, the unbound text box
Text1 would be listed and I would select it. In Access 2007, I can't
use the wizard unless the search form includes a data field from a
table (which would be pointless, except maybe a combo box).

 

[Access Developer]

Did you try what we suggested?

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

Access Developer wrote, on 18th March 2012 02:17 UTC + 1 :

Unless my memory plays tricks on me, string criteria must be enclosed in
quotes, and quotes within a string must be doubled. Did you review the
code
that actually worked in the older version or just rely on your memory?

Instead of what you show, give the following a try:

"[PostCode]="""& Me![Text1]& """"
SearchCriteria = "[Surname]="""& SearchTerm& """"

Right.

I took the habit to write it like that :

SearchCriteria = "Surname='" + Replace(SearchTerm, "'", "''") + "'"

Perhaps you will find it more readable like that :

vbQuot = Chr$(39)

SearchCriteria = "Surname=" + vbQuot + _
Replace(SearchTerm, vbQuot, vbQuot + vbQuot) _
+ vbQuot

meaning that inside the value that is searched for the field, you double
the quote character that is used to delimit it.

It is also OK with vbQuot = Chr$(34), except when using Chr$(34) at the
upper level.

Dear Both

Maybe there has been a change in string handling. In Access 2003 and
before, appending a specific string (in single double quotes, if you
see what I mean) to a string variable is interpreted as a text string,
so you don't need to worry about additional quotes around the string
variable.

Is this likely? I've never had to use triple quotes in this sort of
situation before.

 

[Gloops]

MIG wrote, on 18th March 2012 16:04 UTC + 1 :

Is this likely? I've never had to use triple quotes in this sort of
situation before.

Hello,

You mean, if you forget to put a quote character at the end of a line
where there was one at the beginning, it is added automatically.

For sure.

That being said, it is always better to understand what happens and what
it means.
Maybe, considering this, you will eventually realize that your
instruction was differently split into lines ?

Oh no, you said you put a quote and directly resume the string ... I did
not know that trick.

Well, doing the separation with three quote characters is possible, but
I am always hesitant about that count, that can be different in another
application. So, adding the character explicitly with the Chr$ function
sounds to me as allowing to be more sure of what you do.
Of course, if you do it with the three quotes and it works, that is
fine, and probably quicker to type.

 

[Gloops]

MIG wrote, on 18th March 2012 17:54 UTC + 1 :

My problem was that unless I can select a field from the form I am
designing, to link to a field in the form I want to open, I can't
continue with the wizard at all. In Access 2003, the unbound text box
Text1 would be listed and I would select it. In Access 2007, I can't
use the wizard unless the search form includes a data field from a
table (which would be pointless, except maybe a combo box).

Oh, did it ?
I have to say I did not have the time to take conscience of that, as I
remember it did not on previous versions. So, maybe 2003 is the only one
that allows that. It is strange indeed. Maybe it was buggy ?