Autofill text field

[hermanko]

Hi,

In my database, I have a filed called [File Code] which uniquely
identifies each record. The database contains and manages a collection
of document's information such as their filenames, locations, version,
etc...

An option for the user is to add a new document, which requires them to
input a unique file code in the first field on this form. I have error
checking in place to ensure the user does not enter a code already
existing, however I would like to implement some kind of autofill but
it's not as simple as it may sound. Why? My file code structure is
shown below in an example:

MAP-2006-001
the first 3 letters represents a type of document, the middle portion
is the year and the last 3 digits is the doc # within that group.

so, what I am trying to autofill is once the user types in "MAP-2006"
the field will autofill the next available number (i.e. "002") so that
the user doesn't skip a number and accidentally insert 003.

This field already has an input mask in place as well as Required set
to YES.

I am not sure if what I am asking is possible???

Thanks
Herman

 

[Guest]

You can do this, but it will take some reworking of your form.
Create an unbound control on your form (I would suggest a combo box) to
allow the user to select the document type. Then in the After Update event
of that combo, you can construct the value using the selection in the combo
and the current year.

Dim strFileCode as String

If Me.NewRecord Then
strFileCode = Me.cboDocType & "-" & Format(Date,"yyyy") & "-"
Me.txtFileCode = strFileCode & _
Format(Nz(DLookup("[File_Code],"tblDocuments", _
"Left([File_Code], Len(strFileCode)) = '" & _
strFileCode & "'),0) + 1, "000")
End If