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

Automatic entry generation

P: 33
Hi,

I would like to have a form where a filing number is assigned to a new document being entered into the system via a form. The filing number will combine 4 fields to create a number. Using the alphameric system would be ideal so short froms would be required from the first field ex. High lake= HL followed by a number being assisgned to a file type rom teh file type field and then a dash followed by a folder number and a letter for a sub folder.
I hope this made sense. I have little coding experience and I am not even sure where to start so any assistance at all would be really really appreciated.

Thank you
Jul 24 '07 #1
Share this Question
Share on Google+
42 Replies


ADezii
Expert 5K+
P: 8,607
Hi,

I would like to have a form where a filing number is assigned to a new document being entered into the system via a form. The filing number will combine 4 fields to create a number. Using the alphameric system would be ideal so short froms would be required from the first field ex. High lake= HL followed by a number being assisgned to a file type rom teh file type field and then a dash followed by a folder number and a letter for a sub folder.
I hope this made sense. I have little coding experience and I am not even sure where to start so any assistance at all would be really really appreciated.

Thank you
We need much more information on these 4 Fields which will comprise a File Number for a New Document:
  1. Field Names
  2. Field Data Types
  3. Sample Field data for each of the 4 Fields
  4. Sequence in which Field Values are to be combined
  5. Once the values from these 4 Fields are combined to create a File Number, are you positive that this new File Number will be Unique?
  6. Will the File Number be the Primry Key for the Table?
  7. What will the Name for for the File Number Field?
  8. etc...........
Jul 24 '07 #2

P: 33
We need much more information on these 4 Fields which will comprise a File Number for a New Document:
  1. Field Names
  2. Field Data Types
  3. Sample Field data for each of the 4 Fields
  4. Sequence in which Field Values are to be combined
  5. Once the values from these 4 Fields are combined to create a File Number, are you positive that this new File Number will be Unique?
  6. Will the File Number be the Primry Key for the Table?
  7. What will the Name for for the File Number Field?
  8. etc...........
Ok thanks for the response. Here is the rest of the info you requested:

1)PROPERTY, FILE TYPE, MAIN FILE NAME AND SUB FILE NAME

2)PROPERTY and FILE TYPE are combo boxes, MAIN and SUB SILE NAME are text boxes.

3)How would you like this???

4)Yes it will need to be unique and with this new file numbering system each will be unique. One other thing will be that each new file added will need to have the next corresponding number associated.(hope that makes sense) It is just so that when a new file is added, the proper unique filing number will be assigned to it automatically depending on which values are chosen from teh previous 4 fields and the next corresponding filenumber number.

5)Yes the file number will be unique

6) the name of the new field will be FILE NUMBER

I really appreciate your time and effort and I do not expect this whole thing to be written for me. My problem is I am not good with teh coding aspect I understand the logic behind it all (for the most part) I am basically just looking for any assistance anyone could give whether it is code, tips or direction.

Thank you again
Jul 24 '07 #3

ADezii
Expert 5K+
P: 8,607
Ok thanks for the response. Here is the rest of the info you requested:

1)PROPERTY, FILE TYPE, MAIN FILE NAME AND SUB FILE NAME

2)PROPERTY and FILE TYPE are combo boxes, MAIN and SUB SILE NAME are text boxes.

3)How would you like this???

4)Yes it will need to be unique and with this new file numbering system each will be unique. One other thing will be that each new file added will need to have the next corresponding number associated.(hope that makes sense) It is just so that when a new file is added, the proper unique filing number will be assigned to it automatically depending on which values are chosen from teh previous 4 fields and the next corresponding filenumber number.

5)Yes the file number will be unique

6) the name of the new field will be FILE NUMBER

I really appreciate your time and effort and I do not expect this whole thing to be written for me. My problem is I am not good with teh coding aspect I understand the logic behind it all (for the most part) I am basically just looking for any assistance anyone could give whether it is code, tips or direction.

