[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mshflexgrid problem

Posted on 2005-05-15
10
Medium Priority
?
574 Views
Last Modified: 2008-02-01
Hi,

I have got a strange problem with an Mshflexgrid. It’s kind of hard to explain so here goes.

When I launch my program the grid is populated with data from an access query. Row 0 contains the field names.Now depending on what the user does data could be added or removed from the grid. This works fine until the grid is empy. If the grid becomes empty and you then add data to the grid the rows get muddled up. The first row which contains the data becomes row 0. So in order to get the combo box on the screen I have to click below the cell I actually want.

If I restart the program it sorts itself and works again.

Any ideas?

Thanks in advance

Option Explicit
Private Sub Form_Load()
Dim ssql As String
sconnect = "provider=microsoft.jet.OLEDB.4.0;data source = " & App.Path & "\db1.mdb" 'DB path
ssql = "select * from qrynotassigned "
Set cn = New Connection
cn.Open sconnect
Set rs = New Recordset
    rs.CursorLocation = adUseClient
    rs.Open ssql, cn, adOpenForwardOnly, adLockReadOnly

Set MSHFlexGrid1.DataSource = rs
End Sub

Private Sub mshflexgrid1_Click()
Dim col As Integer
Dim row As Integer

  col = MSHFlexGrid1.MouseCol
  row = MSHFlexGrid1.MouseRow
   
If row > 0 And col = 3 Then
CBJobAssigned.Visible = True
CBJobAssigned.Top = MSHFlexGrid1.Top + MSHFlexGrid1.CellTop
CBJobAssigned.Left = MSHFlexGrid1.Left + MSHFlexGrid1.CellLeft
CBJobAssigned.Width = MSHFlexGrid1.CellWidth
CBJobAssigned.Visible = True
End If

End Sub

Private Sub CmdAssign_Click()
    Dim sSql2 As String
        sSql2 = "update tblmain set username = '" & CBJobAssigned.Text & "' where [ticket number] = " & Val(MSHFlexGrid1.TextMatrix(MSHFlexGrid1.row, 1))
        Set cn = New Connection
        cn.Open sconnect
        cn.Execute sSql2
        CBJobAssigned.Visible = False
       
        Call CmdRefresh_Click

End Sub

Private Sub CmdRefresh_Click()
Dim ssql As String
ssql = "select * from qrynotassigned "
Set cn = New Connection
cn.Open sconnect
Set rs = New Recordset

    rs.CursorLocation = adUseClient
    rs.Open ssql, cn, adOpenForwardOnly, adLockReadOnly

Set MSHFlexGrid1.DataSource = rs
End Sub

0
Comment
Question by:neoice
  • 5
  • 5
10 Comments
 
LVL 19

Expert Comment

by:Shauli
ID: 14005853
Private Sub mshflexgrid1_Click()
CBJobAssigned.Visible = False
If MSHFlexGrid1.MouseRow = 0 Then exit Sub
 
If MSHFlexGrid1.MouseCol = 3 Then
    CBJobAssigned.Top = MSHFlexGrid1.Top + MSHFlexGrid1.CellTop
    CBJobAssigned.Left = MSHFlexGrid1.Left + MSHFlexGrid1.CellLeft
    CBJobAssigned.Width = MSHFlexGrid1.CellWidth
    CBJobAssigned.Visible = True
End If

S
0
 

Author Comment

by:neoice
ID: 14006735
Hi Shauli,

That has not solved my problem. I have recreated the problem in a new VB app (37kb) that I was going to attach to give you a better chance of helping me, however I cannot work out how to attach it!

Are you allowed to attach files?

Thanks

Neil




0
 
LVL 19

Expert Comment

by:Shauli
ID: 14006745
If you can upload a zip file to any site, and post the link here, then we can download it from there. There is no attachement option at EE, and emails are not allowed.

S
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:neoice
ID: 14006893
http://members.aol.com/jono1970/

download, unzip and launch the file.

1)click on row 1, col 3 and select JBloggs from the combo box and click on assign. The data has been updated to the DB and removed from the grid.

2) Leave the VB app RUNNING and open the Access DB. Select TBLMain and changeJBloggs to "not assigned" in one of the records - simply copy this from the default at the bottom.

