automatic field in a form

[mar7632]

Hello everyone,



I am developing an Access Application to my department. There is one field called “codigoRFQ” which I want to automatize when a new record is introduced. The way we use Access is only by forms.

I am quite “new” in Access, so my code level is not high enough to build my own script, but rather I look it up through internet.

Before writing down the code, it is better to explain what it is required:

The structure of “codigoRFQ” is the followed one: yyyyddd(letter)

• yyyy: actual year we are.
• ddd: actual day we are out of 365. For example: if today is 02 February, the ddd value will be -> 032. Another one: If today is 28 May, the ddd value will be 150.
• (leter): In order to differentiate records that have the same yyyyddd values the letter is required.

For example:
2019033A … 2019034B … 2019034C … [other day] … 2019035A …

So, the idea behind is the followed: When we go into a new record, automatically “codigoRFQ” will update following the same structure as I wrote before.

I found a code which has the same principle, but its structure is like this: A0001 … A0002 … […] A9999
… B0001

I need some help in order to modify the previous code and adapt it into my own.

The link I provide has both the database and a PDF with the code, although written in Spanish.

http://neckkito.xyz/nck/index.php/ej...o-autonumerico

As you can see, the second example is the one which I considered.

 
'Form1: FDatos2 ->

Private Sub Form_Current()

'A tener en cuenta:
'La letra A tiene el código de carácter 65
'La letra Z tiene el código de carácter 90
'Declaramos las variables
'Con la constante miSerie indicaríamos qué letras hay que añadir antes del número

Dim miSerie As String
Dim miAN As String, ultimoAN As String
Dim ascSerie As Integer
Dim ultimoANNum As Long

'Comprobamos si hay valor en [Autonum]. Si lo hay es que estamos recorriendo
'los registros, por lo que no necesitamos calcular nada. En este caso salimos

If Not IsNull(Me.codigoRFQ.Value) Then Exit Sub
'Comprobamos cuál es el último [Autonum] introducido a través de DLast()

ultimoAN = Nz(DLast("codigoRFQ", "aa_DatosRecibidos"), "")
'Si no hay registros obtendríamos una cadena vacía (""). En este caso tendríamos
'que empezar por el primero

If ultimoAN = "" Then
'Escribimos directamente el primer valor. Lo escribiremos en función del
'tamaño de campo que hayamos establecido

Me.codigoRFQ.Value = "A0001"
Exit Sub
End If

'Cogemos el valor de la serie en la que estamos
miSerie = Left(ultimoAN, 1)

'Vamos a coger su parte numérica. Como hemos definido el tamaño
'del campo en 5 debemos coger los últimos 4 caracteres. Si hubiéramos definido el tamaño
'del campo en 8, por ejemplo, deberíamos coger los últimos 7, así:
'<ultimoANNum = CLng(Right(ultimoAN, 7))>
'Convertimos al mismo tiempo ese valor en un entero largo

ultimoANNum = CLng(Right(ultimoAN, 4))
'Le añadimos una unidad
ultimoANNum = ultimoANNum + 1

'Si el valor obtenido es menor que 9999 seguimos con el procedimiento. Lógicamente, si
'el tamaño que hubiéramos definido para el campo fuera, por ejemplo, 6, la comprobación
'se haría sobre el valor 99999

If ultimoANNum > 9999 Then
'Si pasamos el valor 9999 debemos hacer dos cosas:
'*** Empezar la numeración a 1 de nuevo
'*** Cambiar la letra de la serie

ultimoANNum = 1 'Con esto hemos reinicializado el contador
'Calculamos el valor numérico del carácter de la serie

ascSerie = Asc(miSerie)
'Le añadimos una unidad

ascSerie = ascSerie + 1
'Si el valor resultante superara el valor 90 volvemos a empezar
'por la A

If ascSerie > 90 Then ascSerie = 65

'Reconvertimos el ascii en carácter

miSerie = Chr(ascSerie)
End If

'Reconstruimos el alfanumérico teniendo en cuenta que debemos
añadir los ceros anteriores
'al número. Eso lo hacemos con la adición de cuatro ceros (la parte
'numérica) y la
'función right()

miAN = miSerie & Right("0000" & ultimoANNum, 4)
'Si hubiéramos fijado el tamaño del campo en 7, por ejemplo,
debemos:
 
'***Quitar una unidad para respetar la serie, por lo que nos quedan 6
'***En lugar de escribir 4 ceros escribiríamos 6 ceros, y cogeríamos los 6
' últimos caracteres de la derecha, así:
'<miSerie & Right("000000" & ultimoANNum, 6)
'Escribimos el valor en el campo del formulario

Me.codigoRFQ.Value = miAN
End Sub

I would appreciate any help provided,

Thank you in advance,

Regards.

[ranman256]

I think you are making this difficult.
There already is an autonum field that creates an ID when users creates a record.
Throw in a DateStamp field (default set to Now()) that enters the date when users creates a record.

now you have a date (DateStamp field) and order (autonum) and you didn't write a single line of code.

[pbaldy]

