Results 1 to 13 of 13
  1. #1
    cjk2 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7

    Can't modify field value in "form error" event?

    I've been spending the whole day on this issue with no luck so hopefully someone can help me out!

    I thought it'd be a relatively simple thing: basically I have a large form, with many date fields to be entered in a subform. To make data entry a lot faster, I'm trying to make it so users only have to enter the day. By default, the month/year should be set to a "main" date they enter in a textbox at the top of the form.

    e.g. if they enter "Jun-12-2010" in the main textbox, I'd like for them to only have to enter "15" in the subform textbox, and then when they jump to another field the form automatically changes it to "Jun-15-2010". (It's actually a bit more complicated than that, as it has times as well and there are some more calculations involved, but for the sake of simplicity this is the basics of it.)

    I've already written a function "replaceDate" which takes in the "15" and outputs "Jun-15-2010". Unfortunately, "15" is not a valid date. Therefore, if I enter it in the textbox and then move away, access throws a "The value you entered isn't valid for this field" error. It looks like this occurs immediately, before any of the "BeforeUpdate", "LostFocus", "Exit" etc. events occur.

    As a result I attempted to put my code in the "form error" event. Here's what it looks like:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2113 Then


    Me.ActiveControl.Value = replaceDate(Me.ActiveControl.Text)
    End If
    End Sub
    This would work great if not for one problem: apparently when the error is thrown, the form is already in the process of "updating" this field. Thus, it won't allow me to change the value of the field. Essentially it looks like the same problem which makes it impossible to change the field value in the BeforeUpdate event:

    Run-time error "2115". The macro or function set to the Before Update or Validation property for this field is preventing the application from saving the data in the field.
    Any ideas of what might be a workaround for this issue? (e.g. is there maybe an event I don't know about that fires after the form error event is complete?)

  2. #2
    cjk2 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    Also, I've considered various alternatives:

    1) Use the "key up" event to check if the user hit tab/enter, and if so, replace the text. However, this would only work if they hit the tab/enter keys, not if they clicked away from it etc.

    2) Make the bound textbox hidden, and have a separate textbox where the user enters data. In the "AfterUpdate" event, make the bound textbox's value correspond to what they entered. However, I have the field on a tabular subform, which means I would have to add a new field in my table JUST for the purpose of having a "temporary" date value (otherwise the textbox would be identical for each record). I will probably end up doing this if there isn't any other solution, but it seems silly to have more or less a "useless" field in my table.

  3. #3
    Join Date
    May 2010
    Posts
    339
    I guess I'm being picky here but Access all ready knows the time/date why make your users enter it? Unless your trying to find a specific record?

  4. #4
    cjk2 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    Sorry, maybe I wasn't clear and my example was bad (or maybe I'm misunderstanding your question) but the date is not the current date so Access has no way of "knowing" what the user wants to enter.

    e.g. the "main date" might be "Jan-10-2009" (it could be any date really). Then the user can add any number of records in the subform, each with a date field. It'd be a lot easier for them to just type in "12" if they want to enter "Jan-12-2009".

    As it stands, if you try to just enter "12" into a date field on the form, access takes it to be invalid.

    Let me know if it's more understandable now!

  5. #5
    Join Date
    May 2010
    Posts
    339
    Ok so you have a form/subform, the subform has records that [may] have older dates? and your users can edit this date?

  6. #6
    cjk2 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    The actual situation is kind of complicated but let me try this analogy:

    Let's say the situation is a person arriving at a resort. There's a textbox at the top of the form where they can enter the arrival date, say Jan-10-2009.

    There's a subform of "events" that the person attends while they're at the resort. The user entering this data can add/delete/edit etc. records in this subform (each record storing data for one event the person attended). Each "event" record also has a date field, which may not be the same as the arrival date but is probably very close after. Thus, if the person attended an event on Jan-15, I want the user to type in just "15" and have my code automatically change this to "Jan-15-2009" so they don't have to type in the whole thing.

    (I didn't mention this originally as I felt it was irrelevant, but in my function I actually check to see if the day is before their arrival and if so, I add 1 to the month. E.g. if they arrived on Jan-30-2009, entering "4" in the textbox should be converted to "Feb-04-2009".)

  7. #7
    Join Date
    May 2010
    Posts
    339
    Ok got it, sometime its tough to visualize. BTW I get why you trying to shorten the strokes of your users ...they are cussing you out under their breath...haha jk How about no matter what they put in the date textbox it equals the current date? I was trying to work something like that out in my head. Using the afterupdate.

    afterupdate

    me.textbox =date()

    me.requery

    end sub


    maybe they could just hit enter?

  8. #8
    Join Date
    May 2010
    Posts
    339
    Oh you would have to adjust the code to acount for the subform..

  9. #9
    cjk2 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    Thanks for the suggestion--unfortunately, that was the first thing I tried.

    (Also, not sure about the current date thing, as I do need the users to enter their own date, so I'm gonna have to stick with my "replaceDate" function.)

    Anyways, usually AfterUpdate would work. The problem here is that the field is a datetime in the database. So Access refuses to accept anything that's not date-time format, like a number. Thus when I type "15" in the textbox, Access throws an error before even getting to the BeforeUpdate and AfterUpdate events. That's why I put it into the "Form Error" event, to catch the error and try to modify the data before it occurs.

    Which is when I ran into this annoying problem of Access not letting me modify the data in the form error event...

  10. #10
    Join Date
    May 2010
    Posts
    339
    If your user double clicked on the date field and a calendar popped up would that help? No typing..

  11. #11
    cjk2 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    Nah, that would take even more time...they'd have to click on it, navigate to the month they want, etc etc. And repeat for maybe hundreds of dates. Pretty sure they wouldn't be happy with that.

    I really just need a way for my automated function to take effect...

  12. #12
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    cjk2,

    Forgive me if I don't make any sense - I don't have any experience with Tabular Forms. But why can't you use an unbound text box that's not connected to the table to store the user's date value?

    Also, it would require more coding (and would probably only work if the dates being entered are all close to the "main" date), but could you create a pair of buttons: one to add one day to the current field and one to subtract? Then the users don't have to use the keyboard at all, they just click the buttons.

  13. #13
    cjk2 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    7
    That's what I tried to do...unfortunately with the unbound text box, if there are 10 records in the subform access treats all 10 instances of the unbound text box like 1 item. So changing its value will change all 10--definitely not what I want. As for the buttons--90% of the time the dates will probably be within around 5 days of the "main" date. However even 5 days after would involve them clicking the button 5 times, and there will be a few instances where it might be 3 weeks after. While they could just manually enter the date in that case, I really want to try and simplify the data input as much as possible.

    Anyways, after much searching and experimenting I've resigned myself to the fact that the only way around this is to create a "useless" field in the table which will temporarily store the entered date before conversion. It seems rather silly because I have to add this field to each table with a date field (and some tables have more than one date field, so I need a "holder" field for each date field!). It's annoying but the only option I can see right now.

    Thanks so much for trying to help guys, and I definitely still welcome any further suggestions (will probably be useful in the future).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 12-05-2011, 05:10 AM
  2. Replies: 0
    Last Post: 03-04-2010, 06:32 PM
  3. "Add a new field" form
    By vCallNSPF in forum Forms
    Replies: 2
    Last Post: 02-10-2010, 09:56 PM
  4. Error message and How do I find the "value Field" ?
    By createdwithfire in forum Forms
    Replies: 1
    Last Post: 11-05-2009, 12:26 PM
  5. Replies: 21
    Last Post: 06-03-2009, 05:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums