Run-time error 3061 - Recordsets

Robjenj

Registered User.
Local time
Today, 22:12
Joined
Dec 17, 2009
Messages
21
Got myself in real need of help here, and wonder whether you’d 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 CWH 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 CWH 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!

Cheers!
 
Last edited:
Your query references a form in the where clause, the reason for your error.
Ýou can't use a query that uses form lookups from VBA instead create your query on the fly like:
Code:
Dim strSQL as String
strSQL = “SELECT TBL_Accession.AccessionID, TBL_Accession.FirstAccNo FROM TBL_Accession " & _
            "WHERE (((TBL_Accession.FirstAccNo)= " & [Forms]![FRM_AdmnReCheck].[FirstAccNo].[Value])) " & _ 
[COLOR="SeaGreen"]'or if you start from this form:
'     "WHERE (((TBL_Accession.FirstAccNo)= " & me.FirstAccNo)) " & _ [/COLOR]
            "ORDER BY TBL_Accession.AccessionID;”
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Note: if you use me.FirstAccNo you need to refer to the control not the table field
 

Users who are viewing this thread

Back
Top Bottom