Forms in Access - Auto populating fields

[Guest]

Two questions:

1) How do I autopopulate a text box with an ID number from a table that is
associated with an employee name in that table that has been entered into
another text box?

2) I have three seperate drop down lists for the different departments in my
business. Only one of these positions can be worked at a time, but rightnow,
you are able to select values from all three of the drop downs. How do you
recommend going about setting up this part of the form so that the person can
only work one section, Housing, Booking, or Control. After the section is
established, a position in that section needs to be selected (i guess from a
drop down). How can i go about solving this problem so only one position can
be selected and inserted into the table?

Thanks in advance!

 

[Guest]

Hi, Ty.

As a recommendation, typically responses are quicker if you make separate
posts for each question. Luckily, I think I can answer both, so here you go:

1) Generally, it's not a good strategy to have a textbox for data entry of
a name, especially when you want to limit the list to names that exist in
your employee table. The typical strategy is to use a combo box. It's
easiest to enter one using the wizard, so in form design view, display the
Toolbox with View, Toolbox, and toggle the wizard button on (a magic wand and
five stars). Then place a combo box, selecting the key field, the name
field, and the ID number (if it isn't the key field). Size your columns, and
choose Hide Key Field, and tell it to store the selection in your Name field.
This, of course, is a *numeric* field, because you are storing the key field
rather than the text.

If your IDNumber is not the key field, go to the Column Widths property of
the combo box, and set its width to 0" to prevent it from displaying in the
drop-down.
To auto-populate your textbox with the IDNumber, set the textbox' Control
Source to:

=YourComboBoxName.Column(N-1), where N is the column number corresponding to
the IDNumber. If its the 3rd column, then enter:

=YourComboBoxName.Column(2)

2) The strategy here is to use two combo boxes--one for the section, and one
for the position--and use the selection of the first combo box to filter the
records for the second.

In the AfterUpdate event of the first combo box, set the Row Source property
of the second. Something like:

Me![YourSecondCBox.RowSource] =
"SELECT Position from SectionPosition WHERE Section =
Me![YourFirstCBox]
ORDER BY Position"

HTH
Sprinks

 

[Guest]

Hi Sprinks. Thanks for the help on the first question. I am having a little
bitof trouble with the ladder. I have a table of the positions. three of the
columns in this table are the sections, Housing, Booking, and Control. The
first column is an Identity column for the individual positions. Now the
first drop down should be those three sections. Once one of those sections is
selected from that first drop down, the second drop down should populate with
the positions under that section in the table. I am confused about the code i
should actually use. I see an afterUpdate in the properties, but I dont know
where the Row Source is that you are talking about. I appreciate your help,
I'm just still a little bit confused. It's been a long time since I've used
Access!

Thanks, Ty

 

[Guest]

Ty, firstly let me say that perhaps it was me that confused you, because the
syntax should have been:

Me![YourSecondCBox].RowSource rather than:
Me![YourSecondCBox.RowSource]

RowSource is a property of a combo box. It is filled with an SQL statement
(a query) that returns rows of records that are displayed in the drop-down
list. Two other combo box properties are important. The Bound Column
specifies which column is actually stored in the field specified in the
Control Source property. Column Widths controls how the columns are
displayed in the drop-down. To hide a column from the display, simply set
its width to 0".

So what the code was trying to do was to specify that the second combo box
display all records where the Section equalled what was chosen in the first
combo box.

On receiving your response, however, I'm not clear on the structure of your
table. You said three of the columns are the sections--Housing, Booking, and
Control, and the fourth column is an ID of the position. I don't understand
why you'd need the three columns rather than a single Section field that
could take one of three values.

I was thinking you had a structure like this:

Position
----------
PositionID AutoNumber (Primary Key [PK])
Position Text

Section
---------
SectionID AutoNumber (PK)
Section Text

PositionBySection
--------------------
PKey AutoNumber (PK)
Section Numeric (Foreign Key to Section)
Position Numeric (Foreign Key to Position)

So the combo box would display all Positions associated with a given
section, i.e.,
the Row Source would be:

SELECT PositionBySection.Position FROM PositionBySection WHERE Section =
Me!YourComboBox

If your table structures do not match my assumption, please post the
structures, and I can help you solve this.

HTH
Sprinks

 

[Guest]

Spinks. My table structures are now like yours. Combo53 has the sections in
it. Combo57 has the positions.. here is the Row Source for Combo57:

SELECT PosBySec.Position FROM PosBySec WHERE Section=Me!combo53;

