resetting the recordsource

[bobh]

Hi All,

Has anyone figured out how to reset the recordsource of a openned
form??

ex: User opens a form, selects a value from combo1, selects a value
from combo2, in the AfterUpdate event of combo2 write the sql for the
recordsource using those two choosen values then do a
me.recordsource = strSQL
me.requery

or do I 'have' to do a form/subform for this

thanks
bobh.

[fredg]

Re: resetting the recordsource

On Wed, 5 Mar 2008 13:40:06 -0800 (PST), bobh wrote:

Hi All,
>
Has anyone figured out how to reset the recordsource of a openned
form??
>
ex: User opens a form, selects a value from combo1, selects a value
from combo2, in the AfterUpdate event of combo2 write the sql for the
recordsource using those two choosen values then do a
me.recordsource = strSQL
me.requery
>
or do I 'have' to do a form/subform for this
>
thanks
bobh.

Yes, but I can't do it for you with the limited info you have given.
Code the Combo AfterUpdate event something like:

Dim strSQL as String
strSQL = "Select ....blah, blah.."
Me.RecordSource = strSQL
Me.Refresh

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Salad]

Re: resetting the recordsource

fredg wrote:

On Wed, 5 Mar 2008 13:40:06 -0800 (PST), bobh wrote:
>
>

>>Hi All,
>>
>>Has anyone figured out how to reset the recordsource of a openned
>>form??
>>
>>ex: User opens a form, selects a value from combo1, selects a value
>>from combo2, in the AfterUpdate event of combo2 write the sql for the
>>recordsourc e using those two choosen values then do a
>>me.recordsour ce = strSQL
>>me.requery
>>
>>or do I 'have' to do a form/subform for this
>>
>>thanks
>>bobh.

>
>
Yes, but I can't do it for you with the limited info you have given.
Code the Combo AfterUpdate event something like:
>
Dim strSQL as String
strSQL = "Select ....blah, blah.."
Me.RecordSource = strSQL
Me.Refresh
>

Is Me.Refresh or Bob's Me.Requery even needed since the recordsource is
being updated?

I'm wondering if updating the recordsource is required. If the combos
are used for filtering, simply keep the same recordsource but change the
filter
Me.Filter = ....
Me.FilterOn = True

[Larry Linson]

Re: resetting the recordsource

IIRC, in a Form, you can change the RecordSource at the Open event or any
time thereafter, and you do not have to requery, because changing the
RecordSource causes the Form to be Requeried. It would seem from your
question that your experience differs... what are you observing?

In Reports, OTOH, you can successfully change the RecordSource only in the
Open event.

Larry Linson
Microsoft Office Access MVP

[bobh]

Re: resetting the recordsource

On Mar 6, 12:06 am, "Larry Linson" <boun...@localh ost.notwrote:

IIRC, in a Form, you can change the RecordSource at the Open event or any
time thereafter, and you do not have to requery, because changing the
RecordSource causes the Form to be Requeried.  It would seem from your
question that your experience differs... what are you observing?
>
In Reports, OTOH, you can successfully change the RecordSource only in the
Open event.
>
 Larry Linson
 Microsoft Office Access MVP

this is my code in the after update of the cboProcessor
strSQL = "SELECT tblMain.* FROM tblMain" & _
" WHERE tblMain.Status= 'Processed'" & _
" AND tblMain.CashTic ket=" & [Forms]![frmEditChecksNe w]!
[cboTicket] & _
" AND tblMain.Process or='" & [Forms]![frmEditChecksNe w]!
[cboProcessor] & "'"
Me.RecordSource = strSQL
Me.Requery

the form opens with No recordsource, when the after update of
cboProcessor code executes I get the following error
Run-Time error '2001'
You Canceled the Previous Operation

if I then choose debug the me.recordsource =strsql is highlighted in
yellow
bobh.

[Salad]

Re: resetting the recordsource

bobh wrote:

On Mar 6, 12:06 am, "Larry Linson" <boun...@localh ost.notwrote:
>

>>IIRC, in a Form, you can change the RecordSource at the Open event or any
>>time thereafter, and you do not have to requery, because changing the
>>RecordSourc e causes the Form to be Requeried. It would seem from your
>>question that your experience differs... what are you observing?
>>
>>In Reports, OTOH, you can successfully change the RecordSource only in the
>>Open event.
>>
>Larry Linson
>Microsoft Office Access MVP

>
>
>
this is my code in the after update of the cboProcessor
strSQL = "SELECT tblMain.* FROM tblMain" & _
" WHERE tblMain.Status= 'Processed'" & _
" AND tblMain.CashTic ket=" & [Forms]![frmEditChecksNe w]!
[cboTicket] & _
" AND tblMain.Process or='" & [Forms]![frmEditChecksNe w]!
[cboProcessor] & "'"
Me.RecordSource = strSQL
Me.Requery
>
the form opens with No recordsource, when the after update of
cboProcessor code executes I get the following error
Run-Time error '2001'
You Canceled the Previous Operation
>
if I then choose debug the me.recordsource =strsql is highlighted in
yellow
bobh.

Have you done a
Debug.Print strSQL
and then copy/pasted the result into a query?

[bobh]

Re: resetting the recordsource

On Mar 6, 2:08 pm, Salad <o...@vinegar.c omwrote:

bobhwrote:

On Mar 6, 12:06 am, "Larry Linson" <boun...@localh ost.notwrote:

>

