Updating/displaying MSHFlexGrid - Xtreme Visual Basic Talk

04-19-2005, 11:55 PM

phillg11

Newcomer

Join Date: Apr 2005

Posts: 7

Updating/displaying MSHFlexGrid

Hi,
I have an annoying problem. In a nutshell I have a small database program in which I add data to an existing (Access) database, on clicking a command button the data is added, the form is closed and a second form is shown which displays the data in an MSHFlexGrid. Here’s the problem, when the second form is displayed the MSHFlexGrid doesn’t show the just added data. If I insert a break point between closing the first and opening the second forms it works fine and if I manually open the database the updated data is present. I have tried various combinations of refresh/update etc but have had no result.

Code below

Thanks for any help,
Phill

Code:

......................................................
Dim conn As ADODB.Connection
__________________________________________
Private Sub cmdAddData_Click()
Dim recAddClient As ADODB.Recordset
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\db1.mdb"
conn.Open
Set recAddClient = New ADODB.Recordset
recAddClient.Open "select * from table1", conn, adOpenDynamic, adLockOptimistic, adCmdText
With recAddClient
    .AddNew
        .Fields("title").Value = cmbTitle.Text
        .Fields("FirstName").Value = txtFields(0).Text
        .Fields("surname").Value = txtFields(1).Text
        .Fields("ClientID").Value = txtClientID.Text
    .Update
End With
Unload Me
frmPatientSearch.Show
End Sub
......................................................
Dim conn As ADODB.Connection
__________________________________________
Private Sub Form_Load()
Dim recPopGrid As ADODB.Recordset
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\db1.mdb"
conn.Open
Set recPopGrid = New ADODB.Recordset
recPopGrid.CursorLocation = adUseClient
recPopGrid.Open "select * from table1", conn, adOpenDynamic, adLockOptimistic, adCmdText
Set MSHFlexGrid1.DataSource = recPopGrid
End Sub

Last edited by Shurik12; 04-21-2005 at 01:09 AM.

04-20-2005, 02:22 AM

Shurik12

Steppe Walker

Retired Moderator
* Expert *

Join Date: Jul 2002

Location: Ukraine/Russia/Belgium

Posts: 7,227

Hi,

Every time you update the underlying recordset you have to rebind it

Code:

....
Set MSHFlexGrid1.DataSource = Nothing
Set MSHFlexGrid1.DataSource = recPopGrid
....

__________________
"A diaper is not like a computer that makes satisfying burbling noises from time to time, hinting at great inner complexity." Malcolm Gladwell

"I'm sitting here completely surrounded by no beer." Onslow, 'Keeping up appearances'

04-20-2005, 08:30 PM

phillg11

Newcomer

Join Date: Apr 2005

Posts: 7

Shurik12, thanks for the reply, but I'm still struggling. I have further simplified the example putting it on one form with MSHFlexGrid1, one text box and one command button. I added the code you suggested, but I must be missing something. Could you check what I have below.

Thanks,
Phill

Code:

Dim conn As ADODB.Connection
Dim recPopGrid As ADODB.Recordset
Dim recAddClient As ADODB.Recordset
____________________________________________
Private Sub Command1_Click()

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\db1.mdb"
conn.Open
        
Set recAddClient = New ADODB.Recordset

recAddClient.Open "select * from table1", conn, adOpenDynamic, adLockOptimistic, adCmdText

With recAddClient
    .AddNew
        .Fields("title").Value = Text1.Text
    .Update
End With

Set MSHFlexGrid1.DataSource = Nothing
Set MSHFlexGrid1.DataSource = recPopGrid
MSHFlexGrid1.Refresh
End Sub
____________________________________________
Private Sub Form_Load()

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\db1.mdb"
conn.Open
        
Set recPopGrid = New ADODB.Recordset
recPopGrid.CursorLocation = adUseClient
recPopGrid.Open "select * from table1", conn, adOpenDynamic, adLockOptimistic, adCmdText

Set MSHFlexGrid1.DataSource = recPopGrid

End Sub

Last edited by Shurik12; 04-21-2005 at 01:08 AM. Reason: please use vb-tags

04-21-2005, 01:19 AM

Shurik12

Steppe Walker

Retired Moderator
* Expert *

Join Date: Jul 2002

Location: Ukraine/Russia/Belgium

Posts: 7,227

Let's go back to your original example and try the following

Code:

__________________________________________
Private Sub cmdAddData_Click()

Dim recAddClient As ADODB.Recordset
Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
Set recAddClient = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\db1.mdb"
conn.Open
recAddClient.Open "select * from table1", conn, adOpenKeySet, adLockOptimistic, adCmdText
With recAddClient
    .AddNew
        .Fields("title").Value = cmbTitle.Text
        .Fields("FirstName").Value = txtFields(0).Text
        .Fields("surname").Value = txtFields(1).Text
        .Fields("ClientID").Value = txtClientID.Text
    .Update
End With

DoEvents

Set recAddClient=Nothing
Set conn=Nothing

Unload Me
frmPatientSearch.Show
End Sub
......................................................
__________________________________________
Private Sub Form_Load()

Dim conn As ADODB.Connection
Dim recPopGrid As ADODB.Recordset

Set conn = New ADODB.Connection
Set recPopGrid = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\db1.mdb"
conn.Open

recPopGrid.CursorLocation = adUseClient
recPopGrid.Open "select * from table1", conn, adOpenKeySet, adLockOptimistic, adCmdText

Set MSHFlexGrid1.DataSource = recPopGrid

.....

End Sub

Regards,
Shurik.

Last edited by Shurik12; 04-21-2005 at 01:26 AM.

04-25-2005, 11:22 PM

phillg11

Newcomer

Join Date: Apr 2005

Posts: 7

Thanks, it feels good to stop hitting my head agaianst that wall