Automaticly populate a field on a form?

[ChampionDuy]

I am creating a form where the user enters in Case informaiton and the first field which is the Primary key needs to automatically populated with the next case number. Case numbers go like this. 02-001, 02-002, 02-003, 02-004, and so on. The only problem is that the first two digits depend on the year. I could easily get the last three digits to increase by one each time, but how would I get it so that the first two digits will change when the year changes? I might just have to have the user type it in every time, but that is exactly what I am trying to avoid. Thank for any help.

 

[cpod]

Try this code in the BeforeInsert event of your form:

Dim DBS As ADODB.Connection
Dim RST As ADODB.Recordset
Set DBS = CurrentProject.Connection
Set RST = New ADODB.Recordset
RST.Open "SELECT table1.id FROM table1 WHERE (Left([id],2))='" & Format(Date, "yy") & "' ORDER BY table1.id DESC;", DBS, adOpenKeyset, adLockReadOnly
If RST.RecordCount = 0 Then
Me!txtID = Format(Date, "yy") & "-" & "001"
Else
Me!txtID = Format(Date, "yy") & "-" & Format(CInt(Right(RST!ID, 3) + 1), "000")
End If
Set RST = Nothing
Set DBS = Nothing

Of course, you will have to change the table and field names to correspond to yours.

 

[ChampionDuy]

I will give it a shot and let you know how it goes.