[RESOLVED] loop problem using adodb , acess ASAP

[jackob247]

Private Sub cmdsave_Click()
Set rs248 = New ADODB.Recordset
rs248.Open "Select * from tblStock", cnn, adOpenDynamic, adLockPessimistic
With rs248
If Not .EOF Then
rs248.Fields("category").Value = catecmb.Text
rs248.Fields("Subcategory").Value = subcatcmb.Text
rs248.Fields("Mused").Value = metalcmb.Text
rs248.Fields("Weight").Value = weightcmb.Text
rs248.Fields("Stin").Value = rs248.Fields("Stin").Value + quantitytxt.Text
rs248.Update
rs248.Close
Else
rs248.AddNew
rs248.Fields("Category").Value = catecmb.Text
rs248.Fields("Subcategory").Value = subcatcmb.Text
rs248.Fields("Mused").Value = metalcmb.Text
rs248.Fields("Weight").Value = weightcmb.Text
rs248.Fields("Stin").Value = quantitytxt.Text
rs248.Update
rs248.Close
End If
End With
end sub

i enter
necklace pendent gold24k 3.2 8
necklace pendent gold24k 3.2 10
ring xyzz gold22k 1.1 15

table should store
necklace pendent gold24k 3.2 18
ring xyzz gold22k 1.1 15

[DataMiser]

What do you mean by loop problem? There is no loop in the code shown.

Your code needs to use a where clause in the select so you only get the matching record. As coded if there are any records in the DB then it will update the first record every time and never add a second record.

On another note rs248 is an odd name for a recordset. Please tell me that you are not using 248 different recordsets.

[jackob247]

What do you mean by loop problem? There is no loop in the code shown.

Your code needs to use a where clause in the select so you only get the matching record. As coded if there are any records in the DB then it will update the first record every time and never add a second record.

On another note rs248 is an odd name for a recordset. Please tell me that you are not using 248 different recordsets.

Could u tell what kind of where clause that would be helpful.

I am 1st time doing vb project so new to this.

And rs248 was a random pick.

[DataMiser]

It depends on what field(s) would determine if the record should be updated or added.

For example

rs248.Open "Select * from tblStock where Category='" & catecmb.Text &"' And Subcategory='" & subcatcmb.Text &"'", cnn, adOpenDynamic, adLockPessimistic

Ideally though there should be an item number of some sort and should be defined as a primary key

[jackob247]

Thx it is working now I spent a day to solve it

[Elroy]

Say jackob247,

I guess it's my day to be the hall monitor. Two things: 1) It's always a nice thing to do to show people what the problem was, and make a post that explains/shows how you fixed it, and 2) It helps us if you mark your thread as "resolved" under "Thread Tools" at the top. Only you can do that (well, other than the forum moderators).

Glad to hear you got it going.

All The Best,
Elroy