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. 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.
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.
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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
|
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...
|
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...
|
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...
|
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...
|
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";
|
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 = #" &...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
...
|
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...
|
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...
|
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...
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
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...
| |