472,785 Members | 1,221 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,785 software developers and data experts.

Problem passing WHERE string in DoCmd.OpenForm

Lyn
Hi,
I have a Search input form which collects from the user a person's name. I
am using LIKE with a "%" suffix in the SQL so that the user does not have to
type in the full name. When they hit the Search button, a query is run to
search the Person table for a match. This produces a recordset (I am using
ADO).

If the RecordCount is zero, they get a No Match message.

If the RecordCount is 1, a DoCmd.OpenForm is performed to open the person's
Detail form -- this passes the record's RecordID as the OpenForm's WHERE
argument (ie, "[RecordID] = n"). This works fine.

If the RecordCount is greater than 1, a DoCmd.OpenForm is performed to open
a Listing form which will list all matching records -- the user can then
select one from the list and use it to open the Detail form. This part is
NOT WORKING.

The DoCmd statement looks like this:-

DoCmd.OpenForm "frmPersonList", , , strWhere, acFormReadOnly

strWhere is declared as a String (I have also tried making it a Variant to
match the OpenForm argument). This variable is concatenated with the SQL
string to produce the original RecordSet referred to above -- ie:-

mySQL = "SELECT * FROM <table> WHERE " & strWhere

This successfully returns the correct RecordCount for all scenarios, so I
know it works.

For debugging, I have reduced the code to using just Surname in the search,
so the value of strWhere is simply (eg) "[Surname] LIKE 'Smit%'". This
results in a RecordCount that accounts for all the Smiths in the table.
However, when I pass strWhere with this value as the OpenForm argument, the
Listing form is opened as an empty form.

If I modify strWhere to "[Surname] LIKE 'Smith'", the Listing form opens
with all the Smiths listed. So I suspect that it is a quoting problem with
the "%" character. I have tried various alternatives -- single quotes,
escaped double quotes, Chr(39), etc, but nothing seems to work.

Can anyone see what I am doing wrong? Why does strWhere work in the SQL in
the parent form, but not when it is passed in OpenForm?

I am using Access 2003 under WinXP. I also have a related question, but I
will create another post for that (this one is to long already).

Thanks for any advice.

--
Cheers,
Lyn.
Nov 13 '05 #1
3 4344
Perhaps a * instead of the %?

"Lyn" <lh******@ihug.com.au> wrote in message
news:cr**********@lust.ihug.co.nz...
Hi,
I have a Search input form which collects from the user a person's name. I am using LIKE with a "%" suffix in the SQL so that the user does not have to type in the full name. When they hit the Search button, a query is run to
search the Person table for a match. This produces a recordset (I am using ADO).

If the RecordCount is zero, they get a No Match message.

If the RecordCount is 1, a DoCmd.OpenForm is performed to open the person's Detail form -- this passes the record's RecordID as the OpenForm's WHERE
argument (ie, "[RecordID] = n"). This works fine.

If the RecordCount is greater than 1, a DoCmd.OpenForm is performed to open a Listing form which will list all matching records -- the user can then
select one from the list and use it to open the Detail form. This part is
NOT WORKING.

The DoCmd statement looks like this:-

DoCmd.OpenForm "frmPersonList", , , strWhere, acFormReadOnly

strWhere is declared as a String (I have also tried making it a Variant to
match the OpenForm argument). This variable is concatenated with the SQL
string to produce the original RecordSet referred to above -- ie:-

mySQL = "SELECT * FROM <table> WHERE " & strWhere

This successfully returns the correct RecordCount for all scenarios, so I
know it works.

For debugging, I have reduced the code to using just Surname in the search, so the value of strWhere is simply (eg) "[Surname] LIKE 'Smit%'". This
results in a RecordCount that accounts for all the Smiths in the table.
However, when I pass strWhere with this value as the OpenForm argument, the Listing form is opened as an empty form.

If I modify strWhere to "[Surname] LIKE 'Smith'", the Listing form opens
with all the Smiths listed. So I suspect that it is a quoting problem with the "%" character. I have tried various alternatives -- single quotes,
escaped double quotes, Chr(39), etc, but nothing seems to work.

