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:-
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!
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
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: