Better coding...? 2

[rahulpatel]

I have this code when a txtBox loses its focus.

Private Sub txtSearch_LostFocus()

Dim sSQL As String
    Set cn = New ADODB.Connection 
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb" 
    cn.Open
Set rs = New ADODB.Recordset 'adds to quantity when barcode in is scanned
sSQL = "UPDATE Stores SET quantity = quantity + quantityin WHERE barcodein = '" & (txtSearch.Text) & "'"
rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText 'updates db

Dim sSQL1 As String
    Set cn1 = New ADODB.Connection 
    cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb" 
    cn1.Open
Set rs1 = New ADODB.Recordset 'subtracts from quantity when barcode out is scanned
sSQL1 = "UPDATE Stores SET quantity = quantity - quantityout WHERE barcodeout = '" & (txtSearch.Text) & "'"
rs1.Open sSQL1, cn1, adOpenKeyset, adLockOptimistic, adCmdText 'updates database

Dim sSQL2 As String
    Set cn2 = New ADODB.Connection 'makes the tobeordered field equal to Max subtract quantity after stock out
    cn2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb" 
    cn2.Open
Set rs2 = New ADODB.Recordset
sSQL2 = "UPDATE Stores SET tobeordered = [Max] - quantity WHERE barcodeout = '" & (txtSearch.Text) & "'"
rs2.Open sSQL2, cn2, adOpenKeyset, adLockOptimistic, adCmdText

Dim sSQL3 As String
    Set cn3 = New ADODB.Connection 'makes the tobeordered field equal to Max subtract quantity after stock in
    cn3.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb" 
    cn3.Open
Set rs3 = New ADODB.Recordset
sSQL3 = "UPDATE Stores SET tobeordered = [Max] - quantity WHERE barcodein = '" & (txtSearch.Text) & "'"
rs3.Open sSQL3, cn3, adOpenKeyset, adLockOptimistic, adCmdText

Dim sSQL5 As String
    Set cn5 = New ADODB.Connection
    cn5.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb" 
    cn5.Open
Set rs5 = New ADODB.Recordset

sSQL5 = "SELECT * FROM Stores WHERE barcodeout = '" & (txtSearch.Text) & "'"
rs5.Open sSQL5, cn5, adOpenKeyset, adLockOptimistic, adCmdText

txtItemNo.Text = rs5.Fields("ItemNo") & ""
txtCatalog.Text = rs5.Fields("CatalogueNo") & ""
txtMax.Text = rs5.Fields("Max") & ""
txtMin.Text = rs5.Fields("Min") & ""
txtQuantity.Text = rs5.Fields("quantity") & ""
txtUnits.Text = rs5.Fields("Unit") & ""
txtExpiry.Text = rs5.Fields("expiry") & ""
txtLotNo.Text = rs5.Fields("lotno") & ""
txtToOrder.Text = rs5.Fields("tobeordered") & ""

Dim sSQL4 As String
    Set cn4 = New ADODB.Connection 
    cn4.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb" 
    cn4.Open
Set rs4 = New ADODB.Recordset

sSQL4 = "SELECT * FROM Stores WHERE barcodein = '" & (txtSearch.Text) & "'"
rs4.Open sSQL4, cn4, adOpenKeyset, adLockOptimistic, adCmdText

txtItemNo.Text = rs4.Fields("ItemNo") & ""
txtCatalog.Text = rs4.Fields("CatalogueNo") & ""
txtMax.Text = rs4.Fields("Max") & ""
txtMin.Text = rs4.Fields("Min") & ""
txtQuantity.Text = rs4.Fields("quantity") & ""
txtUnits.Text = rs4.Fields("Unit") & ""
txtExpiry.Text = rs4.Fields("expiry") & ""
txtLotNo.Text = rs4.Fields("lotno") & ""
txtToOrder.Text = rs4.Fields("tobeordered") & ""

End Sub

The SQLs work just as I want them apart from sSQL4 and 5. What I want it to do is update the db according to which value is entered in the txtSearch box. If the value is in barcodein field it adds and if it's in barcodeout field it subtracts from quantity field. What I'm trying to do with sSQL4 and 5 is show the data from the db in my textboxes after it's all been updated.

