473,323 Members | 1,547 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,323 software developers and data experts.

RecordSource OnOpen

I am changing the recorsource in the onopen event of my report simply
using this line :

RecordSource = "SELECT * FROM tbl_Temp_ImpressionQuestionnaire WHERE
Num_Questionnaire = 19"

However how could I pass a parameter defined in another sub to this
onopen code to replace 19 with the number chosen by my user.

Or WHEN can I define the recordsource other than in the onopen event.

I would like to define the recordsource based on a variable in my sub
then open (??) the report.

Thank you

Nov 13 '05 #1
3 1917
you could set the recordsource to the result of a function that
receives the Num_Questionnaire value...

Function MyRowsource (byval lngQuestionID As Long) As String
MyRowsource = "SELECT * FROM tbl_Temp_ImpressionQuestionnaire WHERE
Num_Questionnaire =" & lngQuestionID
End Function

Nov 13 '05 #2
Duh... it's a *report*... forgetting how to read.

Pass the filter in the Open event of the report.

Just Num_Questionnaire = <something>

.... could be Forms!MyOpenForm!MyTextControl

or some such...

See OpenReport in the help - it gives examples

Nov 13 '05 #3
Patrik:

Is the '19' the only part of your report's recordsource that changes?
If that's the case, and you're opening the report from within another
sub, use the built in feature of the DoCmd.OpenReport method to limit
the records it returns in the report. Here's what you can do:

In Design View of the report, set the report's recordsource to "SELECT
* FROM tbl_Temp_ImpressionQuestionnaire" which will be a completely
unfiltered report, and save it. Be sure to comment out the code that
affects the recordsource in your OnOpen event of the report.

In the sub where you're assigning the parameter and opening the report,
do this:

Dim strCriteria as String
strCriteria = "Num_Questionnaire = " & Whatever#YouWantOnReport

Find the line that says DoCmd.OpenReport "YourReportName" and replace
it with:
DoCmd.OpenReport "YourReportName", , , strCriteria

Note that the strCriteria is basically your "WHERE" clause of the SQL,
without the word "WHERE", so you can build this string into whatever
criteria you want. Remember that date criteria will need to be wrapped
with # signs and text with single quotes. Numerical parameters won't
need any wrapping.

HTH,

Jana

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

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...
3
by: Martin Austin | last post by:
Here's my problem. I'm launching an MS Word object on a Notes document Postopen event. The script kicks off a macro that saves the Word document to a temporary file every n minutes (backup of key...
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...
1
by: Jimmy Stewart | last post by:
I have a continuous form with a list of items from a table. One of the fields in the table is "print". this allows the user to select items from the list on the form for printing in a report. on...
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: franc sutherland | last post by:
Hello, I have a report which I filter using the me.filter command in the OnOpen event. Me.Filter = "OrderID=" & Forms!variable_form_name! Me.FilterOn = True I want to be able to open that...
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"
4
by: RLN | last post by:
RE: Access 2003 I have a SQL variable (strSQL1 dimmed as String) that gets built using Select Case logic. The first half of strSQL1 is etched in stone no matter what. The 2nd half of strSQL1...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.