Value and Text Properties of controls

Discussion in 'Microsoft Access Form Coding' started by Ronald Dodge, Aug 23, 2007.

  1. Ronald Dodge

    Ronald Dodge Guest

    How do you have a value put into the Value property of a control on the form
    via VBA code?

    Situation:

    User determine what to do with the record after the fields been filled as
    opposed to Access wanting to know which edit mode to go into prior to
    filling in the fields. This is the real stickler as I'm attempting at
    keeping the database as the same feel as JDE to the extent reasonably
    possible.

    Question: In order to set the value or text on these controls with the
    "RecordSource" on the form set to "", do I need to *UNBIND* the controls,
    set the value of the control, then *BIND* the controls again for this to
    work?

    --

    Sincerely,

    Ronald R. Dodge, Jr.
    Master MOUS 2000
     
    Ronald Dodge, Aug 23, 2007
    #1
    1. Advertisements

  2. Ronald Dodge

    Dirk Goldgar Guest

    In news:%,
    Ronald Dodge <> wrote:
    > How do you have a value put into the Value property of a control on
    > the form via VBA code?
    >
    > Situation:
    >
    > User determine what to do with the record after the fields been
    > filled as opposed to Access wanting to know which edit mode to go
    > into prior to filling in the fields. This is the real stickler as
    > I'm attempting at keeping the database as the same feel as JDE to the
    > extent reasonably possible.
    >
    > Question: In order to set the value or text on these controls with
    > the "RecordSource" on the form set to "", do I need to *UNBIND* the
    > controls, set the value of the control, then *BIND* the controls
    > again for this to work?


    I'm not sure I follow you. (And what is JDE?) If the form has no
    recordsource, then it is unbound, and the controlsources of all the
    controls should be blank. You can, if you want, use an unbound form in
    this way to enter data that won't be saved until/unless the user
    requests it somehow. Then your code can build and execute an append or
    update query on the fly, or execute a stored query that references the
    form's controls for values.

    But I'm not sure if this is what you're trying to accomplish. And bound
    forms are much easier to work with, if your requirements permit it.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
    Dirk Goldgar, Aug 23, 2007
    #2
    1. Advertisements

  3. Ronald Dodge

    Ronald Dodge Guest

    JDE is our main data base program, which users fills in the data and then
    determines rather to add it or modify the current record, if the record was
    previously inquired. Records must be previously inquired before it can be
    editted.

    Now the problem with bound forms in Access, it's immediately either adding
    to a new record or editing the current record, and when it leaves that
    record, it's updated by default, which doesn't work for the scenerio that
    I'm having to work with. Users are use to determing after putting in the
    values, not before putting in the values, and I rather not face too much
    resistance from the users.

    --

    Sincerely,

    Ronald R. Dodge, Jr.
    Master MOUS 2000

    "Dirk Goldgar" <> wrote in message
    news:...
    > In news:%,
    > Ronald Dodge <> wrote:
    >> How do you have a value put into the Value property of a control on
    >> the form via VBA code?
    >>
    >> Situation:
    >>
    >> User determine what to do with the record after the fields been
    >> filled as opposed to Access wanting to know which edit mode to go
    >> into prior to filling in the fields. This is the real stickler as
    >> I'm attempting at keeping the database as the same feel as JDE to the
    >> extent reasonably possible.
    >>
    >> Question: In order to set the value or text on these controls with
    >> the "RecordSource" on the form set to "", do I need to *UNBIND* the
    >> controls, set the value of the control, then *BIND* the controls
    >> again for this to work?

    >
    > I'm not sure I follow you. (And what is JDE?) If the form has no
    > recordsource, then it is unbound, and the controlsources of all the
    > controls should be blank. You can, if you want, use an unbound form in
    > this way to enter data that won't be saved until/unless the user requests
    > it somehow. Then your code can build and execute an append or update
    > query on the fly, or execute a stored query that references the form's
    > controls for values.
    >
    > But I'm not sure if this is what you're trying to accomplish. And bound
    > forms are much easier to work with, if your requirements permit it.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
    Ronald Dodge, Aug 23, 2007
    #3
  4. Ronald Dodge

    Dirk Goldgar Guest

    In news:,
    Ronald Dodge <> wrote:
    > JDE is our main data base program, which users fills in the data and
    > then determines rather to add it or modify the current record, if the
    > record was previously inquired. Records must be previously inquired
    > before it can be editted.
    >
    > Now the problem with bound forms in Access, it's immediately either
    > adding to a new record or editing the current record, and when it
    > leaves that record, it's updated by default, which doesn't work for
    > the scenerio that I'm having to work with. Users are use to
    > determing after putting in the values, not before putting in the
    > values, and I rather not face too much resistance from the users.


    Even with a bound form, there are ways to prevent a record from being
    saved until the user specifically requests it. However, it sounds like
    an unbound form may be the way for you to go. You can use the approach
    I described previously for that.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
    Dirk Goldgar, Aug 23, 2007
    #4
  5. Ronald Dodge

    Ronald Dodge Guest

    Thank you for the update, which more or less confirmed my suspicions. That
    means in many regards, I'm back to square 1 as I'm back to using the forms
    as unbound, but only as a different reason as before.

    However, I already know I will have to treat this project similar to
    ADO.NET's disconnected model, but only without the enhanced features for the
    following reasons:

    As much as I was hoping to be able to use, bound forms don't seem to be the
    answer given the mode is determined prior to filling in the fields as
    opposed to the preferred afterwards.

    DAO coding has a real problem with the "EditMode" property not working
    properly at times (there's no official documentation on this bug, but I have
    ran into it numerous times), which means I had to have a work around in
    place to address this issue.

    ADO coding doesn't allow for dynamic cursor keyset recordsets against a Jet
    Engine as documented.

    Another thing I don't like about unbound controls, certain properties aren't
    updated *AND* readable by VBA until *AFTER* that control has lost focus,
    which doesn't help me out one bit in many cases. Listboxes has this problem
    and impacted me so great to the point that I had to create my own custom
    listbox class object to emulate the actual listbox on the form, which then I
    ran into another bug dealing with listbox selections via keyboard method.
    That's cause I was like, if I'm going to emulate the listbox, I might as
    well go full out and get everything including mouse movement, how selections
    are done (Keyboard and mouse methods) under the 3 different selection mode
    options, and what it will take, just as I emulated VB6's CausesValidation
    property and Validate event to get around the fact in Access, using the
    default error checking stuff on a control by control basis, it either always
    or never gets checked, which you would want checks to take place most of the
    time prior to a control losing it's focus, but not everytime in a pretty big
    majority of the cases.

    One of the reasons why I put in checks on a control by control basis, not
    just record by record basis, I did data entry for a time and if an error was
    made that could reasonably be caught right there on the spot without slowing
    things up, I would want to have it caught right away and allow me to go back
    just briefly, and make the adjustment as needed. The other thing, at the
    time when I did do this at the IRS, it was all text based, no mouse.
    However, even with having the mouse available, if you doing nothing but
    strictly data entry (either numeric, straight, or alphanumeric), that
    constant switching back and forth between the keyboard and mouse is a huge
    time waster from the point of view how much time it takes to go back and
    forth as opposed to just sticking to the keyboard in most cases. Along
    those same lines, I also like to avoid alphanumeric data entry as much as
    reasonably possible and have it either as strictly straight typing or
    strictly as numeric as that constant switching back and forth between the
    QWETRY keyboard and numpad (MS calls it keypad) is also a time waster. Of
    course, some give and take will have to be done.

    As a production statistician and the person responsible for maintaining the
    data, I certainly don't want any corrupt data, thus I have to have strict
    rules in place for most things to reasonably assure this factor.

    Also as a programmer, I'm also don't want to have to spend too much time
    having to go back and fix things, but also need to make things as user
    friendly as possible while keeping the data validity in place.

    I'm now doing the administator type work as well on the data base, which
    that also has to be taken into account.

    --

    Sincerely,

    Ronald R. Dodge, Jr.
    Master MOUS 2000

    "Dirk Goldgar" <> wrote in message
    news:...
    > In news:,
    > Ronald Dodge <> wrote:
    >> JDE is our main data base program, which users fills in the data and
    >> then determines rather to add it or modify the current record, if the
    >> record was previously inquired. Records must be previously inquired
    >> before it can be editted.
    >>
    >> Now the problem with bound forms in Access, it's immediately either
    >> adding to a new record or editing the current record, and when it
    >> leaves that record, it's updated by default, which doesn't work for
    >> the scenerio that I'm having to work with. Users are use to
    >> determing after putting in the values, not before putting in the
    >> values, and I rather not face too much resistance from the users.

    >
    > Even with a bound form, there are ways to prevent a record from being
    > saved until the user specifically requests it. However, it sounds like an
    > unbound form may be the way for you to go. You can use the approach I
    > described previously for that.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
     
    Ronald Dodge, Aug 23, 2007
    #5
  6. Ronald Dodge

    Dirk Goldgar Guest

    In news:u0plD$,
    Ronald Dodge <> wrote:
    >
    > DAO coding has a real problem with the "EditMode" property not working
    > properly at times (there's no official documentation on this bug, but
    > I have ran into it numerous times), which means I had to have a work
    > around in place to address this issue.


    I don't know what you're referring to, so I can't comment.

    > ADO coding doesn't allow for dynamic cursor keyset recordsets against
    > a Jet Engine as documented.


    The fact that you're delving into these details suggests to me that
    you're either working in an unusual environment, are facing unusual
    operational constraints, or are "over-programming". I couldn't say
    which.

    > Another thing I don't like about unbound controls, certain properties
    > aren't updated *AND* readable by VBA until *AFTER* that control has
    > lost focus, which doesn't help me out one bit in many cases. Listboxes
    > has this problem and impacted me so great to the point that
    > I had to create my own custom listbox class object to emulate the
    > actual listbox on the form, which then I ran into another bug dealing
    > with listbox selections via keyboard method. That's cause I was like,
    > if I'm going to emulate the listbox, I might as well go full out and
    > get everything including mouse movement, how selections are done
    > (Keyboard and mouse methods) under the 3 different selection mode
    > options, and what it will take, just as I emulated VB6's
    > CausesValidation property and Validate event to get around the fact
    > in Access, using the default error checking stuff on a control by
    > control basis, it either always or never gets checked, which you
    > would want checks to take place most of the time prior to a control
    > losing it's focus, but not everytime in a pretty big majority of the
    > cases.


    I'm somewhat curious as to what deficiencies of the Access listbox
    forced you to go to such lengths. I've found it to be a very functional
    control, only occasionally needing to be supplemented by a little bit of
    VBA programming.

    > One of the reasons why I put in checks on a control by control basis,
    > not just record by record basis, I did data entry for a time and if
    > an error was made that could reasonably be caught right there on the
    > spot without slowing things up, I would want to have it caught right
    > away and allow me to go back just briefly, and make the adjustment as
    > needed.


    There are differing ideas about this. I prefer immediate trapping of
    invalid data, but deferred trapping of missing data and inconsistent
    data.

    > The other thing, at the time when I did do this at the IRS,
    > it was all text based, no mouse. However, even with having the mouse
    > available, if you doing nothing but strictly data entry (either
    > numeric, straight, or alphanumeric), that constant switching back and
    > forth between the keyboard and mouse is a huge time waster from the
    > point of view how much time it takes to go back and forth as opposed
    > to just sticking to the keyboard in most cases.


    I don't follow you. You can easily create an Access UI that doesn't
    require using the mouse at all.

    Getting back to the original question, one thing we haven't really
    talked about is that you can fairly easily create a form that is
    dynamically bound to a single record, and that controls the saving of
    that record. So you can use bound forms with a high degree of control.
    It's not the default for Access, and requires a bit of programming, but
    it can be done.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
    Dirk Goldgar, Aug 23, 2007
    #6
  7. Ronald Dodge

    Ronald Dodge Guest

    So what would be required to go that route (bound to a single record)? I
    like to explore my options, especially if one that hasn't been explored is
    the one that's going to save me some heart ache and time. As for JDE, it
    primarily works on a record by record basis, so it could be quite the way
    for me to go in Access.

    As you said, it may be some of each, which the first 2 I definitely would
    agree with to a large extent. The third one (Over programming) could be a
    factor given that I'm detail oriented and there's been occassions when I had
    been too deep into the forest and didn't see the overall picture at that
    point of time.

    As you can probably tell, when I find something not working as it suppose
    to, I don't stop there but rather look for ways to either get rid of the
    issue (preferred means, but normally not the case when it's not dealing with
    my own coding) or getting around the issue.

    One of the properties on the listbox that didn't work for me while it still
    has the focus was the fact that the Selected Property (I think of it as more
    of a collection or an array) wasn't updated for as long as the listbox has
    control, which then I been known at times to have other actions performed,
    but would be dependent on what is or isn't selected as well as what's added
    and/or removed from the list. The Column property seemed to return the
    values based on what the listbox had in it prior to either the "Enter" or
    "GotFocus" event was on it, which then had to emulate the listbox so as I
    could perform certain things based on what's currently in it, not what was
    in it prior to making changes to it rather it be items themselves or the
    selection.
    --

    Sincerely,

    Ronald R. Dodge, Jr.
    Master MOUS 2000
     
    Ronald Dodge, Aug 24, 2007
    #7
  8. On 23 Aug, 22:51, "Ronald Dodge" <> wrote:
    > I will have to treat this project similar to ADO.NET's disconnected model, but only without the enhanced features for the
    > following reasons...
    >
    > ADO coding doesn't allow for dynamic cursor keyset recordsets against a Jet
    > Engine as documented.


    Do you need a dynamic cursor? It's not a requirement for working with
    disconnection recordsets and Jet. You do need a client side cursor,
    which is available when using other cursor types e.g. a keyset
    cursor.

    Jamie.

    --
     
    Jamie Collins, Aug 24, 2007
    #8
  9. Ronald Dodge

    Ronald Dodge Guest

    It would have been nice to have a dynamic cursor keyset to be able to see
    changes within the DB as they take place without having to requery, but
    given the issues that I facing, I see that doesn't seem to be a realistic
    possibility, which then has forced me to go the disconnected recordset type
    model such as what's used in ADO.NET programming, though keeping to within
    Access' objects and behavior.

    A dynamic cursor keyset allows for pessimistic record locking while a
    disconnected recordset model only allows for optimistic record locking,
    rather if it's done in batch mode (transactions) or done individually for
    updating the database.

    --

    Sincerely,

    Ronald R. Dodge, Jr.
    Master MOUS 2000

    "Jamie Collins" <> wrote in message
    news:...
    > On 23 Aug, 22:51, "Ronald Dodge" <> wrote:
    >> I will have to treat this project similar to ADO.NET's disconnected
    >> model, but only without the enhanced features for the
    >> following reasons...
    >>
    >> ADO coding doesn't allow for dynamic cursor keyset recordsets against a
    >> Jet
    >> Engine as documented.

    >
    > Do you need a dynamic cursor? It's not a requirement for working with
    > disconnection recordsets and Jet. You do need a client side cursor,
    > which is available when using other cursor types e.g. a keyset
    > cursor.
    >
    > Jamie.
    >
    > --
    >
    >
     
    Ronald Dodge, Aug 24, 2007
    #9
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Will

    Design View Properties do not match Form View Properties

    Will, Aug 15, 2003, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    352
  2. Brant Burrow

    Custom Properties on Controls - possible?

    Brant Burrow, Feb 20, 2004, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    173
    Guest
    Feb 26, 2004
  3. Jesper Fjølner

    loosing "[event procedure]" in properties when cutting and pasting controls

    Jesper Fjølner, Apr 22, 2005, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    222
    Jesper Fjølner
    Apr 22, 2005
  4. Guest

    Hide Controls based on the value of a Text

    Guest, Aug 3, 2006, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    231
    BruceM
    Aug 3, 2006
  5. Rebecca_SUNY

    Toggle text box properties based on option group value

    Rebecca_SUNY, Jul 9, 2008, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    145
    Beetle
    Jul 9, 2008
Loading...

Share This Page