If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Field on Form = next record
Oh and I should also mention they DO need to write the project number
down-it's given out to all the vendors on the project and how the project is tracked for the rest of the year. Typically as soon as the number gets assinged to them they wrote it on the folder. Thanks do much for all the help. |
#22
|
|||
|
|||
Field on Form = next record
I don't know if this is why there is always a leading zero kept when I
do the: =Last([ProjectID])+1 in the control source but also in the text box under FORMAT it is: 00000 and under INPUT MASK it says: 99999;;* I don't know if either of those help to make the zero stay leading but I figured I'd mention them. Thanks again. |
#23
|
|||
|
|||
Field on Form = next record
Well I spoke too soon - it actually does not write the correct project
number: now for each one I get 6016 the next number should be 06019 I just keep putting the zero's in manually at the front. Kim Webb wrote: You're correct it did write the value after I closed it. The problem I had was that the projects are two digit year codes then three digits - ie 98001 was the first project we did in 1998 so the DMax found a 99 project as the maximum and added one to that. I change the formula to DLast and it's very close but it writes it as 6013 instead of 06013- any idea how I remedy that? Me.ProjectID = Nz(DLast("ProjectID","Projects"), 0) +1 Thanks very, very much for your help with this. |
#24
|
|||
|
|||
Field on Form = next record
Did you use both lines? The strYear line should preserve
the leading zero. And, Yes, having a format of 00000 will display the leading zero whether it is stored in the table or not. Check the table ***without using a format for the field*** to see if the leading zero is really there or not. If it isn't there, my DMax expression will not work. I really need to know this before you can expect to arrive at a solution. You are being sidetracked by the DLast experiment. There is no guarantee that it will retrieve the largest number in the table. Sometimes it will, but other times it can retrieve any old value ftom the field. If you need to see the number before the record is saved and if you might have multiple users performing this activity, then this whole concept goes out the window and it would require some really advanced programming and table manipulations. -- Marsh MVP [MS Access] Kim Webb wrote: Since you are combining two values to make the ProjectID, it should be stored in two separate fields. You can always put them together in your forms or reports so the users won't know how the table keeps the values. Actually you may already have a project date field that already contains the year information. Well I'm not combining two values the value of ProjectID is just 95234, 99765, 01987, 06111- I've always just calculated. Back to your specific question. For the leading zero to be saved in the table, the ProjectID field must be a TEXT type field. You should confirm that before making any more changes. It is a text type. If it is a text field, then the DMax (not DLast) could be: strYear = Format(Date, "yy") Me.ProjectID = strYear & Nz(DMax("Val(Right(ProjectID, 3))","Projects", "Left(ProjectID,2)='" & strYear & "'"), 0) +1 This does not work since my last project is 06010 and my next one assigned needs to be 06011. It gives me 6011 but I just need to add something to the formula below to get the zero to be the lead number: Me.ProjectID = Nz(DLast("ProjectID","Projects"), 0) +1 What's wierd is that if I use =Last([ProjectID])+1 It does give me the leading zero |
#25
|
|||
|
|||
Field on Form = next record
Marshall Barton wrote: Did you use both lines? The strYear line should preserve the leading zero. yes i used both lines but how does it know what the latest project number is to begin with. And, Yes, having a format of 00000 will display the leading zero whether it is stored in the table or not. Check the table ***without using a format for the field*** to see if the leading zero is really there or not. If it isn't there, my DMax expression will not work. I really need to know this before you can expect to arrive at a solution. if i look at field projectID in the table it shows the leading zero for all the projects after 1999. You are being sidetracked by the DLast experiment. There is no guarantee that it will retrieve the largest number in the table. Sometimes it will, but other times it can retrieve any old value ftom the field. but Dmax doesn't work because 99888 is a larger number than 06010 so it will always find the biggest number. Also this is a text field not a number field. If you need to see the number before the record is saved and if you might have multiple users performing this activity, then this whole concept goes out the window and it would require some really advanced programming and table manipulations. I don't need to see the number - I can also just have another field that shows them what it'll be using =Last([ProjectID])+1 |
#26
|
|||
|
|||
Field on Form = next record
The DMax call I used:
DMax("Val(Right(ProjectID,3))","Projects", "Left(ProjectID,2)='" & strYear & "'") retrieves the highest numeric value of the right most three digits where the first two characters match the current year (with possible leading zero). In your example where you said "99888 is a larger number than 06010", it doesn't matter because the part "Left(ProjectID,2)='" & strYear & "'" forces the max to only look at projects that start with 06. Then the value returned by "Val(Right(ProjectID,3))" is just the 10 in 06010. Ahhh, I just realized I did not specify the declaration for strYear and if that's not correct, it might cause the leading zero to be dropped. I also forgot to force the zeros in front of the right three digits. The code should have been: Dim strYear As String strYear = Format(Date, "yy") Me.ProjectID = strYear & _ Format(Nz(DMax("Val(Right(ProjectID,3))", _ "Projects", "Left(ProjectID,2)='" & strYear & _ "'"), 0) +1, "000") Sorry if my mistakes have caused you any grief. -- Marsh MVP [MS Access] Kim Webb wrote: Marshall Barton wrote: Did you use both lines? The strYear line should preserve the leading zero. yes i used both lines but how does it know what the latest project number is to begin with. And, Yes, having a format of 00000 will display the leading zero whether it is stored in the table or not. Check the table ***without using a format for the field*** to see if the leading zero is really there or not. If it isn't there, my DMax expression will not work. I really need to know this before you can expect to arrive at a solution. if i look at field projectID in the table it shows the leading zero for all the projects after 1999. You are being sidetracked by the DLast experiment. There is no guarantee that it will retrieve the largest number in the table. Sometimes it will, but other times it can retrieve any old value ftom the field. but Dmax doesn't work because 99888 is a larger number than 06010 so it will always find the biggest number. Also this is a text field not a number field. If you need to see the number before the record is saved and if you might have multiple users performing this activity, then this whole concept goes out the window and it would require some really advanced programming and table manipulations. I don't need to see the number - I can also just have another field that shows them what it'll be using =Last([ProjectID])+1 |
#27
|
|||
|
|||
Field on Form = next record
I used this:
Dim strYear As String strYear = Format(Date, "yy") Me.ProjectID = strYear & _ Format(Nz(DMax("Val(Right(ProjectID,3))", _ "Projects", "Left(ProjectID,2)='" & strYear & _ "'"), 0) +1, "000") And it worked - many thanks for all the help. I have a similar issue with the INVOICE form and I'm wondering if BeforeUpdate assigns the number when I save the record where can I put that command so it saves the record ONLY if the number is already blank? Can I add an IF/THEN to this statement somehow. Private Sub Description_BeforeUpdate(Cancel As Integer) Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1 End Sub Thanks. Marshall Barton wrote: The DMax call I used: DMax("Val(Right(ProjectID,3))","Projects", "Left(ProjectID,2)='" & strYear & "'") retrieves the highest numeric value of the right most three digits where the first two characters match the current year (with possible leading zero). In your example where you said "99888 is a larger number than 06010", it doesn't matter because the part "Left(ProjectID,2)='" & strYear & "'" forces the max to only look at projects that start with 06. Then the value returned by "Val(Right(ProjectID,3))" is just the 10 in 06010. Ahhh, I just realized I did not specify the declaration for strYear and if that's not correct, it might cause the leading zero to be dropped. I also forgot to force the zeros in front of the right three digits. The code should have been: Dim strYear As String strYear = Format(Date, "yy") Me.ProjectID = strYear & _ Format(Nz(DMax("Val(Right(ProjectID,3))", _ "Projects", "Left(ProjectID,2)='" & strYear & _ "'"), 0) +1, "000") Sorry if my mistakes have caused you any grief. -- Marsh MVP [MS Access] Kim Webb wrote: Marshall Barton wrote: Did you use both lines? The strYear line should preserve the leading zero. yes i used both lines but how does it know what the latest project number is to begin with. And, Yes, having a format of 00000 will display the leading zero whether it is stored in the table or not. Check the table ***without using a format for the field*** to see if the leading zero is really there or not. If it isn't there, my DMax expression will not work. I really need to know this before you can expect to arrive at a solution. if i look at field projectID in the table it shows the leading zero for all the projects after 1999. You are being sidetracked by the DLast experiment. There is no guarantee that it will retrieve the largest number in the table. Sometimes it will, but other times it can retrieve any old value ftom the field. but Dmax doesn't work because 99888 is a larger number than 06010 so it will always find the biggest number. Also this is a text field not a number field. If you need to see the number before the record is saved and if you might have multiple users performing this activity, then this whole concept goes out the window and it would require some really advanced programming and table manipulations. I don't need to see the number - I can also just have another field that shows them what it'll be using =Last([ProjectID])+1 |
#28
|
|||
|
|||
Field on Form = next record
Kim Webb wrote:
I used this: Dim strYear As String strYear = Format(Date, "yy") Me.ProjectID = strYear & _ Format(Nz(DMax("Val(Right(ProjectID,3))", _ "Projects", "Left(ProjectID,2)='" & strYear & _ "'"), 0) +1, "000") And it worked - many thanks for all the help. I have a similar issue with the INVOICE form and I'm wondering if BeforeUpdate assigns the number when I save the record where can I put that command so it saves the record ONLY if the number is already blank? Can I add an IF/THEN to this statement somehow. Private Sub Description_BeforeUpdate(Cancel As Integer) Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1 End Sub I'm not sure I understand this latest twist. If you are assigning the record's primary key, then you want to use the **form** BerforeUpdate event, not a control's event. I guess I'm guilty of assuming you have all the other logic in place and it's looking like that was not a valid assumption, so let me back up and fill in some background. This activity we've been discussing needs to be done at the time that the form is saving a **new** record. To reduce the window where another user can get the same number, we use the **form** BeforeUpdate event. To make sure this only happens for a new record (not when an existing record has just been edited), the code to set the number needs to be enclosed in an If that checks for the new record situation: Private Sub FORM_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1 End If End Sub I don't think I've ever seen the entire event procedure for the Projects form, but, if you don't already have it, the same If statement needs to be used there too. -- Marsh MVP [MS Access] |
#29
|
|||
|
|||
Field on Form = next record
I'm not sure I understand this latest twist. If you are assigning the record's primary key, then you want to use the **form** BerforeUpdate event, not a control's event. For invoices the form was setup to allow users to print out the invoice before closing the form. So you enter the invoice info - and most of the fields are marked as being requred - and then print the invoice- then close it. If the number is not assigned until after the form is closed then the invoice can't be printed until it's closed and opened back up. So I put the event in after the last field that is requred - which happens to be the description field. This works except if someone goes in later and changes the description the invoice number changes. I guess I'm guilty of assuming you have all the other logic in place and it's looking like that was not a valid assumption, so let me back up and fill in some background. This activity we've been discussing needs to be done at the time that the form is saving a **new** record. To reduce the window where another user can get the same number, we use the **form** BeforeUpdate event. To make sure this only happens for a new record (not when an existing record has just been edited), the code to set the number needs to be enclosed in an If that checks for the new record situation: Private Sub FORM_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1 End If End Sub I don't think I've ever seen the entire event procedure for the Projects form, but, if you don't already have it, the same If statement needs to be used there too. -- Marsh MVP [MS Access] |
#30
|
|||
|
|||
Field on Form = next record
You should still use the form's BeforeUpdate procedure. The
issue is that the record needs to be saved before it is printed. Presumably, you have a button on the form that users click on to print the invoice. If so, then the first thing the button's Click event procedure should do is save the record using this line of code: If Me.Dirty Then Me.Dirty = False As part of saving the record, the form's BeforeUpdate event will fire and, for a new record, assign the invoice number. This way, the wole situation hangs together without all the complications you're running into now. -- Marsh MVP [MS Access] Kim Webb wrote: I'm not sure I understand this latest twist. If you are assigning the record's primary key, then you want to use the **form** BerforeUpdate event, not a control's event. For invoices the form was setup to allow users to print out the invoice before closing the form. So you enter the invoice info - and most of the fields are marked as being requred - and then print the invoice- then close it. If the number is not assigned until after the form is closed then the invoice can't be printed until it's closed and opened back up. So I put the event in after the last field that is requred - which happens to be the description field. This works except if someone goes in later and changes the description the invoice number changes. I guess I'm guilty of assuming you have all the other logic in place and it's looking like that was not a valid assumption, so let me back up and fill in some background. This activity we've been discussing needs to be done at the time that the form is saving a **new** record. To reduce the window where another user can get the same number, we use the **form** BeforeUpdate event. To make sure this only happens for a new record (not when an existing record has just been edited), the code to set the number needs to be enclosed in an If that checks for the new record situation: Private Sub FORM_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then Me.Invoice = Nz(DMax("Invoice", "Invoices"), 0) + 1 End If End Sub I don't think I've ever seen the entire event procedure for the Projects form, but, if you don't already have it, the same If statement needs to be used there too. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Move feild entries from form to form using global variables | JackCGW | General Discussion | 11 | November 14th, 2005 05:22 AM |
Requerying a pop up form to display in the main form | Jennifer P | Using Forms | 13 | April 5th, 2005 06:59 PM |
Inspect record, replace null field on output | RNUSZ@OKDPS | Setting Up & Running Reports | 3 | April 5th, 2005 04:27 PM |
Prevent Blank Records being written. Need Help. | Robert Nusz @ DPS | Using Forms | 4 | December 29th, 2004 05:15 PM |
Recordset in subform based on field in parent form | Lyn | General Discussion | 15 | June 14th, 2004 03:10 PM |