Thank you again
This would be the basic logic and should be contained in the BeforeUpdate() Event of the Form. I'm not exactly sure how you want the next corresponding number to be generated and where it should be placed in this sequence. Get back to me on this, if you wish to.
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![cboPROPERTY]) And Not IsNull(Me![cboFILE TYPE]) And Not IsNull(Me![txtMAIN]) And Not IsNull(Me![txtSUB SILE NUMBER]) Then
  7.     strFileNum = Me![cboPROPERTY] & Me![cboFILE TYPE] & Me![txtMAIN] & Me![txtSUB SILE NUMBER]      'concatenate the 4 entries
  8.       'Write this value to the FILE NUMBER Field
  9.       Me![FILE NUMBER] = strFileNum
  10.   Else     '1 or more Fields contain no value - cannot do!
  11.     Cancel = True
  12.   End If
  13. Else
  14.   End If
  15. End Sub
Jul 24 '07 #4

P: 33
This would be the basic logic and should be contained in the BeforeUpdate() Event of the Form. I'm not exactly sure how you want the next corresponding number to be generated and where it should be placed in this sequence. Get back to me on this, if you wish to.
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![cboPROPERTY]) And Not IsNull(Me![cboFILE TYPE]) And Not IsNull(Me![txtMAIN]) And Not IsNull(Me![txtSUB SILE NUMBER]) Then
  7.     strFileNum = Me![cboPROPERTY] & Me![cboFILE TYPE] & Me![txtMAIN] & Me![txtSUB SILE NUMBER]      'concatenate the 4 entries
  8.       'Write this value to the FILE NUMBER Field
  9.       Me![FILE NUMBER] = strFileNum
  10.   Else     '1 or more Fields contain no value - cannot do!
  11.     Cancel = True
  12.   End If
  13. Else
  14.   End If
  15. End Sub

Thank you for your help I really appreciate it. This is such a great start. I have inserted it into the BeforeUpdate() Event of the Form and I understand how the code is now working. What would you suggest I do next?

As for the next number I think this will be the complicated part as because this is a filing program each new file entry needs to be placed in the proper order whether its within an already existing main folder/sub folder or whether its a new folder all together it needs to be placed in the correct location of the database. Does that make sense?

An example of how the file number should look is: HL01-01a
With the HL being the property, the 01 being the file type and the second 01 being the main file name and the a being the sub folder.

So lets say the next file is also from HL and it is of the same file type but a brand new folder then it should be HL01-02a.

I hope this helps to clear up any confusion. Again I am so thankful for this cause as you can see I am kinda stuck and although I am trying to teach myself it is a slow process and work needs to be donequicker than I can learn it lol. Anyways thanks again.
Jul 25 '07 #5

P: 33
Also I am getting an error when I try to create a new record saying:
Run-time error '2465':

Microsoft Office Access can't find the field 'cboPROPERTY' referred to in your expression.

Any idea why? My PROPERTY field is a combo box.

And in the code you have "And Not IsNull(Me![txtSUB SILE NUMBER])" Should this be "File" instead of "SILE"?

Tahnk you
Jul 25 '07 #6

ADezii
Expert 5K+
P: 8,607
Also I am getting an error when I try to create a new record saying:
Run-time error '2465':

Microsoft Office Access can't find the field 'cboPROPERTY' referred to in your expression.

Any idea why? My PROPERTY field is a combo box.

And in the code you have "And Not IsNull(Me![txtSUB SILE NUMBER])" Should this be "File" instead of "SILE"?

Tahnk you
Microsoft Office Access can't find the field 'cboPROPERTY' referred to in your expression
Substitute the correct Name of your Property Combo Box
And in the code you have "And Not IsNull(Me![txtSUB SILE NUMBER])" Should this be "File" instead of "SILE"?
I was going with your original Field Description

NOTE: We will not proceed any further until we get these little kinks ironed out.
Jul 25 '07 #7

P: 33
Ok Awesome got it to work. I thought I had to have the cbo or txt infront to declare whether it was a combo or text box. But no errors now. So if you would lik eto continue on now that would be great. I'm pumped now that I can see it starting to work out. Any more suggestions?

Thanks again
Jul 25 '07 #8

P: 33
I am trying to get it so that each value in the 4 fields has a unique code. For example High Lake= HL Ulu= UL File type Permit= 01 and Claims= 02. Is there an automated way to do thsi or do i have to do each one manually. Basically I need to abbreviate the property field, assign a number to each File Type, a number for each different Main File and a small letter for each Sub File.

Sorry is this is getting to be too much and I have been trying on my own but to no avail. Any assistance would be great.

Thank you
Jul 25 '07 #9

ADezii
Expert 5K+
P: 8,607
I am trying to get it so that each value in the 4 fields has a unique code. For example High Lake= HL Ulu= UL File type Permit= 01 and Claims= 02. Is there an automated way to do thsi or do i have to do each one manually. Basically I need to abbreviate the property field, assign a number to each File Type, a number for each different Main File and a small letter for each Sub File.

Sorry is this is getting to be too much and I have been trying on my own but to no avail. Any assistance would be great.

Thank you
If you want one or more Fields to have a Unique, Numeric, code you can create an AutoNumber Field, make it the Primary Key, and set its Format to 000. This would produce a sequence like: 001, 002, 003, 004, 005, etc. Is this what you want?
Jul 26 '07 #10

P: 33
If you want one or more Fields to have a Unique, Numeric, code you can create an AutoNumber Field, make it the Primary Key, and set its Format to 000. This would produce a sequence like: 001, 002, 003, 004, 005, etc. Is this what you want?
Well not exactly. I need it so that when another employee enters a file into the system via the form, the File Number field automatically gives it the proper file name whether its a brand new file all together or a sub file. So the file name needs to correspond with the info input into the form. Because its a filing system the file needs to be placed in proper spot within the database so that it can be located properly when needed. Does that make sense??? Its hard for me to explain and this seems very complicated to me to code but i'm a newbie so who knows. Anyways if you can help in anyway that would be great.

Thanks
Jul 26 '07 #11

P: 33
Do you think this is even possible??? I mean obviously it is but how difficult will it be???
Jul 26 '07 #12

ADezii
Expert 5K+
P: 8,607
Do you think this is even possible??? I mean obviously it is but how difficult will it be???
As previously described by you, a File Number such as (HL01-01a) consists of 4 distinct parts, the sum (concatenation) of each part will comprise a new, and unique File Number. The breakdown is as follows:
  1. HL - Property
  2. 01 - File Type
  3. 01 - Main File Name
  4. a - Sub-Folder

If all this is true, then you must describe exactly how, and under what circumstances, a new File Number will be generated based on the last File Number entry for the Last Record. Base all responses on a File Number of HL01-01a as the last entry in this Field.
  1. If the Property is the same, but the File Type is different, then will the next File Number will be HL02-01a?
  2. If the Property and File Type are the same,but the Main File Name is different, then the new File Number will be HL01-02a?
  3. If the Property, FileType , and Main File Name are the same, will the next File Number be HL01-01b? What happens when you reach z for the Sub-Folder?
  4. As you can plainly see, this Automatic File Numbering System can get a little complex.
Jul 26 '07 #13

P: 33
As previously described by you, a File Number such as (HL01-01a) consists of 4 distinct parts, the sum (concatenation) of each part will comprise a new, and unique File Number. The breakdown is as follows:
  1. HL - Property
  2. 01 - File Type
  3. 01 - Main File Name
  4. a - Sub-Folder

If all this is true, then you must describe exactly how, and under what circumstances, a new File Number will be generated based on the last File Number entry for the Last Record. Base all responses on a File Number of HL01-01a as the last entry in this Field.
  1. If the Property is the same, but the File Type is different, then will the next File Number will be HL02-01a?
  2. If the Property and File Type are the same,but the Main File Name is different, then the new File Number will be HL01-02a?
  3. If the Property, FileType , and Main File Name are the same, will the next File Number be HL01-01b? What happens when you reach z for the Sub-Folder?
  4. As you can plainly see, this Automatic File Numbering System can get a little complex.
