resetting the recordsource

#1

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.

#2

fredg wrote:

Quote:

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

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

#3

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

#4

On Mar 6, 12:06*am, "Larry Linson" <boun... (AT) localhost (DOT) not> wrote:

Quote:

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.CashTicket=" & [Forms]![frmEditChecksNew]!
[cboTicket] & _
" AND tblMain.Processor='" & [Forms]![frmEditChecksNew]!
[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.

#5

bobh wrote:

Quote:

On Mar 6, 12:06 am, "Larry Linson" <boun... (AT) localhost (DOT) not> wrote:

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.CashTicket=" & [Forms]![frmEditChecksNew]!
[cboTicket] & _
" AND tblMain.Processor='" & [Forms]![frmEditChecksNew]!
[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?

#6

On Mar 6, 2:08*pm, Salad <o... (AT) vinegar (DOT) com> wrote:

Quote:

bobhwrote:
On Mar 6, 12:06 am, "Larry Linson" <boun... (AT) localhost (DOT) not> wrote:

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.CashTicket=" & [Forms]![frmEditChecksNew]!
[cboTicket] & _
* * * " AND tblMain.Processor='" & [Forms]![frmEditChecksNew]!
[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.

#7

bobh wrote:

Quote:

On Mar 6, 2:08 pm, Salad <o... (AT) vinegar (DOT) com> wrote:

bobhwrote:

On Mar 6, 12:06 am, "Larry Linson" <boun... (AT) localhost (DOT) not> wrote:

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.CashTicket=" & [Forms]![frmEditChecksNew]!
[cboTicket] & _
" AND tblMain.Processor='" & [Forms]![frmEditChecksNew]!
[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.

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.

#8

On Mar 6, 3:08*pm, Salad <o... (AT) vinegar (DOT) com> wrote:

Quote:

bobhwrote:
On Mar 6, 2:08 pm, Salad <o... (AT) vinegar (DOT) com> wrote:

bobhwrote:

On Mar 6, 12:06 am, "Larry Linson" <boun... (AT) localhost (DOT) not> wrote:

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 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.CashTicket=" & [Forms]![frmEditChecksNew]!
[cboTicket] & _
* * *" AND tblMain.Processor='" & [Forms]![frmEditChecksNew]!
[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.

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

resetting the recordsource

comp.databases.ms-access comp.databases.ms-access

resetting the recordsource - 03-05-2008 , 03:40 PM

Re: resetting the recordsource - 03-05-2008 , 05:24 PM

Re: resetting the recordsource - 03-05-2008 , 11:06 PM

Re: resetting the recordsource - 03-06-2008 , 08:03 AM

Re: resetting the recordsource - 03-06-2008 , 01:08 PM

Re: resetting the recordsource - 03-06-2008 , 01:44 PM

Re: resetting the recordsource - 03-06-2008 , 02:08 PM

Re: resetting the recordsource - 03-06-2008 , 02:53 PM