Community discussion forum

checking for duplicate entries

• • Reply

  1 year ago

  by belbug11

  cristover vidal

  , Philippines

  Joined 1 year ago

  Thakns for this forum, i hope am going to get my answer in this place. I have a system for registering new students in an institution. Registration includes assigning a unique admission number to each of them. this is done by the registrar by typing the admission number to the system. The problem as of now is that the system can allow duplicate entries for this , which causes a danger cause every student shuld have a unique number. I dont know how i could prevent this using VB6, could somebody please send some assistance, i will be grateful. NB. the records are stored in a table called registration in a database called royaldb. My vb6 uses MS access 95 format. The admission number can not be autonumber.
  

  thanks in advance. 

  Report abuse
• • Reply

  1 year ago

  by mtaivalmaa

  Mike Taivalmaa

  , United States

  Joined 1 year ago

  Hi,

  Set "primary key" or "unique key" to the registration number column in ur Database table. 

   

  
  
  Report abuse
• • Reply

  1 year ago

  by ivarka

  Ivar Katmo

  , Norway

  Joined 1 year ago

   Another way is to check the database entries for any similar entry with this id

  The below is a simple code:

   Dim Db1 As Database
  Dim Rec1 As Recordset
  Dim R_Count As Currency
  
  Set Db1 = Workspaces(0).OpenDatabase(App.Path & "\royaldb")
  Set Rec1 = Db1.OpenRecordset("registration", dbOpenDynaset)
  
  If Rec1.AbsolutePosition > -1 Then
      Rec1.movelast
      R_Count = Rec1.recordcount
      Rec1.movefirst
      For i = 1 To R_Count
          If Rec1.fields("id") = Trim(text1.Text) Then
              MsgBox "This id exists, please type a new one", vbCritical, "Exists"
              text1.SetFocus
              Exit Sub
          End If
          Rec1.movenext
      Next i
  UpdateData 'function to save data to the database
  Else
  UpdateData 'function to save data to the database
  End If

  Place the above code inside the command button for 'Saving' . I think this will help you in solving this problem....

  
  

  Report abuse
• • Reply

  1 year ago

  by belbug11

  cristover vidal

  , Philippines

  Joined 1 year ago

   Thanks alot men, this completly sorted my problem, have been stuck for days, thanks again.

  Mind if i ask another question still about vb but not  duplicates now, okay, when one designs a vb application, can it run on a server machine so that it can be opened by different users? To elaborate, i have designed a school management program that should utilize a single database. The program has a form for the accountant and the registrar. both forms are on two different tables but on the same database. Am thinking, if i install the proram on a single server, could it be opened by both the registrar and the accountant? help buddy pls. thanks in advance. 

  Report abuse
• • Reply

  1 year ago

  by ivarka

  Ivar Katmo

  , Norway

  Joined 1 year ago

   Brother,

  I think you can use RDO connection for client - server  applications. I haven't used it much. So, I don't know much about it. Try to search in Google or have a look at the below tutorial...:)

  http://www.vb6.us/tutorials/database-access-rdo-remote-data-objects-vb6
  

  Report abuse
• • Reply

  1 year ago

  by Judau_Ashta

  R. N. Infante

  Manila, Philippines

  Joined 3 years ago

  Open a Recordset and Throw SQL Query
  

  "SELECT * FROM REGISTRATION WHERE LASTNAME = " & strLastName & " AND FIRSNAME = " & strFirstName

  if recordcount  > 0 then

   msgbox "Already Exits".
  

  end if
  

  Report abuse
• • Reply

  1 year ago

  by belbug11

  cristover vidal

  , Philippines

  Joined 1 year ago

   thanks buddy, but i understand a little sql but the problem i dont know where i should type the sql statements, such as the on above. To illustrate what i mean, if i wanted to select all rocords from a table called patients where the patient no is 12, will the code below be correct?

  Private Sub cmdOldPatient_Click()

  datPatients.recordsource=" SELECT * FROM PATIENTS WHERE OP_NO = " 12 "

  end sub 

  will it display the records in the bound text boxes after getting the record?

  Lastly if a patient has been registered more than once in the hospital BUT with the same OP_NO and i want to search for all the patients past records , how would i do this so that the results are displayed in a datagrid control with all the matching OP_No for that patient. Or to put this in another way, if i run an sql that retrieves all records and theisr happen to be more than one, how are their displayed on the form? please sort me bro.
  

  Report abuse
• • Reply

  1 year ago

  by Judau_Ashta

  R. N. Infante

  Manila, Philippines

  Joined 3 years ago
  'Are You Using DAO and Datacontrol?
  'This code is for checking if OP_NO is already Exist..
  'First assign a  SeacrhString to Text1.text
  
  Text1.text = "Your OP_No"
  
  Private Sub Command1_Click()
      Data1.Recordset.MoveFirst
      Do While Not Data1.Recordset.EOF
          If Text1.Text = Data1.Recordset.Fields("OP_No").Value Then
              MsgBox "ALREADY EXIST!"
              Exit Sub
          End If
          Data1.Recordset.MoveNext
      Loop
  End Sub
  
  'Displaying Records in the Grid. I Use Listview as my grid because i have problem binding datagrid to datacontrol.
  'Add Microsoft Windows Common Controls 6.0 (SP6)  in your vb project and get a listview control.
  
  Private Sub cmdDisplayRecord_Click()
      'This initialized the Listview
      With ListView1
          .View = lvwReport
          .LabelEdit = lvwManual
          .HideSelection = False
          .FullRowSelect = True
          .GridLines = True
      End With
      ListView1.ListItems.Clear
  
      'Since  The Datacontrol is already Bind to the database, set it on the first record
      Data1.Recordset.MoveFirst
  
      'add column headers to the listview based on the datafield(s) of your table
      For i = 0 To Data1.Recordset.Fields.Count - 1
          ListView1.ColumnHeaders.Add , , Data1.Recordset.Fields(i).Name
      Next
  
      'Loop and find the OP_No
      Do While Not Data1.Recordset.EOF
          If Text1.Text = Data1.Recordset.Fields("OP_No").Value Then
          'fetch First Field
              ListView1.ListItems.Add , , Data1.Recordset.Fields(0).Value
          'Fetch Succeeding fields
              For i = 1 To Data1.Recordset.Fields.Count - 1
                  ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , Data1.Recordset.Fields(i).Value
              Next
          End If
          Data1.Recordset.MoveNext
      Loop
  End Sub
  
  'Next Time Use ADO for better Functions, it is more functional..
  'I hope This helps you.. Good Luck
  Report abuse