default values in a subform

  • Thread starter Thread starter grace
  • Start date Start date
G

grace

Hello, I have a form that contains a subform. The subform
is used to store the historical or dynamic data for a user
from the tblempchange table. The main form is based off
the tblPersonalInfo table. For example, a personal id
(PIN) never changes so that is stored in the static table
(tblPersonalInfo) yet a person's job code may change so
that is stored in the dynamic table (tblempchange). The
tables are linked by the PIN.

With that said, is there a way to set the fields on the
subform so that if a new record is created in tblempchange
the fields default to the previous values. This way, we
would not have to reenter all the fields that are
contained in the dynamic table. We would just write over
the ones that change.

Hope this makes sense and thanks for your help.
 
Hi Ken,
Thanks for the update. I tried entering the code below
and nothing happens. When I create a new record in the
subform, all the fields are blank. There isn't any
default values. Using the field example: CostCenter, I
entered the folling event procedure:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = """" &
Me.CostCenter.Value & """"
End Sub

In trying to understand the code a little better, what do
the quotation marks do? """"?

An example of what I want is the CostCenter field. If the
value is 0100-03450, I would like that same value to
default in the next recrod in the CostCenter field.

Thanks again for your help.
Grace
 
Thanks again Ken.

Yes, I have the code entered exactly as you stated below.
I know that it's looking at the code because I got a
runtime error when I played around with it. I created a
second record - no default value displayed in the field
containing the event procedure. If I entered data in all
the fields and then deleted the record, some junk data
appears in the field containing the event procedure.

I'm at a loss. It is quite cumbersome for users to have
to reenter all the fields in the second record when they
only need to change the value of one field.

thank you,
Grace
-----Original Message-----
How did you enter this code: by opening Visual Basic Editor and typing it
in? If you didn't set the event to [Event Procedure] in the design view,
this code won't be seen by the form when it runs. Usual way to enter code
for an event is to open the form in design view, click on the control, click
on the Properties icon (toolbar), click on Event tab, click in the box next
to the desired event, select [Event Procedure] in the dropdown list, then
click on the "..." box at far right to open the VBE. Then type in the code.

With respect to the """", that is how to put a " character on either side of
the value so that it is a text string (DefaultValue expects a text string).
In VBA, one " signifies the beginning or ending of a text string; putting
two together "" tells VBA that you want the " character (otherwise, VBA
would not know whether you want the " character or if you're starting or
ending a text string). Thus, four " characters tells VBA 1) start a text
string; 2) use a " character as the first character in the text string, 3)
end the text string.
--
Ken Snell
<MS ACCESS MVP>

Hi Ken,
Thanks for the update. I tried entering the code below
and nothing happens. When I create a new record in the
subform, all the fields are blank. There isn't any
default values. Using the field example: CostCenter, I
entered the folling event procedure:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = """" &
Me.CostCenter.Value & """"
End Sub

In trying to understand the code a little better, what do
the quotation marks do? """"?

An example of what I want is the CostCenter field. If the
value is 0100-03450, I would like that same value to
default in the next recrod in the CostCenter field.

Thanks again for your help.
Grace
Me.ctlName.Value
& """"
Click to expand...


.
Click to expand...
 
You're using the word "field"; do you mean "control"? You cannot use this
code on a field, but only on a control. Can you verify that the name of the
control is what you're using?

Otherwise, the only idea I have is that somehow the code isn't being run
when you think it should.

If you wish, zip up a small example of the database and email it me (delete
it is not real from my address), and I'll take a quick look.

--
Ken Snell
<MS ACCESS MVP>

grace said:
Thanks again Ken.

Yes, I have the code entered exactly as you stated below.
I know that it's looking at the code because I got a
runtime error when I played around with it. I created a
second record - no default value displayed in the field
containing the event procedure. If I entered data in all
the fields and then deleted the record, some junk data
appears in the field containing the event procedure.

I'm at a loss. It is quite cumbersome for users to have
to reenter all the fields in the second record when they
only need to change the value of one field.

thank you,
Grace
-----Original Message-----
How did you enter this code: by opening Visual Basic Editor and typing it
in? If you didn't set the event to [Event Procedure] in the design view,
this code won't be seen by the form when it runs. Usual way to enter code
for an event is to open the form in design view, click on the control, click
on the Properties icon (toolbar), click on Event tab, click in the box next
to the desired event, select [Event Procedure] in the dropdown list, then
click on the "..." box at far right to open the VBE. Then type in the code.