Yes all the above are correct. As for when the sub folders reach zero, we cannot forsee that happening with sub folders. How ofter do u see a folder with 26 sub forlders. Sure it is possible but we don't think it will be a problem for us.

I can see how complex it can get and that is why I was wondering about the difficulty of this. Basically what I am looking for now is a way to automatically have the values changed to the abbrevation or numeric value and so on. Make sense?

As of now half the problem is solved with the new field being automatically populated with the values in the correct order.

Again any assistance would be great and thank you for your efforts.
Jul 27 '07 #14

ADezii
Expert 5K+
P: 8,607
Yes all the above are correct. As for when the sub folders reach zero, we cannot forsee that happening with sub folders. How ofter do u see a folder with 26 sub forlders. Sure it is possible but we don't think it will be a problem for us.

I can see how complex it can get and that is why I was wondering about the difficulty of this. Basically what I am looking for now is a way to automatically have the values changed to the abbrevation or numeric value and so on. Make sense?

As of now half the problem is solved with the new field being automatically populated with the values in the correct order.

Again any assistance would be great and thank you for your efforts.
Very busy at the moment, I'll get back to you as soon as I can.
Jul 28 '07 #15

ADezii
Expert 5K+
P: 8,607
Very busy at the moment, I'll get back to you as soon as I can.
The next logical stepin this process would be to retrieve the last [FILE NUMBER] entered into the Database, so that it may be compared to existing values on the Form, so that a new, sequential [FILE NUMBER] can be created. The solution is not that simple since [FILE NUMBER] is the Primary Key and by nature, Indexed. Any new [FILE NUMBER] added to the Table will be inserted into proper order (alphabetically) in the Table, so we can't simply extract the last value entered. An example will illustrate this point: if HL01-01a is the last [FILE NUMBER] and the new [FILE NUMBER] BA02-03b is added, HL01-01a will still remain the last entry in the Table. What we can do is the following:
  1. Create an AutoNumber Field called [File_ID].
  2. Extract the last [File_ID] from the Table which will represent the ID of the Last Record added.
  3. Lookup the [FILE NUMBER] that is in this last Record which will be the last [FILE NUMBER] entered.
  4. I've done this in a couple of steps for illustration purposes only, but it can be on a single line.
  5. Below, I've isolated the code to extract the last [FILE NUMBER] and then inserted it into the overall code block.
  6. Gool luck.
    Expand|Select|Wrap|Line Numbers
    1. Dim intLastID As Integer, strLastFileNum As String
    2.  
    3. intLastID = DLast("[File_ID]", "tblFileName")
    4. strLastFileNum = DLookup("[FILE NUMBER]", "tblFileName", "[File_ID]=" & intLastID)
    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![CBOproperty]) And Not IsNull(Me![cboFILE TYPE]) And Not IsNull(Me![txtMAIN]) And Not IsNull(Me![txtSUB FILE NUMBER]) Then
    7.     strFileNum = Me![CBOproperty] & Me![cboFILE TYPE] & "-" & Me![txtMAIN] & Me![txtSUB FILE NUMBER]      'concatenate the 4 entries
    8.       Dim intLastID As Integer, strLastFileNum As String
    9.       intLastID = DLast("[File_ID]", "tblFileName")
    10.       strLastFileNum = DLookup("[FILE NUMBER]", "tblFileName", "[File_ID]=" & intLastID)
    11.         'future processing here
    12.       'Write this value to the FILE NUMBER Field (Temporary)
    13.       Me![FILE NUMBER] = strFileNum
    14.   Else     '1 or more Fields contain no value - cannot do!
    15.     Cancel = True
    16.   End If
    17. Else
    18.   End If
    19. End Sub
Jul 28 '07 #16

P: 33
Hi,

Thank you very much for this. You have no idea how much i appreciate it. I entered the code like you mentioned but I ran into a couple errors that I am not sure about. I get an error msg "2001" saying "you have cancelled the previous operation". Here is my code that 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
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

As you can see these are the names of my fields. I changed where you had "tblFileName" to "ALL" because thats my table name that containg my data. Not sure if this is right as I am confused by what is meant by "tblFileName".

Also you comment within the code

" 'future processing here
'Write this value to the FILE NUMBER Field (Temporary)
Me![FILE NUMBER] = strFileNum"

This also confuses me a bit.


If you could please shed some light on thses problems that would be awesome. Once again thank you for your effort and time.
Jul 30 '07 #17

ADezii
Expert 5K+
P: 8,607
Hi,

Thank you very much for this. You have no idea how much i appreciate it. I entered the code like you mentioned but I ran into a couple errors that I am not sure about. I get an error msg "2001" saying "you have cancelled the previous operation". Here is my code that 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
  9.       intLastID = DLast("[File_ID]", "ALL")
  10.       strLastFileNum = DLookup("[FILE NUMBER]", "ALL", "[File_ID]=" & intLastID)
  11.         'future processing here
  12.       'Write this value to the FILE NUMBER Field (Temporary)
  13.       Me![FILE NUMBER] = strFileNum
  14.   Else     '1 or more Fields contain no value - cannot do!
  15.     Cancel = True
  16.   End If
  17. Else
  18.   End If
  19. End Sub
As you can see these are the names of my fields. I changed where you had "tblFileName" to "ALL" because thats my table name that containg my data. Not sure if this is right as I am confused by what is meant by "tblFileName".

Also you comment within the code

" 'future processing here
'Write this value to the FILE NUMBER Field (Temporary)
Me![FILE NUMBER] = strFileNum"

This also confuses me a bit.


If you could please shed some light on thses problems that would be awesome. Once again thank you for your effort and time.
ggtw:
First and foremost, it is imperative that you use Code Tags whenever you Post code for reference. Although the code is familiar to me, it would be very difficult for another Moderator/Expert to decipher it, even now I am having difficulty. I'll place the Code Tags in for now, but in the future it will be your responsibility to do so.
As you can see these are the names of my fields. I changed where you had "tblFileName" to "ALL" because thats my table name that containg my data. Not sure if this is right as I am confused by what is meant by "tblFileName".
It was correct for you to do this - tblFileName was for demonstration purposes only and is the Name of the Table that I test on. Since I had no idea what your Table Name was, this seemed like a logical substitution.

Also you comment within the code

" 'future processing here
'Write this value to the FILE NUMBER Field (Temporary)
Me![FILE NUMBER] = strFileNum"

This also confuses me a bit.
Comments are just that, Comments. They are not compiled and do not affect code execution. They are just lines of text to describe some process, comment out old code lines, provide supplemental information information, etc.

I entered the code like you mentioned but I ran into a couple errors that I am not sure about. I get an error msg "2001" saying "you have cancelled the previous operation".
You are probably getting this Error because in order to generate a FILE NUMBER you must have values in all 4 Fields namely: [Property], [File Type], [Main File Name], and [Sub-Folder]. If you do not have a value in ALL of these Fields, the FILE NUMBER will not be generated and the Update of the Record will be Cancelled as indicated in the Else Clause (Cancel = True). Hope this explains everything.
Jul 30 '07 #18

P: 33
I appologize for not putting in the code the right way. I was unsure how to do it and didn't know how much of an importance it is.

Thank you for your feed back and it did clear up some things. I am still having problems though and I cannot figure it out. I can fix that error I was getting before if I remove the underscore from the FILE_ID field. But when I do that I get a new error saying "Invalid use of Null". I have all the 4 fields filled in correctly. and when I debug it it says the "intLastID = DLast("[File ID]", "All")" line. Below is my code I currently have. If you have any ideas on what i can do or need to do to fix this I would be very thankful.

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
  9.       intLastID = DLast("[File ID]", "All")
  10.       strLastFileNum = DLookup("[FILE NUMBER]", "All", "[File ID]=" & intLastID)
  11.         'future processing here
  12.       'Write this value to the FILE NUMBER Field (Temporary)
  13.       Me![FILE NUMBER] = strFileNum
  14.   Else     '1 or more Fields contain no value - cannot do!
  15.     Cancel = True
  16.   End If
  17. Else
  18.   End If
  19. End Sub
  20.  
Thank you
Jul 30 '07 #19

ADezii
Expert 5K+
P: 8,607
I appologize for not putting in the code the right way. I was unsure how to do it and didn't know how much of an importance it is.

Thank you for your feed back and it did clear up some things. I am still having problems though and I cannot figure it out. I can fix that error I was getting before if I remove the underscore from the FILE_ID field. But when I do that I get a new error saying "Invalid use of Null". I have all the 4 fields filled in correctly. and when I debug it it says the "intLastID = DLast("[File ID]", "All")" line. Below is my code I currently have. If you have any ideas on what i can do or need to do to fix this I would be very thankful.

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
  9.       intLastID = DLast("[File ID]", "All")
  10.       strLastFileNum = DLookup("[FILE NUMBER]", "All", "[File ID]=" & intLastID)
  11.         'future processing here
  12.       'Write this value to the FILE NUMBER Field (Temporary)
  13.       Me![FILE NUMBER] = strFileNum
  14.   Else     '1 or more Fields contain no value - cannot do!
  15.     Cancel = True
  16.   End If
  17. Else
  18.   End If
  19. End Sub
  20.  
Thank you
Is your Field named [File ID] or [File_ID]?
Jul 30 '07 #20

P: 33
The field ID is "FILE ID"
Jul 30 '07 #21

P: 33
ADezii,

I realize your probably incredibly busy, I was just wondering if you recieved my reply to your last post?
Jul 31 '07 #22

ADezii
Expert 5K+
P: 8,607
ADezii,

I realize your probably incredibly busy, I was just wondering if you recieved my reply to your last post?
You are correct on both counts. The only reason that I can see for the Null Error is that there are no Records in the ALL Table, or that Records exist and there is missing a [FILE ID] Field which should definately not be the case since it is an AutoNumber Field and the Required Property should be set to Yes.
Jul 31 '07 #23

P: 33
Well the table is definitely not blank but the file id field is. I already have an ID field that is autonumbered so could that have anything to do with the error?
Jul 31 '07 #24

ADezii
Expert 5K+
P: 8,607
Well the table is definitely not blank but the file id field is. I already have an ID field that is autonumbered so could that have anything to do with the error?
could that have anything to do with the error?
Definately. If the [File ID] Field is blank, it contains the value Null and when you try to assign the return value of DLast("[File ID]", "ALL") to a Variable that is declared as Integer, you will get an Error because the only Data Type that can contain a Null Value is Variant.
Jul 31 '07 #25

P: 33
Ok so now what do I do to fix that? I tried changing the field name in the code from Field ID to ID (which is the name of my autonumber field) but that didn't help. Any ideas?
Jul 31 '07 #26

ADezii
Expert 5K+
P: 8,607
Ok so now what do I do to fix that? I tried changing the field name in the code from Field ID to ID (which is the name of my autonumber field) but that didn't help. Any ideas?
I do not understand, why would the [ID] AutoNumber Field NOT have a value in it?
Jul 31 '07 #27

P: 33
The ID field does have numbers in it and it is my auto number field. The new field that has been created by you in the code called "File ID" is not populated it is blank. So I don't need the FILE ID field if I already have a working autonumber field (ID) right?
Aug 1 '07 #28

ADezii
Expert 5K+
P: 8,607
The ID field does have numbers in it and it is my auto number field. The new field that has been created by you in the code called "File ID" is not populated it is blank. So I don't need the FILE ID field if I already have a working autonumber field (ID) right?
That is correct, you don't need the [File ID] Field if you already have an AutoNumber Field named [ID].
Aug 1 '07 #29

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

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
  9.       intLastID = DLast("[File ID]", "All")
  10.       strLastFileNum = DLookup("[FILE NUMBER]", "All", "[File ID]=" & intLastID)
  11.         'future processing here
  12.       'Write this value to the FILE NUMBER Field (Temporary)
  13.       Me![FILE NUMBER] = strFileNum
  14.   Else     '1 or more Fields contain no value - cannot do!
  15.     Cancel = True
  16.   End If
  17. Else
  18.   End If
  19. End Sub
  20.  