So firstly the code looks messy and I'm pretty sure it's not the best way to do it. Secondly, showing the updated data in textboxes only works to a certain extent. Is this an acceptable way of updating and then showing updated data or is there an easier/quicker way of doing it?

 

[HarleyQuinn]

You don't need recordsets for the update queries, you can just use ch.execute instead, that cleans it up a bit for you.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[JoeAtWork]

There's no need to open 5 connections - they are all connecting to the same database. Just use "cn" for everything. Once you have openend it, you don't need to open it again.

What Harley is referring to is:

cn.Execute sSQL2

BTW, didn't we extablish in your other thread (

http://www.tek-tips.com/viewthread.cfm?qid=1488042&page=1)

that barcodein is numeric, not text, and shouldn't be surrounded by quotes?

Also, when you display the data, first you load the record for barcodeout into the textboxes, then immediately load the record for barcodein in the same textboxes, so your users will never see the data for barcodeout.

 

[Golom]

As others have said

- You need only one connection
- and only one recordset
- and only one SQL text-string
- and you can do the updates with only two SQL statements
- if bar codes are numeric (they should not be), then remove the quotes
- You are over-writing the text boxes so the first set of code to populate them is not relevant.
- You need to check for EOF on the recordset to ensure that the barcodes entered in txtSearch.Text actually exist.

Private Sub txtSearch_LostFocus()

    Dim rs                          As ADODB.Recordset
    Dim sSQL                        As String

    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\stock.mdb"
    cn.Open

    'adds to quantity when barcode in is scanned
    sSQL = "UPDATE Stores SET " & _
           "        tobeordered = [Max] - (quantity + quantityin), " & _
           "        quantity = quantity + quantityin " & _
           "WHERE barcodein = '" & (txtSearch.Text) & "'"
    cn.Execute sSQL

    'subtracts from quantity when barcode out is scanned
    sSQL = "UPDATE Stores SET " & _
           "        tobeordered = [Max] - (quantity - quantityout), " & _
           "        quantity = quantity - quantityout " & _
           "WHERE barcodeout = '" & (txtSearch.Text) & "'"
    cn.Execute sSQL

    ' [COLOR=black cyan] Probably irrelevant ... may be over-written in the next block
    sSQL = "SELECT * FROM Stores WHERE barcodeout = '" & (txtSearch.Text) & "'"
    Set rs = New ADODB.Recordset
    rs.Open sSQL, cn, adOpenForwardOnly, adLockBatchOptimistic, adCmdText

    With rs
        If Not .EOF Then
            txtItemNo.Text = ![ItemNo]
            txtCatalog.Text = ![CatalogueNo]
            txtMax.Text = ![Max]
            txtMin.Text = ![Min]
            txtQuantity.Text = ![quantity]
            txtUnits.Text = ![Unit]
            txtExpiry.Text = ![expiry]
            txtLotNo.Text = ![lotno]
            txtToOrder.Text = ![tobeordered]
        End If
    End With
    ' [/color]

    sSQL = "SELECT * FROM Stores WHERE barcodein = '" & (txtSearch.Text) & "'"
    Set rs = New ADODB.Recordset
    rs.Open sSQL, cn, adOpenForwardOnly, adLockBatchOptimistic, adCmdText

    With rs
        If Not .EOF Then
            txtItemNo.Text = ![ItemNo]
            txtCatalog.Text = ![CatalogueNo]
            txtMax.Text = ![Max]
            txtMin.Text = ![Min]
            txtQuantity.Text = ![quantity]
            txtUnits.Text = ![Unit]
            txtExpiry.Text = ![expiry]
            txtLotNo.Text = ![lotno]
            txtToOrder.Text = ![tobeordered]
        End If
    End With
End Sub

 

[northrd]

Golom

It's a pleasure to read your code. Neat, structured, uncluttered ... how it should be for developers who will follow-on from you.

 

[dilettante]

But why request batch update cursor Recordsets here?

It looks like a forward-only read-only cursor would be fine, and you can obtain those as the result of Execute on the Connection.

I wouldn't use dynamic SQL here either (SQL injection risk). Instead use a parameter query.