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

[sexyhippo]

this is my code for counting:

  
   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:

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=)

[wes4dbt]

Would his work?

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

[sexyhippo]

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

[westconn1]

Would his work?

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

[sexyhippo]

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 =)

[sexyhippo]

any one?

[westconn1]

you can try like this, but check for correct results

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 & "'"