Private Sub Command1_Click()
Dim xSold As Integer
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\inventory.mdb"

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....

3
Contributors
21
Replies
22
Views
6 Years
Discussion Span
Last Post by 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 -

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
Dim xSold As Integer
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\inventory.mdb"

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 = xSold + 1

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...

If rs.BOF = False Or rs.EOF = False Then
MsgBox "No items available to sell."
Exit Sub
Else

xSold = xSold - 1

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...

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.

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...?

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.:)

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

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

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.?

@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...

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.:)

Edited by AndreRet: n/a

Attachments
xQuantity = Text1.Text

xQuantity = xQuantity + xStockTotal

oConn.Close

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

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?:)

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...

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

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...

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.

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

After the update, datagrid1.Refresh

xQuantity = txtQuantity.Text

xQuantity = xQuantity + xStockTotal

Form5.DataGrid1.Refresh
oConn.Close
Form5.Show