Prev: Lock fields in subform except two after authorization date is ente
Next: populate textbox by name(number)
From: LightByrd on 15 Jan 2010 19:43
"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
>
>

Thanks for the improvement....
But my version works fine.
My question was simply why did I have to go through a song and dance to
allow the key field to be displayed in the subform.
Details are contained in what was <snipped> from my message

--
Regards,
Richard Harison


From: Stuart McCall on 15 Jan 2010 20:45
"LightByrd" <rh(a)noway.invalid.com> wrote in message
news:%23VNduTklKHA.4872(a)TK2MSFTNGP05.phx.gbl...
> "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
>>
>>
>
> Thanks for the improvement....
> But my version works fine.
> My question was simply why did I have to go through a song and dance to
> allow the key field to be displayed in the subform.
> Details are contained in what was <snipped> from my message
>
> --
> Regards,
> Richard Harison

I don't know the answer to that. I apologise for snipping out the details,
and I'm posting the original message in order to keep the thread going, so
others may suggest something.

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: Marshall Barton on 16 Jan 2010 08:23
LightByrd wrote:

>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


Sounds like the subform control's LinkMaster/Child
properties are not set up properly.

There have been cases where Access automatically resets the
Link properties when you set a subform's record source so
use a breakpoint to make sure they are still set correctly
after modifying the RecordSource property. If that's what's
happening, it might help to clear the entry in Tools -
Options - Tables/Queries - AutoIndexing ... Otherwise, you
can try using code to set them back to what they are
supposed to be.

--
Marsh
MVP [MS Access]
From: LightByrd on 16 Jan 2010 11:58
"Stuart McCall" <smccall(a)myunrealbox.com> wrote in message
news:O5g7f2klKHA.3476(a)TK2MSFTNGP06.phx.gbl...
> "LightByrd" <rh(a)noway.invalid.com> wrote in message
> news:%23VNduTklKHA.4872(a)TK2MSFTNGP05.phx.gbl...
>> "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
>>>
>>>
>>
>> Thanks for the improvement....
>> But my version works fine.
>> My question was simply why did I have to go through a song and dance to
>> allow the key field to be displayed in the subform.
>> Details are contained in what was <snipped> from my message
>>
>> --
>> Regards,
>> Richard Harison
>
> I don't know the answer to that. I apologise for snipping out the details,
> and I'm posting the original message in order to keep the thread going, so
> others may suggest something.
>
> 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
>
>

Thank you Stuart...most kind

--
Regards,
Richard Harison


From: LightByrd on 16 Jan 2010 14:50
"Marshall Barton" <marshbarton(a)wowway.com> wrote in message
news:m0e3l59o8de20pm7larlk8sf8e34psippu(a)4ax.com...
> LightByrd wrote:
>
>>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
>
>
> Sounds like the subform control's LinkMaster/Child
> properties are not set up properly.
>
> There have been cases where Access automatically resets the
> Link properties when you set a subform's record source so
> use a breakpoint to make sure they are still set correctly
> after modifying the RecordSource property. If that's what's
> happening, it might help to clear the entry in Tools -
> Options - Tables/Queries - AutoIndexing ... Otherwise, you
> can try using code to set them back to what they are
> supposed to be.
>
> --
> Marsh
> MVP [MS Access]


Thank you Marsh....
I had thought of the master/child links situation as well.
Actually that did not solve the problem, but your sage suggestion put me on
the right track.
As Monk would say "Here's what happened..."
Basically my main form is nothing but a vessel for data input through a text
control.
That control is of course unbound.
The subform displays records matching the data in the main form text
control.
This is triggered by the main form "Find Records" command button which
executes the VB code injecting the SQL statement into the recordsource
property of the subform.
The Master/Child linking fields are and remain blank.

My mistake was that, while the text control in the main form was unbound, I
had tied the form itself to the huge data table.
Not necessary...since the SQL statement deals with a simple query of that
data table.
(only necessary fields & allowed me also to inject a three level sort)

As soon as I unbound the entire form. Everything worked fine, so I ditched
the expression
RecNo:val(strs([IncidentID])) from the query and replaced it with the
IncidentID field by itself

Not sure why my error produced those crazy results,but your thoughts got me
to say, "why did you set a control source for a form that did not need to
pull any data!"

Thanks again !!!

--
Regards,
Richard Harison


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