Curious Reaction to subform SQL statement

[LightByrd]

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

 

[Stuart McCall]

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

 

[Stuart McCall]

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.

 

[John W. Vinson]

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.

 

[Stuart McCall]

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.

Rats! I missed that one. Thanks John.

 

[LightByrd]

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

 

[Stuart McCall]

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

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

 

[Marshall Barton]

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.

 

[LightByrd]

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

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

Thank you Stuart...most kind

 

[LightByrd]

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.

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 !!!

 

[Marshall Barton]

"Marshall Barton" 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.

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!"

Well, I can't see how my interjection helped get your brain
on the right track, but I'm glad you resolved the issue,
whatever it was ;-)

I suspect the problem had something to do with converting
text to number and possibly a field or control with the same
or wrong name. If so, it would have been nasty to track it
down, so having a little clean up the design kind of change
make it go away could have saved a lot of hair ;-)

 

[LightByrd]

"Marshall Barton" wrote.

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.

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!"

Well, I can't see how my interjection helped get your brain
on the right track, but I'm glad you resolved the issue,
whatever it was ;-)

I knew there had to be some sort of referencing error, and once I looked
closely as my code, I realized that there were no references for the main
data table, so why in heaven's name did I set it as a control source? Once
I blanked out the control, Voila! I then replaced the dorky looking but
functional expression in the query with the numeric primary key value of the
table. Worked fine.

I suspect the problem had something to do with converting
text to number and possibly a field or control with the same
or wrong name.

The original non-working version took the primary key value at face value.
I only added the val(strs([IncidentID])) to make it work
Blanking out the control source for the main form made that expression
unnecessary


If so, it would have been nasty to track it

down, so having a little clean up the design kind of change
make it go away could have saved a lot of hair ;-)

ABSOULUTLY AGREED!
(how about 3 hours staring at a screen tapping my fingers)

Thanks again!