Hi All,
This should be easy for you.I'm just starting with recordsets in Access.

I need to move through a large recordset and take values from each record to store in some variables.
I then need to move to the next record and retrieve the respective values from that one and compare them to the variables stored previously in order to do some processing then I can update a particular field with some text.

I'm using a command button on a form at the moment to trigger the opening of the recordset etc. and got the ADODB connection and recordset open OK (simple test with some record counting code).

Without using text boxes on Forms etc. how do I get the values out of each record in the recordset to put them into the variables?

All the reference material I've got is quite basic and is based around simple databases using forms (it is also a bit old and is based on DAO). I'm only interested in preparing the dataset for some analysis.

Told you it would be an easy one!

Many thanx

MT

Hi,

   If you want to update a table, use an Update query. Then trigger the query with code.

   If this won't work, can you be more specific about what you want to do? It sounds like you want to put the data in an array.

   A better bet would be to create two recordsets and then compare the data between them, rather than open one, store some data, then open another and compare the second recordset against your array (BTW, do some research on arrays) although this works in some cases. I use it now and then.

   Anywho, you probably want an array and not a bunch of variables.

   If you could give us more info, I am sure we could have a solution (probably many solutions) worked out in no time.

   Info to include would be the data in the first table, the data you want to store in variables or an array, the data in the second table, what you want to compare from the first table, and then what you want to do with a match or not match etc.

HTH




mmcdonal

Hi Michael,
Basically what I want to do is move through a recordset, test date fields in each record with date fields in the previous record where the records belong to the same personthen depending on the date difference, update another field.

This is hospital admission data and I am checking for patients re-admitted within a certain time period. If they are I want to flag the record as a "Readmission", if not, leave it blank.

The process I'd envisaged was something like:

1. Sort query on PatientID, AdmissionDate

2. Read first record, put PatientID, AdmissionDate and DischargeDate into variables PatID1,ADate1 and DDate1

3. Move to next record and read, put PatientID, AdmissionDate and DischargeDate into variables PatID2,ADate2 and DDate2

4. Compare PatID2 with PatID1, if different then move to next record, if the same then compare the date difference between ADate2 and DDate1. If the difference is less than a particular number e.g. 8 days then update the field "Readmission" to "Yes". If the second admission is more than 8 days after the first discharge date then either leave "Readmission" blank or update to "No".

5. Reset PatID1, ADate1 and DDate1 with the values in PatID2, ADate2 and DDate2. Move to next record and loop from Step 3 to EOF.

I don't believe I can do the update part just from a query as the update depends on the comparison of dates from the previous record (if ther is a way I'd like to know!)

I would be interested to hear if there is a better strategy but the fundamental thing I need to know is:
Once I have moved to the first record - what is the syntax of the code that will read the PatientID, AdmissionDate and DischargeDate in order to put them into the variables? This was the easy bit I alluded to and I'm embarrassed to say I'm not sure how to do it when working with a recordset .

Any advice would be gratefully received.

Cheers

Morris

Are you going to be doing this search just on one Patient at a time? What I mean is, are you just going to be running this code when you are entering a single patient? Perhaps triggering the code on the After Update event of the Admission date control on an admission form?

If so, you can capture the variable of the PatID, and ADate from the form, like this: (I am assuming that you have a Patient Table named tblPatient, and a seperate Admission Table I'll call tblAdmission, with a PK of AdmissionID. I'll call your recordset "rs")

'-------- Code Starts ----------

Dim PatID As Integer
Dim AdmisID As Integer
Dim ADate1 As Date
Dim DDate2 As Date
Dim Diff As Integer


PatID = Me.PatientID
AdmisID = Me.AdmissionID
ADate1 = Me.AdmissionDate

'Connection to current database
'recordset pull on this Patient using this WHERE clause...

'"SELECT * FROM tblAdmission WHERE [PatientID] = " & PatID

'This will limit your recordset to only this current patient and will require less loops

'Then start to compare your current record against the pull...

rs.MoveFirst

Do Until rs.EOF
   If rs.Fields(0) <> AdmisID Then 'I am assuming your first field is the PK and you don't want to compare the record against itself...

      DDate2 = rs.Fields(???) 'what field number?
      Diff = DateDiff("d", ADate1, DDate2)

          If Diff > 7 Then
             Me.ReAdmissionCheckBox = "Yes"
          End If
    End If
    rs.MoveNext
Loop

'---------- Code Ends ----------

I haven't tried this code, but it should get you started.

The important issue is to limit your recordset to only the current patient. Then eliminate the current record, even though it shouldn't have a Discharge Date filled, but this will return a Null and stop the whole process.

HTH






mmcdonal

Hi Michael,
Nearly, but not quite! Although you've given me the answer I needed.

I must not have been explaining myself too well. I am not entering data, I have a large, single table with many patients, each with one or more admissions. For each patient I want to check to see if they had been readmitted within a time period - if so, update a field to show this.

What I didn't know what to ask for, you gave me by showing rs.Fields(). I didn't know how to make this bit work - that in fact it you have to reference the fields by an index number.
The basic module is now working OK and I can get the values into my variables. I'll be able to test if the rest of my programming ideas will work shortly (if not I'll be back )
Thanks for your efforts
Morris

Just for your info, Morris, you dont need to reference fields by index number. You can also say

rs.field("AdmissionDate")

or

rs!AdmissionDate

This really simplifies things tremendously. In fact, i never use index numbers unless i am iterating through a loop as in:

For i = 1 to rs.fields.count
  if IsNumeric(rs.fields(i)) then
    msgbox "we got a number"
  endif
Next i

One reason not to use index numbers when a name can be used is that these index numbers may change as columns are added or deleted or juxtaposed in a table. Another reason is, of course, clarity.

Sorry mmcdonal for putting in my 2 cents!

That's a very good reason not to use index numbers.

mmcdonal

WeyHEY! I'm away!
Thanks to you both. I've found I can enter a query name, field names and other values to text boxes on a form and transfer them into my module. The query name defines the recordset and the field names find the values I need no matter what order they're in in the query. I can now use my form to check readmissions on a bag load of data tables I have.
Cheers!
Morris

I can't for the life of me get iteration to work for index numbers. What was listed below

I've tried variations such as
But to no avail. Here is my attempt and I hang where I've commented.
This is using a DAO recordset.