Re: Prevented from saving data in the field
- From: "Wayne Morgan" <comprev_gothroughthenewsgroup@xxxxxxxxxxx>
- Date: Thu, 21 Apr 2005 16:30:43 -0500
The form's AfterUpdate fires after the table has been updated, but the
control's AfterUpdate fires after the control has been updated. The record
has not yet been saved to the table. Changing the value of the control via
VBA doesn't cause the BeforeUpdate to fire, so you won't get a loop, but it
does interfere with the update because you are changing the value while the
update is being processed (the AfterUpdate hasn't fired yet). The
AfterUpdate event of the control will fire before the form's BeforeUpdate
event, so the changes will be saved.
--
Wayne Morgan
MS Access MVP
"Chaim" <Chaim@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3CA10BD3-187C-4336-98C1-282B3A0363C7@xxxxxxxxxxxxxxxx
> Wayne,
>
> Thanks. The vbProperCase works perfectly. I don't use separate first and
> last name fields in the table for a reason that totally escapes me now
> (and
> the documentation that would let me refresh my memory is not available
> from
> this location- don't ask;-(). I started things off that way but I can't
> remember now why I changed them.
>
> Why the AfterUpdate procedure? Doesn't that fire after the table has been
> updated? This is something I haven't been able to get clear from the
> online
> Help. If so, how does the data that's already been saved get changed? And
> on
> the flip side, when does the BeforeUpdate fire? Not before the data in the
> control has been comitted to the table? The Help docs aren't very
> specific-
> they play pretty lose about what exactly is being updated and when.
>
> Thanks again.
>
> "Wayne Morgan" wrote:
>
>> Check the vbProperCase option of the StrConv function, I believe it will
>> do
>> what you're after. You can use this function in the textbox's AfterUpdate
>> event (not BeforeUpdate) to change the case. Also, I would recommend
>> separate first and last name fields. You can concatenate them together
>> when
>> you need them, but splitting them apart later can be a real pain.
>>
>> --
>> Wayne Morgan
>> MS Access MVP
>>
>>
>> "Chaim" <Chaim@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:8AEF2AC1-68F7-48C5-A8EB-D5E23E21ACCE@xxxxxxxxxxxxxxxx
>> >I have a simple form consisting of a text box in which the user enters a
>> >name
>> > in the form 'LastName, FirstName', and a combo box which allows the
>> > user
>> > to
>> > assign a role (one role per person, roles are static) to the name
>> > entered.
>> > The RecordSource for the form is a table consisting of: user ID (PK,
>> > autonumber), Full Name (text, 50), a role ID (long, the bound column of
>> > the
>> > combo box), and an email (text, currently ignored, not required, may be
>> > zero
>> > length). There are no validation rules or input formats set on any of
>> > the
>> > fields in the table.
>> >
>> > I want the txtFullName to be able to accept the user's input as all
>> > lower,
>> > upper or mixed case and either convert on the fly or be 'fixed up' so
>> > that
>> > it
>> > will look like a name: >L<?????????, >L<????????? (the usual format
>> > mask).
>> > However, I don't want to tell the user 'Sorry that name is too long'.
>> > So
>> > if
>> > they enter a 15 character last name or a 12 character first name, the
>> > format
>> > still has to work.
>> >
>> > 1. The simplest way to do this would be with an InputMask. Is there a
>> > way
>> > to
>> > specify an arbitrary length input mask to satisfy the intent mentioned
>> > above?
>> > I.e., is there something equivalent to a [Perl] RE like '.*' (a
>> > multiplier)
>> > so that the Input Mask would read something like: '>L<?*, >L<?*'? (I'm
>> > looking for the 95%+ solution right now.)
>> >
>> > 2. I have tried a BeforeUpdate event procedure that looks as follows:
>> >
>> > Private Sub txtFullName_BeforeUpdate (Cancel as Integer)
>> > dim val as String
>> > val = nameUpper (txtFullName)
>> > txtFullName = val
>> > Cancel = False
>> > End Sub
>> >
>> > Private Function nameUpper (val as String) as String
>> > dim commaPos as Long
>> > val = UCase (Left (val, 1)) & Mid (val, 2)
>> > commaPos = InStr (2, val, ", ")
>> > if commaPos > 0 then
>> > val = Left (val, commaPos + 1) & _ ' Preserve last
>> > name
>> > part
>> > UCase (Mid (val, commaPos + 2, 1) & _ ' Cap the
>> > first name
>> > Mid (val, commaPos + 3) ' Preserve rest
>> > of
>> > first name
>> > end if
>> > nameUpper = val
>> > End Function
>> >
>> > This function works correctly and can be tailored to suit. But the
>> > event
>> > procedure upon return from the function displays the following run time
>> > error
>> > (at the line which reads
>> > 'txtFullName = val'):
>> >
>> > Run Time Error '-2147352567 (800200009)
>> >
>> > The macro or function set to the BeforeUpdate or ValidationRule
>> > property
>> > for
>> > the field is preventing the <name of the database> from saving the data
>> > in
>> > the field. There are, as I said above, no validation rules applied to
>> > any
>> > field in this table.
>> >
>> > What's happening here?
>> >
>> > 3. I've also tried some other event procedures and ended up getting
>> > into
>> > infinite loops (LostFocus, Exit). So either I did something wrong
>> > (highly
>> > probable) when I wrote those (similar to the BeforeUpdate procedure
>> > above;
>> > they called the nameUpper() function to do the real work) or I am
>> > missing
>> > some condition which would break out of the loop. Something like Dirty?
>> >
>> > 4. Another option is a KeyPress event procedure, but I don't know how
>> > to
>> > tell the procedure to convert to upper case only on the first character
>> > entered, and the first character following a blank. Nothing seems to be
>> > 'in
>> > the text control' yet, so I can't test length or do InStr() calls to
>> > look
>> > for
>> > the blanks. When I've tried this, I get 'Invalid Use of Null' errors
>> > because
>> > the text control's value is null.
>> >
>> > If #1 is the best answer, I'll just go with that. But I'm more
>> > interested
>> > in
>> > knowing what the problem is with the other options.
>> >
>> > TIA. Apologies for being so verbose.
>> > --
>> > Chaim
>>
>>
>>
.
- Follow-Ups:
- Re: Prevented from saving data in the field
- From: Chaim
- Re: Prevented from saving data in the field
- References:
- Prevented from saving data in the field
- From: Chaim
- Re: Prevented from saving data in the field
- From: Wayne Morgan
- Re: Prevented from saving data in the field
- From: Chaim
- Prevented from saving data in the field
- Prev by Date: Re: Hidden Records on Form
- Next by Date: Re: Date() error
- Previous by thread: Re: Prevented from saving data in the field
- Next by thread: Re: Prevented from saving data in the field
- Index(es):
Relevant Pages
|
