Generating an alpha-numeric key

[ggtw]

We are trying to create a filing system at work so that when a new file folder is created a form is filled out via MS Access and all the files will be tracked through these entries. Hes my problem I need a file number created for each file that is created via the form in Access. The file number needs to be alphanumeric (ex. HL01-01a) with the first 2 letters being an abbreviation of the value selected in the property field (hope that makes sense).

4 fields will combine to create the file number (in this order):
Property: HL
File type: 01
Main file name: 01
Sub file name: a

The problem I am having is having the values that make up the file number change depending on what value is selected from the property field and the file type field. And having the Main file and sub file numbers auto roll.

Thanks to ADezii I have it so the file number field gets populated with the right values in the proper order so thats awesome. But it needs to be in the format stated above if its at all possible. Below is the code I have right now.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strFileNum As String
 
If Me.NewRecord Then        'is this a New Record
  'All 4 Fields must contain values in order to generate FILE NUMBER
  If Not IsNull(Me![PROPERTY]) And Not IsNull(Me![FILE TYPE]) And Not IsNull(Me![MAIN FILE NAME]) And Not IsNull(Me![SUB FILES NAME]) Then
    strFileNum = Me![PROPERTY] & Me![FILE TYPE] & "-" & Me![MAIN FILE NAME] & Me![SUB FILES NAME]      'concatenate the 4 entries
    Dim intLastID As Integer, strLastFileNum As String, strLastProperty As   String, strLastFileType As String
    Dim strLastMainFileName As String, strLastSubFileName As String
    intLastID = DLast("[File ID]", "ALL")
    strLastFileNum = DLookup("[FILE NUMBER]", "ALL", "[File ID]=" & intLastID)
    strLastProperty = Left$(strLastFileNum, 2)
    strLastFileType = Mid$(strLastFileNum, 3, 2)
    strLastMainFileName = Mid$(strLastFileNum, 6, 2)
    strLastSubFileName = Right$(strLastFileNum, 1)        'future processing here
    'Write this value to the FILE NUMBER Field (Temporary)
    Me![FILE NUMBER] = strFileNum
  Else     '1 or more Fields contain no value - cannot do!
    Cancel = True
  End If
Else
  End If
End Sub

I hope I made this clear enough and if anyone can help in anyway I would really appreciate it.

[JKing]

Please remember to provide a meaningful Title for any threads started (Please Use Appropriate Titles for New Threads!).

This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions.

MODERATOR.

[ggtw]

Ok sorry I appologize

[ggtw]

Any Help? I'm really stuck

[JKing]

With your current code I see you are concatenating the 4 pieces into one string. What format are you currently producing and how exactly do you need it changed? Are any pieces currently correct?

[ggtw]

With your current code I see you are concatenating the 4 pieces into one string. What format are you currently producing and how exactly do you need it changed? Are any pieces currently correct?

Well right now the values selected/entered are what is being put into teh FILE NUMBER field.
ex. HIGHLAKEPERMITW OLFDENRESOURCES INCWOLFDENRESOU RCESINC

So the problem I am having is I need it to go from that to HL(for High Lake) 01(for Permit) -01(Main File name) a(sub file name)

I should also add I am getting an error message saying invalid use of null for the
"strLastFil eNum = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)" line.

Thank you for your response

[JKing]

I guess the big question is now whether you have abbreviations/codes stored somewhere that you can easily lookup to put the string together? Otherwise this could be a very difficult task.

[ggtw]

I guess the big question is now whether you have abbreviations/codes stored somewhere that you can easily lookup to put the string together? Otherwise this could be a very difficult task.

Well no i would need to create them....Thats where i'm having problems would i do each one individually like Permit= 01 Report= 02 etc or what would be the best way?

[ggtw]

Well no i would need to create them....Thats where i'm having problems would i do each one individually like Permit= 01 Report= 02 etc or what would be the best way? All the values for the File type field and property field are in combo boxes. Does this answer yoru question???