Do I need brackets or anythign? I think i am fairly close.Thanks for your
help!

Ty, firstly let me say that perhaps it was me that confused you, because the
syntax should have been:

Me![YourSecondCBox].RowSource rather than:
Me![YourSecondCBox.RowSource]

RowSource is a property of a combo box. It is filled with an SQL statement
(a query) that returns rows of records that are displayed in the drop-down
list. Two other combo box properties are important. The Bound Column
specifies which column is actually stored in the field specified in the
Control Source property. Column Widths controls how the columns are
displayed in the drop-down. To hide a column from the display, simply set
its width to 0".

So what the code was trying to do was to specify that the second combo box
display all records where the Section equalled what was chosen in the first
combo box.

On receiving your response, however, I'm not clear on the structure of your
table. You said three of the columns are the sections--Housing, Booking, and
Control, and the fourth column is an ID of the position. I don't understand
why you'd need the three columns rather than a single Section field that
could take one of three values.

I was thinking you had a structure like this:

Position
----------
PositionID AutoNumber (Primary Key [PK])
Position Text

Section
---------
SectionID AutoNumber (PK)
Section Text

PositionBySection
--------------------
PKey AutoNumber (PK)
Section Numeric (Foreign Key to Section)
Position Numeric (Foreign Key to Position)

So the combo box would display all Positions associated with a given
section, i.e.,
the Row Source would be:

SELECT PositionBySection.Position FROM PositionBySection WHERE Section =
Me!YourComboBox

If your table structures do not match my assumption, please post the
structures, and I can help you solve this.

HTH
Sprinks

Hi Sprinks. Thanks for the help on the first question. I am having a little
bitof trouble with the ladder. I have a table of the positions. three of the
columns in this table are the sections, Housing, Booking, and Control. The
first column is an Identity column for the individual positions. Now the
first drop down should be those three sections. Once one of those sections is
selected from that first drop down, the second drop down should populate with
the positions under that section in the table. I am confused about the code i
should actually use. I see an afterUpdate in the properties, but I dont know
where the Row Source is that you are talking about. I appreciate your help,
I'm just still a little bit confused. It's been a long time since I've used
Access!

Thanks, Ty

 

[Guest]

Hey, Ty.

No, I think your syntax is perfect the way it is. But if you place that SQL
statement in the Row Source property in form design view, it will only be
evaluated at each new record. Since for new records, you haven't specified
the Section, it won't return any records.

Take the code and assign it to the 2nd combo box in the AfterUpdate event of
the first combo box. Then call the Requery method of the second CBox so that
it repopulates its rows:

Sub FirstComboBox_AfterUpdate()
SecondComboBox.RowSource = "SELECT PosBySec.Position FROM PosBySec
WHERE Section=Me!combo53;"
SecondComboBox.Requery
End Sub

Hope that solves it, Ty.
Sprinks

Spinks. My table structures are now like yours. Combo53 has the sections in
it. Combo57 has the positions.. here is the Row Source for Combo57:

SELECT PosBySec.Position FROM PosBySec WHERE Section=Me!combo53;

Do I need brackets or anythign? I think i am fairly close.Thanks for your
help!

Ty, firstly let me say that perhaps it was me that confused you, because the
syntax should have been:

Me![YourSecondCBox].RowSource rather than:
Me![YourSecondCBox.RowSource]

RowSource is a property of a combo box. It is filled with an SQL statement
(a query) that returns rows of records that are displayed in the drop-down
list. Two other combo box properties are important. The Bound Column
specifies which column is actually stored in the field specified in the
Control Source property. Column Widths controls how the columns are
displayed in the drop-down. To hide a column from the display, simply set
its width to 0".

So what the code was trying to do was to specify that the second combo box
display all records where the Section equalled what was chosen in the first
combo box.

On receiving your response, however, I'm not clear on the structure of your
table. You said three of the columns are the sections--Housing, Booking, and
Control, and the fourth column is an ID of the position. I don't understand
why you'd need the three columns rather than a single Section field that
could take one of three values.

I was thinking you had a structure like this:

Position
----------
PositionID AutoNumber (Primary Key [PK])
Position Text

Section
---------
SectionID AutoNumber (PK)
Section Text

PositionBySection
--------------------
PKey AutoNumber (PK)
Section Numeric (Foreign Key to Section)
Position Numeric (Foreign Key to Position)

So the combo box would display all Positions associated with a given
section, i.e.,
the Row Source would be:

SELECT PositionBySection.Position FROM PositionBySection WHERE Section =
Me!YourComboBox

If your table structures do not match my assumption, please post the
structures, and I can help you solve this.

HTH
Sprinks

Hi Sprinks. Thanks for the help on the first question. I am having a little
bitof trouble with the ladder. I have a table of the positions. three of the
columns in this table are the sections, Housing, Booking, and Control. The
first column is an Identity column for the individual positions. Now the
first drop down should be those three sections. Once one of those sections is
selected from that first drop down, the second drop down should populate with
the positions under that section in the table. I am confused about the code i
should actually use. I see an afterUpdate in the properties, but I dont know
where the Row Source is that you are talking about. I appreciate your help,
I'm just still a little bit confused. It's been a long time since I've used
Access!

Thanks, Ty

 

[Guest]

Ty,

One more thing. Learn the naming conventions for controls, which consist of
a lowercase prefix that identify the type of control, and an initial cap
descriptor that describes the data stored there. They make things so much
easier. E.G., rather than Combo53, rename the control cboSection.

Sprinks

Spinks. My table structures are now like yours. Combo53 has the sections in
it. Combo57 has the positions.. here is the Row Source for Combo57:

SELECT PosBySec.Position FROM PosBySec WHERE Section=Me!combo53;

Do I need brackets or anythign? I think i am fairly close.Thanks for your
help!

Ty, firstly let me say that perhaps it was me that confused you, because the
syntax should have been:

Me![YourSecondCBox].RowSource rather than:
Me![YourSecondCBox.RowSource]

RowSource is a property of a combo box. It is filled with an SQL statement
(a query) that returns rows of records that are displayed in the drop-down
list. Two other combo box properties are important. The Bound Column
specifies which column is actually stored in the field specified in the
Control Source property. Column Widths controls how the columns are
displayed in the drop-down. To hide a column from the display, simply set
its width to 0".

So what the code was trying to do was to specify that the second combo box
display all records where the Section equalled what was chosen in the first
combo box.

On receiving your response, however, I'm not clear on the structure of your
table. You said three of the columns are the sections--Housing, Booking, and
Control, and the fourth column is an ID of the position. I don't understand
why you'd need the three columns rather than a single Section field that
could take one of three values.

I was thinking you had a structure like this:

Position
----------
PositionID AutoNumber (Primary Key [PK])
Position Text

Section
---------
SectionID AutoNumber (PK)
Section Text

PositionBySection
--------------------
PKey AutoNumber (PK)
Section Numeric (Foreign Key to Section)
Position Numeric (Foreign Key to Position)

So the combo box would display all Positions associated with a given
section, i.e.,
the Row Source would be:

SELECT PositionBySection.Position FROM PositionBySection WHERE Section =
Me!YourComboBox

If your table structures do not match my assumption, please post the
structures, and I can help you solve this.

HTH
Sprinks

Hi Sprinks. Thanks for the help on the first question. I am having a little
bitof trouble with the ladder. I have a table of the positions. three of the
columns in this table are the sections, Housing, Booking, and Control. The
first column is an Identity column for the individual positions. Now the
first drop down should be those three sections. Once one of those sections is
selected from that first drop down, the second drop down should populate with
the positions under that section in the table. I am confused about the code i
should actually use. I see an afterUpdate in the properties, but I dont know
where the Row Source is that you are talking about. I appreciate your help,
I'm just still a little bit confused. It's been a long time since I've used
Access!

Thanks, Ty

 

[Guest]

Hi Spinks. I am still a little bit confused, mostly dealing with the requery
method you are talking about. let me show you the row source for both combo
boxes and the after update i have for the second combo box. If you could just
inform me what else i need to do, more specifically, i would appreicate it.

Combo53 Row Source: SELECT Sections.[Section ID], Sections.Section FROM
Sections;

Combo57 Row Source: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Combo53 AfterUpdate Event: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Thanks,

Ty

Ty,

One more thing. Learn the naming conventions for controls, which consist of
a lowercase prefix that identify the type of control, and an initial cap
descriptor that describes the data stored there. They make things so much
easier. E.G., rather than Combo53, rename the control cboSection.

Sprinks

Spinks. My table structures are now like yours. Combo53 has the sections in
it. Combo57 has the positions.. here is the Row Source for Combo57:

SELECT PosBySec.Position FROM PosBySec WHERE Section=Me!combo53;

Do I need brackets or anythign? I think i am fairly close.Thanks for your
help!

Ty, firstly let me say that perhaps it was me that confused you, because the
syntax should have been:

Me![YourSecondCBox].RowSource rather than:
Me![YourSecondCBox.RowSource]

RowSource is a property of a combo box. It is filled with an SQL statement
(a query) that returns rows of records that are displayed in the drop-down
list. Two other combo box properties are important. The Bound Column
specifies which column is actually stored in the field specified in the
Control Source property. Column Widths controls how the columns are
displayed in the drop-down. To hide a column from the display, simply set
its width to 0".

So what the code was trying to do was to specify that the second combo box
display all records where the Section equalled what was chosen in the first
combo box.

On receiving your response, however, I'm not clear on the structure of your
table. You said three of the columns are the sections--Housing, Booking, and
Control, and the fourth column is an ID of the position. I don't understand
why you'd need the three columns rather than a single Section field that
could take one of three values.

I was thinking you had a structure like this:

Position
----------
PositionID AutoNumber (Primary Key [PK])
Position Text

Section
---------
SectionID AutoNumber (PK)
Section Text

PositionBySection
--------------------
PKey AutoNumber (PK)
Section Numeric (Foreign Key to Section)
Position Numeric (Foreign Key to Position)

So the combo box would display all Positions associated with a given
section, i.e.,
the Row Source would be:

SELECT PositionBySection.Position FROM PositionBySection WHERE Section =
Me!YourComboBox

If your table structures do not match my assumption, please post the
structures, and I can help you solve this.

HTH
Sprinks

:

Hi Sprinks. Thanks for the help on the first question. I am having a little
bitof trouble with the ladder. I have a table of the positions. three of the
columns in this table are the sections, Housing, Booking, and Control. The
first column is an Identity column for the individual positions. Now the
first drop down should be those three sections. Once one of those sections is
selected from that first drop down, the second drop down should populate with
the positions under that section in the table. I am confused about the code i
should actually use. I see an afterUpdate in the properties, but I dont know
where the Row Source is that you are talking about. I appreciate your help,
I'm just still a little bit confused. It's been a long time since I've used
Access!

Thanks, Ty

 

[Guest]

Hi, Ty.

I believe that the query specified in the Row Source property occurs when a
record receives the focus. If your code changes the Row Source, the query is
not re-executed automatically. To do so, run the Requery method of the Combo
Box object.

Your syntax for the Row Source properties are correct, but in the
AfterUpdate event procedure of the first combo box, you must assign a string
that represents a valid SQL statement to the RowSource property of the second
combo box, and then requery:

Me!Combo57.RowSource = "SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;"
Me!Combo57.Requery

HTH
Sprinks

Hi Spinks. I am still a little bit confused, mostly dealing with the requery
method you are talking about. let me show you the row source for both combo
boxes and the after update i have for the second combo box. If you could just
inform me what else i need to do, more specifically, i would appreicate it.

Combo53 Row Source: SELECT Sections.[Section ID], Sections.Section FROM
Sections;

Combo57 Row Source: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Combo53 AfterUpdate Event: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Thanks,

Ty

Ty,

One more thing. Learn the naming conventions for controls, which consist of
a lowercase prefix that identify the type of control, and an initial cap
descriptor that describes the data stored there. They make things so much
easier. E.G., rather than Combo53, rename the control cboSection.

Sprinks

Spinks. My table structures are now like yours. Combo53 has the sections in
it. Combo57 has the positions.. here is the Row Source for Combo57:

SELECT PosBySec.Position FROM PosBySec WHERE Section=Me!combo53;

Do I need brackets or anythign? I think i am fairly close.Thanks for your
help!

:

Ty, firstly let me say that perhaps it was me that confused you, because the
syntax should have been:

Me![YourSecondCBox].RowSource rather than:
Me![YourSecondCBox.RowSource]

RowSource is a property of a combo box. It is filled with an SQL statement
(a query) that returns rows of records that are displayed in the drop-down
list. Two other combo box properties are important. The Bound Column
specifies which column is actually stored in the field specified in the
Control Source property. Column Widths controls how the columns are
displayed in the drop-down. To hide a column from the display, simply set
its width to 0".

So what the code was trying to do was to specify that the second combo box
display all records where the Section equalled what was chosen in the first
combo box.

On receiving your response, however, I'm not clear on the structure of your
table. You said three of the columns are the sections--Housing, Booking, and
Control, and the fourth column is an ID of the position. I don't understand
why you'd need the three columns rather than a single Section field that
could take one of three values.

I was thinking you had a structure like this:

Position
----------
PositionID AutoNumber (Primary Key [PK])
Position Text

Section
---------
SectionID AutoNumber (PK)
Section Text

PositionBySection
--------------------
PKey AutoNumber (PK)
Section Numeric (Foreign Key to Section)
Position Numeric (Foreign Key to Position)

So the combo box would display all Positions associated with a given
section, i.e.,
the Row Source would be:

SELECT PositionBySection.Position FROM PositionBySection WHERE Section =
Me!YourComboBox

If your table structures do not match my assumption, please post the
structures, and I can help you solve this.

HTH
Sprinks

:

Hi Sprinks. Thanks for the help on the first question. I am having a little
bitof trouble with the ladder. I have a table of the positions. three of the
columns in this table are the sections, Housing, Booking, and Control. The
first column is an Identity column for the individual positions. Now the
first drop down should be those three sections. Once one of those sections is
selected from that first drop down, the second drop down should populate with
the positions under that section in the table. I am confused about the code i
should actually use. I see an afterUpdate in the properties, but I dont know
where the Row Source is that you are talking about. I appreciate your help,
I'm just still a little bit confused. It's been a long time since I've used
Access!

Thanks, Ty

 

[Guest]

Sprinks, It says it can't find the macro combo57 when i click on the section
drop down. I don't understand what this means. I still am quite confused.

Hi, Ty.

I believe that the query specified in the Row Source property occurs when a
record receives the focus. If your code changes the Row Source, the query is
not re-executed automatically. To do so, run the Requery method of the Combo
Box object.

Your syntax for the Row Source properties are correct, but in the
AfterUpdate event procedure of the first combo box, you must assign a string
that represents a valid SQL statement to the RowSource property of the second
combo box, and then requery:

Me!Combo57.RowSource = "SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;"
Me!Combo57.Requery

HTH
Sprinks

Hi Spinks. I am still a little bit confused, mostly dealing with the requery
method you are talking about. let me show you the row source for both combo
boxes and the after update i have for the second combo box. If you could just
inform me what else i need to do, more specifically, i would appreicate it.

Combo53 Row Source: SELECT Sections.[Section ID], Sections.Section FROM
Sections;

Combo57 Row Source: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Combo53 AfterUpdate Event: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Thanks,

Ty

Ty,

One more thing. Learn the naming conventions for controls, which consist of
a lowercase prefix that identify the type of control, and an initial cap
descriptor that describes the data stored there. They make things so much
easier. E.G., rather than Combo53, rename the control cboSection.

Sprinks

:

Spinks. My table structures are now like yours. Combo53 has the sections in
it. Combo57 has the positions.. here is the Row Source for Combo57:

SELECT PosBySec.Position FROM PosBySec WHERE Section=Me!combo53;

Do I need brackets or anythign? I think i am fairly close.Thanks for your
help!

:

Ty, firstly let me say that perhaps it was me that confused you, because the
syntax should have been:

Me![YourSecondCBox].RowSource rather than:
Me![YourSecondCBox.RowSource]

RowSource is a property of a combo box. It is filled with an SQL statement
(a query) that returns rows of records that are displayed in the drop-down
list. Two other combo box properties are important. The Bound Column
specifies which column is actually stored in the field specified in the
Control Source property. Column Widths controls how the columns are
displayed in the drop-down. To hide a column from the display, simply set
its width to 0".

So what the code was trying to do was to specify that the second combo box
display all records where the Section equalled what was chosen in the first
combo box.

On receiving your response, however, I'm not clear on the structure of your
table. You said three of the columns are the sections--Housing, Booking, and
Control, and the fourth column is an ID of the position. I don't understand
why you'd need the three columns rather than a single Section field that
could take one of three values.

I was thinking you had a structure like this:

Position
----------
PositionID AutoNumber (Primary Key [PK])
Position Text

Section
---------
SectionID AutoNumber (PK)
Section Text

PositionBySection
--------------------
PKey AutoNumber (PK)
Section Numeric (Foreign Key to Section)
Position Numeric (Foreign Key to Position)

So the combo box would display all Positions associated with a given
section, i.e.,
the Row Source would be:

SELECT PositionBySection.Position FROM PositionBySection WHERE Section =
Me!YourComboBox

If your table structures do not match my assumption, please post the
structures, and I can help you solve this.

HTH
Sprinks

:

Hi Sprinks. Thanks for the help on the first question. I am having a little
bitof trouble with the ladder. I have a table of the positions. three of the
columns in this table are the sections, Housing, Booking, and Control. The
first column is an Identity column for the individual positions. Now the
first drop down should be those three sections. Once one of those sections is
selected from that first drop down, the second drop down should populate with
the positions under that section in the table. I am confused about the code i
should actually use. I see an afterUpdate in the properties, but I dont know
where the Row Source is that you are talking about. I appreciate your help,
I'm just still a little bit confused. It's been a long time since I've used
Access!