3) Close down the DB and bring the VB app into view. Click on the refresh button and repeat the process in section 1. Hopefully you can see the problem i am faced with.

thanks in advance
0
 
LVL 19

Accepted Solution

by:
Shauli earned 2000 total points
ID: 14007448
Option Explicit
Private sSql As String

Private Sub CBJobAssigned_Click()
MSHFlexGrid1.TextMatrix(MSHFlexGrid1.row, 3) = CBJobAssigned.Text
CBJobAssigned.Visible = False
End Sub

Private Sub Form_Load()
sconnect = "provider=microsoft.jet.OLEDB.4.0;data source = " & App.Path & "\db1.mdb" 'DB path
sSql = "select * from qrynotassigned "
Set cn = New Connection
cn.Open sconnect
Set rs = New Recordset
    rs.CursorLocation = adUseClient
    rs.Open sSql, cn, adOpenForwardOnly, adLockReadOnly
        Set MSHFlexGrid1.DataSource = rs
End Sub

Private Sub CmdAssign_Click()
sSql = "update tblmain set username = '" & MSHFlexGrid1.TextMatrix(MSHFlexGrid1.row, 3) & "' where [ticket number] = " & Val(MSHFlexGrid1.TextMatrix(MSHFlexGrid1.row, 1))
cn.Execute sSql
CBJobAssigned.Visible = False
If MSHFlexGrid1.Rows = 2 Then MSHFlexGrid1.Rows = 3
MSHFlexGrid1.RemoveItem MSHFlexGrid1.row
End Sub

Private Sub CmdRefresh_Click()
rs.Requery
Set MSHFlexGrid1.DataSource = rs
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Private Sub mshflexgrid1_Click()
CBJobAssigned.Visible = False
If MSHFlexGrid1.MouseRow = 0 Then Exit Sub
   
If MSHFlexGrid1.MouseCol = 3 Then
    CBJobAssigned.Top = MSHFlexGrid1.Top + MSHFlexGrid1.CellTop
    CBJobAssigned.Left = MSHFlexGrid1.Left + MSHFlexGrid1.CellLeft
    CBJobAssigned.Width = MSHFlexGrid1.CellWidth
    CBJobAssigned.Visible = True
End If
Label1.Caption = MSHFlexGrid1.MouseRow
Label2.Caption = MSHFlexGrid1.MouseCol
End Sub

S
0
 

Author Comment

by:neoice
ID: 14008229
shauli,

thank you very much. this problem has been bugging me for the past week.

EE is the best investment i have made this year!

cheers

neil
0
 

Author Comment

by:neoice
ID: 14009012
Hi shauli,

I spoke to soon. I have come across the following problem:

when you assign the last record in the grid, row 1 is blank and still present. From what I gather, as long as row 1 is visible all is ok. if you click on refresh and there is no data in the DB to populate the grid I get the same problem as before.

In my full program I do not have a refresh button as it is on a timer to refresh every 2 minutes. So how do i make row 1 stay there even if no data is available.

also i have noticed that when you assign a record it takes several minutes to change in the DB, if i manually open the DB it updates immediately, any ideas?

One more thing, how does the code below get called - is it called when the form is unloaded?

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

many thnaks for your assistance.

Neil
0
 
LVL 19

Expert Comment

by:Shauli
ID: 14009272
Hi Neil,

1. <<So how do i make row 1 stay there even if no data is available.>>

Private Sub CmdRefresh_Click()   'in your case its the timer event
rs.Requery
If rs.RecordCount = 0 Then Exit Sub  '<<<<<< this line will keep the empty line in place. Note the position: before the datasource line.
Set MSHFlexGrid1.DataSource = rs  
End Sub

2. <<also i have noticed that when you assign a record it takes several minutes to change in the DB>>

That is Access database. You want a real fast response, move to SQL Server and use stored procedures

3. <<One more thing, how does the code below get called - is it called when the form is unloaded>>

Yes. We use the QueryUnload event to control the way we exit the application, and the cleanup. QueryUnload fires just before FormUnload.

S
0
 

Author Comment

by:neoice
ID: 14009348
Thanks very much. You have definitely earned your points!
0
 
LVL 19

Expert Comment

by:Shauli
ID: 14009671
Glad I could help :)

S
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month19 days, 18 hours left to enroll

873 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