VB Forum / Database Access / May 2006

Hi,

I update one database table usign the following code. But I need to update 2
tables both of which are joined by the Study_ID field. Study_ID is the  
AUTO_INCREMENTING primary key in the database table which I am updating.

Now, the problem is that how do I get the value of the Study_ID field from
the database since this field is auto incremented when a record is inserted.
I need to get this value for putting it into the other table.

Below is my code: Please help me modify it so that it will get the value of
Study_ID and insert it into the other table.

Dim i, j As Integer
 Dim cn As ADODB.Connection
    Dim rsUpdate As ADODB.Recordset
   Dim strQuery As String
   Dim strStudyImageQuery As String
   Dim rsRecCount As Integer  ' For counting records in a record set
   Dim intStudyId As Integer
   
  Set cn = New ADODB.Connection
  cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=UID;Password=PWD ;Initial Catalog=DB;Data Source=IP"
   cn.Open
     
   Set rsUpdate = New ADODB.Recordset
     
  strQuery = "select patient_idnum, patient_id,
study_date,study_type,study_imagect from studies"

   rsUpdate.CursorType = adOpenDynamic
  rsUpdate.Open strQuery, cn, adOpenDynamic, adLockOptimistic
 
 For i = 1 To UBound(strCols)
    MsgBox "strCols(" & i & ") = " & strCols(i)
 Next
  MsgBox " DATe in update" & strStudyDate
   rsUpdate.AddNew
  i = 0
 
  rsUpdate.Fields("patient_id") = strCols(1)
      rsUpdate.Fields("patient_idnum") = strCols(2)
    rsUpdate.Fields("study_date") = strStudyDate
     rsUpdate.Fields("study_type") = strStudy
    rsUpdate.Fields("study_imagect") = intCountFiles(m)
   
   rsUpdate.Update

strStudyImageQuery = "Select" & intStudyId & "= study_id from inserted i  "
--- I WAS TRYING TO USE THIS LINE BUT IT RETURNS intStudyId =0 which is wrong.

MsgBox "STUDY ID IS " & intStudyId

'Close all Objects
  rsUpdate.Close
  Set rsUpdate = Nothing
     cn.Close
     Set cn = Nothing

Thanks for any help