Prev: Need null values for several fields in db
Next: Convert to Proper or Title Case in Access 2003
From: Marshall Barton on 29 Apr 2010 18:43
Leo wrote:
>Also I made it work by inserting in the afterupdate event of the control. But
>it then updates gobally and not seem to be patient specific. I need to update
>automatically when a new form is opened on the same patient.


That's where you should put it **IF** you want to set the
default when a user enters the patient's height and you want
to use that for any NEW records. If you want to set it to
the value in the form's first record when the form opens,
then use the form's Load event. Possibly, you want to do
both.

"It updates globally" is probably because you set the
control's Value instead of its DefaultValue.

Remember that a DefaultValue is only used when a new record
is started. It should have no effect on existing records.

--
Marsh
MVP [MS Access]
From: Leo on 29 Apr 2010 18:44
Marshall, I did understand that and has recovered from it. Now the problem I
need to solve is this.

I don't want to set the default value globally so that when a user enters
the value that value is carried to all the individuals. I need it to be
specific for that individual so that anytime a form is open for that
individual his/her values are automatically updated from the last entry. It
should also not change the values of that individual's prior records. Each
individual will have one record for each month. (Each individual also has an
Id number that is being automatically inserted into this form since it is a
subform). Any way this could be accomplished.

Thanking you
Leo

"Marshall Barton" wrote:

> Exactly!
>
> The problem is that you tried to put that VBA code in the
> property that the code is trying to set. You seem to have
> misunderstood the difference between VBA code and an
> expression. You said that you used the expression builder
> to create those VBA statements, but I don't see how that can
> be possible. Maybe you took a wrong turn somewhere and used
> the code builder and somehow stuffed the code into the
> property? I think it more likely that the wrong turn was
> when you just typed Dave's VBA code directly into the
> property.
>
> Regardless of all that, the DefaultValue property expects an
> expression (indicated by the = sign). You are NOT doing
> that. You should be using the VBA code statements to create
> the expression (which doesn't really require the = sign) and
> push it into the DefaultValue property.
>
> If you want to set the default value whenever a user
> manually sets the property, then put the VBA code in the
> control's AfterUpdate event (not the form's Load event).
>
> If you do not understand the distinction I am trying to
> explain, then just try what Dave and I are both saying,
> instead persisting with your misinterpretation of what he
> said.
> --
> Marsh
> MVP [MS Access]
>
>
> Leo wrote:
> >But this is what Dave has and it specifies the controls defaualt property
> >though!
> >
> >(A) To use the curent control value for new records, you need to assign
> >it to the defaultvalue of the control. For example something like
> >
> >'******** Code Start **********
> > const cQuote="""" 'Thats two quotes
> > me!Control.DefaultValue = cQuote & me!Control.Value & cQuote
> >'******** Code End **********
> >
> >
> >"Marshall Barton" wrote:
> >
> >> Leo wrote:
> >>
> >> >I need to enter the Height of the patient every time the form opens. The
> >> >name of the control is [Fld37]. Yes I will name it better next time!
> >> >
> >> >I used Dave Ashish method and entered the expression in the default value of
> >> >the control's data. The following is the expression it created after I typed
> >> >in the code in the expression builder (the only option it will give me).
> >> >
> >> > = const cHeight=""""
> >> >me![Fld37].defaultValue=cHeight&me![Fld37].Value&cHeight
> >> >
> >> >It is not working and flashes the following error message:
> >> >
> >> > You might have entered an operand without an operator
> >> >
> >> >What am I doing wrong?
> >>
> >>
> >> You have mixed up the differences between an **expression**
> >> on a control property and **VBA code** in an event
> >> procedure.
> >>
> >> To set a control's DafaultValue property to the value in the
> >> form's first record, use the form's Load event **procedure**
> >> with code like you tried to put in the property:
> >>
> >> Const cHeight = """"
> >> Me![Fld37].DefaultValue = cHeight & Me![Fld37] & cHeight
> >>
> >> --
> >> Marsh
> >> MVP [MS Access]
> >> .
> >>
>
> .
>
From: Leo on 29 Apr 2010 19:13
Also I have inserted a module from Allan Brown that does what I want to do.
But then it inserts values into every field as it is designed to do. Then I
have to specify which are the fields that I dont want to be updated, and
there are too many of them!

"Leo" wrote:

> Marshall, I did understand that and has recovered from it. Now the problem I
> need to solve is this.
>
> I don't want to set the default value globally so that when a user enters
> the value that value is carried to all the individuals. I need it to be
> specific for that individual so that anytime a form is open for that
> individual his/her values are automatically updated from the last entry. It
> should also not change the values of that individual's prior records. Each
> individual will have one record for each month. (Each individual also has an
> Id number that is being automatically inserted into this form since it is a
> subform). Any way this could be accomplished.
>
> Thanking you
> Leo
>
> "Marshall Barton" wrote:
>
> > Exactly!
> >
> > The problem is that you tried to put that VBA code in the
> > property that the code is trying to set. You seem to have
> > misunderstood the difference between VBA code and an
> > expression. You said that you used the expression builder
> > to create those VBA statements, but I don't see how that can
> > be possible. Maybe you took a wrong turn somewhere and used
> > the code builder and somehow stuffed the code into the
> > property? I think it more likely that the wrong turn was
> > when you just typed Dave's VBA code directly into the
> > property.
> >
> > Regardless of all that, the DefaultValue property expects an
> > expression (indicated by the = sign). You are NOT doing
> > that. You should be using the VBA code statements to create
> > the expression (which doesn't really require the = sign) and
> > push it into the DefaultValue property.
> >
> > If you want to set the default value whenever a user
> > manually sets the property, then put the VBA code in the
> > control's AfterUpdate event (not the form's Load event).
> >
> > If you do not understand the distinction I am trying to
> > explain, then just try what Dave and I are both saying,
> > instead persisting with your misinterpretation of what he
> > said.
> > --
> > Marsh
> > MVP [MS Access]
> >
> >
> > Leo wrote:
> > >But this is what Dave has and it specifies the controls defaualt property
> > >though!
> > >
> > >(A) To use the curent control value for new records, you need to assign
> > >it to the defaultvalue of the control. For example something like
> > >
> > >'******** Code Start **********
> > > const cQuote="""" 'Thats two quotes
> > > me!Control.DefaultValue = cQuote & me!Control.Value & cQuote
> > >'******** Code End **********
> > >
> > >
> > >"Marshall Barton" wrote:
> > >
> > >> Leo wrote:
> > >>
> > >> >I need to enter the Height of the patient every time the form opens. The
> > >> >name of the control is [Fld37]. Yes I will name it better next time!
> > >> >
> > >> >I used Dave Ashish method and entered the expression in the default value of
> > >> >the control's data. The following is the expression it created after I typed
> > >> >in the code in the expression builder (the only option it will give me).
> > >> >
> > >> > = const cHeight=""""
> > >> >me![Fld37].defaultValue=cHeight&me![Fld37].Value&cHeight
> > >> >
> > >> >It is not working and flashes the following error message:
> > >> >
> > >> > You might have entered an operand without an operator
> > >> >
> > >> >What am I doing wrong?
> > >>
> > >>
> > >> You have mixed up the differences between an **expression**
> > >> on a control property and **VBA code** in an event
> > >> procedure.
> > >>
> > >> To set a control's DafaultValue property to the value in the
> > >> form's first record, use the form's Load event **procedure**
> > >> with code like you tried to put in the property:
> > >>
> > >> Const cHeight = """"
> > >> Me![Fld37].DefaultValue = cHeight & Me![Fld37] & cHeight
> > >>
> > >> --
> > >> Marsh
> > >> MVP [MS Access]
> > >> .
> > >>
> >
> > .
> >
From: Marshall Barton on 29 Apr 2010 23:42
Leo wrote:

>Marshall, I did understand that and has recovered from it. Now the problem I
>need to solve is this.
>
>I don't want to set the default value globally so that when a user enters
>the value that value is carried to all the individuals. I need it to be
>specific for that individual so that anytime a form is open for that
>individual his/her values are automatically updated from the last entry. It
>should also not change the values of that individual's prior records. Each
>individual will have one record for each month. (Each individual also has an
>Id number that is being automatically inserted into this form since it is a
>subform). Any way this could be accomplished.


Well, there usually is a way to all kinds of things, but
some things are more complex than others. Because
DefaultValue is a property of the text box, it has no
connection to some other information in other records (ie.
the individual)

That means you would have to create a way to determine the
individual and the default height you want to use for the
individual. I think that can be done by having a table with
one record per individual and adding a field for the default
height. Then you could use the form's Current event to
retrieve the default height field from that table and stuff
that into the text box's DefaultValue property. With the
default height in that kind of table, it would probably be
easier to join that table to the table you have now to make
the default height a field in the form's record source
query.

At his point I am not convinced that the DefaultValue
property is really helping here. You could just use the
form's BeforeInsert event to retrieve the value and stuff it
directly into the value property when a new record is being
created. OTOH, considering how new you are to all this, you
may want to put a hold on this particular goal until you
have a firm grasp on using VBA code.

--
Marsh
MVP [MS Access]
From: Marshall Barton on 29 Apr 2010 23:46
Leo wrote:

>Also I have inserted a module from Allan Brown that does what I want to do.
>But then it inserts values into every field as it is designed to do. Then I
>have to specify which are the fields that I dont want to be updated, and
>there are too many of them!


Sorry, I have no idea what you are referring to here.

Generally, you can modify the code to use just the fields
you want or play a few other tricks to identify which fields
should be set an which ones should be ignored.

--
Marsh
MVP [MS Access]
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Need null values for several fields in db
Next: Convert to Proper or Title Case in Access 2003