Crystal Report drives me crazy

Hi,

My company purchased Crystal Report Software two days back only b'coz I requested for it. And now that I started working on it, I am not getting the result what I want. I am really worried as to how am I going to answere to my boss's questions. I even searched the internet SO MUCH, but I couldn't get any help.

Let me tell you what I have done.

I have used unbound fields in the report bcoz I prefer dynamic reports as the system that I am developing is a multiuser system.
I want to display records in Crystal report according to certain criteria that the user picks from a form.

This is my code:

Dim Report As New CrystalReport1
Dim Rs As New ADODB.Recordset

Private Sub Form_Load()

Screen.MousePointer = vbHourglass

Rs.Open "select StaffMaster.[EmpName], StaffHrs.[ATH] from staffmaster,staffhrs where staffmaster.empcode=" & _
"staffhrs.empcode and staffhrs.discipline='architecture' order by empname", Cn, adOpenStatic, adLockReadOnly, adCmdText

Report.DiscardSavedData

Report.Database.SetDataSource Rs

Report.EmpName.SetUnboundFieldSource "{Rs.EmpName}"
Report.ATH.SetUnboundFieldSource "{Rs.ATH}"

CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

The error that I get is as follows:

'The field name is not known.'

Any help would be appreciated.

ThanksProbably one of the fields that you are selecting in the rs.open recordset might be wrong. Either there is a spelling mistake or the field is not there in your database. Check it out. Hope this helps.|||I double checked the names and even the upper case and lower case letters. Still the same.

I looped through the recordset to see if it displays the correct data and it is so. This is the loop:

If Not (Rs.BOF Or Rs.EOF) Then
Rs.MoveFirst
Do Until Rs.EOF
MsgBox Rs(0) & " " & Rs(1)
Rs.MoveNext
Loop
End If|||Where exactly in the line of code do you get the error?Debug it and check where you are getting the error. This might help you in
streamlining the code and closing in the problem

Just check whether the code in the following lines is giving a problem

Report.DiscardSavedData
Report.Database.SetDataSource Rs

Report.EmpName.SetUnboundFieldSource "{Rs.EmpName}"
Report.ATH.SetUnboundFieldSource "{Rs.ATH}"



Check out the syntax for the above lines of code and whether the parameters are correctly passed|||When I stepped through the code, I get the error message in the line

CRViewer1.ViewReport

These lines did not show any error.

Report.DiscardSavedData
Report.Database.SetDataSource Rs

Report.EmpName.SetUnboundFieldSource "{Rs.EmpName}"
Report.ATH.SetUnboundFieldSource "{Rs.ATH}"

Thanks|||Do you have any formula fields in the report. If yes then you will have to set the formulas befor you view the report.

Does your report show the details when you preview it through crystal reports? Or are you having any problems in viewing it.

I am not sure whether this will help but just try to set the location of the database to the report and check it out. Even through the try to set the datafiles property of CR.|||Yes, both the unbound fields are formula fields. How do I set the formula?|||This is the code once again.

Report.DiscardSavedData
Report.Database.SetDataSource Rs

Report.UnboundString11.SetUnboundFieldSource ("{Rs.EmpName}")
Report.ATH.SetUnboundFieldSource ("{Rs.ATH}")

CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault|||I normally use to set the formulas as

CR1.Formulas(0) = "Compname = " & CoName

where coname is a variable populated with the company's name in the initial startupcode

Just check whether the below code does set the formulas

Report.UnboundString11.SetUnboundFieldSource ("{Rs.EmpName}")
Report.ATH.SetUnboundFieldSource ("{Rs.ATH}")


Probably it is in thispart of your code the error is there. I am not sure of how to set the unbound fields as formulas. You will have to check out the syntax in your CR help files.

Check whether the field name you have given in the report has to be entered while setting the unbound fields

Hope this helps.|||Thanks for your reply. I found the solution. The code should be like this.

Report.Database.Tables.Add "", , Rs

I have one other doubt too. I have a table which contains the availability percentage and Month_Year of staff. Each staff can have more than one record in the table since one record is for one month. If he is assigned any work for, say, June, he has a record for June in the table. If not assigned, he does not have one. I wish to create a report in Crystal Report where I can see the report of each staff for 6 months in one line. If he does not have a record of any of these 6 months, then his availability should display 100. Can this be accomplished? If so, please help me.

Thanks.|||you can place a formula field where in you can check whether there is a record for that month. if there are no records display 100 else display the availability.

hope this helps|||Yeah! Exactly! That is what I want. But how do I place a formula field with code? Please help.

Thanks|||First Insert a formula field. In the formula editor you can set the conditions like below. You can place the formula field then right click on the field. you will find an option edit formula. click on that option and you can enter the code.

if not IsNull ({Ttest.SoldQty}) then
{@.Unstk}*{PurchaseReg.Rate} else
{PurchaseReg.Amount}|||But I am formatting the report with code, so that I only have to add one report into the project and design the report with code according to the different reports of requirement.

And one more thing. My table called Availability contains one record for every month that the staff is assigned. So how will I get the recordset? Please help.

Thanks|||I am not getting your point. In a report if you place a formula field and put the conditions in it.

you can create the report based on the availability field and the last column instead of the database field can be the derived field.

what do you mean formatting the report with code ?|||I will explain it clearly.

TableName: Availability

Fields:

EmpCode
Discipline
Month_Year
Percentage_of_Availability

One record in this table is for one month. I wish to display the Percentage of Availability in the report for 6 months in one line.

I hope it can be done with formula fields.

Thanks