I assume you're also storing the date? Presuming so, I wouldn't bother saving the first part of your value, just the letter. The first part can be derived from the date field. You can use a simple DCount() of records on the same day and add 1 to it for the next value, add that to 64 for the ASCII value.

[pbaldy]

Sorry ranman, clearly my fingers are moving slowly today.

[mar7632]

Thank you, both pbaldy and ranman256 for answering.

Unfortunately, it is something I was ordered to do. So the structure needs to be like I described before or something similar to.

Another problem is that the people who will use the forms need to know this "codigoRFQ" field in advance, they will not use autonum field ID because for them it will be hidden.

Throw in a DateStamp field (default set to Now()) that enters the date when users creates a record.

now you have a date (DateStamp field) and order (autonum) and you didn't write a single line of code.

How could I differentiate records posted in the same day?

I assume you're also storing the date? Presuming so, I wouldn't bother saving the first part of your value, just the letter. The first part can be derived from the date field. You can use a simple DCount() of records on the same day and add 1 to it for the next value, add that to 64 for the ASCII value.

I have one field called "fecha_entDT" which it is supposed to be filled in the same day where the record is introduced (by hand). However, I am not using it in this case.

I do not really understand what do you mean about the "ASCII".
How should I proceed in that case?


Once again, Thank you in advance.

Regards.

[orange]

If you had a field called ---SubmittedDate or CreatedDate and populated that with Now() [which is an Access function that provides the current date and time] you would have a field on each record that identifies the year, month, day, hour min and second.
Since this is an intrinsic Access function, and since the Access Date datatype is designed for such a purpose, there is no need to concoct fields with Format YYYYDDD and then suffix that with other characters.
You and/or your bosses are making this difficult by venturing away from a best practice approach.

Since you are new to Access and database, copying code from the internet is, at best, a hit or miss method.
Instead of working/playing "copy/paste and hope" with a physical database that appears to be important to your organization, I recommend you learn some database concepts and some Access best practices, then plan and design what you and your bosses need from a point of knowledge rather than your current "technique".


Good luck with your project.

[mar7632]

Thank you for your response, orange.

How should I proceed in that case? Because I am not acquainted with that. Could you recommend me some link or source to look at? please

Thank you in advance,

Regards.

[orange]

I was restating - in different words and some personal comments - the essence of ranman and pbaldy posts.
A timestamp/datestamp on each record gives you a field whose value will be automatically entered when the record is created and will be different for records added on the same day.

See this free youtube video by Richard Rost for an explanation and example of the Now() function and DateTime stamp.

[mar7632]

Hello orange,

I have tried what you suggested. However, It is not the point I want to achieve because I have already a field for that purpose (to retain the date which a new record is introduced).

Is there anything I can do in order to obtain what I explain at the beginning? Even though only I obtain the default value "yyyyddd". This "codigoRFQ" field is not a Date/hour field.


Thank you in advance,


Regards.

[orange]

I suggest you try describing again what you are trying to do with an example. My posts have been supportive of the suggestion by ranman in post #2. Since you say the Now function as a default for the Datestamp is not what you want, it seems ranman, pbaldy and I may have misunderstood your requirement. This approach is a common technique for identifying when records are/were created.

[mar7632]

Sorry for the misunderstanding,

"CodigoRFQ" has the function of displaying the total amount of queries we receive in a day during the whole year.
It is a way to visualize all the data quickly. and needs to be unique.

So imagine I add a new record into my form.

CodigoRFQ has to identify the year and day we are and differentiate itself from others.

Example:

On 11/02/2019 new record -> "CodigoRFQ" = 2019-042-A
Another record on 11/02/2019 -> "CodigoRFQ" = 2019-042-B

On 26/04/2020 new record -> "CodigoRFQ" = 2020-116-A
On 26/04/2020 another one -> "CodigoRFQ" = 2020-116-B
On 26/04/2020 another -> "CodigoRFQ" = 2020-116-C
On 26/04/2020 another -> "CodigoRFQ" = 2020-116-D

On 27/04/2020 new record -> "CodigoRFQ" = 2020-117-A

On 02/10/2020 new record -> "CodigoRFQ" = 2020-275-A
On 02/10/2020 another -> "CodigoRFQ" = 2020-275-B


Hopefully with this example you understand what I want to achieve.

Regards.

[mar7632]

Hi again,

Finally, I followed your instructions I think that the method I explained before it is too complicated.

I´ve decided to use codigoRFQ as a date/hour field and in its format I set: "yyyy-ww-w" (year/ week (1-53)/ day (1-7))
But what I need is to differentiate this field in the case that a new record is introduced in the same day. Maybe by using a number or a leter. Any idea?

[Minty]

Just to restate what others have said, you don't need this as you have all the information you need at hand.

If you simply want to display the number of records per day use a totals query and display the results on your form somewhere.
You shouldn't store a value that can be calculated. If any of the underlying data gets changes your store value needs to be updated.

In your proposed system what if someone creates a incorrect record then deletes it, all you numbers / letter suffixes are then wrong for the rest of the day, and the indicator as become useless.