check for existing data in table issue

[technocraze]

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

Code


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
rs.MoveFirst
'record = DLookup("[StudentId", "Student, "[StudentId] = '" & Me.StudentId.Value & "'")
If Not rs.NoMatch Then
MsgBox "Duplicate!"
Else
rs.MoveLast
rs.AddNew
rs("StudentId").Value = StudentId.Value
rs("Name").Value = Name.Value
rs.Update
End If
rs.Close

[OBP]

Assuming that Student is the name of your Table that you wish to check there are 2 ways to do this, 1. with a recordset or 2. with a query.
The recordset would be something like this -
Dim rs As Recordset, recount As Integer, count as integer


Set rs = CurrentDb.OpenRecordset("Student")
recount = rs.RecordCount
For count = 1 to recount
if rs.StudentID = me.StudentID then
msgbox "Duplicate"
exit sub
end if
next count
rs.Close
Set rs = Nothing
Msgbox "No Duplicate"


To use a query just set the Query's StudentID Column's Criteria Row to
[forms]![Form Name]![StudentID]

where Form Name is the name of your form.
Run the query in the VBA and check it's record count.

[technocraze]

Hi OBP,

tks for the prompt reply! as stated i want to insert the non-existing data into my table, which part of the code segments where the insert will play the role?
The two criteria for insertion is studentid and name.

[technocraze]

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!

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

[OBP]

technocraze, did you get my code to work?
If you did then to transfer your form's data in to a new record in the Student table you just use the add new record function, between the Next count and rs.Close put

rs.addnew
and then

rs.studentID = me.studentID
etc

In the VBA Editor's Help look up "AddNew" for an Example.

[technocraze]

Hi OBP,

That code doesnt seem to work well even non-existing data also deem as duplicate.

[OBP]

Can you post a zipped copy of the database, I have just used that code in another database and it works OK.

[OBP]

Is your form bound to the Student file via the StudentID?

[OBP]

technocraze, which table do you want to check if the Student is already a record?
Is it the Student table or the Registration table?

[technocraze]

Hi OBP,

first of all tks for your prompt reply and follow-up. Really appreciate your concerted effort.

The duplicate entires issues have already been resolved.

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 table in a sigle form. Hopefully i have stated more clearly and nt misleading. May i knw hw can that be achieved. (expression builder, recordsource).

[technocraze]

Hi OBP,

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

[OBP]

technocraze, thank you I understsand your requirements now.
I am not sure why you want to do this with VBA and SQL though.
The way that I would do this is as follows.
The registration Table has 5 fields RegistrationID - Autonumber, StudentID, CourseId and SubjectID and one more field which contains a Student/Course, which is the CourseID added to the StudentID and is Indexed as "No Duplicates".
In this way only IDs are stored in the Registration Table and the special "No Duplicates" field prevents you booking the same student on the same course.
Another table or 2 Tables to hold the Course and Subject data so that it can be provided to the registration form as 1 or more Combo boxes.
The Form Registration is Bound to the Registration Table and should be opened as a new record (Data Entry Mode).
The Fields StudentID, SubjectID and CourseID are Bound to the Registration Table.
The Combo Box Student Name and StudentID has Data provided by a Query of the Student Table.
A Combo Box for Course and Subject has data provided by a Query from a Table or Tables holding the Course and Subject data.

Now the user only has to select a Student, a Course and a Subject or Course/Subject from the combos which are bound to the From's 3 fields is automatically entered in to the Registration Table.


If you still wish to continue down the Visual Basic Route then the code that I gave you was checking the wrong Table, the Recordset (r)s should be using the Registration Table as in

Set rs = CurrentDb.OpenRecordset("Registration")

[OBP]

technocraze, please see the attached zipped database for an example of using Combos to add records to a registration table.

[technocraze]

tks