Thanks, Ty

 

[Guest]

Ty,

This error can be caused by a spurious space somewhere. Please cut and
paste your AfterUpdate event procedure exactly, and check the RowSource
properties of both your combo boxes and that your 2nd combo box' Name
property is Combo57.

Sprinks

Sprinks, It says it can't find the macro combo57 when i click on the section
drop down. I don't understand what this means. I still am quite confused.

Hi, Ty.

I believe that the query specified in the Row Source property occurs when a
record receives the focus. If your code changes the Row Source, the query is
not re-executed automatically. To do so, run the Requery method of the Combo
Box object.

Your syntax for the Row Source properties are correct, but in the
AfterUpdate event procedure of the first combo box, you must assign a string
that represents a valid SQL statement to the RowSource property of the second
combo box, and then requery:

Me!Combo57.RowSource = "SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;"
Me!Combo57.Requery

HTH
Sprinks

Hi Spinks. I am still a little bit confused, mostly dealing with the requery
method you are talking about. let me show you the row source for both combo
boxes and the after update i have for the second combo box. If you could just
inform me what else i need to do, more specifically, i would appreicate it.

Combo53 Row Source: SELECT Sections.[Section ID], Sections.Section FROM
Sections;

Combo57 Row Source: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Combo53 AfterUpdate Event: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Thanks,

Ty

:

Ty,

One more thing. Learn the naming conventions for controls, which consist of
a lowercase prefix that identify the type of control, and an initial cap
descriptor that describes the data stored there. They make things so much
easier. E.G., rather than Combo53, rename the control cboSection.

Sprinks

:

Spinks. My table structures are now like yours. Combo53 has the sections in
it. Combo57 has the positions.. here is the Row Source for Combo57:

SELECT PosBySec.Position FROM PosBySec WHERE Section=Me!combo53;

Do I need brackets or anythign? I think i am fairly close.Thanks for your
help!

:

Ty, firstly let me say that perhaps it was me that confused you, because the
syntax should have been:

Me![YourSecondCBox].RowSource rather than:
Me![YourSecondCBox.RowSource]

RowSource is a property of a combo box. It is filled with an SQL statement
(a query) that returns rows of records that are displayed in the drop-down
list. Two other combo box properties are important. The Bound Column
specifies which column is actually stored in the field specified in the
Control Source property. Column Widths controls how the columns are
displayed in the drop-down. To hide a column from the display, simply set
its width to 0".

So what the code was trying to do was to specify that the second combo box
display all records where the Section equalled what was chosen in the first
combo box.

On receiving your response, however, I'm not clear on the structure of your
table. You said three of the columns are the sections--Housing, Booking, and
Control, and the fourth column is an ID of the position. I don't understand
why you'd need the three columns rather than a single Section field that
could take one of three values.

I was thinking you had a structure like this:

Position
----------
PositionID AutoNumber (Primary Key [PK])
Position Text

Section
---------
SectionID AutoNumber (PK)
Section Text

PositionBySection
--------------------
PKey AutoNumber (PK)
Section Numeric (Foreign Key to Section)
Position Numeric (Foreign Key to Position)

So the combo box would display all Positions associated with a given
section, i.e.,
the Row Source would be:

SELECT PositionBySection.Position FROM PositionBySection WHERE Section =
Me!YourComboBox

If your table structures do not match my assumption, please post the
structures, and I can help you solve this.

HTH
Sprinks

:

Hi Sprinks. Thanks for the help on the first question. I am having a little
bitof trouble with the ladder. I have a table of the positions. three of the
columns in this table are the sections, Housing, Booking, and Control. The
first column is an Identity column for the individual positions. Now the
first drop down should be those three sections. Once one of those sections is
selected from that first drop down, the second drop down should populate with
the positions under that section in the table. I am confused about the code i
should actually use. I see an afterUpdate in the properties, but I dont know
where the Row Source is that you are talking about. I appreciate your help,
I'm just still a little bit confused. It's been a long time since I've used
Access!

Thanks, Ty

 

[Guest]

AfterUpdate of Combo53:
Combo57.RowSource = "SELECT PosBySec.Position FROM PosBySec WHERE
Section=combo53;"
Combo57.Requery

Row Source Combo53: SELECT Sections.[Section ID], Sections.Section FROM
Sections;

Row Source Combo57: SELECT PosBySec.Position FROM PosBySec WHERE
Section=combo53;

Ty,

This error can be caused by a spurious space somewhere. Please cut and
paste your AfterUpdate event procedure exactly, and check the RowSource
properties of both your combo boxes and that your 2nd combo box' Name
property is Combo57.

Sprinks

Sprinks, It says it can't find the macro combo57 when i click on the section
drop down. I don't understand what this means. I still am quite confused.

Hi, Ty.

I believe that the query specified in the Row Source property occurs when a
record receives the focus. If your code changes the Row Source, the query is
not re-executed automatically. To do so, run the Requery method of the Combo
Box object.

Your syntax for the Row Source properties are correct, but in the
AfterUpdate event procedure of the first combo box, you must assign a string
that represents a valid SQL statement to the RowSource property of the second
combo box, and then requery:

Me!Combo57.RowSource = "SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;"
Me!Combo57.Requery

HTH
Sprinks


:

Hi Spinks. I am still a little bit confused, mostly dealing with the requery
method you are talking about. let me show you the row source for both combo
boxes and the after update i have for the second combo box. If you could just
inform me what else i need to do, more specifically, i would appreicate it.

Combo53 Row Source: SELECT Sections.[Section ID], Sections.Section FROM
Sections;

Combo57 Row Source: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Combo53 AfterUpdate Event: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Thanks,

Ty

:

Ty,

One more thing. Learn the naming conventions for controls, which consist of
a lowercase prefix that identify the type of control, and an initial cap
descriptor that describes the data stored there. They make things so much
easier. E.G., rather than Combo53, rename the control cboSection.

Sprinks

:

Spinks. My table structures are now like yours. Combo53 has the sections in
it. Combo57 has the positions.. here is the Row Source for Combo57:

SELECT PosBySec.Position FROM PosBySec WHERE Section=Me!combo53;

Do I need brackets or anythign? I think i am fairly close.Thanks for your
help!

:

Ty, firstly let me say that perhaps it was me that confused you, because the
syntax should have been:

Me![YourSecondCBox].RowSource rather than:
Me![YourSecondCBox.RowSource]

RowSource is a property of a combo box. It is filled with an SQL statement
(a query) that returns rows of records that are displayed in the drop-down
list. Two other combo box properties are important. The Bound Column
specifies which column is actually stored in the field specified in the
Control Source property. Column Widths controls how the columns are
displayed in the drop-down. To hide a column from the display, simply set
its width to 0".

So what the code was trying to do was to specify that the second combo box
display all records where the Section equalled what was chosen in the first
combo box.

On receiving your response, however, I'm not clear on the structure of your
table. You said three of the columns are the sections--Housing, Booking, and
Control, and the fourth column is an ID of the position. I don't understand
why you'd need the three columns rather than a single Section field that
could take one of three values.

I was thinking you had a structure like this:

Position
----------
PositionID AutoNumber (Primary Key [PK])
Position Text

Section
---------
SectionID AutoNumber (PK)
Section Text

PositionBySection
--------------------
PKey AutoNumber (PK)
Section Numeric (Foreign Key to Section)
Position Numeric (Foreign Key to Position)

So the combo box would display all Positions associated with a given
section, i.e.,
the Row Source would be:

SELECT PositionBySection.Position FROM PositionBySection WHERE Section =
Me!YourComboBox

If your table structures do not match my assumption, please post the
structures, and I can help you solve this.

HTH
Sprinks

:

Hi Sprinks. Thanks for the help on the first question. I am having a little
bitof trouble with the ladder. I have a table of the positions. three of the
columns in this table are the sections, Housing, Booking, and Control. The
first column is an Identity column for the individual positions. Now the
first drop down should be those three sections. Once one of those sections is
selected from that first drop down, the second drop down should populate with
the positions under that section in the table. I am confused about the code i
should actually use. I see an afterUpdate in the properties, but I dont know
where the Row Source is that you are talking about. I appreciate your help,
I'm just still a little bit confused. It's been a long time since I've used
Access!

Thanks, Ty

 

[Guest]

Ty,

I thought the problem might be in not using the Me! prefix to your controls,
but in trying to duplicate your situation, the following code worked fine,
where I had a table that related PositionID to TaskID.

Private Sub cboPosition_AfterUpdate()
cboTask.RowSource = "Select Tasks.TaskID, Tasks.Task FROM Tasks WHERE
Tasks.Position = cboPosition"
cboPosition.Requery
End Sub

I'm out of ideas. So sorry I couldn't solve your problem, but I think you
will need to repost.

Sprinks

AfterUpdate of Combo53:
Combo57.RowSource = "SELECT PosBySec.Position FROM PosBySec WHERE
Section=combo53;"
Combo57.Requery

Row Source Combo53: SELECT Sections.[Section ID], Sections.Section FROM
Sections;

Row Source Combo57: SELECT PosBySec.Position FROM PosBySec WHERE
Section=combo53;

Ty,

This error can be caused by a spurious space somewhere. Please cut and
paste your AfterUpdate event procedure exactly, and check the RowSource
properties of both your combo boxes and that your 2nd combo box' Name
property is Combo57.

Sprinks

Sprinks, It says it can't find the macro combo57 when i click on the section
drop down. I don't understand what this means. I still am quite confused.

:

Hi, Ty.

I believe that the query specified in the Row Source property occurs when a
record receives the focus. If your code changes the Row Source, the query is
not re-executed automatically. To do so, run the Requery method of the Combo
Box object.

Your syntax for the Row Source properties are correct, but in the
AfterUpdate event procedure of the first combo box, you must assign a string
that represents a valid SQL statement to the RowSource property of the second
combo box, and then requery:

Me!Combo57.RowSource = "SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;"
Me!Combo57.Requery

HTH
Sprinks


:

Hi Spinks. I am still a little bit confused, mostly dealing with the requery
method you are talking about. let me show you the row source for both combo
boxes and the after update i have for the second combo box. If you could just
inform me what else i need to do, more specifically, i would appreicate it.

Combo53 Row Source: SELECT Sections.[Section ID], Sections.Section FROM
Sections;

Combo57 Row Source: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Combo53 AfterUpdate Event: SELECT PosBySec.Position FROM PosBySec WHERE
Section=Me!combo53;

Thanks,

Ty

:

Ty,

One more thing. Learn the naming conventions for controls, which consist of
a lowercase prefix that identify the type of control, and an initial cap
descriptor that describes the data stored there. They make things so much
easier. E.G., rather than Combo53, rename the control cboSection.

Sprinks

:

Spinks. My table structures are now like yours. Combo53 has the sections in
it. Combo57 has the positions.. here is the Row Source for Combo57:

SELECT PosBySec.Position FROM PosBySec WHERE Section=Me!combo53;

Do I need brackets or anythign? I think i am fairly close.Thanks for your
help!

:

Ty, firstly let me say that perhaps it was me that confused you, because the
syntax should have been:

Me![YourSecondCBox].RowSource rather than:
Me![YourSecondCBox.RowSource]

RowSource is a property of a combo box. It is filled with an SQL statement
(a query) that returns rows of records that are displayed in the drop-down
list. Two other combo box properties are important. The Bound Column
specifies which column is actually stored in the field specified in the
Control Source property. Column Widths controls how the columns are
displayed in the drop-down. To hide a column from the display, simply set
its width to 0".

So what the code was trying to do was to specify that the second combo box
display all records where the Section equalled what was chosen in the first
combo box.

On receiving your response, however, I'm not clear on the structure of your
table. You said three of the columns are the sections--Housing, Booking, and
Control, and the fourth column is an ID of the position. I don't understand
why you'd need the three columns rather than a single Section field that
could take one of three values.

I was thinking you had a structure like this:

Position
----------
PositionID AutoNumber (Primary Key [PK])
Position Text

Section
---------
SectionID AutoNumber (PK)
Section Text

PositionBySection
--------------------
PKey AutoNumber (PK)
Section Numeric (Foreign Key to Section)
Position Numeric (Foreign Key to Position)

So the combo box would display all Positions associated with a given
section, i.e.,
the Row Source would be:

SELECT PositionBySection.Position FROM PositionBySection WHERE Section =
Me!YourComboBox

If your table structures do not match my assumption, please post the
structures, and I can help you solve this.

HTH
Sprinks

:

Hi Sprinks. Thanks for the help on the first question. I am having a little
bitof trouble with the ladder. I have a table of the positions. three of the
columns in this table are the sections, Housing, Booking, and Control. The
first column is an Identity column for the individual positions. Now the
first drop down should be those three sections. Once one of those sections is
selected from that first drop down, the second drop down should populate with
the positions under that section in the table. I am confused about the code i
should actually use. I see an afterUpdate in the properties, but I dont know
where the Row Source is that you are talking about. I appreciate your help,
I'm just still a little bit confused. It's been a long time since I've used
Access!

Thanks, Ty