Full Version: CODE Confusion
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
Goof2767
I have the following code:

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.[FYStartDate] >= #6/1/2009# Then
Me.txtJobNum = Nz(DMax("JobNum", "tblDetail", "[FYStartDate]>=#6/1/2009#"), "0000") + 1

End If
End Sub

My question is that when I started new records on 6/1/09, the control started to count up the way it should. When it got to 10 and I started a new record the number jumped to 2989 instead of the next due number 11. Any thoughts on why???
Doug Steele
You're telling the Nz function to return a string if there's no value: you should be returning a number.

CODE
Private Sub Form_BeforeInsert(Cancel As Integer)
  
  If Me.[FYStartDate] >= #6/1/2009# Then
    Me.txtJobNum = Nz(DMax("JobNum", "tblDetail", "[FYStartDate]>=#6/1/2009#"), 0) + 1
  End If
  
End Sub


If you're trying to ensure a 4 character field, format the value after the addition, not before:

CODE
Private Sub Form_BeforeInsert(Cancel As Integer)
  
  If Me.[FYStartDate] >= #6/1/2009# Then
    Me.txtJobNum = Format(Nz(DMax("JobNum", "tblDetail", "[FYStartDate]>=#6/1/2009#"), 0) + 1, "0000")
  End If
  
End Sub
Goof2767
I applied your suggestion and it still returns the same number, 2989.
Doug Steele
Okay, let's see what's happening. Try this:

CODE
Private Sub Form_BeforeInsert(Cancel As Integer)
  
Dim varMax As Variant
  
  If Me.[FYStartDate] >= #6/1/2009# Then
    varMax = DMax("JobNum", "tblDetail", "[FYStartDate]>=#6/1/2009#")
    If IsNull(varMax) Then
      MsgBox "Max JobNum is Null."
      varMax = 0
    Else
      MsgBox "Max JobNum is " & varMax
    End If
    Me.txtJobNum = varMax + 1
  Else
    MsgBox "FYStartDate is before June, 2009"
  End If
  
End Sub
Goof2767
The message said "Max JobNum is 2988."

Question: This control can't have duplicating numbers, can it?
Doug Steele
Hold on. I wasn't really understanding your situation before.

Controls simply display data: there aren't really rules about what they can and can't be. The txtJobNum control is presumably bound to the JobNum field in table tblDetail. Somehow data got into that table with a value of 2988 for JobNum. Are there any other ways that data could have got into that table other than through your form?
Goof2767
Sorry for the confusion (as the title says). I have a field in a table that counts up "JobNum". On 6/1 of whatever year it starts counting from 1 to whatever for 1 year and then starts over at 1 and does it again the following 6/1. 6/1 to 5/31 is my fiscal year. To determine what fiscal year I am in I have another field, "FYJobNum". 6/1/09 looks like this: P10-0001, P10-0002, and so on. P10 being my fiscal year designation.

So my question from before, "I can't have duplicate numbers in this field, right???" Because the code is always looking for the highest number. Is there a way to write code to have it start over at 1 and only look at the current fiscal year number??? Or is that too complicated.
Doug Steele
Sorry, but you're still not giving enough information. If JobNum has been designated as the only field in a unique index (which doesn't have to be a primary key), then no, you can't have duplicate numbers in it. However, I don't see a need for both JobNum and FYJobNum. Why not just worry about FYJobNum?

CODE
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim varMaxNumber As Variant
  
  If Me.[FYStartDate] >= #6/1/2009# Then
    varMaxNumber = Nz(DMax("FYJobNum", "tblDetail", "[FYStartDate]>=#6/1/2009#")
    If IsNull(varMaxNumber) Then
      Me.txtFYJobNum = "P10-0001"
    Else
      Me.txtFYJobNum = "P10-" & Format(CLng(Mid(varMaxNumber, 5) + 1, "0000")
    End If
  End If
  
End Sub
Goof2767
I was told in the past that I had to make 2 fields...1 for the "P" number and 1 for the other. Now in talking with you I find that is not the case, correct??? If I go with 1 field (FYJobNum), I can change the "P" number year after year and have the four digit number go back to "0000" at the beginning of the fiscal year???
Doug Steele
Are the two fields about which you're talking JobNum and FYJobNum? Which one is your Primary Key?

My recommendation would be to have two fields, but not those two fields. Instead, the P number should be stored by itself in one field, while the part after the hyphen should be stored in the other field. The combination of the two fields should be the primary key. That will let you restart at 1 each time the Fiscal Year indicator changes. If we assume that the fields are named FYPrefix and FYJobNum, your code would be something like:

CODE
Private Sub Form_BeforeInsert(Cancel As Integer)
  
  If Me.[FYStartDate] >= #6/1/2009# Then
    Me.txtFYJobNum = Nz(DMax("FYJobNum", "tblDetail", "[FYPrefix]='P10'"), 0) + 1
  End If
  
End Sub


You'd then create a query with a computed field that concatenates the two fields for display purposes.
Goof2767
So you don't think a single field is a good idea??
Doug Steele
As in concatenating two pieces of information into a single field? No, it's a bad idea: it violates database normalization principles, and makes queries more difficult than they need be.
Goof2767
OK...thanks for the advice Doug...it was very helpful. Have a good day!!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.