Sorry

[ZanM]

I would like to apologize for disgracing this very nice and helpful MsgBoard by acting like I was some little kid who had a fit.

Thankz, JHausmann I tried to refresh the command, no luck.

If any body knows how I can get in touch with a really great VB Database programmer I would be grateful ( even another tutorial on reports would be great.) I think I will upload the project to a new thread, later. If anybody would like to take a look at it post a reply here and I will upload it after I make sure I have it well commented so it easy to understand.

This really is a great MsgBoard unlike some other VB boards I've seen......

Thankz Agian...

[Chris]

Hey, ZanM, may be you can try to get some help from the followng web site...

• Advisor.com
• Wrox Publisher

...currently am working a public holidat doing Top Forms in a unix environment. Almost completed them all, except the current one which is feeding strange characters into the finished output. At the stage of throwing the keyboard through the monitor in frustration....will grab lunch and then get back to it.

What was the original problem, maybe l can help, am employed as db programmer for a software house.

Just had lunch....got back to my desk...and the problem was obvious, (FTP script crunching the wrong server). Take a break then look at the problem and the solution is sometimes obvious. Almost beer oclock......

[ZanM]

I have a dataEnviporment and a report. I change the command.Text in my dataEnviorment to an SQL string that has user imput added, then I open the report object.

All the requested info comes up the first time the user uses the report, but if they generate a new difrent report the old data stays and the new data is no where to be seen. I had this working exept there was no custom user input. they could generate a report and if they add new data to the database that should be in report it would come up. when they reopened the report.

this the code that changes the command.text

Private Sub flbGen_Click()
If txtYear.Text = " " Or txtYear.Text = "" Then
MsgBox "Please enter the year you wish to Search....", , "User Error.."
Exit Sub
End If

Dim nReport As New Report
'I am at this point loading a new instance of the report object
'rebinding it to the dataEnviorment
'refreshing it(which is pointless by the way)
'then showing it with the new SQL string load
'the report will generate the first time but will not redraw the data even when
'a unused instance is opened
Select Case lstMonth.Text
Case "1 - January"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "1" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
Case "2 - February"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "2" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Show
Case "3 - March"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "3" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
Case "4 - April"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "4" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
Case "5 - May"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "5" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
Case "6 - June"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "6" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
Case "7 - July"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "7" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
Case "8 - August"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "8" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
Case "9 - September"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "9" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
Case "10 - October"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "10" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
Case "11 - November"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "11" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
Case "12 - December"
ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & "12" & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show
End Select
End Sub

I don't think the reBinding to the datasource abd datamemeber is necisary I had add that to see if maybe the report wasn't recieving the new command string...

also when the report loads I have it reruery in
Report_Activate (this is a really cranky thing to do)

Thankz

Zan

[JHausmann]

I still don't have an answer for your original problem but this bit of code should make your code simpler. Use it to replace your entire case statement:

dim sMonth as String

sMonth = Trim(Left(lstMonth.Text,2))

if CInt(sMonth) < 1 or CInt(sMonth) > 12 then
'someone has added a string value you don't want
beep
end if

ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & sMonth & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
nReport.Refresh
nReport.Show

[ZanM]

That added some speed to the code. To fix the original problem I had to add a line.

Dim nReport As New Report
If txtYear.Text = " " Or txtYear.Text = "" Then
MsgBox "Please enter the year you wish to Search....", , "User Error.."
Exit Sub
End If
Dim sMonth As String
sMonth = Trim(Left(lstMonth.Text, 2))

If CInt(sMonth) < 1 Or CInt(sMonth) > 12 Then
'someone has added a string value you don't want
Beep
End If

ClientDataEnvir.Commands("MonthlyReport").CommandText = "SELECT ClientData.Name, ClientData.Address, ClientData.CustomerType, ClientData.LastWork, ClientData.NextScheduled, ClientData.JobStatus, DateDiff('d',Now(),[clientdata].[nextscheduled]) AS Expr1 From ClientData Where (((DatePart('m', [ClientData].[NEXTSCHEDULED])) Like " & "'" & sMonth & "'" & ") And ((DatePart('yyyy', [ClientData].[NEXTSCHEDULED])) Like " & "'" & txtYear.Text & "'" & "))ORDER BY ClientData.Name;"
Set nReport.DataSource = ClientDataEnvir
nReport.DataMember = "MonthlyReport"
ClientDataEnvir.rsMonthlyReport.Close 'Makes sure you open a new data stream
nReport.Refresh
nReport.Show


And that fixes the always loading a fresh report problem.

[JHausmann]

Not bad. You get tighter code and fix the problem to boot.