Eliminar registro en autonumérico y añadir uno nuevo

Vamos a ir por partes

Con respecto a lo de evitar que se duplique un registro. Si tengo un formulario Pedidos, donde voy a repetir la fecha 24/07/2018

Puedes ver que el cursor aún está en el control FechaPedido. Cuando pulso Enter

Al Aceptar el cursor se vuelve al control FechaPedido para que la cambies. En este caso, el código del evento Antes de actualizar del control FechaPedido es

Private Sub FechaPedido_BeforeUpdate(Cancel As Integer)
If DCount("*", "copia", "fechapedido=#" & Me.FechaPedido & "#") >= 1 Then
MsgBox " Esa fecha ya está guardada", vbOKOnly + vbExclamation, "Que tengas una Feliz Navidad"
DoCmd.CancelEvent
End If
End Sub

Con respecto a lo de eliminar un registro no sé si te refieres a que si, por ejemplo, elimino el registro con Idpedido=15, al nuevo pedido que meta le ponga como Idpedido=15 ó a que los renumere. Es decir, que el que tenía el Idpedido =16 pase a ser el Idpedido=15, etc.

Si me aclaras esto último te podré decir algo concreto

Gracias por responder.

Me refiero a la primera opción. Ocupar el id 15 que queda libre

Hago constar que no sé como tienes el formulario. Recorto registro para que veas como al escribir asigna el número borrado, y como tampoco sé como eliminas registros le he puesto un botón de eliminar

Si tal como eliminas un registro escribieras el nuevo, sólo habría que declarar una variable cuyo valor fuera igual al Idpedido que he eliminado, pero como puede pasar un tiempo hasta que rellenes un nuevo registro creo una tabla Aux como en la imagen, con un Idpedido numérico, no clave ni nada

Ahora en el formulario voy a eliminar al Idpedido=4

Puedes ver que pasa del 3 al 5. Ahora pongo el cursor en un registro nuevo

Le ha asignado el 4

El código del botón Eliminar es

Private Sub Comando14_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "insert into aux(idpedido) values(idpedido)"
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End Sub

Es decir, que primero me guarde en la tabla Aux el valor de Idpedido y luego elimine el registro

y el código del evento Al activar registro del formulario es

Private Sub Form_Current()
If IsNull([IdPedido]) And Nz(DCount("*", "aux")) = 0 Then
IdPedido = DMax("Idpedido", "pedidos") + 1
ElseIf IsNull([IdPedido]) And Nz(DCount("*", "aux")) >= 1 Then
IdPedido = DFirst("Idpedido", "aux")
DoCmd.SetWarnings False
DoCmd.RunSQL "delete idpedido from aux"
End If
End Sub

