Default Value on Form with If

[PPCO]

I have two fields on a form: Equipment Name and Activity. I want the field
Activity to auto fill from a list based on what's in Equipment Name. I've
tried a couple of expressions in the expression builder, but to no avail.
Thanks for any help

 

[PPCO]

Here's the expression I tried: =IIf([equipmentname] Is Not
Null,[tblEquipment]![ActivityName],Null).

 

[Ken Sheridan]

You can do it in various ways e.g.

1. Call the DLookup function in an expression as the ControlSource of the
Activity control:

=DLookup("ActivityName", "tblEquipment", "EquipmentName = """ &
[EquipmentName] & """")

This assumes that the tblEquipment table includes a column EquipmentName
which is referenced by the EquipmentName column in the form's underlying
table.

2. Base the form on a query which joins its current underlying table(s) to
the tblEquipment table on EquipmentName and bind a text box control to the
ActivityName column from the tblEquipment table. Note that the control
would be updatable in this case, so you might want to set its locked property
to True and its Enabled property to False to prevent it being edited.

3. For the EquipmentName control on the form use a combo box with a
RowSource property of:

SELECT EquipmentName, ActivityName FROM tblEquipment ORDER BY EquipmentName;

Set its ColumnCount property to 2. For the ControlSource of the Activity
control reference the unseen second column of the combo box with:

=EquipmentName.Column(1)

The column property is zero-based, so Column(1) is the second column.

What you should not do is include an ActivityName column in the forms
underlying table, i.e. the table which references the tblEquipment table via
the EquipmentName foreign key column. That would introduce redundancy and
the risk of inconstant data. The activity for the equipment in question is
known via the relationship so can be shown whenever necessary using the above
methods.

Ken Sheridan
Stafford, England

Here's the expression I tried: =IIf([equipmentname] Is Not
Null,[tblEquipment]![ActivityName],Null).

I have two fields on a form: Equipment Name and Activity. I want the field
Activity to auto fill from a list based on what's in Equipment Name. I've
tried a couple of expressions in the expression builder, but to no avail.
Thanks for any help

 

[PPCO]

Looks like the first one would be the one--not totally sure if I follow it,
but I'll give it a try. I will always be manually filling in a value in the
Equipment Name field, I just want the activity to auto fill. Thanks for all
your help.

You can do it in various ways e.g.

1. Call the DLookup function in an expression as the ControlSource of the
Activity control:

=DLookup("ActivityName", "tblEquipment", "EquipmentName = """ &
[EquipmentName] & """")

This assumes that the tblEquipment table includes a column EquipmentName
which is referenced by the EquipmentName column in the form's underlying
table.

2. Base the form on a query which joins its current underlying table(s) to
the tblEquipment table on EquipmentName and bind a text box control to the
ActivityName column from the tblEquipment table. Note that the control
would be updatable in this case, so you might want to set its locked property
to True and its Enabled property to False to prevent it being edited.

3. For the EquipmentName control on the form use a combo box with a
RowSource property of:

SELECT EquipmentName, ActivityName FROM tblEquipment ORDER BY EquipmentName;

Set its ColumnCount property to 2. For the ControlSource of the Activity
control reference the unseen second column of the combo box with:

=EquipmentName.Column(1)

The column property is zero-based, so Column(1) is the second column.

What you should not do is include an ActivityName column in the forms
underlying table, i.e. the table which references the tblEquipment table via
the EquipmentName foreign key column. That would introduce redundancy and
the risk of inconstant data. The activity for the equipment in question is
known via the relationship so can be shown whenever necessary using the above
methods.

Ken Sheridan
Stafford, England

Here's the expression I tried: =IIf([equipmentname] Is Not
Null,[tblEquipment]![ActivityName],Null).

I have two fields on a form: Equipment Name and Activity. I want the field
Activity to auto fill from a list based on what's in Equipment Name. I've
tried a couple of expressions in the expression builder, but to no avail.
Thanks for any help

 

[PPCO]

Cut and pasted it and it works perfectly. Don't know what I did, but it
works. Out of curiosity, is there a way to do something like this and still
also be able to manually fill in the activity field if needed?

Looks like the first one would be the one--not totally sure if I follow it,
but I'll give it a try. I will always be manually filling in a value in the
Equipment Name field, I just want the activity to auto fill. Thanks for all
your help.

You can do it in various ways e.g.

1. Call the DLookup function in an expression as the ControlSource of the
Activity control:

=DLookup("ActivityName", "tblEquipment", "EquipmentName = """ &
[EquipmentName] & """")

This assumes that the tblEquipment table includes a column EquipmentName
which is referenced by the EquipmentName column in the form's underlying
table.

2. Base the form on a query which joins its current underlying table(s) to
the tblEquipment table on EquipmentName and bind a text box control to the
ActivityName column from the tblEquipment table. Note that the control
would be updatable in this case, so you might want to set its locked property
to True and its Enabled property to False to prevent it being edited.

3. For the EquipmentName control on the form use a combo box with a
RowSource property of:

SELECT EquipmentName, ActivityName FROM tblEquipment ORDER BY EquipmentName;

Set its ColumnCount property to 2. For the ControlSource of the Activity
control reference the unseen second column of the combo box with:

=EquipmentName.Column(1)

The column property is zero-based, so Column(1) is the second column.

What you should not do is include an ActivityName column in the forms
underlying table, i.e. the table which references the tblEquipment table via
the EquipmentName foreign key column. That would introduce redundancy and
the risk of inconstant data. The activity for the equipment in question is
known via the relationship so can be shown whenever necessary using the above
methods.

Ken Sheridan
Stafford, England

Here's the expression I tried: =IIf([equipmentname] Is Not
Null,[tblEquipment]![ActivityName],Null).
On the form, it shows #Name? in the activity field.

:

I have two fields on a form: Equipment Name and Activity. I want the field
Activity to auto fill from a list based on what's in Equipment Name. I've
tried a couple of expressions in the expression builder, but to no avail.
Thanks for any help

 

[PPCO]

What is this part of the expression saying in plain english: "EquipmentName
= """ & [EquipmentName] & """"). Just curious...thanks for all the help!

Looks like the first one would be the one--not totally sure if I follow it,
but I'll give it a try. I will always be manually filling in a value in the
Equipment Name field, I just want the activity to auto fill. Thanks for all
your help.

You can do it in various ways e.g.

1. Call the DLookup function in an expression as the ControlSource of the
Activity control:

=DLookup("ActivityName", "tblEquipment", "EquipmentName = """ &
[EquipmentName] & """")

This assumes that the tblEquipment table includes a column EquipmentName
which is referenced by the EquipmentName column in the form's underlying
table.

2. Base the form on a query which joins its current underlying table(s) to
the tblEquipment table on EquipmentName and bind a text box control to the
ActivityName column from the tblEquipment table. Note that the control
would be updatable in this case, so you might want to set its locked property
to True and its Enabled property to False to prevent it being edited.

3. For the EquipmentName control on the form use a combo box with a
RowSource property of:

SELECT EquipmentName, ActivityName FROM tblEquipment ORDER BY EquipmentName;

Set its ColumnCount property to 2. For the ControlSource of the Activity
control reference the unseen second column of the combo box with:

=EquipmentName.Column(1)

The column property is zero-based, so Column(1) is the second column.

What you should not do is include an ActivityName column in the forms
underlying table, i.e. the table which references the tblEquipment table via
the EquipmentName foreign key column. That would introduce redundancy and
the risk of inconstant data. The activity for the equipment in question is
known via the relationship so can be shown whenever necessary using the above
methods.

Ken Sheridan
Stafford, England

Here's the expression I tried: =IIf([equipmentname] Is Not
Null,[tblEquipment]![ActivityName],Null).
On the form, it shows #Name? in the activity field.

:

I have two fields on a form: Equipment Name and Activity. I want the field
Activity to auto fill from a list based on what's in Equipment Name. I've
tried a couple of expressions in the expression builder, but to no avail.
Thanks for any help

 

[Douglas J. Steele]

That assumes that EquipmentName is a text field, so it's putting quotes
around the value in [EquipmentName].

See what Allen Browne has at http://www.allenbrowne.com/casu-17.html for
more information.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What is this part of the expression saying in plain english:
"EquipmentName
= """ & [EquipmentName] & """"). Just curious...thanks for all the help!

Looks like the first one would be the one--not totally sure if I follow
it,
but I'll give it a try. I will always be manually filling in a value in
the
Equipment Name field, I just want the activity to auto fill. Thanks for
all
your help.

You can do it in various ways e.g.

1. Call the DLookup function in an expression as the ControlSource of
the
Activity control:

=DLookup("ActivityName", "tblEquipment", "EquipmentName = """ &
[EquipmentName] & """")

This assumes that the tblEquipment table includes a column
EquipmentName
which is referenced by the EquipmentName column in the form's
underlying
table.

2. Base the form on a query which joins its current underlying
table(s) to
the tblEquipment table on EquipmentName and bind a text box control to
the
ActivityName column from the tblEquipment table. Note that the
control
would be updatable in this case, so you might want to set its locked
property
to True and its Enabled property to False to prevent it being edited.

3. For the EquipmentName control on the form use a combo box with a
RowSource property of:

SELECT EquipmentName, ActivityName FROM tblEquipment ORDER BY
EquipmentName;

Set its ColumnCount property to 2. For the ControlSource of the
Activity
control reference the unseen second column of the combo box with:

=EquipmentName.Column(1)

The column property is zero-based, so Column(1) is the second column.

What you should not do is include an ActivityName column in the forms
underlying table, i.e. the table which references the tblEquipment
table via
the EquipmentName foreign key column. That would introduce redundancy
and
the risk of inconstant data. The activity for the equipment in
question is
known via the relationship so can be shown whenever necessary using the
above
methods.

Ken Sheridan
Stafford, England

:

Here's the expression I tried: =IIf([equipmentname] Is Not
Null,[tblEquipment]![ActivityName],Null).
On the form, it shows #Name? in the activity field.

:

I have two fields on a form: Equipment Name and Activity. I want
the field
Activity to auto fill from a list based on what's in Equipment
Name. I've
tried a couple of expressions in the expression builder, but to no
avail.
Thanks for any help

 

[Ken Sheridan]

To be able to edit the value, the ActivityName control would have to be bound
to a field in the underlying table. I know I said don't have such a field,
but the fact that you want to be able to change it from the value in the
referenced row in the tblEquipment table changes the model, and means that it
does not now introduce redundancy to have such a field. In the jargon the
ActivityName fields in the two tables are each said to be 'functionally
dependent' solely on the whole of the key of their own table, which means,
other things being equal, that the table is normalized to Third Normal Form
(3NF). Normalization is the process of eliminating redundancy by
'decomposing' tables into related tables so that the normalization rules are
satisfied. There are normal forms from 1 to 5, along with an extra one
(Boyce Codd Normal Form or BCNF) which was inserted when it was found that
the original set didn't cater for one particular situation. There are normal
forms beyond 5, but they are somewhat bizarre and distinct from the basic 5.

So, the ControlSource would now be the name of the ActivityName field in the
form's table, and to set its value you'd 'push' in the value looked up from
the tblEquipment table by putting the following code in the AfterUpdate event
procedure of the EquipmentName control on the form:

Dim strCriteria As String

strCriteria = "EquipmentName = """ & Me.EquipmentName & """"

Me.ActivityName = DLookup("ActivityName", "tblEquipment", strCriteria)

To do this select the control in form design view and open its properties
sheet if its not already open. Then select the AfterUpdate event property in
the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the lines of code between these two existing lines.

Ken Sheridan
Stafford, England

Cut and pasted it and it works perfectly. Don't know what I did, but it
works. Out of curiosity, is there a way to do something like this and still
also be able to manually fill in the activity field if needed?

Looks like the first one would be the one--not totally sure if I follow it,
but I'll give it a try. I will always be manually filling in a value in the
Equipment Name field, I just want the activity to auto fill. Thanks for all
your help.

You can do it in various ways e.g.

1. Call the DLookup function in an expression as the ControlSource of the
Activity control:

=DLookup("ActivityName", "tblEquipment", "EquipmentName = """ &
[EquipmentName] & """")

This assumes that the tblEquipment table includes a column EquipmentName
which is referenced by the EquipmentName column in the form's underlying
table.

2. Base the form on a query which joins its current underlying table(s) to
the tblEquipment table on EquipmentName and bind a text box control to the
ActivityName column from the tblEquipment table. Note that the control
would be updatable in this case, so you might want to set its locked property
to True and its Enabled property to False to prevent it being edited.

3. For the EquipmentName control on the form use a combo box with a
RowSource property of:

SELECT EquipmentName, ActivityName FROM tblEquipment ORDER BY EquipmentName;

Set its ColumnCount property to 2. For the ControlSource of the Activity
control reference the unseen second column of the combo box with:

=EquipmentName.Column(1)

The column property is zero-based, so Column(1) is the second column.

What you should not do is include an ActivityName column in the forms
underlying table, i.e. the table which references the tblEquipment table via
the EquipmentName foreign key column. That would introduce redundancy and
the risk of inconstant data. The activity for the equipment in question is
known via the relationship so can be shown whenever necessary using the above
methods.

Ken Sheridan
Stafford, England

:

Here's the expression I tried: =IIf([equipmentname] Is Not
Null,[tblEquipment]![ActivityName],Null).
On the form, it shows #Name? in the activity field.

:

I have two fields on a form: Equipment Name and Activity. I want the field
Activity to auto fill from a list based on what's in Equipment Name. I've
tried a couple of expressions in the expression builder, but to no avail.
Thanks for any help

 

[PPCO]

So I've been using this for a few days, but noticed that it's not actually
putting the data from the activity field into the table. It only shows up in
the datasheet view for the form. I'm sure I must have just missed something
small...just need the activity automatically entered into the form's
underlying table. Thanks!

That assumes that EquipmentName is a text field, so it's putting quotes
around the value in [EquipmentName].

See what Allen Browne has at http://www.allenbrowne.com/casu-17.html for
more information.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What is this part of the expression saying in plain english:
"EquipmentName
= """ & [EquipmentName] & """"). Just curious...thanks for all the help!

Looks like the first one would be the one--not totally sure if I follow
it,
but I'll give it a try. I will always be manually filling in a value in
the
Equipment Name field, I just want the activity to auto fill. Thanks for
all
your help.

:

You can do it in various ways e.g.

1. Call the DLookup function in an expression as the ControlSource of
the
Activity control:

=DLookup("ActivityName", "tblEquipment", "EquipmentName = """ &
[EquipmentName] & """")

This assumes that the tblEquipment table includes a column
EquipmentName
which is referenced by the EquipmentName column in the form's
underlying
table.

2. Base the form on a query which joins its current underlying
table(s) to
the tblEquipment table on EquipmentName and bind a text box control to
the
ActivityName column from the tblEquipment table. Note that the
control
would be updatable in this case, so you might want to set its locked
property
to True and its Enabled property to False to prevent it being edited.

3. For the EquipmentName control on the form use a combo box with a
RowSource property of:

SELECT EquipmentName, ActivityName FROM tblEquipment ORDER BY
EquipmentName;

Set its ColumnCount property to 2. For the ControlSource of the
Activity
control reference the unseen second column of the combo box with:

=EquipmentName.Column(1)

The column property is zero-based, so Column(1) is the second column.

What you should not do is include an ActivityName column in the forms
underlying table, i.e. the table which references the tblEquipment
table via
the EquipmentName foreign key column. That would introduce redundancy
and
the risk of inconstant data. The activity for the equipment in
question is
known via the relationship so can be shown whenever necessary using the
above
methods.

Ken Sheridan
Stafford, England

:

Here's the expression I tried: =IIf([equipmentname] Is Not
Null,[tblEquipment]![ActivityName],Null).
On the form, it shows #Name? in the activity field.

:

I have two fields on a form: Equipment Name and Activity. I want
the field
Activity to auto fill from a list based on what's in Equipment
Name. I've
tried a couple of expressions in the expression builder, but to no
avail.
Thanks for any help

 

[Ken Sheridan]

That suggests to me you are using the method I first described before you
asked what to do to be able to change the value after it had been inserted
into the control, i.e. you are using an unbound control with a ControlSource
along the lines of:

=DLookup("ActivityName", "tblEquipment", "EquipmentName = """ &
[EquipmentName] & """")

For the value to be assigned to the column in the underlying table you
should use the method I described in my subsequent post, i.e.

1. The ControlSource property of the text box being filled should be the
name of the ActivityName field.

2. In the AfterUpdate event procedure of the EquipmentName control the
value is as9gned to ActivityName with:

Dim strCriteria As String

strCriteria = "EquipmentName = """ & Me.EquipmentName & """"

Me.ActivityName = DLookup("ActivityName", "tblEquipment", strCriteria)

You should only use this second method if (a) you really do need to manually
change the value after its been automatically assigned to the control or (b)
the value might be changed in the referenced tblEquipment table, but you want
the rows in the form's underlying table to retain the original value. If the
ActivityName value is *always* going to be that determined by the
EquipmentName value then you should not have an ActivityName column in the
form's underlying table at all as it introduces redundancy and the table is
not correctly normalized for the reasons I described. Simply seeing the
value in an unbound control in the form is how it should be done in this
scenario, so deleting the column from the form's underlying table would be
all that's required. You can also if necessary see the value in a similarly
computed unbound control in a report or in a query which joins the two
tables. You'll recall that the second method suggested in my original reply
involved such a query.

Ken Sheridan
Stafford, England

So I've been using this for a few days, but noticed that it's not actually
putting the data from the activity field into the table. It only shows up in
the datasheet view for the form. I'm sure I must have just missed something
small...just need the activity automatically entered into the form's
underlying table. Thanks!

That assumes that EquipmentName is a text field, so it's putting quotes
around the value in [EquipmentName].

See what Allen Browne has at http://www.allenbrowne.com/casu-17.html for
more information.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What is this part of the expression saying in plain english:
"EquipmentName
= """ & [EquipmentName] & """"). Just curious...thanks for all the help!


:

Looks like the first one would be the one--not totally sure if I follow
it,
but I'll give it a try. I will always be manually filling in a value in
the
Equipment Name field, I just want the activity to auto fill. Thanks for
all
your help.

:

You can do it in various ways e.g.

1. Call the DLookup function in an expression as the ControlSource of
the
Activity control:

=DLookup("ActivityName", "tblEquipment", "EquipmentName = """ &
[EquipmentName] & """")

This assumes that the tblEquipment table includes a column
EquipmentName
which is referenced by the EquipmentName column in the form's
underlying
table.

2. Base the form on a query which joins its current underlying
table(s) to
the tblEquipment table on EquipmentName and bind a text box control to
the
ActivityName column from the tblEquipment table. Note that the
control
would be updatable in this case, so you might want to set its locked
property
to True and its Enabled property to False to prevent it being edited.

3. For the EquipmentName control on the form use a combo box with a
RowSource property of:

SELECT EquipmentName, ActivityName FROM tblEquipment ORDER BY
EquipmentName;

Set its ColumnCount property to 2. For the ControlSource of the
Activity
control reference the unseen second column of the combo box with:

=EquipmentName.Column(1)

The column property is zero-based, so Column(1) is the second column.

What you should not do is include an ActivityName column in the forms
underlying table, i.e. the table which references the tblEquipment
table via
the EquipmentName foreign key column. That would introduce redundancy
and
the risk of inconstant data. The activity for the equipment in
question is
known via the relationship so can be shown whenever necessary using the
above
methods.

Ken Sheridan
Stafford, England

:

Here's the expression I tried: =IIf([equipmentname] Is Not
Null,[tblEquipment]![ActivityName],Null).
On the form, it shows #Name? in the activity field.

:

I have two fields on a form: Equipment Name and Activity. I want
the field
Activity to auto fill from a list based on what's in Equipment
Name. I've
tried a couple of expressions in the expression builder, but to no
avail.
Thanks for any help