D
dawnykins
How do you make one field on a form dependent on what another field's results
are?
are?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Rick B
If you are asking about cascading combo-boxes where the list in the second
depends on the vvalue in the first, do a search. This is asked and answered
all the time.
The reason posts are maintained is so you can LOOK for your issue before
posting a new thread.
Rick B
depends on the vvalue in the first, do a search. This is asked and answered
all the time.
The reason posts are maintained is so you can LOOK for your issue before
posting a new thread.
Rick B
S
Sprinks
It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
D
dawnykins
Thank you Sprinks I appreciate it. I have searched and I guess I am not
getting the right question in there or something. What I have is this: One
combo box which gives me a choice of choosing the positions I want. By
positions I mean what a person does for a living. Then I want another box to
bring up the ID number that is associated with that position that I have
chosen. Does this make sense. This position ID number is the primary key in
many of my tables. I do not want this second box to be a combo box. Is this
possible? I can't find any specifics in my searching. Also, I can't seem to
get this code thing down in VBA. Your help is greatly appreciated.
getting the right question in there or something. What I have is this: One
combo box which gives me a choice of choosing the positions I want. By
positions I mean what a person does for a living. Then I want another box to
bring up the ID number that is associated with that position that I have
chosen. Does this make sense. This position ID number is the primary key in
many of my tables. I do not want this second box to be a combo box. Is this
possible? I can't find any specifics in my searching. Also, I can't seem to
get this code thing down in VBA. Your help is greatly appreciated.
Sprinks said:It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
dawnykins said:How do you make one field on a form dependent on what another field's results
are?
S
Sprinks
Please post the RowSource, ColumnWidths, Bound Column, and ControlSource
properties. Also, out of curiosity, why do you wish to display the code when
you're already displaying more meaningful text?
Sprinks
properties. Also, out of curiosity, why do you wish to display the code when
you're already displaying more meaningful text?
Sprinks
dawnykins said:Thank you Sprinks I appreciate it. I have searched and I guess I am not
getting the right question in there or something. What I have is this: One
combo box which gives me a choice of choosing the positions I want. By
positions I mean what a person does for a living. Then I want another box to
bring up the ID number that is associated with that position that I have
chosen. Does this make sense. This position ID number is the primary key in
many of my tables. I do not want this second box to be a combo box. Is this
possible? I can't find any specifics in my searching. Also, I can't seem to
get this code thing down in VBA. Your help is greatly appreciated.
Sprinks said:It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
dawnykins said:How do you make one field on a form dependent on what another field's results
are?
D
dawnykins
Box 1
Row Source: SELECT Positions.[Position Title], Positions.ID FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: Blank
Bound Column is: 1
Control Source is: Position (this is the name of my field)
Here's what I am trying to accomplish with my database. I want our office to
be able to enter a person's name in the database (by the form I call
Contacts). From that point I want the information to go to one table with
everything, (names addresses, position name....etc..) and if a person is a
police officer per say, I want that information also to go to a table for the
police officers. Same as another other position going to their perspective
tables. I think I have to have the position ID as the primary in all the
other tables in my contact table and form in order for the information to go
to each table. I am really new to this and all I have to go by is what I find
in this discussion and the book access for dummies. So any help is greatly
appreciated.
Row Source: SELECT Positions.[Position Title], Positions.ID FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: Blank
Bound Column is: 1
Control Source is: Position (this is the name of my field)
Here's what I am trying to accomplish with my database. I want our office to
be able to enter a person's name in the database (by the form I call
Contacts). From that point I want the information to go to one table with
everything, (names addresses, position name....etc..) and if a person is a
police officer per say, I want that information also to go to a table for the
police officers. Same as another other position going to their perspective
tables. I think I have to have the position ID as the primary in all the
other tables in my contact table and form in order for the information to go
to each table. I am really new to this and all I have to go by is what I find
in this discussion and the book access for dummies. So any help is greatly
appreciated.
Sprinks said:Please post the RowSource, ColumnWidths, Bound Column, and ControlSource
properties. Also, out of curiosity, why do you wish to display the code when
you're already displaying more meaningful text?
Sprinks
dawnykins said:Thank you Sprinks I appreciate it. I have searched and I guess I am not
getting the right question in there or something. What I have is this: One
combo box which gives me a choice of choosing the positions I want. By
positions I mean what a person does for a living. Then I want another box to
bring up the ID number that is associated with that position that I have
chosen. Does this make sense. This position ID number is the primary key in
many of my tables. I do not want this second box to be a combo box. Is this
possible? I can't find any specifics in my searching. Also, I can't seem to
get this code thing down in VBA. Your help is greatly appreciated.
Sprinks said:It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another field's results
are?
S
Sprinks
OK, before addressing the combo box, and how to display the information
you're after, you're making this much too difficult. You do not need
separate tables for Contacts, PoliceOfficers, Professors, Restauranteurs,
etc.--you only need to distinguish between them with a different value in a
field. You can then choose any one or several of them easily by selection
criteria within a query.
The first step in database design is defining your "Things" (Tables) and
their "Properties" or "Attributes" (Fields). Assign a numeric primary key to
each table as a unique record identifier. I always, and other application
developers usually, use an AutoNumber field type for the primary key.
So far, you've defined two things, Contacts and Positions. They are related
tables, in that each Contact has a Position. The way to specify a position
for each contact is to use a NUMERIC field for the PositionID that
corresponds to Positions' primary key, called a Foreign Key. By linking the
two tables in a query by the common field, the Foreign Key metaphorically
"unlocks" the Positions table such that you have access to any of its fields
for displaying on a form, or printing on a report. This allows you to
*store* an efficient numerical value, yet display the more meaningful data.
And there's one more big advantage to this approach.
Say you have an Orders table, and you store the name and address fields in
your Orders table. If your customer changes their name or address, you've
got to change every Orders record for that customer. Moreover, if you make a
typo, you might end up with something like the following:
Very Large Company
VeryLarge Company
Very Lrg. Co.
all of which are the same customer, but stored under different names. If,
however, you store only the customer number in the Orders table and a
customer moves or changes their name, you change a single record in the
Customer table and you're done.
Getting back to your example, your Positions table should be structured like
this:
PositionID AutoNumber (Primary Key)
PositionName Text
....any other Position-specific data like salary-class, etc.
and your Contacts table should have a numeric field for the Position.
However, noone wants to have to remember a code, therefore, you create a
combo box that displays a list of names, but *stores* the related code in the
underlying field. They work via these key properties:
RowSource SQL for the fields to include in the combo box. These may be
visible or not. Each field is a column of the combo box.
BoundColumn The index (1, 2, ...) of the column to store in the
ControlSource.
ControlSource The field of the form's underlying table in which to store
the BoundColumn.
ColumnWidths How much horizontal space to allow for display of the column.
If a column width is set to 0", it does not display at all. After the user
makes a selection, the first non-zero-width column is what remains showing in
the box.
In most instances, you don't really care about fields like the PositionID
code; it's just an efficient storage means. What's normally important is
displaying meaningful text. To do this in your situation, alter the Row
Source and Column Widths properties:
Row Source: SELECT Positions.ID, Positions.[Position Title] FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: 0";x" (where x is a width arrived at by trial and error)
Bound Column is: 1
Control Source is: Position (make this a numeric field)
When a selection is made, the PositionTitle will display, and the code will
be stored in the numeric field Position.
Now, if you want to show the code for informational purposes, simply add a
textbox with its control source set to the Position field.
Another way to do it, and this would apply to other combo box cases where
you wish to display other columns of the selected row, is to use the Column
property. Unfortunately, the index for this property begins with 0. To
display the third column of a cbox, you'd set the textbox' Control Source to:
=Me!MyComboBox.Column(2)
Hope that helps.
Sprinks
you're after, you're making this much too difficult. You do not need
separate tables for Contacts, PoliceOfficers, Professors, Restauranteurs,
etc.--you only need to distinguish between them with a different value in a
field. You can then choose any one or several of them easily by selection
criteria within a query.
The first step in database design is defining your "Things" (Tables) and
their "Properties" or "Attributes" (Fields). Assign a numeric primary key to
each table as a unique record identifier. I always, and other application
developers usually, use an AutoNumber field type for the primary key.
So far, you've defined two things, Contacts and Positions. They are related
tables, in that each Contact has a Position. The way to specify a position
for each contact is to use a NUMERIC field for the PositionID that
corresponds to Positions' primary key, called a Foreign Key. By linking the
two tables in a query by the common field, the Foreign Key metaphorically
"unlocks" the Positions table such that you have access to any of its fields
for displaying on a form, or printing on a report. This allows you to
*store* an efficient numerical value, yet display the more meaningful data.
And there's one more big advantage to this approach.
Say you have an Orders table, and you store the name and address fields in
your Orders table. If your customer changes their name or address, you've
got to change every Orders record for that customer. Moreover, if you make a
typo, you might end up with something like the following:
Very Large Company
VeryLarge Company
Very Lrg. Co.
all of which are the same customer, but stored under different names. If,
however, you store only the customer number in the Orders table and a
customer moves or changes their name, you change a single record in the
Customer table and you're done.
Getting back to your example, your Positions table should be structured like
this:
PositionID AutoNumber (Primary Key)
PositionName Text
....any other Position-specific data like salary-class, etc.
and your Contacts table should have a numeric field for the Position.
However, noone wants to have to remember a code, therefore, you create a
combo box that displays a list of names, but *stores* the related code in the
underlying field. They work via these key properties:
RowSource SQL for the fields to include in the combo box. These may be
visible or not. Each field is a column of the combo box.
BoundColumn The index (1, 2, ...) of the column to store in the
ControlSource.
ControlSource The field of the form's underlying table in which to store
the BoundColumn.
ColumnWidths How much horizontal space to allow for display of the column.
If a column width is set to 0", it does not display at all. After the user
makes a selection, the first non-zero-width column is what remains showing in
the box.
In most instances, you don't really care about fields like the PositionID
code; it's just an efficient storage means. What's normally important is
displaying meaningful text. To do this in your situation, alter the Row
Source and Column Widths properties:
Row Source: SELECT Positions.ID, Positions.[Position Title] FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: 0";x" (where x is a width arrived at by trial and error)
Bound Column is: 1
Control Source is: Position (make this a numeric field)
When a selection is made, the PositionTitle will display, and the code will
be stored in the numeric field Position.
Now, if you want to show the code for informational purposes, simply add a
textbox with its control source set to the Position field.
Another way to do it, and this would apply to other combo box cases where
you wish to display other columns of the selected row, is to use the Column
property. Unfortunately, the index for this property begins with 0. To
display the third column of a cbox, you'd set the textbox' Control Source to:
=Me!MyComboBox.Column(2)
Hope that helps.
Sprinks
dawnykins said:Box 1
Row Source: SELECT Positions.[Position Title], Positions.ID FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: Blank
Bound Column is: 1
Control Source is: Position (this is the name of my field)
Here's what I am trying to accomplish with my database. I want our office to
be able to enter a person's name in the database (by the form I call
Contacts). From that point I want the information to go to one table with
everything, (names addresses, position name....etc..) and if a person is a
police officer per say, I want that information also to go to a table for the
police officers. Same as another other position going to their perspective
tables. I think I have to have the position ID as the primary in all the
other tables in my contact table and form in order for the information to go
to each table. I am really new to this and all I have to go by is what I find
in this discussion and the book access for dummies. So any help is greatly
appreciated.
Sprinks said:Please post the RowSource, ColumnWidths, Bound Column, and ControlSource
properties. Also, out of curiosity, why do you wish to display the code when
you're already displaying more meaningful text?
Sprinks
dawnykins said:Thank you Sprinks I appreciate it. I have searched and I guess I am not
getting the right question in there or something. What I have is this: One
combo box which gives me a choice of choosing the positions I want. By
positions I mean what a person does for a living. Then I want another box to
bring up the ID number that is associated with that position that I have
chosen. Does this make sense. This position ID number is the primary key in
many of my tables. I do not want this second box to be a combo box. Is this
possible? I can't find any specifics in my searching. Also, I can't seem to
get this code thing down in VBA. Your help is greatly appreciated.
:
It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another field's results
are?
D
dawnykins
I think I need to go back to the begginning, because now I am really
confused. I understand what your saying but I don't think I have the
knowledge to carry this out yet. I can say this.
I have my first table, which is my contacts table listed with the fields I
want in it and the primary key code is a autonumber that is assigned to each
new record. I have another table that has autonumbered each position I have
entered in there. I have done this for tables named: position (the one were
talking about), county, organization, and State. So in my contacts form I
have the same fields as my table does and a few combo boxes that have drop
down menus for Position Title, Organization name, State and County names. I
wanted to set it up so that if the drop down menu is a position and we enter
that this person is a fire cheif, I wanted this information to go to the Fire
Chief's database.
I think your saying I don't need to do all this. Should I just keep the one
table and form then. Won't that get real big. I am trying to locate someone
in my very small town I live in that knows Access and can help a bit because
I am confused and this book I have isn't helping too much and it's hard to
understand what your saying. I afraid of putting you through too much trouble
to help me and not understand it.
confused. I understand what your saying but I don't think I have the
knowledge to carry this out yet. I can say this.
I have my first table, which is my contacts table listed with the fields I
want in it and the primary key code is a autonumber that is assigned to each
new record. I have another table that has autonumbered each position I have
entered in there. I have done this for tables named: position (the one were
talking about), county, organization, and State. So in my contacts form I
have the same fields as my table does and a few combo boxes that have drop
down menus for Position Title, Organization name, State and County names. I
wanted to set it up so that if the drop down menu is a position and we enter
that this person is a fire cheif, I wanted this information to go to the Fire
Chief's database.
I think your saying I don't need to do all this. Should I just keep the one
table and form then. Won't that get real big. I am trying to locate someone
in my very small town I live in that knows Access and can help a bit because
I am confused and this book I have isn't helping too much and it's hard to
understand what your saying. I afraid of putting you through too much trouble
to help me and not understand it.
Sprinks said:OK, before addressing the combo box, and how to display the information
you're after, you're making this much too difficult. You do not need
separate tables for Contacts, PoliceOfficers, Professors, Restauranteurs,
etc.--you only need to distinguish between them with a different value in a
field. You can then choose any one or several of them easily by selection
criteria within a query.
The first step in database design is defining your "Things" (Tables) and
their "Properties" or "Attributes" (Fields). Assign a numeric primary key to
each table as a unique record identifier. I always, and other application
developers usually, use an AutoNumber field type for the primary key.
So far, you've defined two things, Contacts and Positions. They are related
tables, in that each Contact has a Position. The way to specify a position
for each contact is to use a NUMERIC field for the PositionID that
corresponds to Positions' primary key, called a Foreign Key. By linking the
two tables in a query by the common field, the Foreign Key metaphorically
"unlocks" the Positions table such that you have access to any of its fields
for displaying on a form, or printing on a report. This allows you to
*store* an efficient numerical value, yet display the more meaningful data.
And there's one more big advantage to this approach.
Say you have an Orders table, and you store the name and address fields in
your Orders table. If your customer changes their name or address, you've
got to change every Orders record for that customer. Moreover, if you make a
typo, you might end up with something like the following:
Very Large Company
VeryLarge Company
Very Lrg. Co.
all of which are the same customer, but stored under different names. If,
however, you store only the customer number in the Orders table and a
customer moves or changes their name, you change a single record in the
Customer table and you're done.
Getting back to your example, your Positions table should be structured like
this:
PositionID AutoNumber (Primary Key)
PositionName Text
...any other Position-specific data like salary-class, etc.
and your Contacts table should have a numeric field for the Position.
However, noone wants to have to remember a code, therefore, you create a
combo box that displays a list of names, but *stores* the related code in the
underlying field. They work via these key properties:
RowSource SQL for the fields to include in the combo box. These may be
visible or not. Each field is a column of the combo box.
BoundColumn The index (1, 2, ...) of the column to store in the
ControlSource.
ControlSource The field of the form's underlying table in which to store
the BoundColumn.
ColumnWidths How much horizontal space to allow for display of the column.
If a column width is set to 0", it does not display at all. After the user
makes a selection, the first non-zero-width column is what remains showing in
the box.
In most instances, you don't really care about fields like the PositionID
code; it's just an efficient storage means. What's normally important is
displaying meaningful text. To do this in your situation, alter the Row
Source and Column Widths properties:
Row Source: SELECT Positions.ID, Positions.[Position Title] FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: 0";x" (where x is a width arrived at by trial and error)
Bound Column is: 1
Control Source is: Position (make this a numeric field)
When a selection is made, the PositionTitle will display, and the code will
be stored in the numeric field Position.
Now, if you want to show the code for informational purposes, simply add a
textbox with its control source set to the Position field.
Another way to do it, and this would apply to other combo box cases where
you wish to display other columns of the selected row, is to use the Column
property. Unfortunately, the index for this property begins with 0. To
display the third column of a cbox, you'd set the textbox' Control Source to:
=Me!MyComboBox.Column(2)
Hope that helps.
Sprinks
dawnykins said:Box 1
Row Source: SELECT Positions.[Position Title], Positions.ID FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: Blank
Bound Column is: 1
Control Source is: Position (this is the name of my field)
Here's what I am trying to accomplish with my database. I want our office to
be able to enter a person's name in the database (by the form I call
Contacts). From that point I want the information to go to one table with
everything, (names addresses, position name....etc..) and if a person is a
police officer per say, I want that information also to go to a table for the
police officers. Same as another other position going to their perspective
tables. I think I have to have the position ID as the primary in all the
other tables in my contact table and form in order for the information to go
to each table. I am really new to this and all I have to go by is what I find
in this discussion and the book access for dummies. So any help is greatly
appreciated.
Sprinks said:Please post the RowSource, ColumnWidths, Bound Column, and ControlSource
properties. Also, out of curiosity, why do you wish to display the code when
you're already displaying more meaningful text?
Sprinks
:
Thank you Sprinks I appreciate it. I have searched and I guess I am not
getting the right question in there or something. What I have is this: One
combo box which gives me a choice of choosing the positions I want. By
positions I mean what a person does for a living. Then I want another box to
bring up the ID number that is associated with that position that I have
chosen. Does this make sense. This position ID number is the primary key in
many of my tables. I do not want this second box to be a combo box. Is this
possible? I can't find any specifics in my searching. Also, I can't seem to
get this code thing down in VBA. Your help is greatly appreciated.
:
It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another field's results
are?
S
Sprinks
No, hang in there. It's always darkest before the dawn...
First of all, your approach to creating these mini-tables that consist of a
numeric primary key and a text field is a good one. If County and State are
a property of an Organization, rather than a Contact, though, they should be
fields in the Organization table.
I'm not sure I understand what you mean by "go to the Fire Chief's
database". What I think you mean is that you would like to be able to view
and edit only the Fire Chiefs at some point in time, or to work with some
other subset of the total records. This is done by either a Filter or a
Query. Forms can be based either directly on a table, or on a query that
might have more than one related table, and might have specific selection
criteria. Or, you can dynamically set their filter programmatically by
changing the form's Filter property.
One easy way to do this is to include a combo box in your form header to
select the subset of records you'd like to work with. Do not bind it to a
field. Then use the AfterUpdate event procedure to filter the records based
on your selection:
Me.Filter = "Position = " & Me!MyComboBox
Me.FilterOn = True
If you've never entered an event procedure, right click on the control and
choose Properties, click on the Event tab, click in the AfterUpdate field,
click on the ellipsis, and choose Code Builder. Access will create the shell
of the procedure for you. Then enter the two lines above between the
Sub...End Sub lines. Save and exit. The AfterUpdate field should now
display [Event Procedure], letting you know there is one defined for this
control and that it will execute when the AfterUpdate event occurs (whenever
you change the value).
I hope this makes sense to you. Access can store up to 2 billion records,
so it's not likely you'll be limited by keeping your Contacts in one table.
It will make your life easier, not harder.
Sprinks
First of all, your approach to creating these mini-tables that consist of a
numeric primary key and a text field is a good one. If County and State are
a property of an Organization, rather than a Contact, though, they should be
fields in the Organization table.
I'm not sure I understand what you mean by "go to the Fire Chief's
database". What I think you mean is that you would like to be able to view
and edit only the Fire Chiefs at some point in time, or to work with some
other subset of the total records. This is done by either a Filter or a
Query. Forms can be based either directly on a table, or on a query that
might have more than one related table, and might have specific selection
criteria. Or, you can dynamically set their filter programmatically by
changing the form's Filter property.
One easy way to do this is to include a combo box in your form header to
select the subset of records you'd like to work with. Do not bind it to a
field. Then use the AfterUpdate event procedure to filter the records based
on your selection:
Me.Filter = "Position = " & Me!MyComboBox
Me.FilterOn = True
If you've never entered an event procedure, right click on the control and
choose Properties, click on the Event tab, click in the AfterUpdate field,
click on the ellipsis, and choose Code Builder. Access will create the shell
of the procedure for you. Then enter the two lines above between the
Sub...End Sub lines. Save and exit. The AfterUpdate field should now
display [Event Procedure], letting you know there is one defined for this
control and that it will execute when the AfterUpdate event occurs (whenever
you change the value).
I hope this makes sense to you. Access can store up to 2 billion records,
so it's not likely you'll be limited by keeping your Contacts in one table.
It will make your life easier, not harder.
Sprinks
dawnykins said:I think I need to go back to the begginning, because now I am really
confused. I understand what your saying but I don't think I have the
knowledge to carry this out yet. I can say this.
I have my first table, which is my contacts table listed with the fields I
want in it and the primary key code is a autonumber that is assigned to each
new record. I have another table that has autonumbered each position I have
entered in there. I have done this for tables named: position (the one were
talking about), county, organization, and State. So in my contacts form I
have the same fields as my table does and a few combo boxes that have drop
down menus for Position Title, Organization name, State and County names. I
wanted to set it up so that if the drop down menu is a position and we enter
that this person is a fire cheif, I wanted this information to go to the Fire
Chief's database.
I think your saying I don't need to do all this. Should I just keep the one
table and form then. Won't that get real big. I am trying to locate someone
in my very small town I live in that knows Access and can help a bit because
I am confused and this book I have isn't helping too much and it's hard to
understand what your saying. I afraid of putting you through too much trouble
to help me and not understand it.
Sprinks said:OK, before addressing the combo box, and how to display the information
you're after, you're making this much too difficult. You do not need
separate tables for Contacts, PoliceOfficers, Professors, Restauranteurs,
etc.--you only need to distinguish between them with a different value in a
field. You can then choose any one or several of them easily by selection
criteria within a query.
The first step in database design is defining your "Things" (Tables) and
their "Properties" or "Attributes" (Fields). Assign a numeric primary key to
each table as a unique record identifier. I always, and other application
developers usually, use an AutoNumber field type for the primary key.
So far, you've defined two things, Contacts and Positions. They are related
tables, in that each Contact has a Position. The way to specify a position
for each contact is to use a NUMERIC field for the PositionID that
corresponds to Positions' primary key, called a Foreign Key. By linking the
two tables in a query by the common field, the Foreign Key metaphorically
"unlocks" the Positions table such that you have access to any of its fields
for displaying on a form, or printing on a report. This allows you to
*store* an efficient numerical value, yet display the more meaningful data.
And there's one more big advantage to this approach.
Say you have an Orders table, and you store the name and address fields in
your Orders table. If your customer changes their name or address, you've
got to change every Orders record for that customer. Moreover, if you make a
typo, you might end up with something like the following:
Very Large Company
VeryLarge Company
Very Lrg. Co.
all of which are the same customer, but stored under different names. If,
however, you store only the customer number in the Orders table and a
customer moves or changes their name, you change a single record in the
Customer table and you're done.
Getting back to your example, your Positions table should be structured like
this:
PositionID AutoNumber (Primary Key)
PositionName Text
...any other Position-specific data like salary-class, etc.
and your Contacts table should have a numeric field for the Position.
However, noone wants to have to remember a code, therefore, you create a
combo box that displays a list of names, but *stores* the related code in the
underlying field. They work via these key properties:
RowSource SQL for the fields to include in the combo box. These may be
visible or not. Each field is a column of the combo box.
BoundColumn The index (1, 2, ...) of the column to store in the
ControlSource.
ControlSource The field of the form's underlying table in which to store
the BoundColumn.
ColumnWidths How much horizontal space to allow for display of the column.
If a column width is set to 0", it does not display at all. After the user
makes a selection, the first non-zero-width column is what remains showing in
the box.
In most instances, you don't really care about fields like the PositionID
code; it's just an efficient storage means. What's normally important is
displaying meaningful text. To do this in your situation, alter the Row
Source and Column Widths properties:
Row Source: SELECT Positions.ID, Positions.[Position Title] FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: 0";x" (where x is a width arrived at by trial and error)
Bound Column is: 1
Control Source is: Position (make this a numeric field)
When a selection is made, the PositionTitle will display, and the code will
be stored in the numeric field Position.
Now, if you want to show the code for informational purposes, simply add a
textbox with its control source set to the Position field.
Another way to do it, and this would apply to other combo box cases where
you wish to display other columns of the selected row, is to use the Column
property. Unfortunately, the index for this property begins with 0. To
display the third column of a cbox, you'd set the textbox' Control Source to:
=Me!MyComboBox.Column(2)
Hope that helps.
Sprinks
dawnykins said:Box 1
Row Source: SELECT Positions.[Position Title], Positions.ID FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: Blank
Bound Column is: 1
Control Source is: Position (this is the name of my field)
Here's what I am trying to accomplish with my database. I want our office to
be able to enter a person's name in the database (by the form I call
Contacts). From that point I want the information to go to one table with
everything, (names addresses, position name....etc..) and if a person is a
police officer per say, I want that information also to go to a table for the
police officers. Same as another other position going to their perspective
tables. I think I have to have the position ID as the primary in all the
other tables in my contact table and form in order for the information to go
to each table. I am really new to this and all I have to go by is what I find
in this discussion and the book access for dummies. So any help is greatly
appreciated.
:
Please post the RowSource, ColumnWidths, Bound Column, and ControlSource
properties. Also, out of curiosity, why do you wish to display the code when
you're already displaying more meaningful text?
Sprinks
:
Thank you Sprinks I appreciate it. I have searched and I guess I am not
getting the right question in there or something. What I have is this: One
combo box which gives me a choice of choosing the positions I want. By
positions I mean what a person does for a living. Then I want another box to
bring up the ID number that is associated with that position that I have
chosen. Does this make sense. This position ID number is the primary key in
many of my tables. I do not want this second box to be a combo box. Is this
possible? I can't find any specifics in my searching. Also, I can't seem to
get this code thing down in VBA. Your help is greatly appreciated.
:
It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another field's results
are?
D
dawnykins
I have my startup page made, and working correctly. I have my switchboard
page made and working correctly. Could I put something on the switchboard
that will query what I need instead of a combo box on header? What I meant is
that if I entered information about a contact we have, I would also enter his
position home address, home phone, city, county etc... in the same form. Then
the information would automatically go to the police officer's table (if he's
a police officer) or to the fire chief's (if he's a fire cheif) and so forth.
Yes, I want to be able to go to all the police officers in our eight county
service area or, I want to be able to find certain information ( such as: All
personell in a certain town. Or, all the persons who work for a certain type
of organization.) I hope all this makes sense.
I think I understand what your saying. What I should maybe do then is put
queries in the form of a form on the switchboard...is that what you may be
saying?
page made and working correctly. Could I put something on the switchboard
that will query what I need instead of a combo box on header? What I meant is
that if I entered information about a contact we have, I would also enter his
position home address, home phone, city, county etc... in the same form. Then
the information would automatically go to the police officer's table (if he's
a police officer) or to the fire chief's (if he's a fire cheif) and so forth.
Yes, I want to be able to go to all the police officers in our eight county
service area or, I want to be able to find certain information ( such as: All
personell in a certain town. Or, all the persons who work for a certain type
of organization.) I hope all this makes sense.
I think I understand what your saying. What I should maybe do then is put
queries in the form of a form on the switchboard...is that what you may be
saying?
Sprinks said:No, hang in there. It's always darkest before the dawn...
First of all, your approach to creating these mini-tables that consist of a
numeric primary key and a text field is a good one. If County and State are
a property of an Organization, rather than a Contact, though, they should be
fields in the Organization table.
I'm not sure I understand what you mean by "go to the Fire Chief's
database". What I think you mean is that you would like to be able to view
and edit only the Fire Chiefs at some point in time, or to work with some
other subset of the total records. This is done by either a Filter or a
Query. Forms can be based either directly on a table, or on a query that
might have more than one related table, and might have specific selection
criteria. Or, you can dynamically set their filter programmatically by
changing the form's Filter property.
One easy way to do this is to include a combo box in your form header to
select the subset of records you'd like to work with. Do not bind it to a
field. Then use the AfterUpdate event procedure to filter the records based
on your selection:
Me.Filter = "Position = " & Me!MyComboBox
Me.FilterOn = True
If you've never entered an event procedure, right click on the control and
choose Properties, click on the Event tab, click in the AfterUpdate field,
click on the ellipsis, and choose Code Builder. Access will create the shell
of the procedure for you. Then enter the two lines above between the
Sub...End Sub lines. Save and exit. The AfterUpdate field should now
display [Event Procedure], letting you know there is one defined for this
control and that it will execute when the AfterUpdate event occurs (whenever
you change the value).
I hope this makes sense to you. Access can store up to 2 billion records,
so it's not likely you'll be limited by keeping your Contacts in one table.
It will make your life easier, not harder.
Sprinks
dawnykins said:I think I need to go back to the begginning, because now I am really
confused. I understand what your saying but I don't think I have the
knowledge to carry this out yet. I can say this.
I have my first table, which is my contacts table listed with the fields I
want in it and the primary key code is a autonumber that is assigned to each
new record. I have another table that has autonumbered each position I have
entered in there. I have done this for tables named: position (the one were
talking about), county, organization, and State. So in my contacts form I
have the same fields as my table does and a few combo boxes that have drop
down menus for Position Title, Organization name, State and County names. I
wanted to set it up so that if the drop down menu is a position and we enter
that this person is a fire cheif, I wanted this information to go to the Fire
Chief's database.
I think your saying I don't need to do all this. Should I just keep the one
table and form then. Won't that get real big. I am trying to locate someone
in my very small town I live in that knows Access and can help a bit because
I am confused and this book I have isn't helping too much and it's hard to
understand what your saying. I afraid of putting you through too much trouble
to help me and not understand it.
Sprinks said:OK, before addressing the combo box, and how to display the information
you're after, you're making this much too difficult. You do not need
separate tables for Contacts, PoliceOfficers, Professors, Restauranteurs,
etc.--you only need to distinguish between them with a different value in a
field. You can then choose any one or several of them easily by selection
criteria within a query.
The first step in database design is defining your "Things" (Tables) and
their "Properties" or "Attributes" (Fields). Assign a numeric primary key to
each table as a unique record identifier. I always, and other application
developers usually, use an AutoNumber field type for the primary key.
So far, you've defined two things, Contacts and Positions. They are related
tables, in that each Contact has a Position. The way to specify a position
for each contact is to use a NUMERIC field for the PositionID that
corresponds to Positions' primary key, called a Foreign Key. By linking the
two tables in a query by the common field, the Foreign Key metaphorically
"unlocks" the Positions table such that you have access to any of its fields
for displaying on a form, or printing on a report. This allows you to
*store* an efficient numerical value, yet display the more meaningful data.
And there's one more big advantage to this approach.
Say you have an Orders table, and you store the name and address fields in
your Orders table. If your customer changes their name or address, you've
got to change every Orders record for that customer. Moreover, if you make a
typo, you might end up with something like the following:
Very Large Company
VeryLarge Company
Very Lrg. Co.
all of which are the same customer, but stored under different names. If,
however, you store only the customer number in the Orders table and a
customer moves or changes their name, you change a single record in the
Customer table and you're done.
Getting back to your example, your Positions table should be structured like
this:
PositionID AutoNumber (Primary Key)
PositionName Text
...any other Position-specific data like salary-class, etc.
and your Contacts table should have a numeric field for the Position.
However, noone wants to have to remember a code, therefore, you create a
combo box that displays a list of names, but *stores* the related code in the
underlying field. They work via these key properties:
RowSource SQL for the fields to include in the combo box. These may be
visible or not. Each field is a column of the combo box.
BoundColumn The index (1, 2, ...) of the column to store in the
ControlSource.
ControlSource The field of the form's underlying table in which to store
the BoundColumn.
ColumnWidths How much horizontal space to allow for display of the column.
If a column width is set to 0", it does not display at all. After the user
makes a selection, the first non-zero-width column is what remains showing in
the box.
In most instances, you don't really care about fields like the PositionID
code; it's just an efficient storage means. What's normally important is
displaying meaningful text. To do this in your situation, alter the Row
Source and Column Widths properties:
Row Source: SELECT Positions.ID, Positions.[Position Title] FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: 0";x" (where x is a width arrived at by trial and error)
Bound Column is: 1
Control Source is: Position (make this a numeric field)
When a selection is made, the PositionTitle will display, and the code will
be stored in the numeric field Position.
Now, if you want to show the code for informational purposes, simply add a
textbox with its control source set to the Position field.
Another way to do it, and this would apply to other combo box cases where
you wish to display other columns of the selected row, is to use the Column
property. Unfortunately, the index for this property begins with 0. To
display the third column of a cbox, you'd set the textbox' Control Source to:
=Me!MyComboBox.Column(2)
Hope that helps.
Sprinks
:
Box 1
Row Source: SELECT Positions.[Position Title], Positions.ID FROM Positions
ORDER BY Positions.[Position Title];
Column Widths is: Blank
Bound Column is: 1
Control Source is: Position (this is the name of my field)
Here's what I am trying to accomplish with my database. I want our office to
be able to enter a person's name in the database (by the form I call
Contacts). From that point I want the information to go to one table with
everything, (names addresses, position name....etc..) and if a person is a
police officer per say, I want that information also to go to a table for the
police officers. Same as another other position going to their perspective
tables. I think I have to have the position ID as the primary in all the
other tables in my contact table and form in order for the information to go
to each table. I am really new to this and all I have to go by is what I find
in this discussion and the book access for dummies. So any help is greatly
appreciated.
:
Please post the RowSource, ColumnWidths, Bound Column, and ControlSource
properties. Also, out of curiosity, why do you wish to display the code when
you're already displaying more meaningful text?
Sprinks
:
Thank you Sprinks I appreciate it. I have searched and I guess I am not
getting the right question in there or something. What I have is this: One
combo box which gives me a choice of choosing the positions I want. By
positions I mean what a person does for a living. Then I want another box to
bring up the ID number that is associated with that position that I have
chosen. Does this make sense. This position ID number is the primary key in
many of my tables. I do not want this second box to be a combo box. Is this
possible? I can't find any specifics in my searching. Also, I can't seem to
get this code thing down in VBA. Your help is greatly appreciated.
:
It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another field's results
are?
E
ETC
Hi Sprinks,
My question is the same, but I'm having a little trouble with the coding for
the ControlSource. I have a combo box made for "Fac Loc", which takes the
values from a table. I'm trying to "limit the choices of a downstream combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is the
primary key in the table and it was automatically included when I did the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc
....But it's not working for me. Can you help?
ETC
My question is the same, but I'm having a little trouble with the coding for
the ControlSource. I have a combo box made for "Fac Loc", which takes the
values from a table. I'm trying to "limit the choices of a downstream combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is the
primary key in the table and it was automatically included when I did the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc
....But it's not working for me. Can you help?
ETC
Sprinks said:It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
dawnykins said:How do you make one field on a form dependent on what another field's results
are?
D
Douglas J Steele
If that's an actual cut-and-paste, the semi-colon after [L Codes] needs to
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:Hi Sprinks,
My question is the same, but I'm having a little trouble with the coding for
the ControlSource. I have a combo box made for "Fac Loc", which takes the
values from a table. I'm trying to "limit the choices of a downstream combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is the
primary key in the table and it was automatically included when I did the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc
...But it's not working for me. Can you help?
ETC
Sprinks said:It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
dawnykins said:How do you make one field on a form dependent on what another field's results
are?
E
ETC
Thanks, Douglas, but it's still not working for me. Here's what I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac
Loc]=Me!cboFacLoc
The name of the field in the L Codes table is "Fac Loc", but the name of the
combo box is "cboFacLoc". What did I do wrong?
Thanks again for your time.
ETC
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac
Loc]=Me!cboFacLoc
The name of the field in the L Codes table is "Fac Loc", but the name of the
combo box is "cboFacLoc". What did I do wrong?
Thanks again for your time.
ETC
Douglas J Steele said:If that's an actual cut-and-paste, the semi-colon after [L Codes] needs to
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:Hi Sprinks,
My question is the same, but I'm having a little trouble with the coding for
the ControlSource. I have a combo box made for "Fac Loc", which takes the
values from a table. I'm trying to "limit the choices of a downstream combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is the
primary key in the table and it was automatically included when I did the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc
...But it's not working for me. Can you help?
ETC
Sprinks said:It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another field's results
are?
E
ETC
What it's specifically doing is it keeps asking for the Parameter Value for
Me!cboFacLoc when I exit design view. Hope that helps you to help me. =)
Me!cboFacLoc when I exit design view. Hope that helps you to help me. =)
Douglas J Steele said:If that's an actual cut-and-paste, the semi-colon after [L Codes] needs to
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:Hi Sprinks,
My question is the same, but I'm having a little trouble with the coding for
the ControlSource. I have a combo box made for "Fac Loc", which takes the
values from a table. I'm trying to "limit the choices of a downstream combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is the
primary key in the table and it was automatically included when I did the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc
...But it's not working for me. Can you help?
ETC
Sprinks said:It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the quantity and
unit prices, set the control's ControlSource to a valid expression, such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another field's results
are?
D
Douglas J. Steele
Are you putting that as the Row Source for the combobox, or are you creating
the Row Source in code (the AfterUpdate event for the combobox)?
If the latter, post your exact code.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
the Row Source in code (the AfterUpdate event for the combobox)?
If the latter, post your exact code.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:What it's specifically doing is it keeps asking for the Parameter Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help me. =)
Douglas J Steele said:If that's an actual cut-and-paste, the semi-colon after [L Codes] needs
to
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:Hi Sprinks,
My question is the same, but I'm having a little trouble with the
coding for
the ControlSource. I have a combo box made for "Fac Loc", which takes
the
values from a table. I'm trying to "limit the choices of a downstream combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is the
primary key in the table and it was automatically included when I did
the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc
...But it's not working for me. Can you help?
ETC
:
It depends on what you're trying to do. If you want to display a calculation
in a form control, such as an extended price dependent on the
quantity and
unit prices, set the control's ControlSource to a valid expression,
such as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your
table,
since it can be calculated on-the-fly in a query. To do so also requires VBA
code, since a ControlSource can either be a fieldname, in which case data
entered in the control is stored in the field OR a calculation, but
not both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior field,
you change the former's Row Source as appropriate. For example, once having
chosen Region, you might want to limit the SalesRep field to those in the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you understand
the distinction between a field, which exists in a table, and
controls that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another
field's results
are?
E
ETC
I put it in the Row Source of cboX_ID...
SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L Codes]
WHERE [Fac Loc]=Me!cboFacLoc;
Would it be easier to put it in the After Update portion of cboFacLoc?
SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L Codes]
WHERE [Fac Loc]=Me!cboFacLoc;
Would it be easier to put it in the After Update portion of cboFacLoc?
Douglas J. Steele said:Are you putting that as the Row Source for the combobox, or are you creating
the Row Source in code (the AfterUpdate event for the combobox)?
If the latter, post your exact code.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:What it's specifically doing is it keeps asking for the Parameter Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help me. =)
Douglas J Steele said:If that's an actual cut-and-paste, the semi-colon after [L Codes] needs
to
be removed. As well, your WHERE clause is incorrect. Replace the quotes
around the field name with square brackets.
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Sprinks,
My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which takes
the
values from a table. I'm trying to "limit the choices of a downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No" is
the
primary key in the table and it was automatically included when I did
the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac Loc" =
Me!cboFacLoc
...But it's not working for me. Can you help?
ETC
:
It depends on what you're trying to do. If you want to display a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid expression,
such
as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your
table,
since it can be calculated on-the-fly in a query. To do so also
requires VBA
code, since a ControlSource can either be a fieldname, in which case
data
entered in the control is stored in the field OR a calculation, but
not
both
simultaneously.
If you wish to limit the choices of a downstream combo box by a prior
field,
you change the former's Row Source as appropriate. For example, once
having
chosen Region, you might want to limit the SalesRep field to those in
the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another
field's
results
are?
D
Douglas J. Steele
Yeah, I'd use something like this
Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc
Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery
End Sub
This assumes that Fac Loc is a numeric field. If it's a text field, you'll
need
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Chr$(34) & Me!cboFacLoc & Chr$(34)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc
Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery
End Sub
This assumes that Fac Loc is a numeric field. If it's a text field, you'll
need
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Chr$(34) & Me!cboFacLoc & Chr$(34)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:I put it in the Row Source of cboX_ID...
SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L Codes]
WHERE [Fac Loc]=Me!cboFacLoc;
Would it be easier to put it in the After Update portion of cboFacLoc?
Douglas J. Steele said:Are you putting that as the Row Source for the combobox, or are you
creating
the Row Source in code (the AfterUpdate event for the combobox)?
If the latter, post your exact code.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:What it's specifically doing is it keeps asking for the Parameter Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help me.
=)
:
If that's an actual cut-and-paste, the semi-colon after [L Codes]
needs
to
be removed. As well, your WHERE clause is incorrect. Replace the
quotes
around the field name with square brackets.
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Sprinks,
My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which
takes
the
values from a table. I'm trying to "limit the choices of a
downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No"
is
the
primary key in the table and it was automatically included when I
did
the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac
Loc" =
Me!cboFacLoc
...But it's not working for me. Can you help?
ETC
:
It depends on what you're trying to do. If you want to display a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid
expression,
such
as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your
table,
since it can be calculated on-the-fly in a query. To do so also
requires VBA
code, since a ControlSource can either be a fieldname, in which
case
data
entered in the control is stored in the field OR a calculation,
but
not
both
simultaneously.
If you wish to limit the choices of a downstream combo box by a
prior
field,
you change the former's Row Source as appropriate. For example,
once
having
chosen Region, you might want to limit the SalesRep field to those
in
the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the
form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another
field's
results
are?
E
ETC
Argh! SO close! Here's what I got...
Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc
Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery
End Sub
By the way, I used a value list for the data in this combo box instead of
extracting it from the table. I don't know if that might be posing a problem
with this. I did it that way because I couldn't figure out how to prevent
multiple Fac Locs from being listed. The "DISTINCT" and "DISTINCT ROW"
wouldn't work for me. Also, for the cboX_ID combo box I also used a value
list instead of extracting it from the table.
Here's what's happening...
When I first open the form, the X_ID combo box displays all possible values
from the value list I gave (of course). But what I really need is to list
only the X_ID values associated with the Fac Loc selected in cboFacLoc, which
comes earlier in the form. So, the cboFacLoc displays all appropriate Fac
Locs. When I select one, the values in the cboX_ID displays ONLY "[L
Codes].X_ID".
What do you think?
Thanks again for all of the time you've spent helping me.
ETC
Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc
Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery
End Sub
By the way, I used a value list for the data in this combo box instead of
extracting it from the table. I don't know if that might be posing a problem
with this. I did it that way because I couldn't figure out how to prevent
multiple Fac Locs from being listed. The "DISTINCT" and "DISTINCT ROW"
wouldn't work for me. Also, for the cboX_ID combo box I also used a value
list instead of extracting it from the table.
Here's what's happening...
When I first open the form, the X_ID combo box displays all possible values
from the value list I gave (of course). But what I really need is to list
only the X_ID values associated with the Fac Loc selected in cboFacLoc, which
comes earlier in the form. So, the cboFacLoc displays all appropriate Fac
Locs. When I select one, the values in the cboX_ID displays ONLY "[L
Codes].X_ID".
What do you think?
Thanks again for all of the time you've spent helping me.
ETC
Douglas J. Steele said:Yeah, I'd use something like this
Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc
Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery
End Sub
This assumes that Fac Loc is a numeric field. If it's a text field, you'll
need
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Chr$(34) & Me!cboFacLoc & Chr$(34)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:I put it in the Row Source of cboX_ID...
SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L Codes]
WHERE [Fac Loc]=Me!cboFacLoc;
Would it be easier to put it in the After Update portion of cboFacLoc?
Douglas J. Steele said:Are you putting that as the Row Source for the combobox, or are you
creating
the Row Source in code (the AfterUpdate event for the combobox)?
If the latter, post your exact code.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
What it's specifically doing is it keeps asking for the Parameter Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help me.
=)
:
If that's an actual cut-and-paste, the semi-colon after [L Codes]
needs
to
be removed. As well, your WHERE clause is incorrect. Replace the
quotes
around the field name with square brackets.
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac Loc] =
Me!cboFacLoc
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Sprinks,
My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which
takes
the
values from a table. I'm trying to "limit the choices of a
downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L Codes.No"
is
the
primary key in the table and it was automatically included when I
did
the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac
Loc" =
Me!cboFacLoc
...But it's not working for me. Can you help?
ETC
:
It depends on what you're trying to do. If you want to display a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid
expression,
such
as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in your
table,
since it can be calculated on-the-fly in a query. To do so also
requires VBA
code, since a ControlSource can either be a fieldname, in which
case
data
entered in the control is stored in the field OR a calculation,
but
not
both
simultaneously.
If you wish to limit the choices of a downstream combo box by a
prior
field,
you change the former's Row Source as appropriate. For example,
once
having
chosen Region, you might want to limit the SalesRep field to those
in
the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the
form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another
field's
results
are?
D
Douglas J. Steele
I'm not sure I understand what you're saying.
What do you mean by "SO close"? Are you encountering some sort of error with
that?
When you say that when you select a record, the values in the cboX_ID
displays ONLY "[L Codes].X_ID", that's normal: while a combobox may display
multiple columns when it's dropped down, only one value will show when it's
not.
SELECT DISCTINCT will give you unique combinations of No, X_ID and Fac Loc.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
What do you mean by "SO close"? Are you encountering some sort of error with
that?
When you say that when you select a record, the values in the cboX_ID
displays ONLY "[L Codes].X_ID", that's normal: while a combobox may display
multiple columns when it's dropped down, only one value will show when it's
not.
SELECT DISCTINCT will give you unique combinations of No, X_ID and Fac Loc.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:Argh! SO close! Here's what I got...
Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc
Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery
End Sub
By the way, I used a value list for the data in this combo box instead of
extracting it from the table. I don't know if that might be posing a
problem
with this. I did it that way because I couldn't figure out how to prevent
multiple Fac Locs from being listed. The "DISTINCT" and "DISTINCT ROW"
wouldn't work for me. Also, for the cboX_ID combo box I also used a value
list instead of extracting it from the table.
Here's what's happening...
When I first open the form, the X_ID combo box displays all possible
values
from the value list I gave (of course). But what I really need is to list
only the X_ID values associated with the Fac Loc selected in cboFacLoc,
which
comes earlier in the form. So, the cboFacLoc displays all appropriate Fac
Locs. When I select one, the values in the cboX_ID displays ONLY "[L
Codes].X_ID".
What do you think?
Thanks again for all of the time you've spent helping me.
ETC
Douglas J. Steele said:Yeah, I'd use something like this
Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc
Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery
End Sub
This assumes that Fac Loc is a numeric field. If it's a text field,
you'll
need
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Chr$(34) & Me!cboFacLoc & Chr$(34)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:I put it in the Row Source of cboX_ID...
SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L
Codes]
WHERE [Fac Loc]=Me!cboFacLoc;
Would it be easier to put it in the After Update portion of cboFacLoc?
:
Are you putting that as the Row Source for the combobox, or are you
creating
the Row Source in code (the AfterUpdate event for the combobox)?
If the latter, post your exact code.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
What it's specifically doing is it keeps asking for the Parameter
Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help
me.
=)
:
If that's an actual cut-and-paste, the semi-colon after [L Codes]
needs
to
be removed. As well, your WHERE clause is incorrect. Replace the
quotes
around the field name with square brackets.
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac
Loc] =
Me!cboFacLoc
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Sprinks,
My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which
takes
the
values from a table. I'm trying to "limit the choices of a
downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L
Codes.No"
is
the
primary key in the table and it was automatically included when I
did
the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac
Loc" =
Me!cboFacLoc
...But it's not working for me. Can you help?
ETC
:
It depends on what you're trying to do. If you want to display
a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid
expression,
such
as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in
your
table,
since it can be calculated on-the-fly in a query. To do so
also
requires VBA
code, since a ControlSource can either be a fieldname, in which
case
data
entered in the control is stored in the field OR a calculation,
but
not
both
simultaneously.
If you wish to limit the choices of a downstream combo box by a
prior
field,
you change the former's Row Source as appropriate. For
example,
once
having
chosen Region, you might want to limit the SalesRep field to
those
in
the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the
form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another
field's
results
are?
E
ETC
Unfortunately, yes, I'm still encountering an error with that. What I meant
was that when I select an item from cboFacLoc, it currently displays "[L
Codes].X_ID" in cboX_ID rather than displaying the actually IDs that are
associated with what's selected in cboFac Loc. Here's what the table looks
like...
Fac Loc X_ID
A P
A P
A Q
A R
B S
B T
B T
B U
So, I made a combo box for each field (cboFacLoc and cboX_ID). So, if they
chose "A" from cboFacLoc then I only want "P", "Q" and "R" displayed in
cboX_ID.
Does that help?
was that when I select an item from cboFacLoc, it currently displays "[L
Codes].X_ID" in cboX_ID rather than displaying the actually IDs that are
associated with what's selected in cboFac Loc. Here's what the table looks
like...
Fac Loc X_ID
A P
A P
A Q
A R
B S
B T
B T
B U
So, I made a combo box for each field (cboFacLoc and cboX_ID). So, if they
chose "A" from cboFacLoc then I only want "P", "Q" and "R" displayed in
cboX_ID.
Does that help?
Douglas J. Steele said:I'm not sure I understand what you're saying.
What do you mean by "SO close"? Are you encountering some sort of error with
that?
When you say that when you select a record, the values in the cboX_ID
displays ONLY "[L Codes].X_ID", that's normal: while a combobox may display
multiple columns when it's dropped down, only one value will show when it's
not.
SELECT DISCTINCT will give you unique combinations of No, X_ID and Fac Loc.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
ETC said:Argh! SO close! Here's what I got...
Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc
Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery
End Sub
By the way, I used a value list for the data in this combo box instead of
extracting it from the table. I don't know if that might be posing a
problem
with this. I did it that way because I couldn't figure out how to prevent
multiple Fac Locs from being listed. The "DISTINCT" and "DISTINCT ROW"
wouldn't work for me. Also, for the cboX_ID combo box I also used a value
list instead of extracting it from the table.
Here's what's happening...
When I first open the form, the X_ID combo box displays all possible
values
from the value list I gave (of course). But what I really need is to list
only the X_ID values associated with the Fac Loc selected in cboFacLoc,
which
comes earlier in the form. So, the cboFacLoc displays all appropriate Fac
Locs. When I select one, the values in the cboX_ID displays ONLY "[L
Codes].X_ID".
What do you think?
Thanks again for all of the time you've spent helping me.
ETC
Douglas J. Steele said:Yeah, I'd use something like this
Private Sub cboFacLoc_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Me!cboFacLoc
Me.cboX_ID.RowSource = strSQL
Me.cboX_ID.Requery
End Sub
This assumes that Fac Loc is a numeric field. If it's a text field,
you'll
need
strSQL = "SELECT [L Codes].[No], [L Codes].X_ID, " & _
"[L Codes].[Fac Loc] FROM [L Codes] " & _
"WHERE [Fac Loc] = " & Chr$(34) & Me!cboFacLoc & Chr$(34)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I put it in the Row Source of cboX_ID...
SELECT [L Codes].[No], [L Codes].X_ID, [L Codes].[Fac Loc] FROM [L
Codes]
WHERE [Fac Loc]=Me!cboFacLoc;
Would it be easier to put it in the After Update portion of cboFacLoc?
:
Are you putting that as the Row Source for the combobox, or are you
creating
the Row Source in code (the AfterUpdate event for the combobox)?
If the latter, post your exact code.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
What it's specifically doing is it keeps asking for the Parameter
Value
for
Me!cboFacLoc when I exit design view. Hope that helps you to help
me.
=)
:
If that's an actual cut-and-paste, the semi-colon after [L Codes]
needs
to
be removed. As well, your WHERE clause is incorrect. Replace the
quotes
around the field name with square brackets.
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes] WHERE [Fac
Loc] =
Me!cboFacLoc
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Sprinks,
My question is the same, but I'm having a little trouble with the
coding
for
the ControlSource. I have a combo box made for "Fac Loc", which
takes
the
values from a table. I'm trying to "limit the choices of a
downstream
combo
box" (combo box named "X_ID") by the "Fac Loc" field. ("L
Codes.No"
is
the
primary key in the table and it was automatically included when I
did
the
combo box wizard.)
In the Control Source of the "X_ID" combo box I have...
SELECT [L Codes].[No], [L Codes].X_ID FROM [L Codes]; WHERE "Fac
Loc" =
Me!cboFacLoc
...But it's not working for me. Can you help?
ETC
:
It depends on what you're trying to do. If you want to display
a
calculation
in a form control, such as an extended price dependent on the
quantity
and
unit prices, set the control's ControlSource to a valid
expression,
such
as:
=[Qty] * [UnitPrice]
99.999% of the time, there is no need to store this value in
your
table,
since it can be calculated on-the-fly in a query. To do so
also
requires VBA
code, since a ControlSource can either be a fieldname, in which
case
data
entered in the control is stored in the field OR a calculation,
but
not
both
simultaneously.
If you wish to limit the choices of a downstream combo box by a
prior
field,
you change the former's Row Source as appropriate. For
example,
once
having
chosen Region, you might want to limit the SalesRep field to
those
in
the
region:
Me!MyComboBox.RowSource = "SELECT SalesRepID, SalesRepName FROM
SalesReps
WHERE [Region] = " & Me!MyRegionControl
By the way, your question is a frequent one. Be sure that you
understand
the distinction between a field, which exists in a table, and
controls
that
exist on forms, which may or may not be bound to fields in the
form's
underlying RecordSource.
Hope that helps.
Sprinks
:
How do you make one field on a form dependent on what another
field's
results
are?
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.