Dcount / Dsum or something Else?

[Anthony W]

I have a table with Four Primary Keys.

Below is an example with a couple of extra fields with
it. The first four fields are my keys.

Learn_id Provi_id Lprog_id Lacti_id Qual_ref

AW000001 T0000122 01160606 001 Q1053546


My Learn_id field is generated from another table as is
the provi_id and Lprog_id.

So when i add a new record for this table the Lacti_id is
the field that makes it unique.

I need a way of adding a number to the lacti_id field so
when you add a new record, it add the next number along to
lacti_id field. To do this it needs to be gouped by the
first three fields (i think).

Below is an example of i would like it to look:-

Learn_id Provi_id Lprog_id Lacti_id Qual_ref

AW000001 T0000122 01160606 001 Q1053546
AW000001 T0000122 01160606 002 Q1023528
CY000012 T0000122 18062003 001 Q1052233
CY000012 T0000122 18062003 002 10003268
CY000012 T0000122 18062003 003 10003265

The first three fields stay the same and the Lacti_id
field is a number field that ranges from 1 - 99 and starts
at one for each person.

I hope this explains everything clearly enough.

Thanks for any help given.

Anthony

 

[Wayne Gillespie]

I have a table with Four Primary Keys.

Below is an example with a couple of extra fields with
it. The first four fields are my keys.

Learn_id Provi_id Lprog_id Lacti_id Qual_ref

AW000001 T0000122 01160606 001 Q1053546


My Learn_id field is generated from another table as is
the provi_id and Lprog_id.

So when i add a new record for this table the Lacti_id is
the field that makes it unique.

I need a way of adding a number to the lacti_id field so
when you add a new record, it add the next number along to
lacti_id field. To do this it needs to be gouped by the
first three fields (i think).

Below is an example of i would like it to look:-

Learn_id Provi_id Lprog_id Lacti_id Qual_ref

AW000001 T0000122 01160606 001 Q1053546
AW000001 T0000122 01160606 002 Q1023528
CY000012 T0000122 18062003 001 Q1052233
CY000012 T0000122 18062003 002 10003268
CY000012 T0000122 18062003 003 10003265

The first three fields stay the same and the Lacti_id
field is a number field that ranges from 1 - 99 and starts
at one for each person.

I hope this explains everything clearly enough.

Thanks for any help given.

Anthony

The following function should do what you want
Paste the function into a module and replace MyTable with the name of your actual table.

The function requires you to pass the values of Learn_id, Provi_id and Lprog_id as arguments.

Example of use -
Me.Lacti_id = GetNextLactiID(Me.Learn_id, Me.Provi_id, Me.Lprog_id)

Test results from the debug window =
?GetNextLactiID("aw000001","t0000122","01160606")
3
?GetNextLactiID("CY000012","t0000122","18062003")
4


'=============================================================
Function GetNextLactiID(strLearn As String, _
strProvi As String, strLprog As String) As Integer

Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo HandleIt:

Set db = CurrentDb()
strSQL = "SELECT TOP 1 Lacti_id FROM MyTable " _
& "WHERE (((Learn_id)=" & Chr(34) & strLearn & Chr(34) & ") " _
& "And ((Provi_id)=" & Chr(34) & strProvi & Chr(34) & ") " _
& "And ((Lprog_id)=" & Chr(34) & strLprog & Chr(34) & ")) " _
& "ORDER BY Lacti_id DESC;"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
With rst
If .RecordCount > 0 Then
.MoveFirst
GetNextLactiID = !Lacti_id + 1
Else
GetNextLactiID = 1
End If
.Close
End With

OutHere:
If Not (rst Is Nothing) Then Set rst = Nothing
If Not (db Is Nothing) Then Set db = Nothing
Exit Function

HandleIt:
Select Case Err
Case 0
Resume Next
Case Else
Beep
MsgBox Err & " " & Err.Description
GetNextLactiID = -999
Resume OutHere
End Select


End Function
'=============================================================


Wayne Gillespie
Gosford NSW Australia