Thank you for all your time and effort
Aug 1 '07 #30

ADezii
Expert 5K+
P: 8,607
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:

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
  9.       intLastID = DLast("[File ID]", "All")
  10.       strLastFileNum = DLookup("[FILE NUMBER]", "All", "[File ID]=" & intLastID)
  11.         'future processing here
  12.       'Write this value to the FILE NUMBER Field (Temporary)
  13.       Me![FILE NUMBER] = strFileNum
  14.   Else     '1 or more Fields contain no value - cannot do!
  15.     Cancel = True
  16.   End If
  17. Else
  18.   End If
  19. End Sub
  20.  
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:
Expand|Select|Wrap|Line Numbers
  1. 'Inserted in code block below
  2. Dim intLastID As Integer, strLastFileNum As String, strLastProperty As String, strLastFileType As String
  3. Dim strLastMainFileName As String, strLastSubFileName As String
  4. intLastID = DLast("[File ID]", "ALL")
  5. strLastFileNum = DLookup("[FILE NUMBER]", "ALL", "[File ID]=" & intLastID)
  6. strLastProperty = Left$(strLastFileNum, 2)
  7. strLastFileType = Mid$(strLastFileNum, 3, 2)
  8. strLastMainFileName = Mid$(strLastFileNum, 6, 2)
  9. strLastSubFileName = Right$(strLastFileNum, 1)
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.  
Aug 1 '07 #31

P: 33
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_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("[ID]", "ALL")
strLastFileNum = DLookup("[FILE NUMBER]", "ALL", "[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
Aug 2 '07 #32

P: 33
Adezii? Any ideas???
Aug 13 '07 #33

ADezii
Expert 5K+
P: 8,607
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.
Aug 13 '07 #34

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

ADezii
Expert 5K+
P: 8,607
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.
Aug 14 '07 #36

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

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.     strFileNumber = 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("[ID]", "ALL")
  11.     strLastFileNumber = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)
  12.     strLastProperty = Left$(strLastFileNumber, 2)
  13.     strLastFileType = Mid$(strLastFileNumber, 3, 2)
  14.     strLastMainFileName = Mid$(strLastFileNumber, 6, 2)
  15.     strLastSubFileName = Right$(strLastFileNumber, 1)        'future processing here
  16.     'Write this value to the FILE NUMBER Field (Temporary)
  17.     Me![FILE NUMBER] = strFileNumber
  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.  

Thanks again I really appreciate it.
Aug 14 '07 #37

ADezii
Expert 5K+
P: 8,607
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.

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.     strFileNumber = 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("[ID]", "ALL")
  11.     strLastFileNumber = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)
  12.     strLastProperty = Left$(strLastFileNumber, 2)
  13.     strLastFileType = Mid$(strLastFileNumber, 3, 2)
  14.     strLastMainFileName = Mid$(strLastFileNumber, 6, 2)
  15.     strLastSubFileName = Right$(strLastFileNumber, 1)        'future processing here
  16.     'Write this value to the FILE NUMBER Field (Temporary)
  17.     Me![FILE NUMBER] = strFileNumber
  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.  

Thanks again I really appreciate it.
Try typing this in the Immediate Window or anywhere, wherever appropriate. What response do you get?
Expand|Select|Wrap|Line Numbers
  1. 'Must open the Debug Window (CTRL+G) to see the result
  2. Debug.Print DLast("[ID]", "ALL")
Aug 14 '07 #38

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

ADezii
Expert 5K+
P: 8,607
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.
Expand|Select|Wrap|Line Numbers
  1. strLastFileNumber = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)
Is there a File Number associated with ID 1058?
Aug 14 '07 #40

P: 33
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.
Expand|Select|Wrap|Line Numbers
  1. strLastFileNumber = DLookup("[FILE NUMBER]", "ALL", "[ID]=" & intLastID)
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.
Aug 17 '07 #41

P: 33
Any other ideas ADezii???
Aug 20 '07 #42

ADezii
Expert 5K+
P: 8,607
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.
Aug 20 '07 #43

Post your reply

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