1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Default Value for table field in Access 2007

Discussion in 'Business Applications' started by slikbaz, Nov 22, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    Hi,

    Over a month ago, I posted this topic here:

    http://forums.techguy.org/business-applications/954178-default-value-table-field-access.html

    However I never had the time to try out the suggestion. I've finally gotten a holiday though, and so gave it a go. Here's the code I'm using in the After Update Procedure, as advised by OBP:

    currentyear = Year([DateofEntry])
    newyear = currentyear + Me.[PeriodLength]
    Me.[ScheduledDateofRelease] = Left(Me.[DateofEntry], 6) & newyear

    It does work, so thank you for that :)

    However, it does produce some odd errors. First of all, it only updates the "ScheduledDateofRelease" after I have clicked "Save" on the form, and once I try to close the form access gives me an error saying that:

    "You cannot save this record at this time". In the same dialogue box it says "If you close this object now, the data changes you have made will be lost. Do you want to close the object anyway?"

    After clicking yes on that box, and navigating to the table which is the source for this form, I find that the new record has indeed been added, contrary to what the error message was saying.

    Another odd thing that occurs is that once I enter a new record into the form and click save, I can't go backwards or forwards through records on the form and am instead presented with the error stating "You can't go to the specified record."

    Any help appreciated :)
    Slikbaz.

    edit: I've just realised that it gives me the "You can't go to the specified record" if I edit any field on any record through the form, whether or not it is a new one.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    Why are clicking "Save" on the Form?
    Access automatically saves changed data.
     
  3. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    Yeah, I know, but it was something I tried after I got the "Cannot go to specified record" error and got the new error that I mentioned in my first post. Just thought it might be helpful in pointing out what the problem could be.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    Can you move through the records OK without editing?
    Is this a single form or a form with subform?
    What Recordsource does the form use?
     
  5. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    Yeah, without editing I can move through the records fine. It's a single form, no subform. The record source is a table containing all the records.

    However if I change even one letter in any field of any record (on the form), and then attempt to switch to another record, I will be presented with this error:

    [​IMG]

    and after pressing end I will get this error:

    [​IMG]

    If I try to close the form I will get the first error again, and after pressing end I will get this error:

    [​IMG]

    If I press yes here, and then go to the Table (the source of the form), I will see that the record I edited has been updated, rather than the data being lost (as the error says it will be).
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    What is the setting in the table for the ScheduledDateofRelease field?
     
  7. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    Field Name: ScheduledDateofRelease
    Data Type: Data/Time
    Caption: Scheduled Date of Release
    Validation Rule: Is Null Or >=Date()
    Validation Text: Please enter a valid date.
    Required: No
    Indexed: No
    IME Mode: No control
    IME Sentence Mode: No
    Text Align: General
    Show Date Picker: For dates

    I have just realised that the validation rule isn't very good... It will be fine when the record is entered in, since of course the scheduled date of release will be after the current date.. However as soon as that day is reached, the data will become invalid.. So I've removed it, but the problem still occurs (as there was no record which had a Scheduled Date of Release before the current date).
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    Add this line to your VBA code to yours before setting the me.ScheduledDateofRelease

    msgbox Left(Me.[DateofEntry], 6) & newyear

    ensure that Left(Me.[DateofEntry], 6) & newyear produces a valid date format.
     
  9. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    Ahh.. The result was:

    9/11/22015

    When it should have been:

    9/11/2015

    Hmm, I can't see where the code goes wrong though..
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    So can I your input date is not formatted to dd/mm/yyyy or mm/dd/yyyy
     
  11. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    Well, using the design view of the table, I've set the format to "Short Date", which seems to be MM/DD/YYYY by default.
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    I think You need to set it in the form as well.
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    And Re-enter the dates.
     
  14. slikbaz

    slikbaz Thread Starter

    Joined:
    Oct 4, 2010
    Messages:
    23
    I put the same input mask and format for the fields in both the table and the form, yet the same problem occurs. I tried adding a new record, and entered only the DateofEntry and the SentenceLength. I then tried to press back to go to the previous record, and it presented me with all the errors I presented before, however it did indeed correctly fill the Scheduled Date of Release field. (I entered 11/15/2010 for DateofEntry and 5 for SentenceLength, and ScheduledDateofRelease was filled with 11/15/2015). However, the message box still displayed 22015 for the year.
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,659
    I don't quite understand what is going on there, as 9/11/2010 should give you an errorbut 11/15/2010 shouldn't.
    Can you format the database as Access 2003 and post it on here as an attachment?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/964051

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice