[RESOLVED] Add / Edit Data in DAO...
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
Re: Add / Edit Data in DAO...
We will need to see your code.
1 Attachment(s)
Re: Add / Edit Data in DAO...
Hi Again...
here i attached my form. plz do needful.
thank
kaushal
Re: Add / Edit Data in DAO...
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.
Re: Add / Edit Data in DAO...
thanks DataMiser for ur reply...
here am sending u code as per u said...
Below codes are for EDIT RECORD...
PHP Code:
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...
PHP Code:
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
Re: Add / Edit Data in DAO...
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
Code:
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
Code:
Set rs = db.OpenRecordset("Select Max(TID) as LastTid from Transaction")
Re: Add / Edit Data in DAO...
better sorting in ascending is much better .this way you will get max value at last point of recordset .
Code:
Set rs = db.OpenRecordset("Select TID from Transaction Order By TID Asc")
Re: Add / Edit Data in DAO...
Quote:
Originally Posted by
firoz.raj
better sorting in ascending is much better .this way you will get max value at last point of recordset .
Code:
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.
Re: Add / Edit Data in DAO...
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).
Re: Add / Edit Data in DAO...
DataMiser,
Hi....
Thanks for ur Solution...it works nicely...
your first suggestion works perfectly.
PHP Code:
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.
Re: Add / Edit Data in DAO...
Thanks firoz.raj for your help...
Re: Add / Edit Data in DAO...
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.
Re: Add / Edit Data in DAO...
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() !