how to i increase the (column,row) of a specfic value thanks!!

this is my code for counting:

Code:

---

 
  Dim sql As String
  Dim strCount As String
  sql = "Select Count(*) from Table1 Where BOOKNAME='" & tempBook & "'"
  Data1.RecordSource = sql
  Data1.Refresh
  strCount = Data1.Recordset.Fields(0)
  Data1.Refresh

---

this is the part where i can ONLY INCREASE the availibility column 1 by 1:

when i add the first book, it will show 1
when i add the second book, for the first book it will show 2, but for the second book it will still show 1.

when i add the third book, for the first book it will show 3, for the second book it will show 2 and for the book i just added in it will show 1.

it looks like that:

BOOKNAME----------TAGID-----------TAGCONTENT---------AVALIBILITY
hi------------------------S----------------------S--------------------3
hi------------------------S----------------------S--------------------2
hi------------------------S----------------------S--------------------1


what i want is that when i add e.g. the 4th book, all the AVALIBILITY will be 4. when i add the 5th book, all will be 5.

this is my current code:

Code:

---

Private Sub Command1_Click()
 If Text1.Text = "" Or Text2.Text = "" Or Text3.Text = "" Then
  errormsg = MsgBox("ALL FIELDS MUST CONTAIN DATA!!")
 Else
    Data1.Recordset.AddNew
    Data1.Recordset.Fields(0).Value = Text1.Text
    Data1.Recordset.Fields(1).Value = Text2.Text
    Data1.Recordset.Fields(2).Value = Text3.Text
    'Data1.Recordset.Fields(3).Value =
    Data1.Recordset.Update
  End If
 
  Dim tempBook As String
  tempBook = Text1.Text
 
  MsgBox "Book has been added!!"
 
  Data1.Recordset.MoveFirst
        Do While Not Data1.Recordset.EOF
            Data1.Recordset.Edit
            If UCase(Data1.Recordset![BOOKNAME]) = UCase(tempBook) Then
                Data1.Recordset![AVALIBILITY] = Data1.Recordset![AVALIBILITY] + 1
            Else
                Data1.Recordset![AVALIBILITY] = 1
            End If
            Data1.Recordset.Update
            Data1.Recordset.MoveNext
        Loop
       
       
       
   
 
  Dim sql As String
  Dim strCount As String
  sql = "Select Count(*) from Table1 Where BOOKNAME='" & tempBook & "'"
  Data1.RecordSource = sql
  Data1.Refresh
  strCount = Data1.Recordset.Fields(0)
  Data1.Refresh
       
  Dim strMsg As String
  strMsg = "Book Availability is " & strCount
  Text1.Text = ""
  Text2.Text = ""
  Text3.Text = ""
  Text4.Text = strMsg

---

may i know how/where could i edit it thanks=)

Would his work?

Code:

---

Data1.Recordset![AVALIBILITY] = Data1.Recordset![AVALIBILITY] = Data1.Recordset.Recordcount

---

thanks guys i shall go back to try tml !! will update yea!

Quote:

---

Originally Posted by wes4dbt

Would his work?

Code:

---

Data1.Recordset![AVALIBILITY] = Data1.Recordset![AVALIBILITY] = Data1.Recordset.Recordcount

---

---

that would be the total of all the books in the recordset

what you really need is something like a sql query to insert the value into all the records where bookname = hi, using a count query of the same to get the number to put into all the records

Quote:

---

Originally Posted by westconn1

that would be the total of all the books in the recordset

what you really need is something like a sql query to insert the value into all the records where bookname = hi, using a count query of the same to get the number to put into all the records

---

could you help me with the codes thanks =)

any one?

you can try like this, but check for correct results

vb Code:

Dim rcnt As Recordset, lcnt As Long
Set rcnt = Data1.Database.OpenRecordset("Select Count(*) from Table1 Where BOOKNAME='" & tempBook & "'")
lngcnt = rcnt(0)
rcnt.Close
Set rcnt = Nothing
Data1.Database.Execute "update table1 set avalibility =" & lngcnt & " where BOOKNAME = '" & tempbook & "'"