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
- 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