refernece number missing digit during form transfer

[antonyx]

i have a jobref (pk) as text at the moment because i tried it as Number data type and had a problem..

a reference number is made and when the date loses focus the refno is displayed in its textbox

this makes that refno

Private Sub cbojobdate_LostFocus()
Dim maxRef As Variant, maxID As Integer
Dim codeDate As String, maxDate As String
    codeDate = Format(cbojobdate, "MMYY")
    maxRef = DMax("jobref", "job", "jobref like '" & codeDate & "*'")
    If (IsNull(maxRef)) Then    'test for new month
         maxID = 0                   'reset id to 0
    Else
         maxDate = Left(maxRef, 4)        'get date code
         maxID = CInt(Right(maxRef, 4))   'convert to int
    End If
    Me.cbojobref = codeDate & Format(maxID + 1, "0000")
End Sub

8 digits..

so a job on september 6th 2006 will have a ref no of 09060001

a job on february 1st 2006 will have ref no of 02060001

when i send the jobref to another form using this..

Private Sub cbojobfrom_AfterUpdate()
If Me.cbojobfrom = "t1" Then
Me.cbojobfrom = "LHR - T1"
End If
If Me.cbojobfrom = "t2" Then
Me.cbojobfrom = "LHR - T2"
End If
If Me.cbojobfrom = "t3" Then
Me.cbojobfrom = "LHR - T3"
End If
If Me.cbojobfrom = "t4" Then
Me.cbojobfrom = "LHR - T4"
End If
If Me.cbojobfrom = "h" Then
Me.cbojobfrom = "LHR"
End If
If Me.cbojobfrom = "ga" Then
Me.cbojobfrom = "Gatwick Airport"
End If
If Me.cbojobfrom = "gn" Then
Me.cbojobfrom = "Gatwick North"
End If
If Me.cbojobfrom = "gs" Then
Me.cbojobfrom = "Gatwick South"
End If
If Me.cbojobfrom = "st" Then
Me.cbojobfrom = "Stansted"
End If
If Me.cbojobfrom = "lc" Then
Me.cbojobfrom = "London City Airport"
End If
If Me.cbojobfrom = "lu" Then
Me.cbojobfrom = "Luton Airport"
End If
DoCmd.OpenForm "job_cash_inflight", , , "[jobref]='" & [jobref] & "'"
End Sub

and the job_cash_inflight form loads it with this..

Private Sub Form_Open(Cancel As Integer)
Me.[jobref].DefaultValue = Forms!job_cash_single![jobref]
End Sub

the problem is when i choose february the first for example

02060001.. when the reference number transfers to the incoming flight form.. it displays as 2060001

can anyone see why because before it transfers the refno.. the original job record has already been saved.. maybe when the id is being created it is not the best way.. or it doesnt match the text datatype.. if that is the case what number datatype shall i use in each of the tables jobref appears in?

 

[antonyx]

i know you say i shouldnt use lost focus and i should use onchange.. but i like it the way it works now and when i use onchange and the tab moves straight through to the next field the refno textbox doesnt display anything

 

[boblarson]

Numbers don't display leading zeros by default and you can display them with leading zeros by using formats, but they won't be stored that way unless they are stored as text.

A format for displaying 8 digits for a text box containing a number would be:
00000000 in the format property.

 

[antonyx]

i have the job ref to be a text.. with format 00000000 in both tables.. and when it transfers the value it still misses the zero.. here is the db with just the forms in question..

for convenients sake.. load the cash_single form..

press tab.. choose any date from february.. then any job time.. then tab to the 'pickup' field.. type anything and tab and the next form should load with the 0 missing.

 

[boblarson]

Hmm, it's formatting to 8 digits with a leading zero when I try it. I'm currently trying it on Access 2003, but I can try on A2K when I get to work. But I noticed that you didn't type in 00000000 in the format property of the text box. Normally, you have to format in the final location (so if you want it to always show like that you format in the LAST place it is used - table formatting or query formatting doesn't usually stay around, so you have to format your control too.

 

[antonyx]

ahhh.. ok.. let me do that then and see if it works..

 

[antonyx]

yes it formats to 8 digits for some dates.. but believe me.. if you choose any date in february.. it transfers it as 7 digits..

also i tried formating the controls aswell and still the same problem

 

[boblarson]

I just DID do several dates in February and it worked fine for me. The control that I saw in the example you posted did NOT have the format on the control.

Worked in Access 2000 here at work too.

I think you may have an issue with your installation. Do you have all of the service packs for Office installed?

 

[antonyx]

ok.. i added the format to the controls and it still didnt work..

Do you have all of the service packs for Office installed?

possibly not.. can you direct me to any updates for access i should have and which could be causing this cos its an integral part of this database im making and i need to sort it.

 

[boblarson]

Yes, your situation seems to be specific to your computer. I'm not sure exactly why this could be happening and I suggest the service packs just in case there is something that could cause it to not work on your machine, but it works on mine (and you were the one who created it).

To do the office updates, just go to http://office.microsoft.com and choose the check for updates button.

 

[Fear Naught]

As an aside to the formatting issue that BL is helping you with can I suggest that you use Select Case instead of all the if endif statements that you have. This makes the code more readable and will probably run quicker.

 

[boblarson]

Good Suggestion Kevin.

 

[antonyx]

http://office.microsoft.com/en-gb/downloads/CD010798671033.aspx

do you think i need any of these?

 

[antonyx]

ok.. i will look into that kevin.. but do you think all that code before the refno transfer is causing that problem?

 

[boblarson]

I think you should go ahead and select all of the patches and updates, as you never know what it will be (be sure to set a system restore point beforehand, in case you need to roll back - if you are using WinXP). Also, since it works for me I don't think that the code is causing the problem, but that's only if you included it in the sample.

 

[antonyx]

this is really annoying

 

[boblarson]

I hear you - I really have a love/hate relationship with Access. 90% I love working with it and 10% I wish I could throw it into a blazing inferno.

 

[antonyx]

im gettin office2003sp1 72 meg.. hope it works.

 

[antonyx]

cant update because i dont have the cd.. well thats just great isnt it...

is there not another method i can use bob.. how can i use a number datatype instead cos im gettin nowhere fast

 

[Fear Naught]

Anthony i have been able to reproduce the problem that you have. For BL:- The issue is not with the main form but with the form that pops up after you enter a value into the "From" box. This is supposed to transfer the jobref to the new form Whenever the month number has a leading zero (Jan or Feb) that zero is not transferred to the new form.

I have a had a very quick look but can't find the problem but BL having a greatermind then me may now be able to track it down.

HTH