Multiple Recordsets in one function

[davman2002]

Ok I have been charged with producing a report that will be emailed to the Physicians at my company. The way they want the report to appear is as follows.

Doctor Results
Dr1 1.0
Dr2 2.0
Dr3 3.0
Real Dr Name 4.0
Dr5 5.0
Dr6 6.0

They want the physician to be able to see at which place their results fall but they do not want that physician to be able to see the names of the other physicians wither above or below their name. I have created code that I thought should work unfortuniately it does not. I keep getting one error on the line that states
Set rst2 = dbs.OpenRecordset(strSQL2)
Can someone help Here is my code.

Function test()
Dim dbs As DATABASE
Dim rst As Recordset
Dim rst2 As Recordset
Dim strSQL2 As String
Dim strSQL As String
Dim Current_DEA As String
Dim New_Dr_Name As String
Dim Table_Name As String
Dim counter As Integer

' Return reference to current database.
Set dbs = CurrentDb

' Select record to be updated
strSQL = "SELECT DEA_NUMBER FROM Formulary ;"

Set rst = dbs.OpenRecordset(strSQL)

counter = 0

While Not rst.EOF
Current_DEA = rst("DEA_NUMBER&quot
counter = counter + 1
New_Dr_Name = "Dr" & counter
DoCmd.DeleteObject acTable, "TEMP_TABLE"
DoCmd.OpenQuery "500 Create Temp_Table"

strSQL2 = "UPDATE TEMP_TABLE " & _
"SET TEMP_TABLE.Physician = '" & New_Dr_Name & "'" & _
&quot;WHERE (((TEMP_TABLE.DEA_NUMBER)<> '&quot; & Current_DEA & &quot;'));&quot;

DoCmd.RunSQL (strSQL2)
Set rst2 = dbs.OpenRecordset(strSQL2)
rst.MoveNext
Wend

rst.Close
Set dbs = Nothing

 

[dpimental]

Do you have an id, that identifies the doctor, if so just include the id in your sql query, and do a conditional on the id.

In fact, when the doctor enters the information, you can have his id passed to the query, and then you can do a conditional on the recordset, that if id = &quot;1234&quot; Then
New_Dr_Name = &quot;Fred D. Rogers&quot;.

Perhaps a better method would be to get the results of the record set (including the doctor's id), and pass it into an array.

Then loop through the array.
The first doctor is Dr. Johnson, and he is Dr1
you loop through the array and replace DR1 with Dr. johnson, email the report, and go to the next doctor.

you could also produce 2 recordsets, the first to get a list of all the results of all the doctors, and then copy that into the 2nd recordset.

Use the second recordset instead of the array, to use for your process.

Let me know what you think?

David

 

[davman2002]

Currently I do have a unique ID for each dr. it is stored in the &quot;Current_DEA&quot; variable. The problem that I currently have is that the initial report has the original dr's name already listed in the file. I need to replace the other dr names on the report for each physician that is not the dr recieveing the report. I tried to create two recordsets and the computer doesn't like that for some reason.

 

[cmmrfrds]

This probably defaults to a read only recordset.
Set rst2 = dbs.OpenRecordset(strSQL2)

Change to a dynaset - check spelling
Set rst2 = dbs.OpenRecordset(strSQL2, dbOpenDynaset)

 

[davman2002]

cmmrfrds, I made the adjustment to include dbOpenDynaset. Spelling seems to be correct. I still get a Run-time error '3219' Invalid operation on this line of code. any other suggestions

 

[cmmrfrds]

Why are you doing this
DoCmd.RunSQL (strSQL2)
and the openrecordset
both would be updating the table.

Look at the sql string before running it. A null or empty variable would cause an invalid string.

Debug.Print strSQL2

 

[davman2002]

I am sorry about my original post I have since commented this line out. I tried Docmd.RunSQL to verify the SQL statement and it works however it updates all the records in the table at once to the same value. I need to update each field seperately and I thought that in order to update the records one entry at a time I would need to create a recordset that I could use rst.Movenext to scroll thru the values.

 

[cmmrfrds]

I reread what you are trying to do and you should just be able to send the sql statement to update the table and don't do an openrecordset use the execute instead.

This has to be outside the loop.
DoCmd.DeleteObject acTable, &quot;TEMP_TABLE&quot;
DoCmd.OpenQuery &quot;500 Create Temp_Table&quot;

Does the query &quot;500 create temp_table copy the records from another table with the field names you are using in the update statement? Does the temp table have records with matching keys to update. The where criteria for the update should be matching on 1 unique key - a <> is not typical in an update query.

While Not rst.EOF
Current_DEA = rst(&quot;DEA_NUMBER&quot
counter = counter + 1
New_Dr_Name = &quot;Dr&quot; & counter

strSQL2 = &quot;UPDATE TEMP_TABLE &quot; & _
&quot;SET TEMP_TABLE.Physician = '&quot; & New_Dr_Name & &quot;'&quot; & _
&quot;WHERE (((TEMP_TABLE.DEA_NUMBER)<> '&quot; & Current_DEA & &quot;'));&quot;

dbs.Execute (strSQL2)
rst.MoveNext
Wend

 

[davman2002]

Cmmrfrds,
Thanks for your help. I was able to get the function to work by doing the following.

Function test()
Dim dbs As DATABASE
Dim rst As Recordset
Dim rst2 As Recordset
Dim strSQL As String
Dim Current_DEA As String
Dim New_Dr_Name As String
Dim Table_Name As String
Dim counter As Integer

' Return reference to current database.
Set dbs = CurrentDb

strSQL = &quot;SELECT DEA_NUMBER FROM Formulary ;&quot;

Set rst = dbs.OpenRecordset(strSQL)

While Not rst.EOF
counter = 0
DoCmd.DeleteObject acTable, &quot;TEMP_TABLE&quot;
DoCmd.OpenQuery &quot;500 Create TEMP_TABLE&quot;
Set rst2 = dbs.OpenRecordset(&quot;TEMP_TABLE&quot
While Not rst2.EOF
Current_DEA = rst(&quot;DEA_NUMBER&quot
counter = counter + 1
With rst2
.Edit
!Physician = &quot;Dr&quot; & counter

If !DEA_NUMBER <> Current_DEA Then
.Update
Else
.CancelUpdate
End If
End With
rst2.MoveNext
Wend
rst2.Close
rst.MoveNext
Wend
rst.Close
Set dbs = Nothing
End Function