Run-time Error 3061 - Recordset

[Megagnome]

Got myself in real need of help here, and wonder whether anyone would be able to assist?

Problem:-
In a wildlife hospital we want to automatically select the ‘Previous Accession Number’ under which an animal known by its ‘First Accession Number’ (or ‘PatientID’) was last treated by CWH before the current consultation instance.

Each instance of an animal entering the wildlife hospital is automatically given an auto-numbered AccessionID (or ‘Consultation number’). If it survives and is discharged into the care of a Carer (with a ‘Discharge Advice’), the Carer may later need to bring it back to the hospital any time (and maybe multiple times) for the vet to re-check its progress. When this happens the Carer is asked for its ‘First Accession Number’ (or ‘PatientID’) from the previous ‘Discharge Advice’ so that we can accurately identify the animal and gain access to its latest veterinary notes, etc.. The AnimalGroup of the animal is selected as ‘ReCheck’ which then directs the flow of the re-admission process, which now has already been given a new AccessionID (or ‘Consultation number’) to the Re-Check Admission screen (‘FRM_AdmnReCheck’).

We assume (from experience) that the Carer will not always be able to quote its previous AccessionID (or ‘Consultation number’), but will know (or be able to be prompted to) its ‘First Accession Number’ (or ‘PatientID’).
We want to enter its ‘First Accession Number’ (or ‘PatientID’) in field ‘FirstAccNo’, and having done so, for the program to automatically look up the accession number when it was last admitted.

The program uses linked CWHDB (in the central server) and CWHClient (on the user machines) files.

My Solution So Far:-
Use a recordset of a query pre-defined in the CWHClient file to list all the consultation numbers (AccessionIDs) where the ‘FirstAccNo’ of the animal is the number entered into the ‘FirstAccNo’ field of the Re-Check Admission screen, and then to use the MoveLast, FindPrevious method to identify the previous accession number . This query is ‘QRY_PrevAccNo’ (“

SELECT TBL_Accession.AccessionID, TBL_Accession.FirstAccNo FROM TBL_Accession WHERE (((TBL_Accession.FirstAccNo)=[Forms]![FRM_AdmnReCheck].[FirstAccNo].[Value])) ORDER BY TBL_Accession.AccessionID;”

). The vba I have is:-

Private Sub FirstAccNo_AfterUpdate()
1    Dim dbs As DAO.Database
2    Dim rst As DAO.Recordset
3    Dim pan As Long
 
4    Set dbs = CurrentDb
5    Set rst = dbs.OpenRecordset("QRY_PrevAccNo", dbOpenDynaset)
6    With rst
7        If Not (.BOF And .EOF) Then
8            .MoveLast
9            .FindPrevious ("AccessionID")
10            If Not .NoMatch Then
11                pan = rst!AccessionID.Value
12           Else
13                GoTo ExitSub
14            End If
15        Else
16            MsgBox ("No records!")
17        End If
18    End With
19    Forms!FRM_AdmnReCheck.PrevAccNo.Value = pan
20    Set dbs = Nothing
21    Set rst = Nothing
ExitSub:
End Sub

However, this trips out at Run-time on line 5, with Run-time error 3061 (“Too few parameters. Expected: 1”)

A typical result of running the query for value 21715 in field Forms!FRM_AdmnReCheck.FirstAccNo is:-
QRY_PrevAccNo
AccessionID FirstAccNo
21715 21715
21765 21715
21864 21715
21926 21715
22016 21715
22340 21715
22358 21715
22359 21715
Where 22358 would be the result value needed.

I have spent many hours researching, but so far have failed to find a solution. Can’t think of any other way around this! Any ideas would be very welcome indeed!

[Rabbit]

Please use code tags when posting code.

You really should have another table to track unique animals instead of having to look for the first instance the animal came into the hospital. It would normalize your data and make everything a lot easier to maintain.

As far as what you're trying to do, you can use a max aggregate query to get what you're looking for. No need for any code.

[Megagnome]

Thanks VERY much, 'Rabbit'! What would my 'max aggregate query look like? Could you give me a clue as to the SQL? That's a term that is new to me!

[Megagnome]

Rabbit, the problem that I see with what I think you suggest is that that would give me the highest (max) AccessionID. I don't want that value. It's the PREVIOUS value we want, that is, 22358, NOT 22359 which would be the max, and yes, I agree that if I wanted the max I'd have no need for the code.

[Rabbit]

Just have it ignore the most recent one.

select max(fieldName)
from tableName
where fieldName <> currentValue
   and groupField = groupVale

[Megagnome]

So,

SELECT max(AccessionID) FROM TBL_Accession
WHERE AccessionID <> Me.AccessionID
AND FirstAccNo = Me.FirstAccNo
 

then place this as Record Source for field PrevAccNo? Certainly much simpler, but please confirm!

[Rabbit]

You'll probably need to populate it through code. But the code would only be one line as opposed to what you had before.

[Megagnome]

Thank you, thank you kind Rabbit! MUCH better. By sorting DESC and restricting the ListRows to 1 our receptionists now have no choice other than the correct number. Most grateful!

[Rabbit]

Not a problem, good luck with the rest of your project.