By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Generating an alpha-numeric key

P: 33
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim strFileNum As String
  3.  
  4. If Me.NewRecord Then        'is this a New Record
  5.   'All 4 Fields must contain values in order to generate FILE NUMBER
  6.   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
  7.     strFileNum = Me![PROPERTY] & Me![FILE TYPE] & "-" & Me![MAIN FILE NAME] & Me![SUB FILES NAME]      'concatenate the 4 entries
  8.     Dim intLastID As Integer, strLastFileNum As String, strLastProperty As   String, strLastFileType As String
  9.     Dim strLastMainFileName As String, strLastSubFileName As String
  10.     intLastID = DLast("[File ID]", "ALL")
  11.     strLastFileNum = DLookup("[FILE NUMBER]", "ALL", "[File ID]=" & intLastID)
  12.     strLastProperty = Left$(strLastFileNum, 2)
  13.     strLastFileType = Mid$(strLastFileNum, 3, 2)
  14.     strLastMainFileName = Mid$(strLastFileNum, 6, 2)
  15.     strLastSubFileName = Right$(strLastFileNum, 1)        'future processing here
  16.     'Write this value to the FILE NUMBER Field (Temporary)
  17.     Me![FILE NUMBER] = strFileNum
  18.   Else     '1 or more Fields contain no value - cannot do!
  19.     Cancel = True
  20.   End If
  21. Else
  22.   End If
  23. End Sub
  24.  
I hope I made this clear enough and if anyone can help in anyway I would really appreciate it.
Aug 7 '07 #1
Share this Question
Share on Google+
8 Replies


JKing
Expert 100+
P: 1,206
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.
Aug 7 '07 #2

P: 33
Ok sorry I appologize
Aug 7 '07 #3

P: 33
Any Help? I'm really stuck
Aug 8 '07 #4

JKing
Expert 100+
P: 1,206
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?
Aug 8 '07 #5

P: 33
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. HIGHLAKEPERMITWOLFDENRESOURCESINCWOLFDENRESOURCESI NC

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
"strLastFileNum = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)" line.

Thank you for your response
Aug 8 '07 #6

JKing
Expert 100+
P: 1,206
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.
Aug 8 '07 #7

P: 33
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?
Aug 8 '07 #8

P: 33
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???
Aug 8 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.