By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,948 Members | 1,553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,948 IT Pros & Developers. It's quick & easy.

check for existing data in table issue before insertion

P: 46
Hi guys & commnunity experts,

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 / logic 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. I have provided the relevant information for more understanding. Tks!

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

Form design
StudentId - unbound textbox
Name - unbound textbox
Button - Insert

Code

Expand|Select|Wrap|Line Numbers
  1. Dim StudentId As String
  2. Dim name As String
  3. Dim record As Variant
  4. Dim rs As Recordset
  5. Dim db As Database
  6.  
  7. Set db = CurrentDb
  8. Set rs = Me.RecordsetClone
  9. Set rs = db.OpenRecordset("Student", dbOpenDynaset)
  10. If Not rs.EOF Then
  11.     rs.MoveFirst
  12.     'record = DLookup("[StudentId", "Student, "[StudentId] = '" & Me.StudentId.Value & "'")
  13.     If Not rs.NoMatch Then
  14.         MsgBox "Duplicate!"
  15.     Else
  16.         rs.MoveLast
  17.         rs.AddNew
  18.         rs("StudentId").Value = StudentId.Value
  19.         rs("Name").Value = Name.Value
  20.         rs.Update
  21. End If
  22. rs.Close
Jan 27 '07 #1
Share this Question
Share on Google+
20 Replies


NeoPa
Expert Mod 15k+
P: 31,758
Firstly, let me say that this question is so much better formulated than some that I'm happy to help if I can.
The easiest way to check for a record is to use the DLookup() function but using recordsets is a workable way too. To simplify I'll just show code using DLookup(). I'm assuming the name of the table is tblStudent.
This code is usable by copying/pasting as it is but I've broken the lines for display in here which is not entirely necessary in your proper code module.
Expand|Select|Wrap|Line Numbers
  1. If IsNull(DLookup("[StudentID]", _
  2.                   "[tblStudent]", _
  3.                   "[StudentId]='" & Me!StudentId & "'")) Then
  4.     MsgBox "Duplicate!"
  5. Else
  6.     DoCmd.RunSQL "INSERT INTO [tblStudent] " & _
  7.                  "([StudentId],[Name]) " & _
  8.                  "VALUES ('" & Me!StudentId & "','" & Me!Name & "')"
  9.     Call Me.ReQuery()
  10. End If
Jan 27 '07 #2

ADezii
Expert 5K+
P: 8,692
Hi guys & commnunity experts,

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 / logic 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. I have provided the relevant information for more understanding. Tks!

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

Form design
StudentId - unbound textbox
Name - unbound textbox
Button - Insert

Code

Expand|Select|Wrap|Line Numbers
  1. Dim StudentId As String
  2. Dim name As String
  3. Dim record As Variant
  4. Dim rs As Recordset
  5. Dim db As Database
  6.  
  7. Set db = CurrentDb
  8. Set rs = Me.RecordsetClone
  9. Set rs = db.OpenRecordset("Student", dbOpenDynaset)
  10. If Not rs.EOF Then
  11.     rs.MoveFirst
  12.     'record = DLookup("[StudentId", "Student, "[StudentId] = '" & Me.StudentId.Value & "'")
  13.     If Not rs.NoMatch Then
  14.         MsgBox "Duplicate!"
  15.     Else
  16.         rs.MoveLast
  17.         rs.AddNew
  18.         rs("StudentId").Value = StudentId.Value
  19.         rs("Name").Value = Name.Value
  20.         rs.Update
  21. End If
  22. rs.Close
Expand|Select|Wrap|Line Numbers
  1. If you would like to use a 'Hybrid' of DLookup() and Recordsets:
  2. If IsNull(Me![StudentID])  Then Exit Sub
  3.  
  4. If IsNull(DLookup("[StudentID]", "Student", "[StudentID]='" & Me![StudentID] & "'")) Then
  5.   'No Record for this Student ID exists in the Table so ADD it to the Table
  6.   Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  7.   Set MyDB = CurrentDb()
  8.   Set MyRS = MyDB.OpenRecordset("Student", dbOpenDynaset)
  9.     With MyRS
  10.       .AddNew
  11.            ![StudentID] = Me![StudentID]
  12.            ![Name] = Me![Name]
  13.       .Update
  14.     End With
  15.   MyRS.Close
  16. End If
Jan 28 '07 #3

P: 46
tks guys for the prompt reply
Jan 28 '07 #4

P: 46
Hi NeoPa, Adzeil & 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!

Query

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
Stud

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
Jan 28 '07 #5

NeoPa
Expert Mod 15k+
P: 31,758
I know you're making an effort to give as much information as possible, but with all your strange abbreviations and missed out words I'm afraid I can only understand this if I devote large amounts of time trying to translate it.
As I said, I know you're making the effort (If I weren't convinced you were I'd just ignore your posts) so I don't want to sound too critical, but I don't know from this :
  • Whether or not you tried out my earlier posted code (post #2).
  • If so, whether or not it worked for you.
  • If not, what went wrong.
  • Same for ADezii's post.
It's not fair to expect the site experts to start from scratch every time you get confused (Trust me - you'll soon find that no-one can be bothered answering your posts). You need to communicate where you've got to with the help you've been given already - explain if you're confused by anything but certainly try out the solution first.
Remember, it's not our responsibility to make sense of your posts, but yours to make them understandable.