With respect to the """", that is how to put a " character on either side of
the value so that it is a text string (DefaultValue expects a text string).
In VBA, one " signifies the beginning or ending of a text string; putting
two together "" tells VBA that you want the " character (otherwise, VBA
would not know whether you want the " character or if you're starting or
ending a text string). Thus, four " characters tells VBA 1) start a text
string; 2) use a " character as the first character in the text string, 3)
end the text string.
--
Ken Snell
<MS ACCESS MVP>

Hi Ken,
Thanks for the update. I tried entering the code below
and nothing happens. When I create a new record in the
subform, all the fields are blank. There isn't any
default values. Using the field example: CostCenter, I
entered the folling event procedure:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = """" &
Me.CostCenter.Value & """"
End Sub

In trying to understand the code a little better, what do
the quotation marks do? """"?

An example of what I want is the CostCenter field. If the
value is 0100-03450, I would like that same value to
default in the next recrod in the CostCenter field.

Thanks again for your help.
Grace

-----Original Message-----
Previous values of the controls in the subform? If yes,
you could put code
such as this on each control in the subform in order
to "change" the default
value of the controls as you wish:

Private Sub ctlName_AfterUpdate()
Me.ctlName.DefaultValue = """" & Me.ctlName.Value
& """"
End Sub

--
Ken Snell
<MS ACCESS MVP>

message
Hello, I have a form that contains a subform. The
subform
is used to store the historical or dynamic data for a
user
from the tblempchange table. The main form is based off
the tblPersonalInfo table. For example, a personal id
(PIN) never changes so that is stored in the static
table
(tblPersonalInfo) yet a person's job code may change so
that is stored in the dynamic table (tblempchange). The
tables are linked by the PIN.

With that said, is there a way to set the fields on the
subform so that if a new record is created in
tblempchange
the fields default to the previous values. This way, we
would not have to reenter all the fields that are
contained in the dynamic table. We would just write
over
the ones that change.

Hope this makes sense and thanks for your help.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Grace -

I looked at your code in the subform's control's AfterUpdate event; here is
what you have:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = CostCenter & Me.CostCenter.Value &
CostCenter
End Sub


What I'd posted for you to use is this:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = """" & Me.CostCenter.Value & """"
End Sub

I changed the code in your database to the above, and now the combo box
CostCenter displays the last selected value as you move to a new record.

--
Ken Snell
<MS ACCESS MVP>

Ken Snell said:
You're using the word "field"; do you mean "control"? You cannot use this
code on a field, but only on a control. Can you verify that the name of the
control is what you're using?

Otherwise, the only idea I have is that somehow the code isn't being run
when you think it should.

If you wish, zip up a small example of the database and email it me (delete
it is not real from my address), and I'll take a quick look.

--
Ken Snell
<MS ACCESS MVP>

grace said:
Thanks again Ken.

Yes, I have the code entered exactly as you stated below.
I know that it's looking at the code because I got a
runtime error when I played around with it. I created a
second record - no default value displayed in the field
containing the event procedure. If I entered data in all
the fields and then deleted the record, some junk data
appears in the field containing the event procedure.

I'm at a loss. It is quite cumbersome for users to have
to reenter all the fields in the second record when they
only need to change the value of one field.

thank you,
Grace
-----Original Message-----
How did you enter this code: by opening Visual Basic Editor and typing it
in? If you didn't set the event to [Event Procedure] in the design view,
this code won't be seen by the form when it runs. Usual way to enter code
for an event is to open the form in design view, click on the control, click
on the Properties icon (toolbar), click on Event tab, click in the box next
to the desired event, select [Event Procedure] in the dropdown list, then
click on the "..." box at far right to open the VBE. Then type in the code.

With respect to the """", that is how to put a " character on either side of
the value so that it is a text string (DefaultValue expects a text string).
In VBA, one " signifies the beginning or ending of a text string; putting
two together "" tells VBA that you want the " character (otherwise, VBA
would not know whether you want the " character or if you're starting or
ending a text string). Thus, four " characters tells VBA 1) start a text
string; 2) use a " character as the first character in the text string, 3)
end the text string.
--
Ken Snell
<MS ACCESS MVP>

Hi Ken,
Thanks for the update. I tried entering the code below
and nothing happens. When I create a new record in the
subform, all the fields are blank. There isn't any
default values. Using the field example: CostCenter, I
entered the folling event procedure:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = """" &
Me.CostCenter.Value & """"
End Sub

In trying to understand the code a little better, what do
the quotation marks do? """"?

An example of what I want is the CostCenter field. If the
value is 0100-03450, I would like that same value to
default in the next recrod in the CostCenter field.

Thanks again for your help.
Grace

-----Original Message-----
Previous values of the controls in the subform? If yes,
you could put code
such as this on each control in the subform in order
to "change" the default
value of the controls as you wish:

Private Sub ctlName_AfterUpdate()
Me.ctlName.DefaultValue = """" & Me.ctlName.Value
& """"
End Sub

--
Ken Snell
<MS ACCESS MVP>

message
Hello, I have a form that contains a subform. The
subform
is used to store the historical or dynamic data for a
user
from the tblempchange table. The main form is based off
the tblPersonalInfo table. For example, a personal id
(PIN) never changes so that is stored in the static
table
(tblPersonalInfo) yet a person's job code may change so
that is stored in the dynamic table (tblempchange). The
tables are linked by the PIN.

With that said, is there a way to set the fields on the
subform so that if a new record is created in
tblempchange
the fields default to the previous values. This way, we
would not have to reenter all the fields that are
contained in the dynamic table. We would just write
over
the ones that change.

Hope this makes sense and thanks for your help.


.



.
Click to expand...
Click to expand...
Click to expand...
 
Ken,
I apologize for the mistake and thank you very much for
your help. It is much appreciated.

Grace
-----Original Message-----
Grace -

I looked at your code in the subform's control's AfterUpdate event; here is
what you have:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = CostCenter & Me.CostCenter.Value &
CostCenter
End Sub


What I'd posted for you to use is this:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = """" & Me.CostCenter.Value & """"
End Sub

I changed the code in your database to the above, and now the combo box
CostCenter displays the last selected value as you move to a new record.

--
Ken Snell
<MS ACCESS MVP>

You're using the word "field"; do you mean "control"? You cannot use this
code on a field, but only on a control. Can you verify
Click to expand...
that the name of
the
control is what you're using?

Otherwise, the only idea I have is that somehow the code isn't being run
when you think it should.

If you wish, zip up a small example of the database
Click to expand...
and email it me
(delete
it is not real from my address), and I'll take a quick look.

--
Ken Snell
<MS ACCESS MVP>

Thanks again Ken.

Yes, I have the code entered exactly as you stated below.
I know that it's looking at the code because I got a
runtime error when I played around with it. I created a
second record - no default value displayed in the field
containing the event procedure. If I entered data in all
the fields and then deleted the record, some junk data
appears in the field containing the event procedure.

I'm at a loss. It is quite cumbersome for users to have
to reenter all the fields in the second record when they
only need to change the value of one field.

thank you,
Grace
-----Original Message-----
How did you enter this code: by opening Visual Basic
Editor and typing it
in? If you didn't set the event to [Event Procedure] in
the design view,
this code won't be seen by the form when it runs. Usual
way to enter code
for an event is to open the form in design view, click on
the control, click
on the Properties icon (toolbar), click on Event tab,
click in the box next
to the desired event, select [Event Procedure] in the
dropdown list, then
click on the "..." box at far right to open the VBE. Then
type in the code.

With respect to the """", that is how to put a "
character on either side of
the value so that it is a text string (DefaultValue
expects a text string).
In VBA, one " signifies the beginning or ending of a text
string; putting
two together "" tells VBA that you want the " character
(otherwise, VBA
would not know whether you want the " character or if
you're starting or
ending a text string). Thus, four " characters tells VBA
1) start a text
string; 2) use a " character as the first character in
the text string, 3)
end the text string.
--
Ken Snell
<MS ACCESS MVP>

message
Hi Ken,
Thanks for the update. I tried entering the code below
and nothing happens. When I create a new record in the
subform, all the fields are blank. There isn't any
default values. Using the field example: CostCenter, I
entered the folling event procedure:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = """" &
Me.CostCenter.Value & """"
End Sub

In trying to understand the code a little better, what
do
the quotation marks do? """"?

An example of what I want is the CostCenter field. If
the
value is 0100-03450, I would like that same value to
default in the next recrod in the CostCenter field.

Thanks again for your help.
Grace

-----Original Message-----
Previous values of the controls in the subform? If yes,
you could put code
such as this on each control in the subform in order
to "change" the default
value of the controls as you wish:

Private Sub ctlName_AfterUpdate()
Me.ctlName.DefaultValue = """" &
Me.ctlName.Value
& """"
End Sub

--
Ken Snell
<MS ACCESS MVP>

message
Hello, I have a form that contains a subform. The
subform
is used to store the historical or dynamic data for a
user
from the tblempchange table. The main form is based
off
the tblPersonalInfo table. For example, a personal
id
(PIN) never changes so that is stored in the static
table
(tblPersonalInfo) yet a person's job code may change
so
that is stored in the dynamic table (tblempchange).
The
tables are linked by the PIN.

With that said, is there a way to set the fields on
the
subform so that if a new record is created in
tblempchange
the fields default to the previous values. This
way, we
would not have to reenter all the fields that are
contained in the dynamic table. We would just write
over
the ones that change.

Hope this makes sense and thanks for your help.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
You're welcome.

grace said:
Ken,
I apologize for the mistake and thank you very much for
your help. It is much appreciated.

Grace
-----Original Message-----
Grace -

I looked at your code in the subform's control's AfterUpdate event; here is
what you have:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = CostCenter & Me.CostCenter.Value &
CostCenter
End Sub


What I'd posted for you to use is this:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = """" & Me.CostCenter.Value & """"
End Sub

I changed the code in your database to the above, and now the combo box
CostCenter displays the last selected value as you move to a new record.

--
Ken Snell
<MS ACCESS MVP>

You're using the word "field"; do you mean "control"? You cannot use this
code on a field, but only on a control. Can you verify
Click to expand...
that the name of
the
control is what you're using?

Otherwise, the only idea I have is that somehow the code isn't being run
when you think it should.

If you wish, zip up a small example of the database
Click to expand...
and email it me
(delete
it is not real from my address), and I'll take a quick look.

--
Ken Snell
<MS ACCESS MVP>

Thanks again Ken.

Yes, I have the code entered exactly as you stated below.
I know that it's looking at the code because I got a
runtime error when I played around with it. I created a
second record - no default value displayed in the field
containing the event procedure. If I entered data in all
the fields and then deleted the record, some junk data
appears in the field containing the event procedure.

I'm at a loss. It is quite cumbersome for users to have
to reenter all the fields in the second record when they
only need to change the value of one field.

thank you,
Grace
-----Original Message-----
How did you enter this code: by opening Visual Basic
Editor and typing it
in? If you didn't set the event to [Event Procedure] in
the design view,
this code won't be seen by the form when it runs. Usual
way to enter code
for an event is to open the form in design view, click on
the control, click
on the Properties icon (toolbar), click on Event tab,
click in the box next
to the desired event, select [Event Procedure] in the
dropdown list, then
click on the "..." box at far right to open the VBE. Then
type in the code.

With respect to the """", that is how to put a "
character on either side of
the value so that it is a text string (DefaultValue
expects a text string).
In VBA, one " signifies the beginning or ending of a text
string; putting
two together "" tells VBA that you want the " character
(otherwise, VBA
would not know whether you want the " character or if
you're starting or
ending a text string). Thus, four " characters tells VBA
1) start a text
string; 2) use a " character as the first character in
the text string, 3)
end the text string.
--
Ken Snell
<MS ACCESS MVP>

message
Hi Ken,
Thanks for the update. I tried entering the code below
and nothing happens. When I create a new record in the
subform, all the fields are blank. There isn't any
default values. Using the field example: CostCenter, I
entered the folling event procedure:

Private Sub CostCenter_AfterUpdate()
Me.CostCenter.DefaultValue = """" &
Me.CostCenter.Value & """"
End Sub

In trying to understand the code a little better, what
do
the quotation marks do? """"?

An example of what I want is the CostCenter field. If
the
value is 0100-03450, I would like that same value to
default in the next recrod in the CostCenter field.

Thanks again for your help.
Grace

-----Original Message-----
Previous values of the controls in the subform? If yes,
you could put code
such as this on each control in the subform in order
to "change" the default
value of the controls as you wish:

Private Sub ctlName_AfterUpdate()
Me.ctlName.DefaultValue = """" &
Me.ctlName.Value
& """"
End Sub

--
Ken Snell
<MS ACCESS MVP>

message
Hello, I have a form that contains a subform. The
subform
is used to store the historical or dynamic data for a
user
from the tblempchange table. The main form is based
off
the tblPersonalInfo table. For example, a personal
id
(PIN) never changes so that is stored in the static
table
(tblPersonalInfo) yet a person's job code may change
so
that is stored in the dynamic table (tblempchange).
The
tables are linked by the PIN.

With that said, is there a way to set the fields on
the
subform so that if a new record is created in
tblempchange
the fields default to the previous values. This
way, we
would not have to reenter all the fields that are
contained in the dynamic table. We would just write
over
the ones that change.

Hope this makes sense and thanks for your help.


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top