Prev: Lock fields in subform except two after authorization date is ente
Next: populate textbox by name(number)
From: LightByrd on 15 Jan 2010 12:56
Here's a poser for the experienced...
I have created a form whose header allows the user to enter a partspec of a
persons last name so as to identify and retrieve that individual's personal
record.
The entry of the partspec & clicking a cmd button triggers a VB routine
which creates the following SQL statement:

MySQL = "SELECT * FROM [qryFindRec] WHERE [ClientLastName]Like" & Chr(39) &
[LookForRec] & Chr(42) & Chr(39)

(where [qryFindRec] is a query containing enough fields to positively
identify the individual.
and [LookForRec] is the name of the text box control which receives the user
input.)

The SQL statement is then forwarded to the subform contained in the detail
section of the main form via this logic:

Me![frmFindRec_Subform].Form.RecordSource = MySQL

This works! Umm well except for this:
A recordcount showed no records found--although I knew there were several
that matched my test partspec.
Long story short, I had included the key PersonID field in [qryFindRec],
because I needed as a means of retrieving the entire record.
Deleting the ID field from the query solved the problem but then placed the
dreaded #NAME error in its column in the subform. (datasheet view)
I worked around the problem by turning the ID into a string in the query.
RecNo: str(personID)

My question is how come this happened and is there a preferred method of
treating it?
Thanks
--
Regards,
Richard Harison


From: Stuart McCall on 15 Jan 2010 17:42
"LightByrd" <rh(a)noway.invalid.com> wrote in message
news:%23JCExxglKHA.2160(a)TK2MSFTNGP02.phx.gbl...
> Here's a poser for the experienced...
> I have created a form whose header allows the user to enter a partspec of
> a persons last name so as to identify and retrieve that individual's
> personal record.
> The entry of the partspec & clicking a cmd button triggers a VB routine
> which creates the following SQL statement:
>
> MySQL = "SELECT * FROM [qryFindRec] WHERE [ClientLastName]Like" & Chr(39)
> & [LookForRec] & Chr(42) & Chr(39)
<snip>

Try using Chr(34) in place of Chr(39)

or (better) :

Const qt = Chr(34)

MySQL = "SELECT * FROM [qryFindRec] WHERE [ClientLastName]Like" & qt &
[LookForRec] & Chr(42) & qt


From: Stuart McCall on 15 Jan 2010 18:39
"Stuart McCall" <smccall(a)myunrealbox.com> wrote in message
news:uwRQ7PjlKHA.6096(a)TK2MSFTNGP02.phx.gbl...
> "LightByrd" <rh(a)noway.invalid.com> wrote in message
> news:%23JCExxglKHA.2160(a)TK2MSFTNGP02.phx.gbl...
>> Here's a poser for the experienced...
>> I have created a form whose header allows the user to enter a partspec of
>> a persons last name so as to identify and retrieve that individual's
>> personal record.
>> The entry of the partspec & clicking a cmd button triggers a VB routine
>> which creates the following SQL statement:
>>
>> MySQL = "SELECT * FROM [qryFindRec] WHERE [ClientLastName]Like" & Chr(39)
>> & [LookForRec] & Chr(42) & Chr(39)
> <snip>
>
> Try using Chr(34) in place of Chr(39)
>
> or (better) :
>
> Const qt = Chr(34)
>
> MySQL = "SELECT * FROM [qryFindRec] WHERE [ClientLastName]Like" & qt &
> [LookForRec] & Chr(42) & qt

Also I just noticed you need a space character after the word Like.


From: John W. Vinson on 15 Jan 2010 19:33
On Fri, 15 Jan 2010 23:39:20 -0000, "Stuart McCall" <smccall(a)myunrealbox.com>
wrote:

>> MySQL = "SELECT * FROM [qryFindRec] WHERE [ClientLastName]Like" & qt &
>> [LookForRec] & Chr(42) & qt
>
>Also I just noticed you need a space character after the word Like.

and before it, unless Access puts one in there automatically for you.
--

John W. Vinson [MVP]
From: Stuart McCall on 15 Jan 2010 19:38
"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
news:ra22l5lcc0k29tls69bd8h74ugog3eiq1i(a)4ax.com...
> On Fri, 15 Jan 2010 23:39:20 -0000, "Stuart McCall"
> <smccall(a)myunrealbox.com>
> wrote:
>
>>> MySQL = "SELECT * FROM [qryFindRec] WHERE [ClientLastName]Like" & qt &
>>> [LookForRec] & Chr(42) & qt
>>
>>Also I just noticed you need a space character after the word Like.
>
> and before it, unless Access puts one in there automatically for you.
> --
>
> John W. Vinson [MVP]

Rats! I missed that one. Thanks John.


 |  Next  |  Last
Pages: 1 2 3
Prev: Lock fields in subform except two after authorization date is ente
Next: populate textbox by name(number)