Select Query problem.

[MadCatmk2]

Hi

I'm not sure if i am going about this in the correct way but hopefully someone can shed some light on the matter.

I have a SELECT query in the program that i am writing which returns a subset of data from an several tables in an Access database. The query is displayed in a true data grid and is access using the ADODB recordset. As well as having the fields from the database i would like to have one more blank column in the grid called "DEPENDENCY". I have added the name "DEPENDENCY" to the SELECT part of the query and this seems to display a column by that name which is blank.

The problem i am having is when i try to assign a value to this field in the recordset. I get the error "Item cannnot be found in the collection corresponding to the requested name or ordinal" when trying to assign a value to the field.

This is the sql statement that i'm getting the problems with:

sql = "SELECT PATIENT_REFERRAL.PATIENT_REFERRAL_KEY, PATIENT_REFERRAL.DATE_OF_REFERRAL, PATIENT_REFERRAL.CASELOAD_HOLDER, CONTACTS.DATE_OF_VISIT, CONTACTS.TIME_OF_VISIT, CONTACTS.DURATION, DEPENDENCY FROM ((PATIENT_REFERRAL INNER JOIN CARE_PACKAGES ON PATIENT_REFERRAL.PATIENT_REFERRAL_KEY = CARE_PACKAGES.PATIENT_REFERRAL_KEY) INNER JOIN CARE_AIMS ON CARE_PACKAGES.CARE_PACKAGE_KEY = CARE_AIMS.CARE_PACKAGE_KEY) INNER JOIN CONTACTS ON CARE_AIMS.KEY = CONTACTS.CARE_AIM_KEY WHERE (((PATIENT_REFERRAL.DATE_OF_REFERRAL) Between #" & Format(StartDate, "MM/dd/yyyy") & "# And #" & Format(EndDate, "MM/dd/yyyy") & "#) AND ((PATIENT_REFERRAL.CASELOAD_HOLDER)=10718)) ORDER BY PATIENT_REFERRAL.PATIENT_REFERRAL_KEY, CONTACTS.DATE_OF_VISIT"

This is the code that i use to update the field. Listdependency2 is a list box that has entries for each of the rows in the recordset.

Dim counter As Integer
counter = 0
rs.MoveFirst

  Do While rs.EOF = False
  rs.Fields("DEPENDENCY") = listDependency2.List(counter)
  rs.Update
  counter = counter + 1
  rs.MoveNext
  Loop

Hopefully someone can see where i am going wrong. Any help would be most appreciated.

Thanks in advance.

 

[MadCatmk2]

Just found out that i cannot add updateable fields to a recordset created from a query. That will be why i'm having no joy.

 

[sdraper]

In ADO you shold be able to add the field to the recordset object.

 Dim rsado As New ADODB.Recordset
    With rsado
            .Fields.Append "DEPENDANCY", adChar
            .Fields.Update
    End With

Sam