findfirst command error

[anchamal]

Trying to open a table find specific record and change the stock but i can not get it to work

table: items
controlS: itemcode and stock
field in main form "itemcodesearch"

here is the code i use:
Dim rstITEMS As Recordset
Set rstITEMS = CurrentDb.OpenRecordset(Name:="ITEMS", Type:=RecordsetTypeEnum.dbOpenDynaset)
With rstITEMS

.FindFirst "[ITEMCODE]= ME.ITEMCODESEARCH"

.Edit
![STOCK] = Me.Text180
.Update
End With

 

[moke123]

why not just open your recordset to the record you want rather than a whole table with find first?

 

[anchamal]

ok, how do i do that?

 

[moke123]

use a where clause along the lines of

Dim db As DAO.Database
Dim rs  As DAO.Recordset
Dim strSql  As String
strSql = "select * from  ITEMS where ITEMCODE = " & ME.ITEMCODESEARCH
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql)

rs.Edit
rs![STOCK] = Me.Text180
rs.Update

MyExit:
rs.Close
Set rs = Nothing
Set db = Nothing

you may have to adjust the above depending on datatypes

 

[anchamal]

copy pasted the code but i get error:

too few parameters expected 1.

for line "set rs=db.openrecordset(strsql)

 

[moke123]

what is datatype of ITEMCODE?
if its a string then

strSql = "select * from  ITEMS where ITEMCODE = """ & ME.ITEMCODESEARCH & """"

 

[anchamal]

yes that's what i needed
thank so much for your help

 

[anchamal]

after closing and opening again nothing happenes any more
no errors no nothing

the stock goes to 0

 

[anchamal]

Only the first record changes

 

[moke123]

your only opening one record.
exactly what are you trying to do?
i cant see what you have so its hard to be specific.

 

[moke123]

you should indicate when you cross post - http://www.utteraccess.com/forum/index.php?showtopic=2040357&st=0&gopid=2621635&#entry2621635

 

[anchamal]

what i need to do:

i have two records in table items called CH1 and CH2
when user types the code in itemcodesearch it should go find the code and tehn remove one item from the stock.

your code worked fine the first time for both codes.
after closing the form and opening again it din't do anything. quantities did not change.
i removed all requeries from change event, it worked the first time,and then nothing.
tried to compact and repair. nothing happened again

 

[moke123]

i dont see that your doing any subtraction in your code.
what is Me.Text180? you should use more descriptive names.
is it the number of items that should be subtracted from rs!stock?