If you've clearly made an effort to do this (I noticed your effort earlier) then we'll make allowances, but abbreviations and missed out words are easy to spot and avoid if you read your post before submitting it, which every poster is expected to do.
Jan 28 '07 #6

ADezii
Expert 5K+
P: 8,692
Hi NeoPa, Adzeil & 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!

Query

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
Stud

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
I am just as confused as NeoPa but I also agree with him that you question deserves some attention given your true effort. Please tell me if the following assumptions are correct, and I'll attempt to find a solution to your problem:
_01 You have an Unbound Combo Box containing only StudentIDs and their Names from the Student Table (you will need the Name for possible Append to Registration).
_02 Once a User has selected an ID from the Combo Box, you want to check and see if a corresponding Record exists in the Registration Table.
_03 If a Record does exist, take no further action.
_04 If a matching Record does not exist in the Registration Table, create a New Record by capturing StudentID from the Unbound Combo Box, the Name from the Unbound Column in the Combo Box, and the Course and Subject Fields from the Unbound Text Boxes. The SerialCode in the Registration will self generate when this Data is appended because it is an AutoNumber Field.

Is all this what you are trying to achieve? NeoPa, are you still with me on this one? (LOL)
Jan 29 '07 #7

NeoPa
Expert Mod 15k+
P: 31,758
I'm with you ADezii.
Glad to hear you're still hanging in there.
It's nice to see that writing in clear concise English is not too much to expect. You managed it without any undue stress.
If you get a response to this I will be looking to find an answer for the OP too :)
Jan 29 '07 #8

P: 46
Hi NeoPa and Adzeil,

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 (primary key) Text
Name Text

Registration table

Fields
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)

Code

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
'rs.MoveLast
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)

Conclusion
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.
Jan 29 '07 #9

ADezii
Expert 5K+
P: 8,692
Hi NeoPa and Adzeil,

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 (primary key) Text
Name Text

Registration table

Fields
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)

Code

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
'rs.MoveLast
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)

Conclusion
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.
I think I now understand. Give me a little time, I'll re-create your scenario, and hopefully come up with the appropriate answer.
Jan 29 '07 #10

NeoPa
Expert Mod 15k+
P: 31,758
I'll leave ADezii (not Adzeil) to answer this for the moment - as he's starting to understand what you're saying. Snowed under atm but still have the thread registered and will keep an eye on it.
Jan 30 '07 #11

ADezii
Expert 5K+
P: 8,692
Hi guys & commnunity experts,

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 / logic 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. I have provided the relevant information for more understanding. Tks!

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

Form design
StudentId - unbound textbox
Name - unbound textbox
Button - Insert

Code

Expand|Select|Wrap|Line Numbers
  1. Dim StudentId As String
  2. Dim name As String
  3. Dim record As Variant
  4. Dim rs As Recordset
  5. Dim db As Database
  6.  
  7. Set db = CurrentDb
  8. Set rs = Me.RecordsetClone
  9. Set rs = db.OpenRecordset("Student", dbOpenDynaset)
  10. If Not rs.EOF Then
  11.     rs.MoveFirst
  12.     'record = DLookup("[StudentId", "Student, "[StudentId] = '" & Me.StudentId.Value & "'")
  13.     If Not rs.NoMatch Then
  14.         MsgBox "Duplicate!"
  15.     Else
  16.         rs.MoveLast
  17.         rs.AddNew
  18.         rs("StudentId").Value = StudentId.Value
  19.         rs("Name").Value = Name.Value
  20.         rs.Update
  21. End If
  22. rs.Close
I think that we've finally arrived at an answer. The code has intentionality been centralized in the AfterUpdate() Event of the Combo Box for Debugging and Viewing purposes. It has been thoroughly tested and works fine. It is up to you to adapt it to your specific needs.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboStudentID_AfterUpdate()
  2. On Error GoTo Err_cboStudentID_AfterUpdate
  3.  
  4. Dim Msg As String
  5.  
  6. 'Show the Record for the selected [StudentID] from cboStudentID
  7. Me.RecordSource = "SELECT * FROM Student WHERE [StudentID]='" & Me![cboStudentID] & "'" _
  8.                   & "ORDER BY [StudentID];"
  9.  
  10. 'See if this Record exists in the Registration Table by searching for the Primary Key
  11. 'given the StudentID. If DLookup() returns NULL then it doesn't exist
  12. If IsNull(DLookup("[SerialCode]", "Registration", "[StudentID]='" & Me![cboStudentID] & "'")) Then
  13.   Msg = Me![cboStudentID] & " does not exist in the Registration Table. Do you wish to add "
  14.   Msg = Msg & "this student to the Table?"
  15.     Response = MsgBox(Msg, vbYesNo + vbQuestion + vbDefaultButton1, "Add Student")
  16.       If Response = vbNo Then
  17.         Exit Sub
  18.       Else
  19.         Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  20.         Set MyDB = CurrentDb()
  21.         Set MyRS = MyDB.OpenRecordset("Registration", dbOpenDynaset)
  22.           With MyRS
  23.             .AddNew
  24.                ![StudentID] = Me![StudentID]    'Bound Field
  25.                ![Name] = Me![Name]              'Bound Field
  26.                ![Course] = Me![txtCourse]       'Unbound Field
  27.                ![Subject] = Me![txtSubject]     'Unbound Field
  28.             .Update
  29.           End With
  30.           MyRS.Close
  31.             MsgBox "Student ID# " & Me![cboStudentID] & " has been added to the Registration Table", vbInformation, "Student ID Added"
  32.       End If
  33. End If
  34.  
  35. Exit_cboStudentID_AfterUpdate:
  36.   Exit Sub
  37.  
  38. Err_cboStudentID_AfterUpdate:
  39.   MsgBox Err.Description, vbExclamation, "Error in cboStudentID_AfterUpdate()"
  40.   Resume Exit_cboStudentID_AfterUpdate
  41. End Sub
Jan 30 '07 #12

NeoPa
Expert Mod 15k+
P: 31,758
Nice work ADezii.
I hope it didn't take up too much of your time. It looks as if it might have done though...
Jan 30 '07 #13

ADezii
Expert 5K+
P: 8,692
Nice work ADezii.
I hope it didn't take up too much of your time. It looks as if it might have done though...
Thanks for the kudos. Since technocraze put that much effort in trying to explain his problem, I could at least match it with comparable time.
Jan 30 '07 #14

P: 46
Hi NeoPa and Adezii,

Once again tks for the concerted effort made in following up with my issues. It is very heartening to learn that u guys have done a great deal of help ever since I leverage on MS Acess in vb environment. Thanks NeoPa for the critique made.
Jan 30 '07 #15

P: 46
Hi NeoPa, AdZeii & community experts,

Another issue I am encountering is the error meesage: Type mismatch. This occured at the Combo_After Update event.

To make it simple i am trying to display the corresponding values (StudentId, Name) into the textboxes from registration table. The values from the combo box is from a query. (Select distinct StudentId from Registration order by StudentId).

It mean that when I select the value from the combo box, the corresponding values matching that studentid will be displayed and i only want the studentid and name to be displayed in the bounded textboxes.

I will provide detailed information with explicit explanation so that the experts can look into the issues without much difficiluty and also prevent any misleading of information.

Design View

StudentId - unbounded combo box
Student - bounded textbox
Name - bounded textbox
subject - unbounded textbox
course - unbounded textbox

Table view

Registration
Serialcode - primary key (Autonumber)
StudentId - text
Name - text
course - text
subject -text

Query (sql view)
Select distinct StudentId from Registration


Code

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo1 AfterUpdate()
  2. Dim rs as DAO.Recordset
  3. set rs = Me.RecordSetClone
  4. rs.FindFirst "[Serialcode] = " Str(Nz(Me![Combo1])) - Error
  5. If not rs.EOF then Me.Bookmaek = rs.Bookmark
Message error: Type mismatch
Jan 30 '07 #16

NeoPa
Expert Mod 15k+
P: 31,758
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo1 AfterUpdate()
  2. Dim rs as DAO.Recordset
  3. set rs = Me.RecordSetClone
  4. rs.FindFirst "[Serialcode] = " Str(Nz(Me![Combo1])) - Error
  5. If not rs.EOF then Me.Bookmaek = rs.Bookmark
Try :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo1_AfterUpdate()
  2. Dim rs as DAO.Recordset
  3. set rs = Me.RecordSetClone
  4. rs.FindFirst "[Serialcode] = '" &  Str(Nz(Me![Combo1])) & "'"
  5. If not rs.EOF then Me.Bookmark = rs.Bookmark
Tip: Always compile your code before testing it. This will find some of the more obvious errors for you (like misspelled words or missing characters etc). You had not included the quotes in your fourth line.

NB. Never (I repeat never) post code in here that you type in directly. It MUST be copied from your actual code window. This code (?) has not been anywhere near a code window.
Jan 30 '07 #17

P: 46
Correction

Private Sub Combo1_AfterUpdate()

' Find the record that matches the control.
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "[Serialcode = '" & Str(Nz(Me![Combo1],0)) - Error
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Error: type mismatch

I have tried with the single quotes but the error still persists
Jan 30 '07 #18

NeoPa
Expert Mod 15k+
P: 31,758
When I talk about Copying & Pasting do you understand what I mean?
Jan 30 '07 #19

P: 46
When I talk about Copying & Pasting do you understand what I mean?
Yes i do, but i have added some comments as an indication such as error
Jan 30 '07 #20

NeoPa
Expert Mod 15k+
P: 31,758
But you're not using the code I posted. If you copy/paste in the answer then you can test it properly. To put it another way, if you don't copy/paste it then we have to rely on your typing it accurately into your database which takes attention to detail. You have not shown enough attention to detail in this case as it's not the same.
It's much easier and more reliable to use copy/paste.
Jan 30 '07 #21

Post your reply

Sign in to post your reply or Sign up for a free account.