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 ‘Consultat ion 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 ‘Consultat ion number’) to the Re-Check Admission screen (‘FRM_AdmnReChe ck’).
We assume (from experience) that the Carer will not always be able to quote its previous AccessionID (or ‘Consultat ion 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 ‘FirstAccN o’, 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 ‘FirstAccN o’ of the animal is the number entered into the ‘FirstAccN o’ 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_PrevA ccNo’ (“
). The vba I have is:-
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_AdmnR eCheck.FirstAcc No 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!
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 ‘Consultat ion 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 ‘Consultat ion number’) to the Re-Check Admission screen (‘FRM_AdmnReChe ck’).
We assume (from experience) that the Carer will not always be able to quote its previous AccessionID (or ‘Consultat ion 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 ‘FirstAccN o’, 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 ‘FirstAccN o’ of the animal is the number entered into the ‘FirstAccN o’ 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_PrevA ccNo’ (“
Code:
SELECT TBL_Accession.AccessionID, TBL_Accession.FirstAccNo FROM TBL_Accession WHERE (((TBL_Accession.FirstAccNo)=[Forms]![FRM_AdmnReCheck].[FirstAccNo].[Value])) ORDER BY TBL_Accession.AccessionID;”
Code:
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
A typical result of running the query for value 21715 in field Forms!FRM_AdmnR eCheck.FirstAcc No 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!
Comment