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 |
#1
|
|||
|
|||
Field Before Update property
Ok, here's the deal:
I have a field on my form that defaults to today's date (called RecdDate). The user has a chance to change that field when the form is first opened, as it is the default first field the form tabs to. I want the user to be able to change the default date to a new date, but after the record is saved the first time, I don't want the user to change that field. See, the field generates a Julian Date. I combine the Julian Date along with a Batch Number to create a BatchID - a key that can never change. If I allow the user to change the RecdDate, it would change the Julian Date, which would change the BatchID and create orphan records. How can I use the Before Update property of the field to allow me to store or change the first time the record is opened, but never allow it if the record has been saved? |
#2
|
|||
|
|||
Perhaps use the GotFocus event (or the form's Current event) to test the
value that is in the textbox and see if it matches the default value, and lock the data if it doesn't. Private Sub ControlName_GotFocus() Me.ControlName.Locked = Not (Me.ControlName.Value = _ Me.ControlName.DefaultValue) End Sub -- Ken Snell MS ACCESS MVP "Robert_L_Ross" wrote in message ... Ok, here's the deal: I have a field on my form that defaults to today's date (called RecdDate). The user has a chance to change that field when the form is first opened, as it is the default first field the form tabs to. I want the user to be able to change the default date to a new date, but after the record is saved the first time, I don't want the user to change that field. See, the field generates a Julian Date. I combine the Julian Date along with a Batch Number to create a BatchID - a key that can never change. If I allow the user to change the RecdDate, it would change the Julian Date, which would change the BatchID and create orphan records. How can I use the Before Update property of the field to allow me to store or change the first time the record is opened, but never allow it if the record has been saved? |
#3
|
|||
|
|||
If I do that, it won't allow me change it the first time.
Here's how it should work: New record: RecdDate should default to today's date. User should be able to change the date and move to the next field (since it's a new record) or keep the date as-is and move to the next field. Existing record: RecdDate should be pre-populated If the user attempts to change the date, they are prevented from doing so. I'm thinking there should be a macro or code I could refer to using Before Update that could check to see if the field is null...if so, allow the update to go forward. If the field is not null, prevent the update (stop the update, show a msgbox, something like that). Can't that be programmed into the Before Update event? "Ken Snell [MVP]" wrote: Perhaps use the GotFocus event (or the form's Current event) to test the value that is in the textbox and see if it matches the default value, and lock the data if it doesn't. Private Sub ControlName_GotFocus() Me.ControlName.Locked = Not (Me.ControlName.Value = _ Me.ControlName.DefaultValue) End Sub -- Ken Snell MS ACCESS MVP "Robert_L_Ross" wrote in message ... Ok, here's the deal: I have a field on my form that defaults to today's date (called RecdDate). The user has a chance to change that field when the form is first opened, as it is the default first field the form tabs to. I want the user to be able to change the default date to a new date, but after the record is saved the first time, I don't want the user to change that field. See, the field generates a Julian Date. I combine the Julian Date along with a Batch Number to create a BatchID - a key that can never change. If I allow the user to change the RecdDate, it would change the Julian Date, which would change the BatchID and create orphan records. How can I use the Before Update property of the field to allow me to store or change the first time the record is opened, but never allow it if the record has been saved? |
#4
|
|||
|
|||
How are you putting the default date in the RecdDate textbox? Are you using
the ControlSource to do that? Or the DefaultValue to do that? You should be using the DefaultValue to do this. The code I suggested assumes that that is how it's being done. Post more details about what your setup is. I'm not inclined to look at the BeforeUpdate event for what you seek to do, because that allows the user to go to the trouble of changing the data and then you "try" to stop the change...this won't be "helpful" for your users. That is why I'm suggesting a different event and to lock the data so that the user is prevented from even trying to change the data. -- Ken Snell MS ACCESS MVP "Robert_L_Ross" wrote in message news If I do that, it won't allow me change it the first time. Here's how it should work: New record: RecdDate should default to today's date. User should be able to change the date and move to the next field (since it's a new record) or keep the date as-is and move to the next field. Existing record: RecdDate should be pre-populated If the user attempts to change the date, they are prevented from doing so. I'm thinking there should be a macro or code I could refer to using Before Update that could check to see if the field is null...if so, allow the update to go forward. If the field is not null, prevent the update (stop the update, show a msgbox, something like that). Can't that be programmed into the Before Update event? "Ken Snell [MVP]" wrote: Perhaps use the GotFocus event (or the form's Current event) to test the value that is in the textbox and see if it matches the default value, and lock the data if it doesn't. Private Sub ControlName_GotFocus() Me.ControlName.Locked = Not (Me.ControlName.Value = _ Me.ControlName.DefaultValue) End Sub -- Ken Snell MS ACCESS MVP "Robert_L_Ross" wrote in message ... Ok, here's the deal: I have a field on my form that defaults to today's date (called RecdDate). The user has a chance to change that field when the form is first opened, as it is the default first field the form tabs to. I want the user to be able to change the default date to a new date, but after the record is saved the first time, I don't want the user to change that field. See, the field generates a Julian Date. I combine the Julian Date along with a Batch Number to create a BatchID - a key that can never change. If I allow the user to change the RecdDate, it would change the Julian Date, which would change the BatchID and create orphan records. How can I use the Before Update property of the field to allow me to store or change the first time the record is opened, but never allow it if the record has been saved? |
#5
|
|||
|
|||
The default date is done by using the default value (Default value = now() or
date(), something like that - it's at work right now so I can't reference it exactly). I want the user to be able to change that date if needed when they first create the new record. ...see, the database is to track 'batches' of work. Sometimes the work is already completed (work generated by a phone call). So, when the work is received via paper (we call it batch work), I want the user to be given the default date of today and everything is fine. When the user receives work that was generated from a call (we call it post-batch work), the work is already done, and sometimes it's done the day before or even more. I want the user to be able to set the date back as needed. The cincher is that this date can only be allowed to change when the record is first being created. If for some reason they select the wrong date, the batch will be marked for deletion (it's actually marked for supression as the data is housed on a SQL server). The date that is entered will generate a Julian date that will be combined with a batch number to create what we call a BatchID, a unique key that is then used to tie in the records of work done on the batch. It's a one-to-many relation between the header info (Received date, etc.) and the work table (who worked how many of the batch on what day). You can see, if we allow the user to change a date accidentally on an existing batch, we could cause the Julian date to change, breaking the link between the header info (stored on the BatchControl table) and the work info (stored on the BatchWork table and tied to BatchControl using BatchID - created with the Julian date that was made with the Received date). I want to make sure the code you listed would allow the user to change the date the very first time they see it - when the record is being created - but not any other time AFTER that. Sorry this was so long, but I'm thinking maybe I didn't give enough detail to accurately describe what I need. THX for taking the time to read and reply, I REALLY appreciate it! "Ken Snell [MVP]" wrote: How are you putting the default date in the RecdDate textbox? Are you using the ControlSource to do that? Or the DefaultValue to do that? You should be using the DefaultValue to do this. The code I suggested assumes that that is how it's being done. Post more details about what your setup is. I'm not inclined to look at the BeforeUpdate event for what you seek to do, because that allows the user to go to the trouble of changing the data and then you "try" to stop the change...this won't be "helpful" for your users. That is why I'm suggesting a different event and to lock the data so that the user is prevented from even trying to change the data. -- Ken Snell MS ACCESS MVP "Robert_L_Ross" wrote in message news If I do that, it won't allow me change it the first time. Here's how it should work: New record: RecdDate should default to today's date. User should be able to change the date and move to the next field (since it's a new record) or keep the date as-is and move to the next field. Existing record: RecdDate should be pre-populated If the user attempts to change the date, they are prevented from doing so. I'm thinking there should be a macro or code I could refer to using Before Update that could check to see if the field is null...if so, allow the update to go forward. If the field is not null, prevent the update (stop the update, show a msgbox, something like that). Can't that be programmed into the Before Update event? "Ken Snell [MVP]" wrote: Perhaps use the GotFocus event (or the form's Current event) to test the value that is in the textbox and see if it matches the default value, and lock the data if it doesn't. Private Sub ControlName_GotFocus() Me.ControlName.Locked = Not (Me.ControlName.Value = _ Me.ControlName.DefaultValue) End Sub -- Ken Snell MS ACCESS MVP "Robert_L_Ross" wrote in message ... Ok, here's the deal: I have a field on my form that defaults to today's date (called RecdDate). The user has a chance to change that field when the form is first opened, as it is the default first field the form tabs to. I want the user to be able to change the default date to a new date, but after the record is saved the first time, I don't want the user to change that field. See, the field generates a Julian Date. I combine the Julian Date along with a Batch Number to create a BatchID - a key that can never change. If I allow the user to change the RecdDate, it would change the Julian Date, which would change the BatchID and create orphan records. How can I use the Before Update property of the field to allow me to store or change the first time the record is opened, but never allow it if the record has been saved? |
#6
|
|||
|
|||
Thanks for the explanation. That is helpful.
If you're using Now() as the DefaultValue, then my code won't work for your needs. That is because Now includes seconds, and it won't match the value that was written in as the "default" when the record is created after just one second. If you're using Date(), then the code will work to allow changes anytime on the date that the record is created, and not thereafter. Neither of these situations is what you seek to accomplish. Thus, we can use the Current event of the form to test whether the record is a "new" record -- that is, it's being created. If it is, it's possible then to allow changes to the RecdDate value. If it's not (meaning that the record has already been saved), then it can prevent such changes. Private Sub Form_Current() Me.RecdDate.Locked = Not Me.NewRecord End Sub The above code will lock the data in the RecdDate control if the record has already been saved. If it's not been saved yet, then the data are unlocked and available for editing. We also can use the form's AfterUpdate event to lock the data as soon as the record is saved. Private Sub Form_AfterUpdate() Me.RecdDate.Locked = True End Sub Using both of these codes in your form should allow you to do what you seek. -- Ken Snell MS ACCESS MVP "Robert_L_Ross" wrote in message ... The default date is done by using the default value (Default value = now() or date(), something like that - it's at work right now so I can't reference it exactly). I want the user to be able to change that date if needed when they first create the new record. ...see, the database is to track 'batches' of work. Sometimes the work is already completed (work generated by a phone call). So, when the work is received via paper (we call it batch work), I want the user to be given the default date of today and everything is fine. When the user receives work that was generated from a call (we call it post-batch work), the work is already done, and sometimes it's done the day before or even more. I want the user to be able to set the date back as needed. The cincher is that this date can only be allowed to change when the record is first being created. If for some reason they select the wrong date, the batch will be marked for deletion (it's actually marked for supression as the data is housed on a SQL server). The date that is entered will generate a Julian date that will be combined with a batch number to create what we call a BatchID, a unique key that is then used to tie in the records of work done on the batch. It's a one-to-many relation between the header info (Received date, etc.) and the work table (who worked how many of the batch on what day). You can see, if we allow the user to change a date accidentally on an existing batch, we could cause the Julian date to change, breaking the link between the header info (stored on the BatchControl table) and the work info (stored on the BatchWork table and tied to BatchControl using BatchID - created with the Julian date that was made with the Received date). I want to make sure the code you listed would allow the user to change the date the very first time they see it - when the record is being created - but not any other time AFTER that. Sorry this was so long, but I'm thinking maybe I didn't give enough detail to accurately describe what I need. THX for taking the time to read and reply, I REALLY appreciate it! "Ken Snell [MVP]" wrote: How are you putting the default date in the RecdDate textbox? Are you using the ControlSource to do that? Or the DefaultValue to do that? You should be using the DefaultValue to do this. The code I suggested assumes that that is how it's being done. Post more details about what your setup is. I'm not inclined to look at the BeforeUpdate event for what you seek to do, because that allows the user to go to the trouble of changing the data and then you "try" to stop the change...this won't be "helpful" for your users. That is why I'm suggesting a different event and to lock the data so that the user is prevented from even trying to change the data. -- Ken Snell MS ACCESS MVP "Robert_L_Ross" wrote in message news If I do that, it won't allow me change it the first time. Here's how it should work: New record: RecdDate should default to today's date. User should be able to change the date and move to the next field (since it's a new record) or keep the date as-is and move to the next field. Existing record: RecdDate should be pre-populated If the user attempts to change the date, they are prevented from doing so. I'm thinking there should be a macro or code I could refer to using Before Update that could check to see if the field is null...if so, allow the update to go forward. If the field is not null, prevent the update (stop the update, show a msgbox, something like that). Can't that be programmed into the Before Update event? "Ken Snell [MVP]" wrote: Perhaps use the GotFocus event (or the form's Current event) to test the value that is in the textbox and see if it matches the default value, and lock the data if it doesn't. Private Sub ControlName_GotFocus() Me.ControlName.Locked = Not (Me.ControlName.Value = _ Me.ControlName.DefaultValue) End Sub -- Ken Snell MS ACCESS MVP "Robert_L_Ross" wrote in message ... Ok, here's the deal: I have a field on my form that defaults to today's date (called RecdDate). The user has a chance to change that field when the form is first opened, as it is the default first field the form tabs to. I want the user to be able to change the default date to a new date, but after the record is saved the first time, I don't want the user to change that field. See, the field generates a Julian Date. I combine the Julian Date along with a Batch Number to create a BatchID - a key that can never change. If I allow the user to change the RecdDate, it would change the Julian Date, which would change the BatchID and create orphan records. How can I use the Before Update property of the field to allow me to store or change the first time the record is opened, but never allow it if the record has been saved? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update Query "Only Update if Field is blank" | Amy Adams via AccessMonster.com | Running & Setting Up Queries | 5 | March 25th, 2005 08:03 PM |
How do I update a timestamp field when another field is updated | Swhite951 | Using Forms | 1 | March 15th, 2005 11:40 PM |
Update field value automatically based on subform | Sammie | Using Forms | 0 | February 11th, 2005 10:49 PM |
NUMBERING the pages | Bob | New Users | 7 | June 14th, 2004 12:20 AM |
field manipulation | Steve | Running & Setting Up Queries | 2 | May 28th, 2004 03:12 PM |