VB6 - ADO Recordset - Datasource - Cant refresh DataGrid display 1

[h4fod]

Hi
I may have erroneosuly put this in the VB Db Forum initially - sorry :-(
I have successfully coded a sample application using VB6 for my students using two ADO recordsets. It is a straightforward 'Stock Management' context. The recordsets link to two respective MS Access 97 tables ('tblStock' and 'tblStockAudit' using JET 3.5. Iteratively, the fields in the Stock Table (tblStock) are read using the 'Fields' property and if the current stock level < minStockLevel for each db record then a new record is added to the 'tblStockAudit' Access table, flushed prior to the algorithm commencing. The algorithm works fine. Correctly, populating records meeting this criteria ARE added to the tblStockAudit table in access and '10 records added' are reported using the sample data.

However when the recordset rsStockShortages is set as the datasource to the DataGrid object and is refreshed the grid is blank! I think there must be a simple solution. Have I overlooked something here. (Some variables may be superflous in the liting - still work in progress!)
Many thanks in anticipation.

Private Sub cmdStockLevels_Click()
'Dimension Local variables
Dim intNumberInStock As Integer
Dim strStockID As String
Dim sngStockPrice As Single
Dim intMinReOrderQty As Integer
Dim intQtyInStock As Integer
Dim intShortage As Integer
Dim intSupplier As String
Dim sngStockValueTotal As Single
Dim sngStockValeRunningTotal As Single
Dim intCount As Integer

'Connection to MS Access database common to both ADODB recordsets
Set conn = New ADODB.Connection

'Establish two recordsets connected via this connection to GreenparksSchool.mdb
Set rsStock = New ADODB.Recordset
Set rsStockShortages = New ADODB.Recordset


conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source= " & "C:\temp\GreenparksSchool.mdb" & " ;Persist Security Info = false"
conn.Open

'Open both recordsets
rsStock.Open ("SELECT * FROM tblStock"), conn, adOpenStatic, adLockReadOnly
rsStockShortages.Open ("SELECT * FROM tblStockAudit"), conn, adOpenStatic, adLockOptimistic

'Clear any records which currently exist in the Stock Audit database
'table
'THIS CODE DOES NOT WORK _ CHECK METHODS FOR ADO
        Do While Not rsStockShortages.EOF
            rsStockShortages.Delete
            rsStockShortages.MoveNext
        Loop
        
With rsStock

i = 1

        Do While Not .EOF
            intStockID = .Fields![Stock_ID].Value
            strStockDescription = .Fields![Description]
            sngStockPrice = .Fields![Price].Value
            intQtyInStock = .Fields![Qty_In_Stock].Value
            intMinReOrderQty = .Fields![Qty_Min_ReOrder].Value
            strSupplier = .Fields![Supplier].Value
            intMinStockLevel = .Fields![Qty_MinStockLevel].Value

                    If intQtyInStock <= intMinStockLevel Then
                        'Now add a new Shortages record to the rsShortagesRecordset
                            With rsStockShortages
                                .AddNew
                                    .Fields![ShortageID].Value = intStockID
                                    .Fields![Description].Value = strStockDescription
                                    .Fields![Price] = sngStockPrice
                                    .Fields![Qty_MinStockLevel] = intMinStockLevel
                                    .Fields![Supplier] = strSupplier
                                    .Fields![MinReOrderQty] = intMinReOrderQty
                                    .Fields![QuantityInStock] = intQtyInStock
                                    .Fields![AuditDate] = Date
                                .Update
                            
                            End With
                            
                            
                            
                            
                            
                            
                    End If
                    i = i + 1
            .MoveNext
        Loop
'Fields (0)
'Fields ("name")
'Fields![name]
End With

'Count Shortages
rsStockShortages.MoveLast
rsStockShortages.MoveFirst
intCount = rsStockShortages.RecordCount
lbluserMessage.Caption = intCount & " shortages have been recorded and need to be ordered " & _
    "from our suppliers"
    
'Display in Grid
Set DataGrid1.DataSource = rsStockShortages
DataGrid1.Refresh

'Close both recordsets
rsStock.Close
rsStockShortages.Close
conn.Close

Set conn = Nothing
End Sub

 

[tedsmith]

It may be that the database has not had time to complete it's update before you look at it by the datagrid. This can take milliseconds whereas your computer is much faster refreshing the datagrid refresh too early.

A second manual refresh a second later wound prove this

I usually close the database and reopen it in the datagrid to view the results then move to the last updated record

Try Just
'display in grid
rsStockShortages.Close
rsStockShortages.CursorLocation = adUseClient
rsStockShortages.Open ("SELECT * FROM tblStockAudit"), conn, adOpenStatic, adLockOptimistic
Set DataGrid1.DataSource = rsStockShortages
rsStockShortages.movelast

 

[h4fod]

Hi
Thanks for your post. tried your solution but not 'quiete' there yet. Using your code I still have a blank grid. Interestingly, if I invoke an'Exit Sub' after rsStockShortages.Movelast (your code segment) it displays the data corectly. But the recordset and connection close statements are bypassed (so will still be open)- not elegant! So clearly something to do with closing recordsets prematurely as you suggest (nearly there?). Thanks again

 

[genomon]

You cross-posted this - I answered the other thread...

Gluais faicilleach le cupan làn

 

[tedsmith]

You have to exit the sub sometime anyway to continue doing anything else.

If you close the recordset you will lose the data on the datagrid (which may have been your original problem)!
You only close the recordset when you leave the form with the grid or do some other action when you don't need to see anything anymore on the grid.

I have a form with the one datagrid and use it to show different rows of different criteria statements using the above methods. I change the titles and widths of some columns as I select the different views and it works fine.

The only nuisance I find is when I am using the grid for data corrections I have to change to a different row for the underlying database to permanently reflect the change (I do this on exiting each view or the form by first doing a movefirst statement)

 

[h4fod]

Thanks for this very helpful reply. This will help my progamming considerably as I migrate from DAO to ADO objects belatedly!
Many thanks again

 

[tedsmith]

I dimension everything in the declarations at the head of the form, not in the sub.

That way you can close everything when you exit the form.

This leaves the data showing on the grid.

 

[h4fod]

Thanks for this advice. Modifying my code now!
Many thanks again