ODBC - Insert on linked table [tableName] failed issue

[technocraze]

Hi guys,

I am having trouble resolving this error. Below mentioned is my code and implmentation.

MS Acess is my front end and sql server is my backend.

What i want to achieve is to be able to insert StudentId in the table matching the values chosen in the combo boxes and listboxes. This sound more like searching the table to insert StudentId matching the criterias.

I have set the necessary configuration for ODBC connection (File DSN & System DSN>Add>Sql Server>) and linked the table from MS Acess (Get external source>ODBC Database>DataSo urce>System>Tab le[Student]) but somehow or rather it jux failed to work.

Is there a possibilty whereby i can combine RecordSet property (rs.AddNew) with sql stat (select stat) The prog just hang at rs.update when i step till it and resulted to [ODBC -insert on linked table [tableName] failed. Do I need to set any connection property for ODBC to sql server? If yes, hw can this be set.

Really at a loss as to hw this can be resolve!

Any solutions, suggestions, pointers, code snippets, correction of codes is very much appreciated.

Control
StudentId - TextBox(Userinp ut)
Combo box1 (predefined values) course
Combo box 2 (populated results from Combo box 1) subject - combo change event
ListBox (populated result from Combo box 2) class - combo change event

Private_Sub SaveCommand
On_Error ....

Dim rs as DAO.RecordSet
Dim db as DAO.Database

Set rs = Me.RecordSetClo ne
Set db = CurrentDb
Set rs = db.OpenRecordSe t("Select StudentId, course, subject, class from Student where studentId = '" & Me.StudentId.va lue & "' And course = '" & Me.Combo1.value & "' And subject = '" & Me.Combo2.value & "'" )
If not rs.EOF then
rs.AddNew
rs("StudentId" ) = Me.text1.value
rs.Update
rs.close

End If

set rs= Nothing
set db =Nothing

Private Sub_AfterUpdate ()

Dim rs as Object

Set rs = Me.RecordSet.Cl one
rs.FindFirst "[Serialno] = " Str(Nz(Me![List1]))
If not rs.EOF then Me.Bookmark = rs.Bookmark

[nico5038]

I would have expected:

If rs.EOF and rs.BOF then
' no record found for WHERE criteria
rs.AddNew
rs("StudentId" ) = Me.text1.value
' All unique fields from the WHERE should be filled here so add also
rs("Course") = Me.Combo1.value
rs("Subject") = ....
'etc.
rs.Update
rs.close

End If

Nic;o)