Es decir, que si el valor de Idpedido es nulo( lógico ya que es un registro nuevo, pero así evitas que si te desplazaras por los registros te modifique el Idpedido) y en la tabla Aux no hay ningún Idpedido de un registro eliminado, le asigne el valor de el mayor valor de idpedido+1, y si por el contrario hubiera algun registro eliminado, que tome su valor( le he puesto Dfirst porque no sé si, por ejemplo, eliminas varios de una vez y te vas, y al día siguiente le vas a añadir registros, en ese caso habría que modificar la instrucción...delete idpedido....y dejarla como

docmd.runsql"DELETE IdPedido FROM Aux WHERE idpedido=DFirst(""idpedido"",""aux"")"

La otra respuesta que te dan es un atraso. ¿Para qué hacen falta 26 líneas de código cuando se puede hacer en 7? ¿A santo de que viene definir

Dim db as database si ya estás en una base de datos

Dim rs as recordset. Mientras el mundo no cambie Access trabaja con registros ¿ o es que hay otra cosa en las tablas?

Set db as currentdb. Pero si ya estás en ella. Es como si vienes a verme a mi casa y cuando te abro la puerta te digo ¡Hola, esta es mi casa!

Hola Julián. No termino de dar con la tecla.

Este es el formulario que utilizo

con el botón eliminar elimino registros del subformulario o el registro completo

Después de actualizar en Fecha pedido, que se llama fecha,  tengo el código

La tabla es esta

No sé si tiene algo que ver el formato o no sé pero no me termina de funcionar.

Ayuda. 

Saludos

elimino un registro y quiero que el siguiente que dé de alta se le asigne el Numpedido eliminado. Y que al año siguiente en el formato se cambie de -20 a -21

Cómo necesita determinar el primer número que falta en la serie preparé esta función:

La llama después de la línea

Dim a AS Integer

Algo como:

a=faltante()

IF a=0 THEN ' No hay faltante

   a=(DCOUNT(nz(..............................)+1)

END IF

Numpedido="PD-" & Format(a,"0000") &  "-" & Format(date(),"yy")

Bueno esto es una idea.

Cambios a la función:

TABLA DE PEDIDOS

NUEVO PEDIDO CON LA FUNCIÓN

Ejemplo, si llamo la función de acuerdo con la tabla:

? Faltante()
4

Retorna 4 que es el siguiente primer número secuencial disponible.

Esto es todo sin tantas vueltas no se complique con tablas auxiliares y código innecesario que inclusive es obsoleto. Siga el ejemplo de la respuesta anterior.

Seguro que debe existir una alternativa mejor.

Encontré una inconsistencia cuando el número de pedido del primer registro está entre 2 y 9, es decir, quedaría disponible el número 1. Esta es la nueva función.

Disculpe el error...

Hay otra forma es crear una tabla de secuencia de números, es decir, desde el 1 hasta x número y después hacer una consulta de no coincidentes con la tabla de pedidos y de ésta tomar el número menor. El problema es que no se sabe hasta donde iría la secuencia.

Como se ve que Julián González no sabe VBA no sabe que es una función y el alcance de estas, menos sabe que es un recordset DAO, absurdo la solución que él da a que cerebro se le ocurre crear tablas adicionales cuando se pueden reemplazar por funciones y ocasiones por consultas.

Si quiere envíeme la base datos con algunos datos no relevantes a [email protected] y me comprometo a dar respuesta con la función que le propongo. No se deje confundir.

Listo Antonio de acuerdo con la base de datos que me envío le tengo la solución:

Tabla original sin retirar registro

TABLA CON ELIMINACIÓN DEL REGISTRO Numpedido PD-0003-20

FORMULARIO PEDIO ADICIONANDO UN REGISTRO

Observe como asigna el número de pedido PD-0003-20 efectivamente el que se había eliminado.

CÓDIGO DEL EVENTO DESPUES DE ACTUALIZAR CAMPO Fecha pedido

CÓDIGO DE LA FUNCIÓN FALTANTE

No necesita darle formato al campo autonumérico, se lo retiré, toda vez, que al eliminar un registro no hay forma de tomar nuevamente el consecutivo.

Espero con esto responder a su pregunta y queda demostrado para qué sirve DAO y las funciones, así otros atrasados no lo consideren necesario y sin tanta imagen y mentiras. Ya le suministro el archivo editado.

Al evento Después de actualizar del campo Fecha es necesario incluirle NewRecord, para que solo se ejecute si es un registro nuevo, sería:

Private Sub Fecha_AfterUpdate()

  Dim a As Integer
  If Me.NewRecord Then
    a = faltante(Me.Fecha)
    If a = 0 Then 'No hay faltalte
       a = DCount("[PD]", "pedidos", "Year([fechapedido])=year(date())") + 1
    End If
    Numpedido = "PD-" & Format(a, "0000") & "-" & Format(Date, "yy")
  End If
End Sub

¡Gracias! 

Lo he probado y es exactamente lo que quería. Muy agradecido.

Genial.

Tengo mucho que aprender

Felices fiestas