By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,375 Members | 3,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,375 IT Pros & Developers. It's quick & easy.

resetting the recordsource

P: n/a
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.
Mar 5 '08 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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
Mar 5 '08 #2

P: n/a
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
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
Mar 5 '08 #3

P: n/a
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
Mar 6 '08 #4

P: n/a
On Mar 6, 12:06*am, "Larry Linson" <boun...@localhost.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.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.
Mar 6 '08 #5

P: n/a
bobh wrote:
On Mar 6, 12:06 am, "Larry Linson" <boun...@localhost.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.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?
Mar 6 '08 #6

P: n/a
On Mar 6, 2:08*pm, Salad <o...@vinegar.comwrote:
bobhwrote:
On Mar 6, 12:06 am, "Larry Linson" <boun...@localhost.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.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.
Mar 6 '08 #7

P: n/a
bobh wrote:
On Mar 6, 2:08 pm, Salad <o...@vinegar.comwrote:
>>bobhwrote:
>>>On Mar 6, 12:06 am, "Larry Linson" <boun...@localhost.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.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.
Mar 6 '08 #8

P: n/a
On Mar 6, 3:08*pm, Salad <o...@vinegar.comwrote:
bobhwrote:
On Mar 6, 2:08 pm, Salad <o...@vinegar.comwrote:
>bobhwrote:
>>On Mar 6, 12:06 am, "Larry Linson" <boun...@localhost.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 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.
Mar 6 '08 #9

This discussion thread is closed

Replies have been disabled for this discussion.