error "item variable not found"

[masterfact18]

Private Sub cmdAdd_Click()

Dim sConn As String, xQuantity As Integer, xStockTotal As Integer
Set oConn = New ADODB.Connection
Set rsAdd = New ADODB.Recordset
    
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\inventory.mdb;Persist Security Info=False"
    
oConn.Open sConn
    
If LenB(cmbItemName.Text) = 0 Then
    MsgBox "You have to select an item from the drop down list.", vbOKOnly + vbExclamation, "No Selection"
    
    cmbItemName.SetFocus
    Exit Sub
ElseIf LenB(txtQuantity.Text) = 0 Then
    MsgBox "You have not set a quantity amount to add.", vbOKOnly + vbExclamation, "No Quantity"
    
    txtQuantity.SetFocus
    Exit Sub
        Else
    rsAdd.Open "SELECT Description, [Current Stock] FROM storage WHERE Description = '" & cmbItemName.Text & "'", oConn, adOpenKeyset, adLockOptimistic
    
    xQuantity = txtQuantity.Text
    xStockTotal = rsAdd![Current Stock]
    
    xQuantity = xQuantity + xStockTotal
    
    rsAdd![Current Stock] = xQuantity
    rsAdd.Open "SELECT Description, [Total Stocks] FROM storage WHERE Description = '" & cmbItemName.Text & "'", oConn, adOpenKeyset, adLockOptimistic
    
    xQuantity = txtQuantity.Text
   [U] xStockTotal = rsAdd![Total Stocks]
    
    xQuantity = xQuantity + xStockTotal
    
    rsAdd![Total Stocks] = xQuantity[/U]
    MsgBox "Quantity added!", vbOKOnly + vbInformation, "Success"
    rsAdd.Update
    Form5.DataGrid1.Refresh
    rsAdd.Close
    oConn.Close
    Unload Me
    Form5.Adodc1.Refresh
    Form5.DataGrid1.Refresh
    Form5.Show
End If
End Sub

the underlined code was the problem...Download my attach files to see it for yourself...when i delete the underlined code the program runs perfect but when I add it program error shows...Do I put the wrong code..Pls Help...

[AndreRet]

Morning Martin, I'm glad to see that your coding skills is improving every day. Keep it up!:)

Your problem -

You have made a call to rsAdd while it is still open, hence the error. You need to do one of two things here, either open "Total Stock" in the first recordset as in -

'First declare another variable that will hold the value of "Total Stock"
Dim sConn As String, xQuantity As Integer, xStockTotal As Integer, xTotal As Integer

rsAdd.Open "SELECT Description, [Current Stock], [Total Stock] FROM storage WHERE Description = '" & cmbItemName.Text & "'", oConn, adOpenKeyset, adLockOptimistic

xQuantity = txtQuantity.Text
    xStockTotal = rsAdd![Current Stock]
    
    xQuantity = xQuantity + xStockTotal
    
    rsAdd![Current Stock] = xQuantity
    
    xQuantity = txtQuantity.Text
    xTotal = rsAdd![Total Stocks]
    
    xQuantity = xQuantity + xTotal
    
    rsAdd![Total Stocks] = xQuantity
    MsgBox "Quantity added!", vbOKOnly + vbInformation, "Success"

Or, you need to either close rsAdd and re-open it, or use another recordset. The easiest way is to do it as per my code above.

[masterfact18]

ElseIf LenB(txtQuantity.Text) = 0 Then
    MsgBox "You have not set a quantity amount to add.", vbOKOnly + vbExclamation, "No Quantity"
    
    txtQuantity.SetFocus
    Exit Sub
        Else
    rsAdd.Open "SELECT Description, [Current Stock] FROM storage WHERE Description = '" & cmbItemName.Text & "'", oConn, adOpenKeyset, adLockOptimistic
    
    xQuantity = txtQuantity.Text
    xStockTotal = rsAdd![Current Stock]
    
    xQuantity = xQuantity + xStockTotal
    
    rsAdd![Current Stock] = xQuantity
    rsAdd.Close
    oConn.Open sConn

error in this part

