Forms in Access - Auto populating fields

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

"Ty" wrote:

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.

"Sprinks" wrote:

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


"Ty" wrote:

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

"Sprinks" wrote:

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

"Ty" wrote:

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!

"Sprinks" wrote:

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

"Ty" wrote:

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

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;


"Sprinks" wrote:

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

"Ty" wrote:

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.

"Sprinks" wrote:

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


"Ty" wrote:

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

"Sprinks" wrote:

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

"Ty" wrote:

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!

"Sprinks" wrote:

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

"Ty" wrote:

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

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




"Ty" wrote:

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;


"Sprinks" wrote:

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

"Ty" wrote:

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.

"Sprinks" wrote:

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


"Ty" wrote:

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

"Sprinks" wrote:

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

"Ty" wrote:

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!

"Sprinks" wrote:

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

"Ty" wrote:

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