Viewing Reports in VB based on user input....

[ROCK1999]

Im in need of help in viewing reports in visual basic. however, the reports are from queries based on user input. In other words, the results will be dynamic. Im already aware of how to view an access report in vb, but how do i create and view reports which are based on user input.....

 

[srinivaspvl]

Hi friend

Hope this will solve your problem, You can not directly do user friendly report on data report. I am just giving one example how i did in my project. User will entry purchase order number based on that report will be displayied.

You have to create temp. table and update the data querried data to that table and from temp. table you show the report. This is one way.

Private Sub cmdDisplay_Click()
Dim Sql As String
Dim StrSql As String
Dim total As Double
total = 0
Call pordermain
Set RsTemp = New ADODB.Recordset
Set RsTemp1 = New ADODB.Recordset

With RsTemp.Fields
.Append "Tno", adDouble
.Append "Description", adChar, 100
.Append "Qty", adInteger
.Append "DeliveryDate", adDate
.Append "UnitPrice", adDouble
.Append "Amount", adDouble
End With
RsTemp.Open
'displaying the item descriptions
StrSql = ""
StrSql = "SELECT * FROM ptrans where porderno = "
StrSql = StrSql & Trim(txtPoNo) & ""
If Rs1.State = adStateOpen Then
Rs1.Close
End If
Rs1.Source = StrSql
Rs1.Open
Do While Not Rs1.EOF
RsTemp.AddNew
RsTemp.Fields(0) = Rs1.Fields(1)
RsTemp.Fields(1) = Rs1.Fields(3) & ""
RsTemp.Fields(2) = Rs1.Fields(6)
RsTemp.Fields(3) = Rs1.Fields(2)
RsTemp.Fields(4) = Rs1.Fields(4)
RsTemp.Fields(5) = Rs1.Fields(5)
total = total + Rs1.Fields(5)
Rs1.MoveNext
Loop

With datareport.Sections("section2&quot
.Controls("lblpono&quot.Caption = mporderno
End With

With datareport.Sections("Detail&quot
.Controls("txttno&quot.DataField = RsTemp.Fields(0).Name
.Controls("txtdescrip&quot.DataField = RsTemp.Fields(1).Name
.Controls("txtQty&quot.DataField = RsTemp.Fields(2).Name
.Controls("txtdelvdate&quot.DataField = RsTemp.Fields(3).Name
.Controls("txtUnitPrice&quot.DataField = RsTemp.Fields(4).Name
.Controls("txtAmount&quot.DataField = RsTemp.Fields(5).Name
End With

With datareport.Sections("section5&quot
.Controls("lbltotal&quot.Caption = Format(total, "###,###.00&quot
End With

Set datareport.DataSource = RsTemp
datareport.Show vbModal
Set datareport = Nothing

Rs1.Close
RsTemp.Close

End Sub

Function pordermain()
Dim spsql As String
spsql = ""
If Rs2.State = adStateOpen Then
Rs2.Close
End If
spsql = "SELECT * FROM pordermain where porderno = "
spsql = spsql & Trim(txtPoNo) & ""
Rs2.Source = spsql
Rs2.Open
If Rs2.RecordCount > 0 Then
mporderno = Rs2.Fields("porderno&quot
mpdate = Rs2.Fields("pdate&quot
mpcname = Trim(Rs2.Fields("pcname&quot)
mpcaddress = Trim(Rs2.Fields("pcaddress&quot)
mpterm = Trim(Rs2.Fields("pterm&quot)
mpremarks = Trim(Rs2.Fields("premarks&quot)
Else
MsgBox ("Purchase order Number not found&quot
End If
Rs2.Close
End Function

' Hi i gave you complete code. Hope it will help you
you design first in datareport and from form you call like this.

All the best

Regards
srinivas_pvl@hotmail.com

 

[ROCK1999]

Thank you friend....However, can you show me exactly where the user inputs the product number, so I can get a better feel as to how to manipulate my program to do the same thing...?

 

[srinivaspvl]

txtPoNo - is my text box.

cmdDisplay - is command button

when user enters the value in txtpono. After clicking on command button this code will work. Data report you have to call from Form. Just go through the code. All you can understand very easily. Best of luck

Srinivas_pvl@hotmail.com