Automatic entry generation

**ADezii** · Aug 1 '07, 11:36 PM

Originally posted by ggtw

Ok so now we're back to just having the File Number field being populated with the values entered into the 4 fields. So now I need to find a way to have the values converted into the proper format needed to create the file number we're looking for. If you can help me in any way I would really appreciate it.

Here is what my code currently looks like:

Code:

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
      intLastID = DLast("[File ID]", "All")
      strLastFileNum = DLookup("[FILE NUMBER]", "All", "[File ID]=" & intLastID)
        '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

Thank you for all your time and effort

It's time to extract information about the last FILE NUMBER in order to compare against the current values on the Form:
[CODE=vb]
'Inserted in code block below
Dim intLastID As Integer, strLastFileNum As String, strLastProperty As String, strLastFileType As String
Dim strLastMainFile Name As String, strLastSubFileN ame As String
intLastID = DLast("[File ID]", "ALL")
strLastFileNum = DLookup("[FILE NUMBER]", "ALL", "[File ID]=" & intLastID)
strLastProperty = Left$(strLastFi leNum, 2)
strLastFileType = Mid$(strLastFil eNum, 3, 2)
strLastMainFile Name = Mid$(strLastFil eNum, 6, 2)
strLastSubFileN ame = Right$(strLastF ileNum, 1)[/CODE]

Code:

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

**ggtw** · Aug 2 '07, 04:05 PM

That awesome thanks a lot. Although i keep getting an error still about invalid use of null. And when i debug it says it is the line below that is in bold. Any ideas?

[CODE}
Private Sub Form_BeforeUpda te(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 strLastMainFile Name As String, strLastSubFileN ame As String
intLastID = DLast("[ID]", "ALL")
strLastFileNum = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)
strLastProperty = Left$(strLastFi leNum, 2)
strLastFileType = Mid$(strLastFil eNum, 3, 2)
strLastMainFile Name = Mid$(strLastFil eNum, 6, 2)
strLastSubFileN ame = Right$(strLastF ileNum, 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

**ggtw** · Aug 13 '07, 01:45 PM

Adezii? Any ideas???

**ADezii** · Aug 13 '07, 03:05 PM

Originally posted by ggtw

Adezii? Any ideas???

strLastFileNum = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)

That would probably mean that no File Number exists in the ALL Table for the specified ID, or File Number = Null. Null is being assigned to strLastFileNum which cannot acceot it since it is declared as String and only Variants can hold the value NULL. [FILE NUMBER] shouold be a Required Field and should never be NULL.

**ggtw** · Aug 14 '07, 01:41 PM

Thanks for the reply. I'm so lost now tho. I think I know what your saying, and I have made the File Number field required but no wthe file number field just has #Name? in each one. Any ideas?

**ADezii** · Aug 14 '07, 04:20 PM

Originally posted by ggtw

Thanks for the reply. I'm so lost now tho. I think I know what your saying, and I have made the File Number field required but no wthe file number field just has #Name? in each one. Any ideas?

#Name usually means that you are referring to the wrong Field, click on the Properties for the Text Box, Data Tab, Control Source, make sure you have the File Number Field spelled correctly.

**ggtw** · Aug 14 '07, 06:27 PM

Originally posted by ADezii

#Name usually means that you are referring to the wrong Field, click on the Properties for the Text Box, Data Tab, Control Source, make sure you have the File Number Field spelled correctly.

Ok awesome I got that fixed up. But I am still getting the "invalid use of null" error. Evrything is filled in correctly wheich to me means that maybe something in the code is incorrect. I have pasted it below if you wouldn't mind looking it over and seeing if anything is wrong or needs to be changed.

Code:

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
    strFileNumber = 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("[ID]", "ALL")
    strLastFileNumber = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)
    strLastProperty = Left$(strLastFileNumber, 2)
    strLastFileType = Mid$(strLastFileNumber, 3, 2)
    strLastMainFileName = Mid$(strLastFileNumber, 6, 2)
    strLastSubFileName = Right$(strLastFileNumber, 1)        'future processing here
    'Write this value to the FILE NUMBER Field (Temporary)
    Me![FILE NUMBER] = strFileNumber
  Else     '1 or more Fields contain no value - cannot do!
    Cancel = True
  End If
Else
  End If
End Sub

Thanks again I really appreciate it.

**ADezii** · Aug 14 '07, 07:27 PM

Originally posted by ggtw

Ok awesome I got that fixed up. But I am still getting the "invalid use of null" error. Evrything is filled in correctly wheich to me means that maybe something in the code is incorrect. I have pasted it below if you wouldn't mind looking it over and seeing if anything is wrong or needs to be changed.

Code:

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
    strFileNumber = 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("[ID]", "ALL")
    strLastFileNumber = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)
    strLastProperty = Left$(strLastFileNumber, 2)
    strLastFileType = Mid$(strLastFileNumber, 3, 2)
    strLastMainFileName = Mid$(strLastFileNumber, 6, 2)
    strLastSubFileName = Right$(strLastFileNumber, 1)        'future processing here
    'Write this value to the FILE NUMBER Field (Temporary)
    Me![FILE NUMBER] = strFileNumber
  Else     '1 or more Fields contain no value - cannot do!
    Cancel = True
  End If
Else
  End If
End Sub

Thanks again I really appreciate it.

Try typing this in the Immediate Window or anywhere, wherever appropriate. What response do you get?[CODE=vb]
'Must open the Debug Window (CTRL+G) to see the result
Debug.Print DLast("[ID]", "ALL")[/CODE]

**ggtw** · Aug 14 '07, 07:46 PM

I get "1058" that gets printed in the next line. Is this correct? I opened the immediate window and pasted that into it. I tried to run the form again but I still get the same error. did i do it right? any other ideas?

**ADezii** · Aug 14 '07, 11:14 PM

Originally posted by ggtw

I get "1058" that gets printed in the next line. Is this correct? I opened the immediate window and pasted that into it. I tried to run the form again but I still get the same error. did i do it right? any other ideas?

It seems as though the Error is happening in this Line, which would indicated that there is no File Number for the specified ID, which should not occur, or that there is an Error in the spelling of the [FILE NUMBER] Field.
[CODE=vb]strLastFileNumb er = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)[/CODE]
Is there a File Number associated with ID 1058?

**ggtw** · Aug 17 '07, 01:42 PM

Originally posted by ADezii

It seems as though the Error is happening in this Line, which would indicated that there is no File Number for the specified ID, which should not occur, or that there is an Error in the spelling of the [FILE NUMBER] Field.
[CODE=vb]strLastFileNumb er = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)[/CODE]
Is there a File Number associated with ID 1058?

No file number with 1058 or with any other ID. and when I create a new file the ID jumps to 1074. I'm completely stuck with no idea what to do.

**ggtw** · Aug 20 '07, 01:58 PM

Any other ideas ADezii???

**ADezii** · Aug 20 '07, 07:53 PM

Originally posted by ggtw

Any other ideas ADezii???

Manually enter a File Number for the last Record. As previously stated, the [FILE NUMBER] Fiedl should have its Required Property set to Yes, it should be Indexed with No Duplicates. Enter a File Number such as HL01-01a, then enter a New Record with HL for the 1st 2 characters, the other characters/numbers are irrelevant, and see if the code then generates a New File Number for you.

Automatic entry generation

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment