combo box options based on previous control

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a form set up to allow users to input data relating to a person's
baptism. This includes recording the date of baptism and the parish. The date
is a text box with date formatting and the parish is a combo box with values
selected from another table. The data is historical and the parish names
change over time. The 'parish' table has three columns that have all names
that are valid for various points in time eg. pre-1988, post-2000 and between
these dates.
I have created an invisible text box that extracts the year of baptism from
the complete date entered. I was somehow hoping that based on this value I
would be able to display only the relevant column in the drop down list of
the combo box. For example if I entered the date of baptism as 31/3/05, my
text box would extract 2005 which would then be able to instruct the combo
box to only show parish names from the post-2000 column of this table (which
in this case is column A).
To me this sounds like it should be easy to do, but I haven't been able to
get very far with this and haven't found a solution on this discussion board
that seems to match the problem.
I tend to use mainly macros and expressions because I've got no experience
in VBA - unless it's spelt out.

Hoping someone can help.

thanks,
 
You don't need a hidden text box to extract the year. You could try this in
the After Update Event of the Baptism field.

if Year([Baptism]) < 1998 then
Combo.RowSource = "SELECT [Parish].ColumnA FROM [Parish] ORDER BY
[Parish].ColumnA;"
else
if Year([Baptism]) >=1998 And Year([Baptism]) < 2000 then
Combo.RowSource = "SELECT [Parish].ColumnB FROM [Parish] ORDER BY
[Parish].ColumnB;"
else
Combo.RowSource = "SELECT [Parish].ColumnC FROM [Parish] ORDER
BY [Parish].ColumnC;"
end if
 
Thanks,
I've entered this code on the after update event of the 'Baptism' textbox.
Now how do I link this in to my combo box? On my form I've called this combo
box 'parish combo' and the control source is the 'Parish' field in the
'Bapt1' table.
I'm guessing that I somehow need to do something with the row source in the
combo box properties to tell it to look at the code in the Baptism textbox.

Dennis said:
You don't need a hidden text box to extract the year. You could try this in
the After Update Event of the Baptism field.

if Year([Baptism]) < 1998 then
Combo.RowSource = "SELECT [Parish].ColumnA FROM [Parish] ORDER BY
[Parish].ColumnA;"
else
if Year([Baptism]) >=1998 And Year([Baptism]) < 2000 then
Combo.RowSource = "SELECT [Parish].ColumnB FROM [Parish] ORDER BY
[Parish].ColumnB;"
else
Combo.RowSource = "SELECT [Parish].ColumnC FROM [Parish] ORDER
BY [Parish].ColumnC;"
end if



Caroline said:
Hello,
I have a form set up to allow users to input data relating to a person's
baptism. This includes recording the date of baptism and the parish. The date
is a text box with date formatting and the parish is a combo box with values
selected from another table. The data is historical and the parish names
change over time. The 'parish' table has three columns that have all names
that are valid for various points in time eg. pre-1988, post-2000 and between
these dates.
I have created an invisible text box that extracts the year of baptism from
the complete date entered. I was somehow hoping that based on this value I
would be able to display only the relevant column in the drop down list of
the combo box. For example if I entered the date of baptism as 31/3/05, my
text box would extract 2005 which would then be able to instruct the combo
box to only show parish names from the post-2000 column of this table (which
in this case is column A).
To me this sounds like it should be easy to do, but I haven't been able to
get very far with this and haven't found a solution on this discussion board
that seems to match the problem.
I tend to use mainly macros and expressions because I've got no experience
in VBA - unless it's spelt out.

Hoping someone can help.

thanks,
Click to expand...
Click to expand...
 
I didn't know the name of your combo box when I wrote the code, so where I
have put Combo.RowSoure, you put [parish combo].RowSource.
ColumnA, ColumnB and ColumnC should be your actual field names for these
columns in the parish table.
Also at the end of the code put [parish combo].Requery
Basically the code is defining the contents of the combo box based on the
date you enter in the baptism field and then refreshing the contents after
you have defined them.

Caroline said:
Thanks,
I've entered this code on the after update event of the 'Baptism' textbox.
Now how do I link this in to my combo box? On my form I've called this combo
box 'parish combo' and the control source is the 'Parish' field in the
'Bapt1' table.
I'm guessing that I somehow need to do something with the row source in the
combo box properties to tell it to look at the code in the Baptism textbox.

Dennis said:
You don't need a hidden text box to extract the year. You could try this in
the After Update Event of the Baptism field.

if Year([Baptism]) < 1998 then
Combo.RowSource = "SELECT [Parish].ColumnA FROM [Parish] ORDER BY
[Parish].ColumnA;"
else
if Year([Baptism]) >=1998 And Year([Baptism]) < 2000 then
Combo.RowSource = "SELECT [Parish].ColumnB FROM [Parish] ORDER BY
[Parish].ColumnB;"
else
Combo.RowSource = "SELECT [Parish].ColumnC FROM [Parish] ORDER
BY [Parish].ColumnC;"
end if



Caroline said:
Hello,
I have a form set up to allow users to input data relating to a person's
baptism. This includes recording the date of baptism and the parish. The date
is a text box with date formatting and the parish is a combo box with values
selected from another table. The data is historical and the parish names
change over time. The 'parish' table has three columns that have all names
that are valid for various points in time eg. pre-1988, post-2000 and between
these dates.
I have created an invisible text box that extracts the year of baptism from
the complete date entered. I was somehow hoping that based on this value I
would be able to display only the relevant column in the drop down list of
the combo box. For example if I entered the date of baptism as 31/3/05, my
text box would extract 2005 which would then be able to instruct the combo
box to only show parish names from the post-2000 column of this table (which
in this case is column A).
To me this sounds like it should be easy to do, but I haven't been able to
get very far with this and haven't found a solution on this discussion board
that seems to match the problem.
I tend to use mainly macros and expressions because I've got no experience
in VBA - unless it's spelt out.

Hoping someone can help.

thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top