wrote in
:

I have a field for
the code and fields for each of the four parts but I was
wondering how I could automate the entry of the four
extra fields as the data is redundant.

You would be better off having four fields for the parts and getting rid of
the four-char field altogether. It is easy enough to populate the four
short ones from existing data -- this is a single update query (see help
for details) using a Mid() function (ditto help). It is easy enough to
create a text box to display the code as a single string (use the
ControlSource property) on a form or report.

The trick is to develop a smooth and idiot-proof method of allowing your
users to enter it. If they will go for four controls (eg textboxes,
dropdowns, or whatever) then your problems are over. If they insist on
entering four bits of information into one box then you'll have to do a
little bit of VBA trickery to validate it and then chop it up. This kind of
might work

' the txtCode control is an Unbound Text Box. You can fill
' it in for existing records using the Current event
'
private sub txtCode_BeforeUpdate(Cancel as Integer)

if mid(txtCode,1,1) in ("0", "1", "2", "3", "4", "5") Then
me!yeardigit = CInt(mid(txtCode(1,1)))

else
' no good value
Cancel = True

end if

if mid(txtCode,2,1) in ("1", "2", "3", "4") Then
me!seasondigit = CInt(mid(txtCode(1,1)))

else
' no good value
Cancel = True

end if

if ' etc, checking the other two digits
end if

if Cancel = True then
' warn the user
msgbox "That code is no good!"
end if

' setting the Cancel flag will prevent user from moving off, so
' you don't have to do anything else
End Sub


Hope that helps


Tim F