duplicate entry issue & Compuatation issue

[technocraze]

Hi guys,

I am using MS Acess in visual basic environment. what i wants to achieve is to be able to check for any duplicate entries in the database using sql quey (insert, update). But nt too sure as to hw this can be achieved. Any guidance or code ref is kindly appreciated. I tried to use a foreach loop to iterate through the records but nt working fine, i have declared the variables as a variant for the for each loop. I would like to make of the properties of recordSet to achieve this. if the entries exists it sld nt be inserted in the table. SerialCode AutoNum as the pk. Another issue is whenever i appended, inserted new records the previous record gt overwritten, which shld nt be the case

Set recorset
open database for the recordset
find the records field
check for duplicate entry before inserting or updating

Next, i would like to compute the balance. For instance I have these few fields in my table (TotalParticpants, WithDrawl, Bal) where activitiy = running. I want to write a sql stat that will able to compute the bal of particpants that did not withdrawl from the contest and update the bal in table.

My query as followed
select TotalParticpants, Withdrawl, bal(TotalParticipants - Withdrawl)
from Contest where Activity = Running.

Acess the fields (TotalParticpants, Withdrawl) frm the table. Retreieved the values of fields(TotalParticpants = 100, Withdrawl = 500) and compute the bal = 500 in textbox of the form and update the value in the contest table. But nt too sure hw to mainpulate the recordSet to acess the field, retrieve the fields values from the database and update the bal in the contest table.

Thanks folks for guidance or code ref.

[Tommy]

Hi technocraze,

You say you are using VB, What controls/methods are you using to get data from the database?

Are you using ADODB, Jet ... this information will allow me to help you more accurately.

You want to delete duplicates, have you thought about which one is correct? Or can we just delete all and re-enter?

Can you post what you have or e-mail?

[technocraze]

Hi Tommy,

first of all, thanks for your reply.

Well, in fact i am using the properties of the recordSet(rs.MoveFirst). What i want to achieve is to be able to acess the value of the fields of the table. But nt too sure as hw this can be acheived. What i knew so far are rs.Field(index), set rs = Database.OpenRecordSet("tableName"), loop through the recordSet, rs.MoveFirst.

First encounter(Overwritten issue)

I have a few records in my table. SerialCode is my pk in the table. My form interface is (Combo Box with predefined values, Textboxes. Once i click on the combol box, my textbox wil show the corresponding values that matches that of the combo box. By right when i inserted the values from the textboxes, combo box, the current insertion shouldnt be overwritten by the next insertion.

2nd encounter( Acess the values of the field and compute the balance based on the selected fields)

Next, I want to compute the balance(For instance under my table there are these few fields(SerialCode(pk) ,TotalParticpants(field1), Withdrwal(field2), Balance(field(3)). I shld be able to make use of the recordset to acess that particular values of the fields and based on the fields values to compute the balance and update the balance to my table using an sql stat(select, update). Let say under my table field(1) = 500, field(2) = 300 the sql stat shld be able to compute the balance based on the result of the selected fields and update the balance in the table.

All this I am using MS Acess(frontend) , sql Server (backend) nt too sure whether i need to open the connection, set connection string. perhaps u can guide me along as hw recordSet can be used with some code snippets for better understanding coz i am still fresh in this environment. thks for your kind assistance.


Hi technocraze,

You say you are using VB, What controls/methods are you using to get data from the database?

Are you using ADODB, Jet ... this information will allow me to help you more accurately.

You want to delete duplicates, have you thought about which one is correct? Or can we just delete all and re-enter?

Can you post what you have or e-mail?

[Tommy]

#1 rs.addnew then populate the items

This is one I use to insert a new record in an access database. The recordset is populated from a form that has been filled out. The rs object is an ADODC connection. I fire this sub right after I delete duplicates, delete items according to certein criteria and compact and repair the database.

Private Sub PutANewOneIn()
Dim cnt As Integer
rs.RecordSource = "SELECT name, street, city, state, zip," & _
"phone, fax, cell, email, cord, allow1, allow2, allow3, " & _
"allow4, allow5, allow6 From table Where name = '" & _
UCase$(Combo1.Text) & "' and cord = '" & number & "' order by name"
rs.Refresh
If rs.Recordset.RecordCount = 0 Then
rs.Recordset.AddNew
End If
rs.Recordset!NAME = UCase$(Combo1.Text)
rs.Recordset!street = UCase$(Text1.Text)
rs.Recordset!City = UCase$(Text2.Text)
rs.Recordset!State = UCase$(Text3.Text)
rs.Recordset!Zip = UCase$(Text5.Text)
If Text6.Text > vbNullString Then rs.Recordset!Email = UCase$(Text6.Text)
If Text7.Text > vbNullString Then rs.Recordset!fax = UCase$(Text7.Text)
rs.Recordset!phone = UCase$(Text8.Text)
If Text19.Text > vbNullString Then rs.Recordset!Cell = UCase$(Text19.Text)
If Combo2.ListCount > 0 Then
cnt = Combo2.ListCount - 1
While cnt > -1
Select Case cnt
Case 0
rs.Recordset!allow1 = UCase$(Combo2.List(cnt))
Case 1
rs.Recordset!allow2 = UCase$(Combo2.List(cnt))
Case 2
rs.Recordset!allow3 = UCase$(Combo2.List(cnt))
Case 3
rs.Recordset!allow4 = UCase$(Combo2.List(cnt))
Case 4
rs.Recordset!allow5 = UCase$(Combo2.List(cnt))
Case 5
rs.Recordset!allow6 = UCase$(Combo2.List(cnt))
Case 6
rs.Recordset!allow7 = UCase$(Combo2.List(cnt))
End Select
Combo2.RemoveItem (cnt)
cnt = cnt - 1
Wend
End If
rs.Recordset!Cord = number
rs.Recordset.UpdateBatch
rs.Refresh
End Sub


#2 I am sure there is a way to get the balance with a select, I am having a brain cramp on that particular issue :dunno but as an alternate you could select the items itterate through the recordset and add the items to a balance. Then update.:think: