Passing parameters before opening forms

[Guest]

I know I'm doing this the wrong way; I may also be doing this the long way

I have a table with students' names and ID numbers. To retrieve a record, I
provide a pop-up, frmRecordLookup, where either indexed field can be entered,
either in full or in part. For instance, rather than retrieving a long list
of Smith's, the user could enter "joh smi", and a list matching "joh* smi*"
would be displayed; the entry "smith" would be transformed into "* smith*".

The After Update routine intends to display on frmSelectRecord the retrieved
names and IDs, along with other identifying information, so that the user may
select the correct record to update. lstSelectRecord, a listbox on
frmSelectRecord, has the recordsource "SELECT * FROM qryFindName".
qryFindName contains the code
PARAMETERS prm_strSearch Text ( 255 );
SELECT strApplicantName, pk_strID, [...]
FROM tblRatingDetail
WHERE strApplicantName Like prm_strSearch;
The query runs correctly from the database window, as the parameter is
directly input.

I have tried to set the parameter to the query in several ways.
txtName_AfterUpdate in Form_frmRecordLookup contains
Private Sub txtApplicantName_AfterUpdate()
Dim bytSpace As Byte
Dim strSearch As String

bytSpace = InStr(txtApplicantName, " ")

If bytSpace Then
strSearch = Left$(txtApplicantName, bytSpace - 1) & "* " &
Right$(txtApplicantName, Len(txtApplicantName) - bytSpace) & "*"
Else
strSearch = "* " & txtApplicantName
End If

DoCmd.Close
CurrentDb.QueryDefs!qryFindName!prm_strSearch = strSearch
DoCmd.OpenForm "frmSelectRecord"
End Sub

I have tried making frmSelectRecord bound to qryFindName; I've tried it
unbound. In lieu of opening the form, I've tried
DoCmd.OpenQuery "qryFindName"
I've tried deleting the parameter statement, changing
DoCmd.OpenForm "frmSelectRecord",OpenArgs:=strSearch
and adding
CurrentDb.QueryDefs!qryFindName!prm_strSearch = OpenArgs
to Form_Open in the Form_frmSelectRecord module.

With every option, the parameter pop-up appears! HELP!!!

Must I use object variables with parameters? Must I requery after setting
the parameter in Form_Open? WHAT am I doing WRONG?!?

 

[Bob Hairgrove]

I know I'm doing this the wrong way; I may also be doing this the long way

At the very least, you need to split the single column of names into
first and last name columns in order to do this properly.

Besides, you need to read the help topic for the SQL keyword "LIKE"
and everything about parameters very carefully. You cannot include
more than one star in the "LIKE" criteria.

 

[Bob Hairgrove]

You cannot include
more than one star in the "LIKE" criteria.

Well, actually you can if they are at the beginning and end of the
expression. Anywhere else, it doesn't usually make sense to do so.

 

[Guest]

Thanks for your thoughts!

I wonder if I found a beneficial glitch in Access... I just tested the
multiple-star match, and it worked. I hard-coded the match:
SELECT strApplicantName, pk_strPOISE_ID, [...]
FROM tblRatingDetail
WHERE strApplicantName Like "joh* smi*";
and the query produced correct results. (I have more than one John, but
only John Smith was returned) I also successfully ran qryFindName,
responding
joh* smi*
to the parameter prompt.

I agree that keeping the name segments together is rather awkward, but the
commercially-available system to which this module is indirectly related uses
that construct. I've been asked to remain consistent with that system, as
long as it functions properly. I will test separate fields to see if that
solves my problems.

 

[Guest]

Besides, you need to read the help topic for the SQL keyword "LIKE"

and everything about parameters very carefully. You cannot include
more than one star in the "LIKE" criteria.

Sorry, Bob, that is not correct. You can include multiple * in a Like
statment; however, you may not always get what you are expecting. I just
tested this on a table I have and using:
LIKE "Jo* Sm*" would return more than you would want

John Smith
Joe Smith
Joanne Smith
Joshua Smothers

 

[Guest]

Because we are aware of the possible ambiguity, this is just a gross screen;
these results are fed into a listbox with more fields. The conventional
search construction in our purchased system is the first three letters of the
first name and the first three letters of the last name. In a database of
over 100K names, this usually produces a list of a dozen or less -- except
for very common names whose identical entries would only be discriminated by
other data (e.g., enrollment term, and city and state).