Increment Field Values

[ViRi]

Hi

I have a numeric field called FileNo and an autonumber field called FileID in a table called tblFile.
I also have a lookup numeric field called FileTypeID (with values 1 or 2 or 3) on another table called tblFileType.

I need the value of FileNo which I am showing on a Form frmFile to change dependant on the value of the FileTypeID
i.e. If FileTypeID = 1 FlieNo should start at 100
If FileTypeID = 2 FileNo should start at 200
If FileTypeID = 3 FileNo should start at 300
Then when I create a new record I need to increment by 1 the value of FileNo according to the FileTypeID

I've setup a button and attached this code to its onclick event but it only works as long as I don't change the FileTypeID

Dim B As Integer
Dim H As Integer
Dim N As Integer

Do While FileID > 0
  DoCmd.GoToRecord,,acPrevious
  If FileTypeID = 1 Then
    B = FileNo
  Else
   If FileTypeID = 2 Then
    H = FileNo
  Else
    If FileTypeID = 3 Then
    N = FileNo
    End If
   End If
  End If
Loop

DoCmd.GoToRecord,,acLast
If FileType = 1 Then
 FileNumber = B + 1
Else
 If FileType = 2 Then
 FileNumber = H + 1
 Else
  If FileType = 3 Then
   FileNumber = N + 1
  End If
 End If
End If

ViRi

 

[neileg]

You can use DMax() to return the largest existing number that matches your criteria and then add 1 to that.

 

[ViRi]

Fields on different tables

Hi

Thanks for your interest the problem with using DMax() in this example is that the field criteria upon which the FileNo field value should change is not in the same domain i.e. table. The FileNo field is in tblFile and the FileTypeID field is in tblFileType so they can't be used with DMax().

Please correct me if I am wrong

ViRi

 

[neileg]

Aren't you holding the FileTypeID in tblFile? How do you know which type the file is? I was assuming that you were using tblFileType to drive a combo box on your data entry form so the user would select the file type, and this would be saved in tblFile. Please explain. I still think Dmax() is your answer, but how you implement it will depend on what you are storing and where.

 

[ViRi]

Hi

I'm holding FileType in tblFileType but I suppose I can change this to tblFile this way I can use DMax() as you suggested.

I'm gonna try this now

Thanks
ViRi

 

[Pat Hartman]

Numbering schemes similar to the one you are describing always fail sooner or later. What will happen if you need more than 100 numbers for any particular file type? What will happen if you need more than 9 file types? At a minimum, the file type should be separate from the sequence number. That will give you more flexibility. You can concatenate the two fields for display if you like.