473,320 Members | 2,097 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

resetting the recordsource

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
8 10546
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jenny Kurniawan | last post by:
I have a procedure that goes like this: Private Sub Form_Current() Dim strSQL1 As String strSQL1 = "Select Price_Year_Name from Price_Year where Category_ID = 1 And By_Default <> 0" ' I...
2
by: ColinWard | last post by:
Hi. I have a form which has as its recordsource an SQL string. The SQL String is as follows: SELECT * from CONTACTS where false. this ensures that there is no data loaded in the form when the...
4
by: ColinWard | last post by:
Hi. I use two different pieces of code to manipulate a recordsource for a form. The first one sets the recordsource to null when the form loads. The second is supposed to display the corresponding...
1
by: CSDunn | last post by:
Hello, In an Access 2003 Project that uses a SQL Server 2000 database as its data source, I am attempting to set the RecordSource property of a sub report called 'RptTeacherHistory2' that appears...
3
by: Susan Bricker | last post by:
I might not have phrased the question correctly in the Subject of this post. Please read the entire explanation. I have a form with a command button (Add New Person). This button opens up...
32
by: deko | last post by:
I have a popup form with a textbox that is bound to a memo field. I've been warned about memo fields so I'm wondering if I should use this code. Is there any risk with changing the form's...
9
by: robert d via AccessMonster.com | last post by:
I'm not sure why the following isn't working. The subform loads correctly, but no data is displayed. I'm certain that there is data and I have checked that the SQL statement does in fact return...
2
by: G Gerard | last post by:
Hello I have noticed that whenever I set a form's RecordSource property in code it launches the form's OnActivate event. In one of my application I set a form's RecordSource to zero lenght...
3
by: Simon | last post by:
Dear reader, The syntax for the VBA code to change the RecordSource of a Master Report is: Me.RecordSource = "TableOrQueryName"
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.