Is it possible to ...

[lemonjuice]

I have created an application in visual basic 6 connecting to a database in microsoft access. I did not use a data environment and I want to create reports. Is it possible to create a report in microsoft access and call it from the visual basic application & if it is possible how will I code it. If it is not possible what should I do?

Thanx

 

[FatOldGit]

Hi lemonjuice,
Yes it is possible and it's nice and easy.

First (after creating your report of course) set a reference in your project to the Microsoft Access Object Library.

Second in the declaratioins section of your code create and access.application object with this line:

Dim app As Access.Application

then add these lines to the appropriate section of your code:

Set app = GetObject(<Full path to your database>)
app.DoCmd.OpenReport <Report Name>,acViewNormal

this will print out the report immediately.

Hope this does the job for you.

All the best,

Dave.

 

[lemonjuice]

Thanx for the help!!

 

[nairsaju]

HI lemonjuice,
i hope this will help u . to work this code u shuld place this appropriate items on ur forms and also want ot create a table in that same application path.(Keep ur Access Db in the Same folder where ur application is running with this names. "EMPLOYEE_DB.mdb"
Table:- EMP
EMPNO number , EMPNAME Text, DOFJ date/time, DEPTNO Number, GRADE text, BASIC number etc.....(First 4 Column is must)
Reports:- EMPQUR, EMPREP(use wizard to create this)
Take all the fields in the Table. Access part is over.

Place all the component mentioned bellow:-
After that Copy and past this code.

' NOTE:-
'--------
'<< select from &quot;References&quot; -> &quot;Microsoft Access 10.0 object Library &quot; and
' &quot;Visualbasic Objects and Procedures&quot; . >>
'<< Next select from Project menu -> &quot;Components&quot; -> &quot;Microsoft ADODatacontrol 6.0&quot; and
' &quot;Microsoft Calendar Control 10.0&quot; >>

'<< place 2 option buttons. option1,option2>>
'<< 2 text boxes text1,text2 >>
'<< 3 command buttons. command1, command2,command3 >>
'<< 2 calendars. calendar1,calendar2 >>

-=START HERE=-

Dim Acc As Application
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim connectionString As String

Dim cn As Connection
Dim dn As Integer
Dim d1, d2 As Date
Dim O As Boolean



Private Sub ShowReport(opt As Integer)
Dim strID As String
Dim strSQL As String
Dim pa As String
Dim rnam, rnam1 As String

rnam = &quot;EMPQUR&quot;
rnam1 = &quot;EMPREP&quot;
Dim Acc As Access.Application
Set Acc = New Access.Application
Acc.OpenCurrentDatabase App.Path & &quot;\EMPLOYEE_DB.mdb&quot;

If opt = 1 Then

Acc.DoCmd.OpenReport rnam1, acViewPreview, , WhereCondition:=&quot;DOFJ Between # &quot; & d1 & &quot;# and #&quot; & d2 & &quot;#&quot;
'Between #1/1/2001# And #2/2/2003#
Acc.Visible = True

ElseIf opt = 2 Then

Acc.DoCmd.OpenReport rnam, acViewPreview, , WhereCondition:=&quot;DEPTNO = &quot; & dn
Acc.Visible = True

End If

End Sub

Public Sub shrep()
Dim opts As Integer
opts = 1

Call ShowReport(opts)

End Sub

Public Sub shrep1()
Dim opts1 As Integer
opts1 = 2

Call ShowReport(opts1)

End Sub

Private Sub Calendar1_Click()
Text1.Text = Calendar1.Value
End Sub

Private Sub Calendar2_Click()
Text2.Text = Calendar2.Value
End Sub

Private Sub Command1_Click()
If Combo1.Text <> &quot;&quot; Then
dn = Combo1.Text
Call shrep1
Else
MsgBox &quot; Please Select DEPTNO&quot;
End If

End Sub

Private Sub Command2_Click()
Unload Me

End Sub

Private Sub Command3_Click()

If Text1.Text <> &quot;&quot; Then
If Text2.Text <> &quot;&quot; Then
d1 = Text1.Text
d2 = Text2.Text
Call shrep
Else
MsgBox &quot;Please Enter Date2&quot;
Exit Sub
End If
Else
MsgBox &quot; Please Enter Date1&quot;
Exit Sub
End If

End Sub

Private Sub Form_Load()
' These are used to call avalue to combobox.
' if yo can pass value directly then this codings can be reduced.
Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset
adoConnection.connectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; _
& &quot;Data Source=D:\SATHISH\VBPRJ\REPORTS\EMPLOYEE_DB.mdb&quot;

adoConnection.Open
adoRecordset.CursorType = adOpenKeyset
adoRecordset.LockType = adLockOptimistic
adoRecordset.CursorLocation = adUseClient 'Used with adoRecordset.AbsolutePosition

If adoConnection.State = adStateOpen Then
adoRecordset.Open &quot;SELECT DISTINCT(DEPTNO) FROM EMP&quot;, adoConnection, adOpenDynamic, adLockOptimistic, adCmdText

While adoRecordset.EOF = False

' This is Taking values into Combobox
Combo1.AddItem (adoRecordset.Fields(0).Value)
adoRecordset.MoveNext
Wend
Else
MsgBox &quot;SORRY! The connection could not be opened.&quot;
adoConnection.Close
Exit Sub
End If



Text1.Text = Calendar1.Value
Text2.Text = Calendar2.Value

Command1.Caption = &quot;Dept&quot;
Command2.Caption = &quot;Exit&quot;
Command3.Caption = &quot;Between Dates&quot;

Option1.Caption = &quot;Date&quot;
Option2.Caption = &quot;Deptno&quot;


End Sub

Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Calendar1.Visible = False
Calendar2.Visible = False
End Sub

Private Sub Option1_Click()
O = True
Command3.Enabled = True
If Text1.Enabled = False Then
Text1.Enabled = True
Text2.Enabled = True
Combo1.Enabled = False
Command1.Enabled = False
Else
Combo1.Enabled = False
Command1.Enabled = False
End If

End Sub

Private Sub Option2_Click()
O = False
Command1.Enabled = True
If Combo1.Enabled = False Then
Combo1.Enabled = True
Text1.Enabled = False
Text2.Enabled = False
Command3.Enabled = False
Else
Text1.Enabled = False
Text2.Enabled = False
Command3.Enabled = False
End If

End Sub

Private Sub Text1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Calendar1.Visible = True
End Sub

Private Sub Text2_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Calendar2.Visible = True
End Sub


-= THE END=-

Best of luck
SAJU

 

[nairsaju]

Hi lemonjuice,
I forgot to tell u that One Combobox is also Required

Bye
Saju