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 |
#11
|
|||
|
|||
Field on Form = next record
What would be the reason to not just use the default value property of
the control to do this task? |
#12
|
|||
|
|||
Field on Form = next record
I had a nice reply, but then my webbrowser screwed me...
Anyhow, yes, you could use it as the default property, but then you'd have to double check the value hadn't changed prior to saving it, or at regular intervals. |
#13
|
|||
|
|||
Field on Form = next record
Red wrote:
How about this? Option Compare Database Option Explicit Private Sub form_load() Me.TimerInterval = 1000 Text0 = GetNewProjectID End Sub Private Function GetNewProjectID() As Integer Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("SELECT Max(Projects.ProjectID) AS NewID FROM Projects;") If rs.RecordCount 1 Then GetNewProjectID = 1 Exit Function Else rs.MoveLast GetNewProjectID = rs("NewID") + 1 End If End Function Private Sub Form_Timer() If Text0 GetNewProjectID Then MsgBox ("Another user has used your number, please update your written records") Text0 = GetNewProjectID End If End Sub The MoveLast unnecessary since the query will retrieve exactly one record. The max field's value will be Null if there are no records. I hate using the timer to do anything because it interferes with too many other things. Better to have a separate table with the next available number and if a record is canceled save the canceled record's number to the table so it can be "reused". To be totally safe, you should lock this table until the data is consistent for this record and other users working on other records at exactly the same time. Bottom line: In a full blown scenario, this is not a "simple" problem. The BeforeUpdate event leaves an extremely small window for duplicate IDs and most folks use this instead of worring about the full blown solution. -- Marsh MVP [MS Access] |
#14
|
|||
|
|||
Field on Form = next record
Comments inline
-- Marsh MVP [MS Access] Red wrote: True true... I errantly wrote that while I was waiting for a macro to finish running.... But, a couple things about this question I'd like to clear up before I continue my hot air... A developer should never allow a user to directly access the tables. (Commandment #2 http://www.mvps.org/access/tencommandments.htm). So I made the assumption that the form would have some sort of "Save" button, instead of saving after pressing the next record button (or whatever)...And, Access isn't really meant to be used for multiple users....but w/e... Hardly anyone uses a "save" button since Access takes care of the save automatically when the user does just about anthing that indicates editing the record is completed. Access is actually pretty good at multi-user databases. I've heard of a few very carefully designed and implemented mdbs with close to 100 users (although 5 to 20 is much more common). Next thing I was thinking... Kim probably wants this project ID number so she can document it on paper somewhere, so the number must NOT change in between starting and finishing...but with your solution this is not possible.. and with my solution, you pointed out the problems... I have never run into a user need for writing the number down, but there probably exists some situation that wants to do that. In my experience a button to print a summary of the new record's data is what the user's would prefer to use. The print button can save the record before printing so the number will appear on the report. At this point, I'd also like to note the possible problem with a user "grabbing" a number, and end up not using it... So, given what I think Kim was wanting, what you were thinking, and what I was thinking, here is the total scenario as I see it: (Of course, I usually over think things....) 1: Need to be able to see the Project ID # when entering information into the form 2: Need to lock the # so multiple users can find the next number, and not use one being currently used 3. Need to identify an unused number and place it back in the "usable" list I don't think either of our solutions fixes all 3 of these problems... This set of requirements is getting pretty complex and way beyond the scope of how I read Kim's original question. Personally, I have never run into a situation with all of those requirements. You're welcome to attack the problem if you feel so inclined, but you may also want to think about some of the other issues I posted to one of your other replies. |
#15
|
|||
|
|||
Field on Form = next record
Hardly anyone uses a "save" button since Access takes care
of the save automatically when the user does just about anthing that indicates editing the record is completed. I use them all the time, you never know when a user will screw up and delete a record that is vital to business... One of the most common things I hear is "I accidentally delete this field occasionaly, can you make it so I can change it, but undo it if I accidentally screw up?"... Now, I know there are tons of ways to get around it, but the simplest way is to make a simple "save" button to add the records to the table. Now, I'm sure this could be one hell of a statement is the table has 50+ fields, but, generally, most forms I deal with are on a much smaller scale. Access is actually pretty good at multi-user databases. I've heard of a few very carefully designed and implemented mdbs with close to 100 users (although 5 to 20 is much more common). I inherited some mdb's that are used by dozens of users. While they CAN be used for multiple users, it is NOT optimal to be using access (imho) for multiple users. I could link dozens of articles on both the pro's and con's of Access, and most of them agree that there are far better options for multiple users than Access (especially the version I'm currently 'stuck' in... icky 97...)... I have never run into a user need for writing the number down, but there probably exists some situation that wants to do that. In my experience a button to print a summary of the new record's data is what the user's would prefer to use. The print button can save the record before printing so the number will appear on the report. It must be insanely nice to have users/supervisors that get with the times, and don't write anything down on paper. I give an example of a job I had 4-5 years ago... I was temp'ing midnight as a data entry operator at a warehouse.. entering orders... I had to enter each order individually, and then write the system assigned number on the order. Then, print the orders from the starting (system) number to the ending (system) number. Then, I had to match up each original order with the order from the system I was inputting them into. Yada yada yada, yes, people often need to see that incremental number when doing things. This set of requirements is getting pretty complex and way beyond the scope of how I read Kim's original question. Personally, I have never run into a situation with all of those requirements. You're welcome to attack the problem if you feel so inclined, but you may also want to think about some of the other issues I posted to one of your other replies. Indeed, it is a complex situation, but, we are both making assumptions that Kim herself did not mention (You - the multiple user environment, me- needing the numbers while inputing). As far as attacking the problem, I was just trying to help while I was wasting time at work..... I would be glad to help if she had additional questions, as I'm sure you would be to. Your advice has been taken, and as always, is greatly appreciated! Thanks, ~Red |
#16
|
|||
|
|||
Field on Form = next record
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. 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. 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. 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 -- Marsh MVP [MS Access] |
#17
|
|||
|
|||
Field on Form = next record
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 |
#18
|
|||
|
|||
Field on Form = next record
Since I'm not using numbers but text the autonumber does not work since
there is no way to convince access that 06000 comes after 99006, etc. I did not come up with the current system and it can't be changed since out DB is somehow exporting to an accounting package -soloman I think - and the numbering system can't be changed. |
#19
|
|||
|
|||
Field on Form = next record
|
#20
|
|||
|
|||
Field on Form = next record
Perhaps in an effort to make this easier to understand - before this
year what happened was the form had a field to SHOW people what the next number was in the project list- so the ProjectID field was empty and then a box to the right showed them which one to type in to the ProjectID box. I had a text box named NextNumber and had in the control source =Last([ProjectID])+1 This worked well for many years until my users either got to busy or too careless or both and suddenly were entering the wrong number by mistake so if the number said 06010 was the next number they might enter 06110 by accident. This was a huge problem last year when we did 800 projects and had hundreds of wasted numbers from mistakes. So all I'm trying to do is avoid have users have to type in their own project number and rather have it assigned to them automatically. Also we previously had many users in the DB at the same time trying to assign the same numbers so I was hoping to fix that also. Thanks. |
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 |