Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006

    Unanswered: check for existing data in table before insertion issue

    Hi guys,

    Does anyone knw how to go about checking for existing data in an MS Acess table? I have tried out the following code using vb but doesnt seem to work that well? Can anyone ps take a look at this code and if possible point out the error or make correction?

    What i need is to check for existing studentId and name before insertion. If the inserted value existed in the table, message "duplicate" else insert.

    Alternatively, i also want to do it in the way by implmenting a combo box with the studentid from the student table, check for duplicate value against student table, if found insert failed else activate the textbox prompt user input for insertion but nt too sure as to hw to use boolean in vb environment to acheive this. Tks!

    Table design
    S/No - auto number
    StudentId - text pk
    Name - text

    Form design

    StudentId - unbound textbox
    Name - unbound textbox
    Button - Insert


    Dim StudentId As String
    Dim name As String
    Dim record As Variant
    Dim rs As Recordset
    Dim db As Database

    Set db = CurrentDb
    Set rs = Me.RecordsetClone
    Set rs = db.OpenRecordset("Student", dbOpenDynaset)
    If Not rs.EOF Then
    'record = DLookup("[StudentId", "Student, "[StudentId] = '" & Me.StudentId.Value & "'")
    If Not rs.NoMatch Then
    MsgBox "Duplicate!"
    rs("StudentId").Value = StudentId.Value
    rs("Name").Value = Name.Value
    End If

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if the student ID is unique, whynot make it an indexed column with duplicates not allowed

    save you hjaving to coe.. you "just" need to trap for the error the db will throw when trying to insert a duplciate row (Usually this error can be trappe d in the forms after udpate event
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2006
    Hi guys & community experts

    I am having with inserting my values into another table. What i want to achieve is to be able to select the values from my drop-down combo box from student table and insert the values of the unbounded textboxes to Registration table. May i knw can that be achieve using expression builder, if so hw can that be achieved?
    I Want to be able to shw the fields (course, subject) in the controlSource of the unbounded textboxes (course, subject) of the Stud form.

    Once again tks guys!


    SELECT Student.StudentId, Student.Name, Registration.course, Registration.subject
    FROM Student INNER JOIN Registration ON Student.StudentId = Registration.StudentId;

    Form RecordSource - Student

    Table design

    Student table

    SerialCode autonumber
    StudentId text pk
    Name text

    Registration table

    SerialCode autonumber pk
    StudentId text
    Name text
    course text
    subject text

    Form design

    Form Control-Source-Student
    StudentId-unbounded combo box (Student table)
    StudentId-bounded textbox (Student table)
    Name-bounded textbox (Student Table)
    course-unbounded textbox
    subject-unbounded textbox

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    you should be able to get there with the expression builder
    (forms/allforms/yourform/dbl-click the field etc).

    meanwhile the INSERT is easy enough in code.
    dim strSQL as string
    strSQL = "INSERT INTO Registration(Sutudentid, Name, Course, Subject) VALUES('" _  
       & me.studentid & "', '"  & & "', '" & me.course & "', '" & me.subject & "')"
    currentdb.execute strSQL
    you seem to have two controls for studentid - i assumed that one of them is named 'studentid' with the .value (text) that you want to insert.

    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2006
    Hi izyrider,

    tks for the prompt reply but it shw us #Error when i load the form. Do i have to set anything under the third column of expression builder?

  6. #6
    Join Date
    Dec 2002
    Préverenges, Switzerland
    what you are trying to do.

    are you trying to INSERT a new record?
    display the new record after insert?
    or ????????????????

    ultimately you need to do BOTH. something somewhere has to INSERT the new, something somewhere has to display the new - where are you stuck?

    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2006
    Hi community experts,

    First of all tks for your concerted effort in follow-up with the issues. Really appreciate.

    What i want to achieve is to insert the values to the Registration table. This is acheived from two tables (Student table, Registration table). The controlSource of the form is Registration table and the combo box is Student table.

    I want to insert the values of the combo box (StudentId), bounded textboxes (StudentId, name) from student and two unbounded textboxes (course, subject) into Registration table. But nw the problem is the bounded combo box (StudentId) and bounded textboxes (StudentId, name) is from the Student table and unbounded textboxes (subject, course) is from Registration table.

    Basically, it meant that i want to insert the values from two tables (Student, Registration) in a single form. Hopefully i have stated more clearly and nt misleading. May I knw how shdl it be done? Is it possible to achieve using expression builder?

    Student table

    Fields Data type
    SerialCode Autonumber
    StudentId Text (primary key)
    Name Text

    Registration table

    SerialCode (primary key) Autonumber
    StudentId Text
    course Text
    subject Text

    Design View

    Form controlSource - Registration table
    NB: Not too sure whether shld it be registration or student tables

    1 unbounded combo box - StudentId
    2 bounded textboxes - ControlSources (StudentId, Name)

    2 unbounded textboxes - (course, subject)


    Private Sub Save_Click()
    On Error GoTo Err_Command74_Click

    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim counter As Integer
    Dim records As Variant
    Dim fld As field
    Dim sql As String
    Dim sqlInsert As String

    sql = "Select * from [Student] where StudentId = '" & Me.StudentId.Value & "' and Name = '" & Me.Name.Value & "';"

    Set rs = Me.Recordset
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

    If Not rs.EOF Then
    sqlInsert = "INSERT INTO [Registration] " & _
    "([StudentId],[Name],[cousre],[subject]) " & _
    "VALUES ('" & Me.StudentId.Value & "','" & Me.Name.Value & "'," & Me.course.Value & ", '" & Me.subject.Value & "')"
    CurrentDb,Execute sql (error: synatx error in insert stat)


    whatever values chosen from the combo box, the corresponding field values from the table will be shown in the bounded textboxes and the unbounded textboxes is for user to enter the inputs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts