Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Posts
    29

    Unanswered: Setting field value on load in unbound form

    Hi all,

    I'm experiencing a problem trying to dynamically set the value of some fields when opening an unbound form.

    Let me explain.
    The form is used for both adding and updating records. It is not a "regular" Access form bound to a table because the data that is input requires some processing before being stored in tables, so I process it and then use SQL to store it.

    Adding records is no problem as no values need to be loaded in the fields.
    When editing a record, the form is opened with Me.OpenArgs giving the necessary information to retrieve the record's current values.

    So the code is very simple :
    Code:
    Sub Form_Load()
    
    'aFormFields = Array( [list of form fields] )
    'aInitValues = Array( [list of default values] )
    '**use Me.OpenArgs, retrieve the record's information, process it and put it into aInitValues**
    
    For i = 0 To UBound(aFormFields)
      Me.Controls(aFormFields(i)).Value = aInitValues(i)
    Next
    
    End Sub
    Each call of the line that sets the value of a field triggers a "Runtime Error 2113 : The value is not valid for this field."

    Since these fields are unbound, have no pre-defined validation whatsoever, and since the values are only numbers, I just don't understand how they can be "not valid". It cannot be a type problem.

    Ideas, anyone ?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't think you can set values in controls in the load event, its something to do with how the form is intialised

    I can't remember offhand what the 'correct' event handler is, but there is one that comes after on load
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2008
    Posts
    29
    Actually I tried the whole event chain, which is supposed to be

    Open - Load - Resize - Activate - Current

    but it worked on neither of them.

    I'm trying another solution, using the "Text" property of the controls instead of "Value". I read somewhere that "Value" is only set when actually updating the controls, whereas "Text" represents what's shown in the control at any time. So I figured out maybe I could programmatically set "Text" and then force focus to get the value updated, like :
    Code:
    For i = 0 To UBound(aFormFields)
       Me.Controls(aFormFields(i)).Text = aInitValues(i)
       Me.Controls(aFormFields(i)).SetFocus
    Next
    But then I get a "Runtime Error 13: Incompatible type". Which I just don't understand either.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I was sure On Load occurs before On Open. I just try one and if it doesn't work I put it in the other ^^

    Try removing the .Text ?

    Code:
    For i = 0 To UBound(aFormFields)
       Me.Controls(aFormFields(i)) = aInitValues(i)
       Me.Controls(aFormFields(i)).SetFocus
    Next
    When you step through the code, do you see what you expect when you examine the contents of the array element and the text box control? What type of object are they anyway... text boxes? Labels? Combo boxes?
    Last edited by StarTrekker; 06-17-08 at 11:35.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jun 2008
    Posts
    29
    Well, as for the load sequence, I got that from the Access manual

    Anyway, I tried without .Text/.Value and still get that Runtime Error 2113.

    All the controls in there are either text boxes or combo boxes, but setting a value for both of these shouldn't be different, should it ?

    I also re-checked what comes up in the aInitValues() array and it's just as expected (ie. integers). The aFormFields() array is defined manually and has the names of the fields to edit, and I checked those too without finding any error.

    There must be something I don't get but I really can't see what that is !

  6. #6
    Join Date
    Jun 2008
    Posts
    29
    OK I just got it.
    In short, I had this:
    Code:
    Set RS = CurrentDb.OpenRecordset( [some query] )
    If Not RS.EOF Then
       For i = 0 To UBound(aFormFields)
          aInitValues(i) = RS(i)
       Next
    Else
       aInitValues = aInitEmpty
    End If
    RS.Close
    Set RS = Nothing
    
    '**some processing and then
    For i = 0 To UBound(aFormFields)
       Me.Controls(aFormFields(i)).Value = aInitValues(i)
    Next
    So when I checked aInitValues() after I got the RS into it, it was fine. But by writing RS(i) I thought I was copying the value of RS(i) into the array, not referencing the Field object RS(i), which is what in fact was done.
    By the time I tried to update the controls, as RS was destroyed, the references in the array became invalid. Hence the error.
    Solution is : change line 4 into
    Code:
    aInitValues(i) = RS(i).Value
    Thanks all.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Glad you got it sorted... and thanks for posting the detailed solution

    You should have posted the whole code in the first place though, probably would have made it easier!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •