[RESOLVED] Add / Edit Data in DAO...

[kaushal.jam]

Hi all,

I am using access as a backhand (for database) and in vb i am using DAO.

theres are almost 60 records in my database. till this i dnt have any problem in ADD or EDIT record.

now after 60, my record counter save new record as a 61, but aftr that my counter doesnt move to 62. it save record on 62 only. am using code for record number. same way when i edit my record now its count it as new record. means it keep both records - old one and edited one.

so what sould i do. please help ASAP.

thanks again,

kaushal

[Doogle]

We will need to see your code.

[kaushal.jam]

Hi Again...

here i attached my form. plz do needful.

thank

kaushal

[DataMiser]

Paste the code used to update the data into a message and use code tags around it to retain formatting.

In DAO using an RS to update a record you must use the RS.Edit and to add one you must use RS.AddNew from what you have said it would seem that your code is getting confused and using the wrong one and whatever code you are using to generate ID numbers is not working properly. Your first action should be to set a break point in this routine and step through the code line by line to see what is executing and what the id number is doing.

[kaushal.jam]

thanks DataMiser for ur reply...

here am sending u code as per u said...

Below codes are for EDIT RECORD...

Private Function UpDateData()
    Set db5 = OpenDatabase(App.Path & "\HOME.mdb")
    Set rs5 = db5.OpenRecordset("Select * from TRANSACTION where TID = " & TransLstView.SelectedItem.Tag & " ")
        If rs5.EOF Or rs5.BOF Then
        Else
        rs5.Edit
            rs5!TID = Text4.Text
            rs5!TInOut = Combo1.Text
            rs5!TType = Combo2.Text
            rs5!TDate = Format(DTPicker1.Value, "dd/mm/yyyy")
            rs5!TMode = Combo3.Text
            If Combo3.Text = "CASH" And Combo4.Text = "" Then
                rs5!TBank = ""
                rs5!TBAcNo = ""
            Else
                rs5!TBank = Combo4.Text
                rs5!TBAcNo = Text1.Text
            End If
            If Combo1.Text = "INCOME" Then
                rs5!TAmtIn = Text2.Text
                rs5!TAmtOt = Val("") '"0.00"
            Else
                rs5!TAmtIn = Val("") '"0.00"
                rs5!TAmtOt = Text2.Text
            End If
            rs5!TRemarks = Text3.Text
            rs5!InOutID = Text5.Text
        rs5.Update
        End If
        rs5.Close
    FrmLoad
End Function 

and below codes for RECORD NUMBER...

Private Function TransID()
    Set db = OpenDatabase(App.Path & "\HOME.mdb")
    Set rs = db.OpenRecordset("Transaction", dbOpenTable)
         
        If rs.EOF = True And rs.BOF = True Then
            counter = 1
            Text4.Text = counter
        Else
            rs.MoveLast
            counter = rs!TID
            Text4.Text = counter + 1
        End If
End Function 

i have checked with line to line, but still no result...
so, plz help...

thanks in advance....
kaushal

[DataMiser]

Ah you are not sorting your data. You are moving to the last record and getting the value but unless you sort the data you can not know what order the data will be in so you could very well get something other than you expect.

In your transID routine you should change the recordset to sort your data

Set rs = db.OpenRecordset("Select TID from Transaction Order By TID DESC")

This should always give you the highest value as the first record. If you omit the DESC then it would be in the last record.

Or better still you could get only the value you want by using something like

Set rs = db.OpenRecordset("Select Max(TID) as LastTid from Transaction")

[firoz.raj]

better sorting in ascending is much better .this way you will get max value at last point of recordset .

Set rs = db.OpenRecordset("Select TID from Transaction Order By TID Asc")

[DataMiser]

better sorting in ascending is much better .this way you will get max value at last point of recordset .

Set rs = db.OpenRecordset("Select TID from Transaction Order By TID Asc")

How is that "much better" ?

The best way is to select the max value as I have shown
The second best way is the DESC sort which prevents the need to jump to the last record.

Please don't something is better unless you know that to be the case.

btw ASC is not required as that is the default value anyway.

[firoz.raj]

also just make query as datamiser suggested using max(id) this way .you will get max value .and just use in openrecordset function .this way you will get each time latest value on the basis of max(id).

[kaushal.jam]

DataMiser,

Hi....

Thanks for ur Solution...it works nicely...

your first suggestion works perfectly.

Set rs = db.OpenRecordset("Select Max(TID) as LastTid from Transaction") 

but in above suggestion it shows TID = 0 every time.

anyway thanks for ur solution.

[kaushal.jam]

Thanks firoz.raj for your help...

[DataMiser]

In my suggestion to use max there would be no TID in the resulting recordset it would be LastTid and should contain the highest value.

[firoz.raj]

if you are using msAccess .you can make query which select rows on the basis of max id .and just call that query in openrecordset() !