report recordsource does "not exist"

[Happy YN]

report recordsource does "not exist" But it does!

I have painfully built a long sql for a form which works fine
however when I put the same statement into an on open event for a report using me.recordsource= "sqlstring" I get a message that the recordsource does not exist.It actually says:
The recordsource 'SELECT......' specified does not exist
so it is reading it but just refusing to accept it even though the form accepts it and if pasted into a qbe its fine.
This is really frustrating as in the imediate window I can see the sql perfectly yet it refuses to assign itself to the record source. Any ideas??
Thanks a million!
Happy YN

 

[bjackson]

Me.Report.RecordSource = sqlstring
assuming sqlstring is a public string Variable

 

[Happy YN]

Of course I have it without the quotes but still does not work
Also why must it be public? I have it defined in the actual on open of the report
Thanks

 

[bjackson]

I misunderstood your question
i thought you were opening the report from a form that
was using strsql as its record source.

so where does strsql get executed.

 

[Happy YN]

just to be clear
the form has many textboxes and a search cmdbutton which when clicked generates the sql depending on the content if any of those textboxes. it then passes the sql to the recordsource of a subform.This works fine
Another button previews a report based on the results of the search. I put exactly the same code in the building of the sql for the recordsource of the report (on open) as for the subform. The subform works but the report just says select.... is not found etc.

I thought of opening the report with the where clause i.e docmd.openreport ......., fieldname= form.primarykey but this does not seem to work either
Any help would be appreciated
Thanks

 

[bjackson]

if you already have created the recordset in your search form
then if you declare strsql as a public string variable placed in a code module(not a form module) and use
me.report.recordsource=strsql on the open event of the report
should do the trick,you would not create the recordset in any of the reports code module,i think you have to create the recordsource for the report before it is opened that why its
telling you it doesnt exist

hope this helps

 

[Happy YN]

I'll try it but I know you can definitely set the recordsource for a report on opening it but perhaps you are right that it needs to be created beforehand
thanks

 

[Happy YN]

Nope!
I've put the sql as a publicstring variable and set the report.recordsource for that sql.
the report opens but without any info?? just errror etc

 

[bjackson]

well i am stumped as that is how i have my reports set up,perhaps someone else here can help you

 

[bjackson]

just an after thought

have you set the reports record source by using
the on open event or did you set its record source in the
propertys

the only way i can generate the error that it doesnt exist is
if i set the record source in the propertys,if i leave the record source blank in the propertys and set it using the on open event
then my reports work

 

[Happy YN]

