Solved

Using a timer control to refresh a grid which pulls data from a database in VB 6

Posted on 2007-11-28
4
214 Views
Last Modified: 2013-12-25
Please help me to refresh a grid that receives data from a database.  I want to use a VB 6 timer control to refresh the grid every 30 seconds.  

The code is attached below.
Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
 

MSHFlexGrid1.Visible = True
 

'===================================================================

If Button.Index = 3 Then

'===================================================================
 

Dim conn As adodb.Connection 'connection

Dim rs As adodb.Recordset 'record set

Dim rs3 As adodb.Recordset 'record set

Dim rs4 As adodb.Recordset 'record set

Dim rs5 As adodb.Recordset 'record set

Dim rs6 As adodb.Recordset 'record set

Dim strconn As String 'conn string

Dim strsql As String

Dim inti

Dim intj
 

'===================================================================

'Open connection to Database

'===================================================================
 

strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TechLog.mdb"

Set conn = New adodb.Connection

conn.Open strconn
 

'Open Rs

strsql = "Select * from tbl_Open"

Set rs = New adodb.Recordset

rs.Open strsql, conn, adOpenDynamic, adLockOptimistic
 

'===================================================================

'Grid setup

'===================================================================
 

'MSHFlexGrid1.Clear

'MSHFlexGrid1.Rows = 1

'MSHFlexGrid1.Cols = rs.Fields.Count - 4
 

With MSHFlexGrid1
 

.TextMatrix(0, 1) = "Case"

.TextMatrix(0, 2) = "Firm Name"

.TextMatrix(0, 3) = "Name"

.TextMatrix(0, 4) = "User ID"

.TextMatrix(0, 5) = "Application"

.TextMatrix(0, 6) = "Problem"

.TextMatrix(0, 7) = "Working Time"

.TextMatrix(0, 8) = "Time Opened"

.TextMatrix(0, 9) = "Opened By"
 

.Row = 0

.Col = 0

.RowSel = 0

.ColSel = 9

.CellAlignment = flexAlignCenterCenter

.CellFontBold = True
 

.ColWidth(0) = 300

.ColWidth(1) = 2000

.ColWidth(2) = 2300

.ColWidth(3) = 1300

.ColWidth(4) = 2000

.ColWidth(5) = 2300

.ColWidth(6) = 2000

.ColWidth(7) = 2300

.ColWidth(8) = 2000

.ColWidth(9) = 2000
 

End With
 

'===================================================================
 

intj = 1
 

Do Until rs.EOF = True 'do until all records in recordset
 

MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1
 

MSHFlexGrid1.TextMatrix(intj, 1) = rs.Fields("CaseID").Value

MSHFlexGrid1.TextMatrix(intj, 4) = rs.Fields("UserID").Value

MSHFlexGrid1.TextMatrix(intj, 5) = rs.Fields("ApplicationID").Value

MSHFlexGrid1.TextMatrix(intj, 6) = rs.Fields("ProblemID").Value

MSHFlexGrid1.TextMatrix(intj, 7) = rs.Fields("Time_Working").Value

MSHFlexGrid1.TextMatrix(intj, 8) = rs.Fields("Time_Open").Value

MSHFlexGrid1.TextMatrix(intj, 9) = rs.Fields("QueueID").Value
 

'================================================================================

'Application ID Selection

'================================================================================
 

ApplicationID = MSHFlexGrid1.TextMatrix(intj, 5)

Set rs3 = New adodb.Recordset

AppIDTemp = "select Application from tbl_Applications where ApplicationID = " & ApplicationID

rs3.Open AppIDTemp, conn, adOpenDynamic, adLockOptimistic

If rs3.EOF = False And rs3.BOF = False Then

ApplicationName = rs3.Fields("Application")

End If

MSHFlexGrid1.TextMatrix(intj, 5) = ApplicationName
 

'================================================================================

'FirstLast Name Selection

'================================================================================
 

UserID = MSHFlexGrid1.TextMatrix(intj, 4)

Set rs4 = New adodb.Recordset

UserIDTemp = "select [First Name], [Last Name] from [User ID] where UserID = '" & UserID & "'"

