VB Autofill Aplication.

[Aeon.Divine]

Hi there,

Lest get straight to the problem. I've bumped myself into a slight inconvenient trying to design a simple vb module which would autofill a serial number into a textbox on a form when i click over it. The structure of my serial number is MM-AA-XXXXX ( When MM is the current month, AA is the current year, and XXXXX is a 5 digit number equal to my main field. I need the field to block itself upon autocompleting itself, to ensure my field wont refill with a future date later.

I have already tried myself, but im sure my final code is completely wrong, ill paste it here just in case.

Private Sub Codigo_Enter()
FIELDNAME.Locked = False
FIELDNAME.Text = str(month(date))+str(year(date))+idordenes.text
FIELDNAME.Locked = True
End Sub

idordenes is the name of my main field ( autonumeric )

This module should autocomplete my field with the current month, year and main field value, and block it so it wont be editable any longer.

Many thanks in advance. ^^

 

[FizzyFish]

There is no need to unlock the field before populating it in code, all you have to do is set the field to locked and save it. Add code similar to the following and it should automatically populate when the user clicks on on, but only if the field is not already filled in.

Private Sub Codigo_Enter()
If Nz(Len(Me.Codigo), 0) < 1 Then
Me.Codigo = CStr(Month(Date)) + CStr(Year(Date)) + CStr(Me.idordenes)
End If
End Sub

 

[Aeon.Divine]

It works ok, just what i needed. But there are some details i need to correct.

- When i end up filling a form, and use the arrow to start the next, an error comes up, telling me im doing an invalid operacion with a null value. I supose this is because the autonumerical field is not automatically set until i click on it. would it be ok if i do something like adding an outer if, so it would only autocomplete if the main field is bigger than 0 ?

- The first time i tried the program, the number it gave me was 4200828, i need it to write 04-08-00028. How can i program it to do so ? MM-YY-XXXXX. I tried an entry mask, but it would divide the already given number randomly in the shape i've given.

- About the code, i dont know what de NZ does in the if sentence, and what the "Len" and the "Me" and the Cstr does either. Would you be so kind to explain it to me ? Im sure is from the basics, but ive never used VB in access before.

You've helped me a lot already, thanks.

 

[FizzyFish]

I think the easiest thing to do is check that the Condigo field length is less than 1 and the idordenes fiel length is greater than zero.

The NZ converts null values to whatever you tell it to, in this case it converts the null values of both condigo and idordenes to zero so you can check if the length for a numeric value, even if the value is null.

Len(FIELDNAME) returns the length of the field. In this case I've used it to determine if there is any text in the Condigo field so that it only gets written in if there is no text in the field already. I have also now used it to determine if the the idordenes field has a value. If there is no value it won't create the value for condigo.

CStr converts the value to a string. This way if there is numeric values they won't accidently be added mathmetically together. Ie. Month = 2 and Year = 2008 2 + 2008 = 2010. Converting them to strings concantenates them instead. Ie. 22008

Me is something I've gotten into the habit of using to indicate a control on the current form (me).

Here is the code with changes to put dashes in between the different sections and to check for idordenes having something in the field before attempting to create the value.

Private Sub Codigo_Enter()
If Nz(Len(Me.Codigo), 0) < 1 AND NZ(LEN(Me.idordenes), 0) > 0 Then
Me.Codigo = CStr(Month(Date)) + "-"+CStr(Year(Date)) + "-"+CStr(Me.idordenes)
End If
End Sub

 

[Aeon.Divine]

It works just fine now, thanks! I could manage to understand the code as well with what you've told me. One last thing though, about the second question. the dashed correct part of my problem, but i still need the last number to be showed in its five numbers, even though they might be zero.