Adding amount in database proble

[masterfact18]

Private Sub Command1_Click()
Dim xSold As Integer
 Set db = New ADODB.Connection
        db.CursorLocation = adUseClient
        db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\inventory.mdb"
 
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM storage WHERE Current Stock = '" & Text1.Text & "'", db, adOpenStatic, adLockOptimistic
  
    If rs.BOF = True Or rs.EOF = True Then
        MsgBox "No items available to sell."
 
        Exit Sub
            Else
        xSold = rs!Form5.Adodc1.Recordset.Fields("Current Stock") = Text7.Text
 
        xSold = xSold - 1
        rs!ItemInStockAmountNameHere = xSold
        rs.Update
    End If
End Sub

'can someone tell what is wrong with my codes...error says that "Method 'Open' of object '_Recordset' failed....
please help me...need help ASAP...

[AndreRet]

Nice, now we are getting there, thank you.:)

This part -

Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM storage WHERE Current Stock = '" & Text1.Text & "'", db, adOpenStatic, adLockOptimistic

'Assuming that "Current Stock is the stock number you are using, because there is a space between them, put it between brackets -

Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM storage WHERE [Current Stock] = '" & Text1.Text & "'", db, adOpenStatic, adLockOptimistic

Change the following -

Else
        xSold = rs!YourFieldNameThatHasTheQuantityHere
'Change your field name here at rs!......
        
        xSold = xSold - 1
        rs!YourFieldNameThatHasTheQuantityHere = xSold
'Again, change the field name here ....

        rs.Update
    End If

[masterfact18]

Dim xSold As Integer
 Set db = New ADODB.Connection
        db.CursorLocation = adUseClient
        db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\inventory.mdb"
 
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM storage WHERE [Current Stock] = '" & Text1 & "'", db, adOpenStatic, adLockOptimistic
  
    If rs.BOF = True Or rs.EOF = True Then
        MsgBox "No items available to sell."
 
        Exit Sub
Else
        xSold = rs!Form5.Adodc1.Recordset.Fields("Current Stock")
 
        xSold = xSold + 1
        rs!Form5.Adodc1.Recordset.Fields ("Current Stock")
 
        rs.Update
    End If

right now this is my code...but when i run the system it this MsgBox"No items available to sell" always shows up and it did'nt deduct the stock in my database...

[masterfact18]

If rs.BOF = False Or rs.EOF = False Then
        MsgBox "No items available to sell."
Exit Sub
Else
        xSold = rs!Form5.Adodc1.Recordset.Fields("Current Stock")
 
        xSold = xSold - 1
        rs!Form5.Adodc1.Recordset.Fields ("Current Stock")
 
        rs.Update
    End If

an error has shown it says that ("runtime error '3265' Item cannot be found in the collection corresponding to the requested name or ordinal")

right now i don't what to do, my mind is in the mental block situation every time i see this error pls. help me...

[AndreRet]

Do not refer to your form 5 to get the records. You already have it on hand with your rs recordset. Again, refer to my code. If there is something you do not ubderstand about it, tell me so I can try and make it clearer.

At the moment you are dropping data by using your form 5 data control.

The error above is given because there are no stock to sell. You have to have something before it can be sold.

[masterfact18]

xSold = rs!Adodc1.Recordset.Fields("Current Stock")

same error happens even if i delete form5 in the code..do i need to replace some code/s
or do i need to use other code/s...?

[AndreRet]

xSold = rs!Adodc1.Recordset.Fields("Current Stock")

Have a look at my code.

xSold = rs!YourFieldNameThatHasTheQuantityHere
'Change your field name here at rs!......

What is your field name in your database for the stock count. You need to change the name of the field above.:)

[masterfact18]

error shows that "syntax error"
and this line was highlightened "rs!["Current Stock"]

[masterfact18]

im sorry i forgot to add this "=xold"
now that my program run..messagebox always and database records didn't even add or deduct..as in nothing happens only messagebox shows

[abelingaw]

If rs.BOF = True Or rs.EOF = True Then
     MsgBox "No items available to sell."

Try using

If rs.Recordcount = 0 Then
     MsgBox "No items available to sell."

Can you show your modified code.?

[masterfact18]

@Abelingaw : I also try that, but same problem happens..and messagebox just show but it didn't even add or deduct a in my database records....here is my database fields for you to try "Item Number,Description,Last Week Stocks,Deliveries,Total Stocks,Total Withdrawal and Current Stock...Current Stock is where I add or deduct...

[AndreRet]

Ok, I have attached a sample project that will add and deduct stock. You can use the code in everything else you do in your project.

Please mark THIS thread as well as the PREVIOUS one as solved, thanks.:)

[masterfact18]

xQuantity = Text1.Text
    xStockTotal = rsAdd![Current Stock]
    
    xQuantity = xQuantity + xStockTotal
    
    rsAdd![Current Stock] = xQuantity
    rsAdd.Update
        
    rsAdd.Close
    oConn.Close

what is the meaning of this "xQuantity,xStockTotal" is it a textbox name..?

[AndreRet]

Read the entire block of code, you'll see that I have declared these at the top. They are the variables that holds the values to add the stock. You do not need text boxes to perform this duty. If it was a textbox, it would have been xStockTotal.Text...

Did you try the code?:)

[masterfact18]

yeah i tried your program... it works... I copy all your codings you've done there i change my textbox name the same as yours and i also add combobox and i name it the same as yours but when i rum my program it's fine and no error but it did'n't add to my database.. my stock is 100 but when i add 2 it is still 100 nothing change..how does it happen if your program runs perfectly and mine was not...

[AndreRet]

Where did you put the database. It must be in the same folder as where your application is saved.

[masterfact18]

now it's running complete but not perfect...the problem is when i 2 add in current stock "where current stock = 100" the form which where the database contain it it doesn't changed but when i stop the program and run it again it change...in short it didn't update...

[AndreRet]

What form does it not show. Is it on a datagrid? You need to refresh it after the update, It will show the old data until you tell it to show the new data.

[masterfact18]

it is in the datagrid..all forms are fine.. the only problem is the datagrid didn't update after i add a number...

[AndreRet]

After the update, datagrid1.Refresh

[masterfact18]

xQuantity = txtQuantity.Text
    xStockTotal = rsAdd![Current Stock]
    
    xQuantity = xQuantity + xStockTotal
    
    rsAdd![Current Stock] = xQuantity
    rsAdd.Update
    Form5.DataGrid1.Refresh
    rsAdd.Close
    oConn.Close
    Unload Me
    Form5.Show

nothing change...did i put it in the wrong line..?

[AndreRet]

I will have a look at your form 5 and post the correct code in your new thread. You need to close this one, the original question has been answered.

Open a new thread with a title "Datagrid not refreshing". I'll post the datagrid solution there. We will confuse other members that might be searching fo this specific question by discussing multiple questions.;)

Please mark this as solved, as well as the first one you opened. I'll see you on your NEW thread soon.:)