report record source in properties is blank.I set recrdsource on open here is my sql (which works in a form fine but not a report)
Public searchSQL As String
searchSQL = "SELECT [tblBook].[lngBookNo] AS Valmadonna, [tblBook].[strBookName] AS BookName, [tblBook].[strBookNameHebMapped] AS [Hebrew Name], [tblBook].[strDateInNums] AS PrintDate, [tblBook].[strAuthor] AS author, [tblPrinter].[strPrinter] AS Printer, [tblType].[strType] AS Type, [tblBook].[dateStamp], [tblBook].[strDateHebMapped] AS hebdate, [tblBook].[strBibliography], [tblBook].[strNotes], [tblBook].[strSizeMapped], [tblBook].[mmoInfo], [tblbook].[mmoAddNotes], [tblCity].[strCity] AS city, [tblBook].[strDatAcronymMapped], [tblbook].[chkprint],[qrystringDates].[fromsearch],[qrystringdates].[tosearch]" & _
"FROM tblCity INNER JOIN (tblType INNER JOIN (tblPrinter INNER JOIN(tblBook INNER JOIN qrystringDates ON [tblBook].[lngBookNo] = [qrystringDates].[lngBookNo]) ON [tblPrinter].[ID]=[tblBook].[tblPrinter_ID]) ON [tblType].[ID]=[tblBook].[tblType_ID]) ON [tblCity].[ID]=[tblBook].[tblCity_ID]"
If [Forms]![frmbook]!Checkval = True Then
searchSQL = searchSQL & " WHERE [tblbook].[lngbookno]<100000 And"
Else
searchSQL = searchSQL & " WHERE"
End If
If IsNull([Forms]![frmbook]!TxtAndVal) Then
Else
searchSQL = searchSQL & " [tblbook].[lngbookno] LIKE """ & [Forms]![frmbook]![TxtAndVal] & """ AND"
End If
If IsNull([Forms]![frmbook]!txtAndTitle) Then
Else
searchSQL = searchSQL & " [tblbook].[strbookname] LIKE """ & [Forms]![frmbook]!txtAndTitle & """ AND"
End If
If IsNull([Forms]![frmbook]!txtAndAuthor) Then
Else
searchSQL = searchSQL & " [tblbook].[strauthor] LIKE """ & [Forms]![frmbook]!txtAndAuthor & """ AND"
End If
If IsNull([Forms]![frmbook]!txtAndPrinter) Then
Else
searchSQL = searchSQL & " [tblprinter].[strprinter] LIKE """ & [Forms]![frmbook]!txtAndPrinter & """ AND"
End If
If IsNull([Forms]![frmbook]!txtAndCity) Then
Else
searchSQL = searchSQL & " [tblcity].[strcity] LIKE """ & [Forms]![frmbook]!txtAndCity & """ AND"
End If
If IsNull([Forms]![frmbook]!TxtAndType) Then
Else
searchSQL = searchSQL & " [tbltype].[strtype] LIKE """ & [Forms]![frmbook]!TxtAndType & """ AND"
End If
If IsNull([Forms]![frmbook]!txtAndDate) Then
Else
Dim txtFrom As String
txtFrom = [Forms]![frmbook]!txtAndDate - 1
If [Forms]![frmbook]!combodateOperator = "between" Then
Dim txtTo As String
txtTo = [Forms]![frmbook]!TxtToDate + 1
searchSQL = searchSQL & "(([qrystringdates].[fromsearch]> " & txtFrom & ") and ([qrystringdates].[tosearch] < " & txtTo & ")) And"

ElseIf [Forms]![frmbook]!combodateOperator = "r" Then
Dim txtR As String
txtR = [Forms]![frmbook]!txtAndDate + 1
searchSQL = searchSQL & " (([qrystringdates].[fromsearch]> " & txtFrom & ") and ([qrystringdates].[tosearch] < " & txtR & ")) And"
Else
Dim txtoperator As String
txtoperator = [Forms]![frmbook]!combodateOperator

searchSQL = searchSQL & " (([qrystringdates].[fromsearch] " & txtoperator & [Forms]![frmbook]!txtAndDate & ")and ([qrystringdates].[fromsearch]>0)) And"
End If


If IsNull([Forms]![frmbook]!txtAndAny) Then
Else
Dim strAny As String
strAny = Forms!frmbook!txtAndAny

searchSQL = searchSQL & " (([tblbook].[lngbookno] Like """ & strAny & """) Or ([tblbook].[strbookname] Like """ & strAny & """) Or ([tblPrinter].[strPrinter] Like """ & strAny & """) Or ([tblcity].[strcity] Like """ & strAny & """) Or ([tbltype].[strType] Like """ & strAny & """) Or ([tblbook].[mmoInfo] Like """ & strAny & """) Or ([tblbook].[lngbookid] Like """ & strAny & """) Or ([tblbook].[mmoAddNotes] Like """ & strAny & """) Or ([tblbook].[strBibliography] Like """ & strAny & """) Or ([tblbook].[strNotes] Like """ & strAny & """) Or ([tblbook].[strSizeMapped] Like """ & strAny & """) Or ([tblbook].[DateStamp] Like """ & strAny & """) Or ([tblbook].[strauthor] Like """ & strAny & """) Or ([tblbook].[strDateInNums] Like """ & strAny & """)) And"
End If

If [Forms]![frmbook]!ChkIncDeleted = True Then
Else
searchSQL = searchSQL & " (([tblbook].[mmoAddNotes] not Like ""deleted*"") Or (isnull([tblbook].[mmoAddNotes]))) AND"
End If
searchSQL = Left(searchSQL, (Len(searchSQL) - 3))

searchSQL = searchSQL & "ORDER BY [tblbook].[lngbookno]"
End If


Thanks for your continued interest
HappyYN

 

[bjackson]

sorry mate, i cant help any further as i dont know what else to suggest. i have a search form that has as many possible criterias as your form appears to have and i build the sqlstring similiar to you. My sql feeds a subform as yours does and all i do is open a report and use the on open event to set the reports record source.When i first started doing this i had the same problem that you are having and i found that by declare a public variable in a code module, not the form or report module that fixed my problem

sorry i cant be of more assistance

 

[Happy YN]

Thanks for your help
By the way I tried to debug & got a message invalid outside procedure???
D'you think this means its conflicting with the identical code I left in in the original form?
Thanks again

 

[bjackson]

im not sure what you mean. identical code original form??
the sql code should only appear once- i.e in your seachform code
module.when you click your print button it should run your sql
then open the report. Is this what you are doing, i will stay on line for a while if you like

 

[Happy YN]

Thankx for continued support!!!
I have tied putting this code in the declarations section of my form but as soon as I called it public it starts saying invalid outside procedure?
Now there is nothing wrong with this code and it works fine for populating the form but as soon as i make it public...Problems occur. I need it to be available to both the form and report(s). I just can't get the report to open with this recordsource. I don't see why it does not let me say reports.recordsource=forms recordsource like it accepted in the good old days when I was filling the form from a query

 

[bjackson]

i am getting confused, see if i have it right

you have a search form with a subform
the search form has many possible criterias that may be selected
when you click a button on your form it builds your sqlstring and populates your subform.the code for this resides in your searchform code module. and obviously has no syntax errors or it
would not populate your subform

when you click your print button on the search form it
1.runs the code to build your sql
2.populates or refreshes your subform
3.opens the report

in the on open event for the report, you have
me.report.recordsource=StrSql

StrSql is a public variable declared in a standard code module
ie not a class module,form or report module

when you debug what code module is the error in

 

[Happy YN]

Thanks agin
your first paragraph is 100% right
At first I had the sql code in the search button event but then that did not help the report so I put it in the print button as well but there it refused to provide a recordsource for the report
after your reply I put it in the declarations section of a module called general which is not a form but I'm not sure of the difference betwen standard code module or class module I changed dim to public
When I debug it stops on first line saying invalid call to outside procedure
I can't get any further
If I comment out the first line it stops on the second etc with the same error message
Thanks
Must go to bed soon here as its soon time to get up!!!

 

[Happy YN]

am attaching doc which shows error when I try to open report using sql as recordsource

 

[bjackson]

this is a small demo db in access2k

 

[bjackson]

it may be of more use if i attach it !!!