Can anyone see what I am doing wrong? Why does strWhere work in the SQL in the parent form, but not when it is passed in OpenForm?

I am using Access 2003 under WinXP. I also have a related question, but I
will create another post for that (this one is to long already).

Thanks for any advice.

--
Cheers,
Lyn.

Nov 13 '05 #2
Lyn
Yes, that worked! I still find it puzzling -- in an SQL statement you have
to use "%" as the wildcard for LIKE ("*" doesn't work here). But in the
Help for OpenForm, the WHERE argument is described as a "string expression
that's a valid SQL WHERE clause without the word WHERE." Apparently that is
not strictly true.

Anyway, I have worked around the issue by leaving strWhere with "%" for the
SQL statement, then doing a Replace function on it ("*" for "%") before
applying it to the OpenForm statement.

A trap for young players.

Many thanks for the prompt assistance.

--
Cheers,
Lyn.

"MacDermott" <ma********@nospam.com> wrote in message
news:fs*************@newsread3.news.atl.earthlink. net...
Perhaps a * instead of the %?

"Lyn" <lh******@ihug.com.au> wrote in message
news:cr**********@lust.ihug.co.nz...
Hi,
I have a Search input form which collects from the user a person's name.

I
am using LIKE with a "%" suffix in the SQL so that the user does not have

to
type in the full name. When they hit the Search button, a query is run
to
search the Person table for a match. This produces a recordset (I am

using
ADO).

If the RecordCount is zero, they get a No Match message.

If the RecordCount is 1, a DoCmd.OpenForm is performed to open the

person's
Detail form -- this passes the record's RecordID as the OpenForm's WHERE
argument (ie, "[RecordID] = n"). This works fine.

If the RecordCount is greater than 1, a DoCmd.OpenForm is performed to

open
a Listing form which will list all matching records -- the user can then
select one from the list and use it to open the Detail form. This part
is
NOT WORKING.

The DoCmd statement looks like this:-

DoCmd.OpenForm "frmPersonList", , , strWhere, acFormReadOnly

strWhere is declared as a String (I have also tried making it a Variant
to
match the OpenForm argument). This variable is concatenated with the SQL
string to produce the original RecordSet referred to above -- ie:-

mySQL = "SELECT * FROM <table> WHERE " & strWhere

This successfully returns the correct RecordCount for all scenarios, so I
know it works.

For debugging, I have reduced the code to using just Surname in the

search,
so the value of strWhere is simply (eg) "[Surname] LIKE 'Smit%'". This
results in a RecordCount that accounts for all the Smiths in the table.
However, when I pass strWhere with this value as the OpenForm argument,

the
Listing form is opened as an empty form.

If I modify strWhere to "[Surname] LIKE 'Smith'", the Listing form opens
with all the Smiths listed. So I suspect that it is a quoting problem

with
the "%" character. I have tried various alternatives -- single quotes,
escaped double quotes, Chr(39), etc, but nothing seems to work.

Can anyone see what I am doing wrong? Why does strWhere work in the SQL

in
the parent form, but not when it is passed in OpenForm?

I am using Access 2003 under WinXP. I also have a related question, but
I
will create another post for that (this one is to long already).

Thanks for any advice.

--
Cheers,
Lyn.


Nov 13 '05 #3
I think % is typically used in T-SQL, the language used by SQL Server, while
* is used in ANSI-SQL, which is used by Jet, which is native to Access.

"Lyn" <lh******@ihug.com.au> wrote in message
news:cr*********@lust.ihug.co.nz...
Yes, that worked! I still find it puzzling -- in an SQL statement you have to use "%" as the wildcard for LIKE ("*" doesn't work here). But in the
Help for OpenForm, the WHERE argument is described as a "string expression
that's a valid SQL WHERE clause without the word WHERE." Apparently that is not strictly true.

Anyway, I have worked around the issue by leaving strWhere with "%" for the SQL statement, then doing a Replace function on it ("*" for "%") before
applying it to the OpenForm statement.

A trap for young players.

Many thanks for the prompt assistance.

--
Cheers,
Lyn.

"MacDermott" <ma********@nospam.com> wrote in message
news:fs*************@newsread3.news.atl.earthlink. net...
Perhaps a * instead of the %?

"Lyn" <lh******@ihug.com.au> wrote in message
news:cr**********@lust.ihug.co.nz...
Hi,
I have a Search input form which collects from the user a person's name.
I
am using LIKE with a "%" suffix in the SQL so that the user does not
have to
type in the full name. When they hit the Search button, a query is run
to
search the Person table for a match. This produces a recordset (I am

using
ADO).

If the RecordCount is zero, they get a No Match message.

If the RecordCount is 1, a DoCmd.OpenForm is performed to open the

person's
Detail form -- this passes the record's RecordID as the OpenForm's
WHERE argument (ie, "[RecordID] = n"). This works fine.

If the RecordCount is greater than 1, a DoCmd.OpenForm is performed to

open
a Listing form which will list all matching records -- the user can then select one from the list and use it to open the Detail form. This part
is
NOT WORKING.

The DoCmd statement looks like this:-

DoCmd.OpenForm "frmPersonList", , , strWhere, acFormReadOnly

strWhere is declared as a String (I have also tried making it a Variant
to
match the OpenForm argument). This variable is concatenated with the SQL string to produce the original RecordSet referred to above -- ie:-

mySQL = "SELECT * FROM <table> WHERE " & strWhere

This successfully returns the correct RecordCount for all scenarios, so I know it works.

For debugging, I have reduced the code to using just Surname in the

search,
so the value of strWhere is simply (eg) "[Surname] LIKE 'Smit%'". This
results in a RecordCount that accounts for all the Smiths in the table.
However, when I pass strWhere with this value as the OpenForm argument,

the
Listing form is opened as an empty form.

If I modify strWhere to "[Surname] LIKE 'Smith'", the Listing form opens with all the Smiths listed. So I suspect that it is a quoting problem

with
the "%" character. I have tried various alternatives -- single quotes,
escaped double quotes, Chr(39), etc, but nothing seems to work.

Can anyone see what I am doing wrong? Why does strWhere work in the SQL in
the parent form, but not when it is passed in OpenForm?

I am using Access 2003 under WinXP. I also have a related question,

but I
will create another post for that (this one is to long already).

Thanks for any advice.

--
Cheers,
Lyn.



Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: David | last post by:
Hi, I'm working with Acces2000 under W2000. I'm using these sentences: Dim Camp As Variant Dim stDocName As String Dim stCriteri As String
6
by: beowulfs | last post by:
Here's what I've got: I've got a form with combo boxes. you can select already existing company names or type in new ones. if you type in a new one, it prompts you to double click the combo...
15
by: Thelma Lubkin | last post by:
formA determines some_where and some_value and issues docmd.close ,Me docmd.openform "formB", , ,some_where, , ,some_value formB receives the correct some_where and some_value After...
2
by: Mike | last post by:
I am trying to open a search results form based on the input from a prompt form. I am using the following code: --- Begin Code --- Private Sub btnSearch_Click() 'Dim Variable and assign data...
2
by: mark mestrom | last post by:
hi, i have this problem with OpenForm and the stLinkCriteria. I have the following code: Private Sub Knop22_Click() On Error GoTo Err_Knop22_Click Dim stDocName As String Dim...
2
by: pcaisse | last post by:
Could someone please tell me why this stupid script doesn't print the string being passed to the sub?: #!/usr/bin/perl -w use strict; print "Work, damnit!\n"; my $val = "print me";
3
by: gavm360 | last post by:
Hello, im trying to open a form from an dialog box form: the button on the dialog box has this on the onclick event: DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , "MCH_CASECODE = #" &...
1
by: silen | last post by:
i am using Access2000. Currently i had a main form call "frmDownload" and sub form "fsubAdmmission". Under the fsubAdmmission, i do have another sub form "fsubHospital". Once i link my application to...
3
by: JenniferM | last post by:
Back again with a brand new problem. My head hurts.... I've got a couple of listboxes on a form, FrmPatientDemographics. One contains knee surgeries for that patient (LbxKneeSx) and the other...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.