Automatically Assigning A Project Ref

[BenSC]

Dear All,
I have the following problem, which I am really stumped on. I have a database to store details of building projects, which can be funded from one or more budgets and also have a financial year attached to them. On my data entry form I want to enter the relevant data and have access automatically provide me with a project reference in the following format:

Single Funded Project - ACC-04/05-01. If this reference already existed then I would want access to return ACC-04/05-02 and so on.

Multi-Funded Project - ACC/AMPC-04/05-01. If this reference already existed then I would want Access to return ACC/AMPC-04/05-02 and so on.

I have the following code running on a timer event and can't figure out how to get the recordset to select only the projects which match the budget (e.g. the ACC- part). I am ok with restricting the records returned for the financial year because that is a seperate field.

If anyone could help me with this I'd be very grateful.

Many Thanks

Ben Cooper

------------------------------------------------------

On Error Resume Next

Dim strFunding As String
Dim rst As Recordset
Dim strProjectCount As String

Me.TotalACCAllocation = Me.ACCAllocation0203 + Me.ACCAllocation0304 + Me.ACCAllocation0405 + Me.ACCAllocation0506
Me.TotalAMPCAllocation = Me.AMPCAllocation0203 + Me.AMPCAllocation0304 + Me.AMPCAllocation0405 + Me.AMPCAllocation0506
Me.TotalAMPSAllocation = Me.AMPSAllocation0203 + Me.AMPSAllocation0304 + Me.AMPSAllocation0405 + Me.AMPSAllocation0506
Me.TotalCRFCAllocation = Me.CRFCAllocation0203 + Me.CRFCAllocation0304 + Me.CRFCAllocation0405 + Me.CRFCAllocation0506

If Me.TotalACCAllocation > 0 Then
If Len(strFunding) > 0 Then
strFunding = strFunding & "/ACC"
Else
strFunding = "ACC"
End If
Else
End If
If Me.TotalAMPCAllocation > 0 Then
If Len(strFunding) > 0 Then
strFunding = strFunding & "/AMPC"
Else
strFunding = "AMPC"
End If
Else
End If
If Me.TotalAMPSAllocation > 0 Then
If Len(strFunding) > 0 Then
strFunding = strFunding & "/AMPS"
Else
strFunding = "AMPS"
End If
Else
End If
If Me.TotalCRFCAllocation > 0 Then
If Len(strFunding) > 0 Then
strFunding = strFunding & "/CRFC"
Else
strFunding = "CRFC"
End If
Else
End If

Set rst = CurrentDb.OpenRecordset("SELECT tblProject.* FROM tblProject WHERE tblProject.FinancialYear = '" & Me.FinancialYear & "'")

If rst.EOF Then
strProjectCount = "01"
Else
rst.MoveLast
strProjectCount = rst.RecordCount + 1
End If

If strProjectCount < 10 And Mid(strProjectCount, 1, 1) <> "0" Then
strProjectCount = "0" & strProjectCount
Else
End If

Me.ProjectRef.Value = strFunding & "-" & Me.FinancialYear & "-" & strProjectCount

End Sub