rs4.Open UserIDTemp, conn, adOpenDynamic, adLockOptimistic

If rs4.EOF = False And rs4.BOF = False Then

FirstName = rs4.Fields("First Name")

LastName = rs4.Fields("Last Name")

End If

FirstLastName = FirstName & " " & LastName

MSHFlexGrid1.TextMatrix(intj, 3) = FirstLastName
 

'================================================================================

'Problem Name Selection

'================================================================================
 

ProblemID = MSHFlexGrid1.TextMatrix(intj, 6)

Set rs5 = New adodb.Recordset

ProblemIDTemp = "select Problem from tbl_Problems where ProblemID = " & ProblemID

rs5.Open ProblemIDTemp, conn, adOpenDynamic, adLockOptimistic

If rs5.EOF = False And rs5.BOF = False Then

ProblemName = rs5.Fields("Problem")

End If

MSHFlexGrid1.TextMatrix(intj, 6) = ProblemName
 

'================================================================================

'Queue Name Selection

'================================================================================
 

QueueID = MSHFlexGrid1.TextMatrix(intj, 9)

Set rs6 = New adodb.Recordset

QueueIDTemp = "select UserName from tbl_Users where UserID = " & QueueID

rs6.Open QueueIDTemp, conn, adOpenDynamic, adLockOptimistic

If rs6.EOF = False And rs6.BOF = False Then

UserName = rs6.Fields("UserName")

End If

MSHFlexGrid1.TextMatrix(intj, 9) = UserName
 

'================================================================================

'Firm Name Selection

'================================================================================
 

UserID = MSHFlexGrid1.TextMatrix(intj, 4)

Set rs7 = New adodb.Recordset

FirmIDTemp = "select FirmSub from [User ID] where UserID = '" & UserID & "'"

rs7.Open FirmIDTemp, conn, adOpenDynamic, adLockOptimistic

If rs7.EOF = False And rs7.BOF = False Then

FirmSub = rs7.Fields("FirmSub")

End If
 

Set rs8 = New adodb.Recordset

FirmNameTemp = "select [Firm Name] from Correspondents where FirmSub = '" & FirmSub & "'"

rs8.Open FirmNameTemp, conn, adOpenDynamic, adLockOptimistic

If rs8.EOF = False And rs8.BOF = False Then

FirmName = rs8.Fields("Firm Name")

End If

MSHFlexGrid1.TextMatrix(intj, 2) = FirmName
 

'================================================================================
 

intj = intj + 1
 

rs.MoveNext
 

Loop
 

'===================================================================
 

rs.Close

Set rs = Nothing
 

rs3.Close

Set rs3 = Nothing
 

rs4.Close

Set rs4 = Nothing
 

rs5.Close

Set rs5 = Nothing
 

rs6.Close

Set rs6 = Nothing
 

rs7.Close

Set rs7 = Nothing
 

rs8.Close

Set rs8 = Nothing
 

conn.Close

Set conn = Nothing
 

End If

Open in new window

0
Comment
Question by:Tony789
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
JackOfPH earned 250 total points
ID: 20370740
First, set the timer interval to 30000 which means means 30 seconds.

Then put this on timer1_interval code.

Private sub Timer1_Interval()

Toolbar1_ButtonClick(3)

end sub
0
 

Author Comment

by:Tony789
ID: 20375102
I don't want this to refresh only when the button is clicked.  It should refresh by itself.  
0
 
LVL 10

Expert Comment

by:3_S
ID: 20385331
the code of JackOfPH is correct.
If you want to make you code more understandeable then you should put you code in a new routine for example

Private sub GetData ()
   'put all the code you have in your code when button.index = 3 here (this is the code between If Button.Index = 3 Then.... end if)

end sub

Private sub Timer1_Interval()
 call getData()
end sub

adapt your existing code

Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
MSHFlexGrid1.Visible = True
'===================================================================
 If Button.Index = 3 Then
   call getData()
 end if
end sub




0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 20393067
The timer will automatically refresh it for you.

Did you enabled the timer?
Set it on timers property.
Timer1.eneabled = true

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now