Form Event not formatting correctly

[Sondra]

I have a form with the following code:
**********************
Private Sub Form_BeforeInsert(Cancel As Integer)

Dim mTable As String, mField As String, mYearPart As Long, mNextNumber
As Long
'number is returned as 6 characters: YY####
'where YY is the last 2 digits of the year
'#### is the next number for that year
'D is the DSCR Code
'format code to display this number should be "00D-0000"
mTable = "DSCR"
mField = "DSCRNumber"
mYearPart = Right(CStr(Year(Me.DSCRYear)), 2) * 1000
mNextNumber = Nz(DMax(mField, mTable, mField & ">=" & mYearPart), 0)

If mNextNumber = 0 Then
mNextNumber = mYearPart
End If

mNextNumber = mNextNumber + 1
Me.DSCRNumber = mNextNumber

End Sub

***************
The procedure is working correctly with one exception. I would like the
user to see the following in the field 05D-0001, etc., but what they see is
050001.

Can someone advise how to force the field to look as "00"D-"0000 versus 000000

Thanks in advance.

 

[Klatuu]

Put this in the Format property of your text box: 00D-0000

 

[Sondra]

Klatuu:

I did that; however because it isn't text it turns my Format to 00\D000.

The field is a number Datatype.

The end result is 00D5001. The year 05 is now in the wrong place.

Any other ideas from anyone would be great.

 

[Klatuu]

I don't know what the problem is. I tested it here and it worked for me.

 

[Sondra]

And you are entering the on the form the format of 00"D-"0000?

Is there anyway I can send you my database and have you look at it??

 

[Klatuu]

Yes, I entered 00D-0000
and it change to 00"D-"0000
It works for me. I get 05D-0001
I am on XP Pro /2003

 

[Sondra]

I wonder if it is a problem with the version. We are using XP Pro 2002.

I've done everything you did and it still comes out the same.

 

[Klatuu]

I guess that could be possible, I have never worked in 2002. I went from 97
to 2000 to 2003.

 

[Sondra]

I could still use some help. It is not working for me and I've double and
triple checked everything. Please advise on your thoughts?

 

[Klatuu]

Try going into Help from your VB Editor and look up the Format function.
Read up on it and do some experimenting. If our two versions are working
differently, I don't know that anything I could do would work for you.

 

[Marshall Barton]

I have a form with the following code:
**********************
Private Sub Form_BeforeInsert(Cancel As Integer)

Dim mTable As String, mField As String, mYearPart As Long, mNextNumber
As Long
'number is returned as 6 characters: YY####
'where YY is the last 2 digits of the year
'#### is the next number for that year
'D is the DSCR Code
'format code to display this number should be "00D-0000"
mTable = "DSCR"
mField = "DSCRNumber"
mYearPart = Right(CStr(Year(Me.DSCRYear)), 2) * 1000

There is a problem with the *1000, it should be *10000