Que puedo hacer para que al rellenar un campo de formulario, me avise mediante msgbox de que ese valor ya existe en la tabla?

En el evento Antes de actualizar del cuadro de texto donde vas a escribir el DNI, al que para el ejemplo llamaremos también DNI crea un procedimiento de evento y entre Private Sub y End Sub escribe

If dcount("*","T_personas","DNI like '" & me.dni & "'")>=1 then

Msgbox"Ese dni ya está en la tabla", vbokonly+vbexclamation,"Lo siento, otro día será"

Docmd. Cancelevent

end if

O sea, cuando escribas un DNI, que he supuesto que lleva la letra, y por tanto es texto, y pulses Enter, comprueba si ya existe en la tabla. Si existe te aparece el mensaje y al Aceptar el cursor se vuelve al cuadro de texto para que lo cambies.

Amigo Julian, he puesto lo que me dices y no me funciona. Pense que podria ser debido a que el nombre el campo en el formulario es C_NIF, lo he sustituido en tu código  y tampoco funciona.  

Te comento una cosilla, el cuadro de texto "C_NIF", donde introducimos el valor del DNI, en el evento "Al Perder el foco", esta codificado de manera que solo se puede introducir datos numéricos y él le calcula la letra, con el siguiente código:

Private Sub C_NIF_LostFocus()

On Error GoTo etiqueta

If Not IsNull(C_NIF) Then
Etiq_NIF.Visible = False
C_NIF.BackColor = vbWhite
C_NIF.Value = C_NIF.Value & calcula_letradelNIF(C_NIF.Value)

End If
Exit Sub

etiqueta:

If Err.Number = 13 Then

MsgBox "Has introducido datos que no son validos"
C_NIF.Value = Null
C_Nombre.SetFocus 'Ponemos el foco en el campo nombre antes de pasarlo al campo NIF (esto es necesario porque ya tenemos el foco en el NIF
C_NIF.SetFocus
ElseIf Err.Number = 6 Then

MsgBox "El NIF introducido tiene demasiados caracteres, revisalo"
C_Nombre.SetFocus 'Ponemos el foco en el campo nombre antes de pasarlo al campo NIF (esto es necesario porque ya tenemos el foco en el NIF
C_NIF.SetFocus
End If

End Sub

Por favor, échale un vistazo a ver si lo podemos solucionar. GRACIAS

Si tengo la tabla T_Personal, con un campo DNI al que que le doy una longitud de 9 dígitos

Si tengo un formulario, que por lo que he visto en la instrucción que pones( que me vas a perdonar, pero es demasiado larga, desde el momento que se puede usar For each control in form. Controls)

El formulario es independiente y escribo un DNI que ya está en la tabla T_personal

Puedes ver que el cursor aún está en ese control. Si pulso Enter

Cuando Acepto, el cursor se vuelve al control DNI para cambiar el valor

El código del evento Antes de actualizar del control DNI es

Private Sub DNI_BeforeUpdate(Cancel As Integer)
If DCount("*", "T_personal", "dni like '" & Me.DNI & "'") >= 1 Then
MsgBox "De eso nada, monada. Ese DNI ya está, tendrás que cambiarlo", vbOKOnly + vbInformation, "Señor, dame paciencia"
DoCmd.CancelEvent
End If
End Sub

Me había olvidado de lo de C_Nif. Mira

Y sucede lo mismo

En este caso el código va en el evento Antes de actualizar del cuadro de texto C-Nif

Private Sub C_NIF_BeforeUpdate(Cancel As Integer)
If DCount("*", "T_personal", "dni like '" & Me.C_NIF & "'") >= 1 Then
MsgBox "De eso nada, monada. Ese DNI ya está, tendrás que cambiarlo", vbOKOnly + vbInformation, "Señor, dame paciencia"
DoCmd.CancelEvent
End If
End Sub

Los campos Origen y destino no tienen porqué llamarse igual, pero sí ser de datos coherentes, texto con texto, número con número, etc.

Por cierto, en Access, por defecto, cuando haces referencia a un control a secas, entiende que te refieres al valor que muestra, no hace falta lo de

TextoX.value basta con Textox=.. 

ó

Textb=textoX

El campo de destino tenia una longitud de 15 digitos, la he cambiado a 9 y he repasado el sitacxis y, curiosamente, ahora funciona a la perfección. con tespecto a lo que me dices de que la instrucción que tengo es demasiado larga, te comento que ese formulario lo hice siguiendo un tutorial y la verdad es que me deje guiar por lo que estaba viendo y como funionó bien, no me preocupe de si había otras formas de hacerlo mas economica. Te ruego, si no es mucha molestia, me me hables un poco de la instrucción For each control in form. Controls) y donde deberia ponerla para sustituir a la que tengo y para otros supuestos que se me presenten. UN SALUDO