rsAdd.Open "SELECT Description, [Total Stocks] FROM storage WHERE Description = '" & cmbItemName.Text & "'", oConn, adOpenKeyset, adLockOptimistic

xQuantity = txtQuantity.Text
    xWholeStock = rsAdd![Total Stocks]
    
    xQuantity = xQuantity + xWholeStock
    
    rsAdd![Total Stocks] = xQuantity
    MsgBox "Quantity added!", vbOKOnly + vbInformation, "Success"
    rsAdd.Update
    Form5.DataGrid1.Refresh
    rsAdd.Close
    oConn.Close
    Unload Me
    Form5.Adodc1.Refresh
    Form5.DataGrid1.Refresh
    Form5.Show
End If

i have the rsAdd but still there's an error

[AndreRet]

Have you read my post above properly?;)

You already used rsAdd, you can not use it again for [Total Stocks], because you have to close it first. Either use my code above exactly as it is, or create another recordset, say rsTotal.

[masterfact18]

ohh i see.. the one i did is to close rsAdd but it's not working so i'll declare another variable for that...

[AndreRet]

Set rsTotal = New ADODB.Recordset

rsTotal.Open "SELECT Description, [Total Stocks] FROM storage WHERE Description = '" & cmbItemName.Text & "'", oConn, adOpenKeyset, adLockOptimistic

[masterfact18]

Dim sConn As String, xQuantity As Integer, xStockTotal As Integer, xWholeStock As Integer
Set oConn = New ADODB.Connection
Set rsAdd = New ADODB.Recordset
[U]Set rsDone = New ADODB.Recordset[/U]

still there's an error, error says "varible not defined"

did i put it in the wrong place..? or i put a wrong code..?

[AndreRet]

At the bottom, do not forget to close it with rsAdd -

rsTotal.Close

[AndreRet]

Your code is fine, just use the .Open statement now as in my code 4 posts up.:)

[masterfact18]

still "variable not define" in this line

Dim sConn As String, xQuantity As Integer, xStockTotal As Integer, xWholeStock As Integer
Set oConn = New ADODB.Connection
Set rsAdd = New ADODB.Recordset
[U]Set rsTotal = New ADODB.Recordset[/U]

[AndreRet]

My bad here. At the top of your code page, add this to the other conn and rs's -

Dim rsTotal As New ADODB.Recordset

Sorry.:)

[masterfact18]

ohh i forgot to put "rsTotal as ADODB.recordset" in option explicit...sorry to confuse you...now it runs but it only add in [Current Stocks] it didn't add in [Total Stocks] and i have to erase this "rsTotal.close" because if i didn't erase it error shows that "Operation is not allowed in this context" in rsTotal.close

[masterfact18]

hehehe I solved it all by myself...I'm improving...hahah....tnx.. here is the code that I changed...

Else
    rsAdd.Open "SELECT Description, [Current Stock] FROM storage WHERE Description = '" & cmbItemName.Text & "'", oConn, adOpenKeyset, adLockOptimistic
    
    xQuantity = txtQuantity.Text
    xStockTotal = rsAdd![Current Stock]
    
    xQuantity = xQuantity + xStockTotal
    
    rsAdd![Current Stock] = xQuantity
    rsAdd.Update
 
    rsTotal.Open "SELECT Description, [Total Stocks] FROM storage WHERE Description = '" & cmbItemName.Text & "'", oConn, adOpenKeyset, adLockOptimistic
    
    xQuantity = txtQuantity.Text
    xWholeStock = rsTotal![Total Stocks]
    
    xQuantity = xQuantity + xWholeStock
    
    rsTotal![Total Stocks] = xQuantity
    MsgBox "Quantity added!", vbOKOnly + vbInformation, "Success"
    
    rsTotal.Update
    rsAdd.Close
    rsTotal.Close
    oConn.Close
    Unload Me
    Form5.Adodc1.Refresh
    Form5.DataGrid1.Refresh
    Form5.Show
End If

by the way thanks again your my savior...
now all i gotta to do is to mark this thread as solved...Again...Thanks...

[AndreRet]

It was only a pleasure. You are really getting the hang of this. Keep going and happy coding.;)