Hi
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.
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.
Code:
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