Prev: Lock fields in subform except two after authorization date is ente
Next: populate textbox by name(number)
From: Marshall Barton on 17 Jan 2010 11:46
LightByrd wrote:
>"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 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 ;-)

--
Marsh
MVP [MS Access]
From: LightByrd on 17 Jan 2010 13:46
"Marshall Barton" <marshbarton(a)wowway.com> wrote in message
news:81f6l5lm9aso6q0on4s7oq7a0qbah3pop6(a)4ax.com...
> LightByrd wrote:
>>"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)
>
> --
> Marsh
> MVP [MS Access]


Thanks again!

--
Regards,
Richard Harison


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