>IIRC, in a Form, you can change the RecordSource at the Open event or any
>time thereafter, and you do not have to requery, because changing the
>RecordSource causes the Form to be Requeried.  It would seem from your
>question that your experience differs... what are you observing?

>

>In Reports, OTOH, you can successfully change the RecordSource only in the
>Open event.

>

Larry Linson
Microsoft Office Access MVP

>

this is my code in the after update of the cboProcessor
   strSQL = "SELECT tblMain.* FROM tblMain" & _
      " WHERE tblMain.Status= 'Processed'" & _
      " AND tblMain.CashTic ket=" & [Forms]![frmEditChecksNe w]!
[cboTicket] & _
      " AND tblMain.Process or='" & [Forms]![frmEditChecksNe w]!
[cboProcessor] & "'"
   Me.RecordSource = strSQL
   Me.Requery

>

the form opens with No recordsource, when the after update of
cboProcessor code executes I get the following error
Run-Time error '2001'
You Canceled the Previous Operation

>

if I then choose debug the me.recordsource =strsql is highlighted in
yellow
bobh.

>
Have you done a
        Debug.Print strSQL
and then copy/pasted the result into a query?- Hide quoted text -
>
- Show quoted text -

Oooops! just did, didn't have tick marks around CaskTicket(it's a
text value)......... all is well again :)
thanks
bobh.

[Salad]

Re: resetting the recordsource

bobh wrote:

On Mar 6, 2:08 pm, Salad <o...@vinegar.c omwrote:
>

>>bobhwrote:
>>

>>>On Mar 6, 12:06 am, "Larry Linson" <boun...@localh ost.notwrote:

>>

>>>>IIRC, in a Form, you can change the RecordSource at the Open event or any
>>>>time thereafter, and you do not have to requery, because changing the
>>>>RecordSourc e causes the Form to be Requeried. It would seem from your
>>>>question that your experience differs... what are you observing?

>>

>>>>In Reports, OTOH, you can successfully change the RecordSource only in the
>>>>Open event.

>>

>>>>Larry Linson
>>>>Microsoft Office Access MVP

>>

>>>this is my code in the after update of the cboProcessor
>> strSQL = "SELECT tblMain.* FROM tblMain" & _
>> " WHERE tblMain.Status= 'Processed'" & _
>> " AND tblMain.CashTic ket=" & [Forms]![frmEditChecksNe w]!
>>>[cboTicket] & _
>> " AND tblMain.Process or='" & [Forms]![frmEditChecksNe w]!
>>>[cboProcessor] & "'"
>> Me.RecordSource = strSQL
>> Me.Requery

>>

>>>the form opens with No recordsource, when the after update of
>>>cboProcess or code executes I get the following error
>>>Run-Time error '2001'
>>>You Canceled the Previous Operation

>>

>>>if I then choose debug the me.recordsource =strsql is highlighted in
>>>yellow
>>>bobh.

>>
>>Have you done a
> Debug.Print strSQL
>>and then copy/pasted the result into a query?- Hide quoted text -
>>
>>- Show quoted text -

>
>
Oooops! just did, didn't have tick marks around CaskTicket(it's a
text value)......... all is well again :)
thanks
bobh.

Not a prob. I was wondering how you present a form with no record
source. When I do so, I get a bunch of #Names.

[bobh]

Re: resetting the recordsource

On Mar 6, 3:08 pm, Salad <o...@vinegar.c omwrote:

bobhwrote:

On Mar 6, 2:08 pm, Salad <o...@vinegar.c omwrote:

>

>bobhwrote:

>

>>On Mar 6, 12:06 am, "Larry Linson" <boun...@localh ost.notwrote:

>

>>>IIRC, in a Form, you can change the RecordSource at the Open event or any
>>>time thereafter, and you do not have to requery, because changing the
>>>RecordSour ce causes the Form to be Requeried.  It would seem from your
>>>question that your experience differs... what are you observing?

>

>>>In Reports, OTOH, you can successfully change the RecordSource only inthe
>>>Open event.

>

>>>Larry Linson
>>>Microsoft Office Access MVP

>

>>this is my code in the after update of the cboProcessor
>  strSQL = "SELECT tblMain.* FROM tblMain" & _
>     " WHERE tblMain.Status= 'Processed'" & _
>     " AND tblMain.CashTic ket=" & [Forms]![frmEditChecksNe w]!
>>[cboTicket] & _
>     " AND tblMain.Process or='" & [Forms]![frmEditChecksNe w]!
>>[cboProcessor] & "'"
>  Me.RecordSource = strSQL
>  Me.Requery

>

>>the form opens with No recordsource, when the after update of
>>cboProcesso r code executes I get the following error
>>Run-Time error '2001'
>>You Canceled the Previous Operation

>

>>if I then choose debug the me.recordsource =strsql is highlighted in
>>yellow
>>bobh.

>

>Have you done a
       Debug.Print strSQL
>and then copy/pasted the result into a query?- Hide quoted text -

>

>- Show quoted text -

>

Oooops! just did,   didn't have tick marks around CaskTicket(it's a
text value).........  all is well again  :)
thanks
bobh.

>
Not a prob.  I was wondering how you present a form with no record
source.  When I do so, I get a bunch of #Names.- Hide quoted text -
>
- Show quoted text -

The hard way, all controls in the detail are set to visible=false and
I added a tag value of 'v',
in the after update event of the cboProcessor I run a procedure that
loops thru each control, if tag="v" then visible=true.
I inherited this db and I'm just trying to do a quick fix for this one
issue on this one form. I'm sure I will eventually convince business
management to let me re-write the whole app.
bobh.