how cen I use VBA to open a combo box with parameters

[Keith]

I am having a problem coding events on a form which has one combo box that
references a different query with a parameter. Everything works fine except
that When the form opens, a dialog box from the parameter query pops up. I
would like to be able to use information contained in the basic record to
answer the parameter question without having to enter it manually.

I know just about enough about VBA to work up a variable for the information
I need, (a Year date). Then I really get lost. What I really like to know
is where ( - combo or form? and what event?) and how I should code this
information in order to satisfy the parameter request without the dialog box
opening.

Thanks

 

[Graham Mandeno]

Hi Keith

You should remove the parameter from the query and supply the parameter
value using a WHERE clause.

For example, the SQL statement in your query probably includes a WHERE
clause like this:
WHERE Year([DateField]) = [Enter year:]

So you should remove that WHERE condition from your query and, when you want
to populate the combo box, use some code like this:

Me.YourComboBoxName.RowSource = _
"Select * from YourQuery where Year([DateField])=" _
& SomeYearValue

Alternatively, is the parameter value in a textbox control on your form
somewhere? If so, then you could refer to it directly in your query instead
of using the parameter.

In the criteria cell of your query, instead of:
[Enter year:]
use
Forms![YourFormName]![YourTextboxName]

If the value in the textbox changes, you will need to requery the combo box.

 

[Keith]

Thanks for your prompt reply. I think that I was not quite clear on my first
input (I am finding it very difficult to explain - maybe thats why I can't
figure it out) Anyhow, I like the second alternative that you mentioned, but
still need to figure out one more part of the problem.

The text box that contains the date formation (this is about horses -
FoalYear), needs to extract the Year and then take off one year (that is the
year the horse was bred) This reason is that we have to be sure that the
Stallion was eligible during the year of breeding. This way the drop-down
list only contains Stallions that were eligible that year.

I was able to work a a variable to give me the right year for the parameter,
but could not figure out how to apply it.

I hope this makes sense.

Keith

Hi Keith

You should remove the parameter from the query and supply the parameter
value using a WHERE clause.

For example, the SQL statement in your query probably includes a WHERE
clause like this:
WHERE Year([DateField]) = [Enter year:]

So you should remove that WHERE condition from your query and, when you want
to populate the combo box, use some code like this:

Me.YourComboBoxName.RowSource = _
"Select * from YourQuery where Year([DateField])=" _
& SomeYearValue

Alternatively, is the parameter value in a textbox control on your form
somewhere? If so, then you could refer to it directly in your query instead
of using the parameter.

In the criteria cell of your query, instead of:
[Enter year:]
use
Forms![YourFormName]![YourTextboxName]

If the value in the textbox changes, you will need to requery the combo box.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having a problem coding events on a form which has one combo box that
references a different query with a parameter. Everything works fine
except
that When the form opens, a dialog box from the parameter query pops up.
I
would like to be able to use information contained in the basic record to
answer the parameter question without having to enter it manually.

I know just about enough about VBA to work up a variable for the
information
I need, (a Year date). Then I really get lost. What I really like to
know
is where ( - combo or form? and what event?) and how I should code this
information in order to satisfy the parameter request without the dialog
box
opening.

Thanks

 

[Graham Mandeno]

Hi Keith

The Year function will give you the year part only from a given date. Let's
say [SomeDate] contains today's date (13 May 2008).

Year( [SomeDate] ) returns 2008

To get the previous year, simply subtract one from the result.

Year( [SomeDate] ) - 1 returns 2007

Now, I'm not sure what field(s) you want to compare this with in your
Stallions table.

Do you have two fields for when the stallion started and stopped being
eligible?

If so, what about a stallion that is currently eligible - is his second
field blank?

And are they date fields of number fields containing only the year?

And is the Foal's birth field a date or a year?

So many questions!

Can you please post back with some more details about your table structures
and "business rules"?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your prompt reply. I think that I was not quite clear on my
first
input (I am finding it very difficult to explain - maybe thats why I can't
figure it out) Anyhow, I like the second alternative that you mentioned,
but
still need to figure out one more part of the problem.

The text box that contains the date formation (this is about horses -
FoalYear), needs to extract the Year and then take off one year (that is
the
year the horse was bred) This reason is that we have to be sure that the
Stallion was eligible during the year of breeding. This way the
drop-down
list only contains Stallions that were eligible that year.

I was able to work a a variable to give me the right year for the
parameter,
but could not figure out how to apply it.

I hope this makes sense.

Keith

Hi Keith

You should remove the parameter from the query and supply the parameter
value using a WHERE clause.

For example, the SQL statement in your query probably includes a WHERE
clause like this:
WHERE Year([DateField]) = [Enter year:]

So you should remove that WHERE condition from your query and, when you
want
to populate the combo box, use some code like this:

Me.YourComboBoxName.RowSource = _
"Select * from YourQuery where Year([DateField])=" _
& SomeYearValue

Alternatively, is the parameter value in a textbox control on your form
somewhere? If so, then you could refer to it directly in your query
instead
of using the parameter.

In the criteria cell of your query, instead of:
[Enter year:]
use
Forms![YourFormName]![YourTextboxName]

If the value in the textbox changes, you will need to requery the combo
box.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having a problem coding events on a form which has one combo box
that
references a different query with a parameter. Everything works fine
except
that When the form opens, a dialog box from the parameter query pops
up.
I
would like to be able to use information contained in the basic record
to
answer the parameter question without having to enter it manually.

I know just about enough about VBA to work up a variable for the
information
I need, (a Year date). Then I really get lost. What I really like
to
know
is where ( - combo or form? and what event?) and how I should code this
information in order to satisfy the parameter request without the
dialog
box
opening.

Thanks

 

[David Glienna]

Use the DateDiff("D",startDate,endDate) function for DAYS elapsed, or Y for
year.

--
David Glienna
MVP - Visual Developer (Visual Basic)
2006 thru 2008

Hi Keith

The Year function will give you the year part only from a given date.
Let's say [SomeDate] contains today's date (13 May 2008).

Year( [SomeDate] ) returns 2008

To get the previous year, simply subtract one from the result.

Year( [SomeDate] ) - 1 returns 2007

Now, I'm not sure what field(s) you want to compare this with in your
Stallions table.

Do you have two fields for when the stallion started and stopped being
eligible?

If so, what about a stallion that is currently eligible - is his second
field blank?

And are they date fields of number fields containing only the year?

And is the Foal's birth field a date or a year?

So many questions!

Can you please post back with some more details about your table
structures and "business rules"?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your prompt reply. I think that I was not quite clear on my
first
input (I am finding it very difficult to explain - maybe thats why I
can't
figure it out) Anyhow, I like the second alternative that you mentioned,
but
still need to figure out one more part of the problem.

The text box that contains the date formation (this is about horses -
FoalYear), needs to extract the Year and then take off one year (that is
the
year the horse was bred) This reason is that we have to be sure that the
Stallion was eligible during the year of breeding. This way the
drop-down
list only contains Stallions that were eligible that year.

I was able to work a a variable to give me the right year for the
parameter,
but could not figure out how to apply it.

I hope this makes sense.

Keith

Hi Keith

You should remove the parameter from the query and supply the parameter
value using a WHERE clause.

For example, the SQL statement in your query probably includes a WHERE
clause like this:
WHERE Year([DateField]) = [Enter year:]

So you should remove that WHERE condition from your query and, when you
want
to populate the combo box, use some code like this:

Me.YourComboBoxName.RowSource = _
"Select * from YourQuery where Year([DateField])=" _
& SomeYearValue

Alternatively, is the parameter value in a textbox control on your form
somewhere? If so, then you could refer to it directly in your query
instead
of using the parameter.

In the criteria cell of your query, instead of:
[Enter year:]
use
Forms![YourFormName]![YourTextboxName]

If the value in the textbox changes, you will need to requery the combo
box.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having a problem coding events on a form which has one combo box
that
references a different query with a parameter. Everything works fine
except
that When the form opens, a dialog box from the parameter query pops
up.
I
would like to be able to use information contained in the basic record
to
answer the parameter question without having to enter it manually.

I know just about enough about VBA to work up a variable for the
information
I need, (a Year date). Then I really get lost. What I really like
to
know
is where ( - combo or form? and what event?) and how I should code
this
information in order to satisfy the parameter request without the
dialog
box
opening.

Thanks

 

[Keith]

Please see inserted answers to your questions below --

Thanks - Keith

Use the DateDiff("D",startDate,endDate) function for DAYS elapsed, or Y for
year.

--
David Glienna
MVP - Visual Developer (Visual Basic)
2006 thru 2008

Hi Keith

The Year function will give you the year part only from a given date.
Let's say [SomeDate] contains today's date (13 May 2008).

Year( [SomeDate] ) returns 2008

To get the previous year, simply subtract one from the result.

Year( [SomeDate] ) - 1 returns 2007

Now, I'm not sure what field(s) you want to compare this with in your
Stallions table.

Here is the Stallion Table so we compare with the StallionYear field

HorseID Long Integer
4
StallionYear Date/Time
8
Paid Date Date/Time
8
Paid Amount Currency
8
Paid Yes/No
1


No - a StallionYear and a Paid

I would say yes -

Date Fields

Foals Birth is a date

Here is the other table (main horse table) and the query that I am using:

HorseId Long Integer
4
HorseName Text
30
HorseStatus Byte
1
Color Long Integer
4
Sex Long Integer
4
Gait Long Integer
4
DateFoal Date/Time
8
StallionID Long Integer
4
TattooNo Text
15
DamID Long Integer
4
BreederID Long Integer
4
OwnerID Long Integer
4
CalBredNo Text
15


Query: qryMasterListStallionsbyYear


SQL

PARAMETERS [Year] Short;
SELECT tblHorses.HorseId, tblHorses.HorseName,
tblHorsesStallionYears.StallionYear,
tblHorsesAttribColors.[Horse Color], tblHorsesAttribSex.Sex,
tblHorsesAttribGaits.[Horse Gait],
tblHorses.DateFoal, tblHorses.CalBredNo, tblHorses_1.HorseName,
tblDams.DamName
FROM ((((((tblHorsesStallionYears INNER JOIN tblHorses ON
tblHorsesStallionYears.HorseID =
tblHorses.HorseId) INNER JOIN tblHorsesAttribColors ON
tblHorses.Color =
tblHorsesAttribColors.ColorID) INNER JOIN tblHorsesAttribSex ON
tblHorses.Sex =
tblHorsesAttribSex.SexID) INNER JOIN tblHorsesAttribGaits ON
tblHorses.Gait =
tblHorsesAttribGaits.GaitID) LEFT JOIN tblDams ON tblHorses.DamID =
tblDams.DamID) LEFT JOIN
tblHorses AS tblHorses_1 ON tblHorses.StallionID =
tblHorses_1.HorseId) LEFT JOIN tblHorsemen
ON tblHorses.BreederID = tblHorsemen.HorsemanID
WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" & [Year]))
ORDER BY tblHorses.HorseName;
Query Parameters

Name Type
[Year] Integer

Columns

Name Type
Size

HorseId Long Integer
4
tblHorses.HorseName Text
30
StallionYear Date/Time
8
Horse Color Text
15
Sex Text
8
Horse Gait Text
7
DateFoal Date/Time
8
CalBredNo Text
15
tblHorses_1.HorseName Text
30
DamName Text
25

So many questions!

Can you please post back with some more details about your table
structures and "business rules"?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your prompt reply. I think that I was not quite clear on my
first
input (I am finding it very difficult to explain - maybe thats why I
can't
figure it out) Anyhow, I like the second alternative that you mentioned,
but
still need to figure out one more part of the problem.

The text box that contains the date formation (this is about horses -
FoalYear), needs to extract the Year and then take off one year (that is
the
year the horse was bred) This reason is that we have to be sure that the
Stallion was eligible during the year of breeding. This way the
drop-down
list only contains Stallions that were eligible that year.

I was able to work a a variable to give me the right year for the
parameter,
but could not figure out how to apply it.

I hope this makes sense.

Keith

:

Hi Keith

You should remove the parameter from the query and supply the parameter
value using a WHERE clause.

For example, the SQL statement in your query probably includes a WHERE
clause like this:
WHERE Year([DateField]) = [Enter year:]

So you should remove that WHERE condition from your query and, when you
want
to populate the combo box, use some code like this:

Me.YourComboBoxName.RowSource = _
"Select * from YourQuery where Year([DateField])=" _
& SomeYearValue

Alternatively, is the parameter value in a textbox control on your form
somewhere? If so, then you could refer to it directly in your query
instead
of using the parameter.

In the criteria cell of your query, instead of:
[Enter year:]
use
Forms![YourFormName]![YourTextboxName]

If the value in the textbox changes, you will need to requery the combo
box.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having a problem coding events on a form which has one combo box
that
references a different query with a parameter. Everything works fine
except
that When the form opens, a dialog box from the parameter query pops
up.
I
would like to be able to use information contained in the basic record
to
answer the parameter question without having to enter it manually.

I know just about enough about VBA to work up a variable for the
information
I need, (a Year date). Then I really get lost. What I really like
to
know
is where ( - combo or form? and what event?) and how I should code
this
information in order to satisfy the parameter request without the
dialog
box
opening.

Thanks

 

[Graham Mandeno]

Hi Keith

Well, you've replied with a wealth of information. Perhaps I brought that
on myself ;-)

The important bits are:

1. StallionDate is a date/time field
2. DateFoal is a date/time field

You imply that every StallionYear value is 1 January and that the only part
that is of importance is the year:

WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" & [Year]))

It would therefore simplify things somewhat if you store only the year as an
integer.

What you haven't mentioned is where the value [Year] comes from. Is this
the year part of the DateFoal field, or the year previous to that, or
something else.

I asked for your "business rules". By that, I meant you to describe the
relevant rule(s) for selection of an eligible record...

For example:
"If a Horse has a FoalDate in a particular calendar year then its sire's
Stallion record must have a StallionDate on 1 January of the previous year"

Furthermore, if the RowSource of your combo box is the query
qryMasterListStallionsbyYear, then I reckon you have about 8 fields too
many. All you should normally have in a RowSource query are:
1. a key field
2. a display field (text)
3. any field(s) necessary to filter the rows in the list.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Please see inserted answers to your questions below --

Thanks - Keith

Use the DateDiff("D",startDate,endDate) function for DAYS elapsed, or Y
for
year.

--
David Glienna
MVP - Visual Developer (Visual Basic)
2006 thru 2008

Hi Keith

The Year function will give you the year part only from a given date.
Let's say [SomeDate] contains today's date (13 May 2008).

Year( [SomeDate] ) returns 2008

To get the previous year, simply subtract one from the result.

Year( [SomeDate] ) - 1 returns 2007

Now, I'm not sure what field(s) you want to compare this with in your
Stallions table.

Here is the Stallion Table so we compare with the StallionYear field

HorseID Long Integer
4
StallionYear Date/Time
8
Paid Date Date/Time
8
Paid Amount Currency
8
Paid Yes/No
1


No - a StallionYear and a Paid

I would say yes -

Date Fields

Foals Birth is a date

Here is the other table (main horse table) and the query that I am using:

HorseId Long Integer
4
HorseName Text
30
HorseStatus Byte
1
Color Long Integer
4
Sex Long Integer
4
Gait Long Integer
4
DateFoal Date/Time
8
StallionID Long Integer
4
TattooNo Text
15
DamID Long Integer
4
BreederID Long Integer
4
OwnerID Long Integer
4
CalBredNo Text
15


Query: qryMasterListStallionsbyYear


SQL

PARAMETERS [Year] Short;
SELECT tblHorses.HorseId, tblHorses.HorseName,
tblHorsesStallionYears.StallionYear,
tblHorsesAttribColors.[Horse Color], tblHorsesAttribSex.Sex,
tblHorsesAttribGaits.[Horse Gait],
tblHorses.DateFoal, tblHorses.CalBredNo, tblHorses_1.HorseName,
tblDams.DamName
FROM ((((((tblHorsesStallionYears INNER JOIN tblHorses ON
tblHorsesStallionYears.HorseID =
tblHorses.HorseId) INNER JOIN tblHorsesAttribColors ON
tblHorses.Color =
tblHorsesAttribColors.ColorID) INNER JOIN tblHorsesAttribSex ON
tblHorses.Sex =
tblHorsesAttribSex.SexID) INNER JOIN tblHorsesAttribGaits ON
tblHorses.Gait =
tblHorsesAttribGaits.GaitID) LEFT JOIN tblDams ON tblHorses.DamID
=
tblDams.DamID) LEFT JOIN
tblHorses AS tblHorses_1 ON tblHorses.StallionID =
tblHorses_1.HorseId) LEFT JOIN tblHorsemen
ON tblHorses.BreederID = tblHorsemen.HorsemanID
WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" & [Year]))
ORDER BY tblHorses.HorseName;
Query Parameters

Name Type
[Year] Integer

Columns

Name Type
Size

HorseId Long Integer
4
tblHorses.HorseName Text
30
StallionYear Date/Time
8
Horse Color Text
15
Sex Text
8
Horse Gait Text
7
DateFoal Date/Time
8
CalBredNo Text
15
tblHorses_1.HorseName Text
30
DamName Text
25

So many questions!

Can you please post back with some more details about your table
structures and "business rules"?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your prompt reply. I think that I was not quite clear on
my
first
input (I am finding it very difficult to explain - maybe thats why I
can't
figure it out) Anyhow, I like the second alternative that you
mentioned,
but
still need to figure out one more part of the problem.

The text box that contains the date formation (this is about horses -
FoalYear), needs to extract the Year and then take off one year (that
is
the
year the horse was bred) This reason is that we have to be sure that
the
Stallion was eligible during the year of breeding. This way the
drop-down
list only contains Stallions that were eligible that year.

I was able to work a a variable to give me the right year for the
parameter,
but could not figure out how to apply it.

I hope this makes sense.

Keith

:

Hi Keith

You should remove the parameter from the query and supply the
parameter
value using a WHERE clause.

For example, the SQL statement in your query probably includes a
WHERE
clause like this:
WHERE Year([DateField]) = [Enter year:]

So you should remove that WHERE condition from your query and, when
you
want
to populate the combo box, use some code like this:

Me.YourComboBoxName.RowSource = _
"Select * from YourQuery where Year([DateField])=" _
& SomeYearValue

Alternatively, is the parameter value in a textbox control on your
form
somewhere? If so, then you could refer to it directly in your query
instead
of using the parameter.

In the criteria cell of your query, instead of:
[Enter year:]
use
Forms![YourFormName]![YourTextboxName]

If the value in the textbox changes, you will need to requery the
combo
box.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having a problem coding events on a form which has one combo
box
that
references a different query with a parameter. Everything works
fine
except
that When the form opens, a dialog box from the parameter query
pops
up.
I
would like to be able to use information contained in the basic
record
to
answer the parameter question without having to enter it manually.

I know just about enough about VBA to work up a variable for the
information
I need, (a Year date). Then I really get lost. What I really
like
to
know
is where ( - combo or form? and what event?) and how I should code
this
information in order to satisfy the parameter request without the
dialog
box
opening.

Thanks

 

[Keith]

Graham - Thanks for all the help you have given me so far. I think we are
getting close to the answer.

Perhaps this might help - the program is for California Sires Stakes horses.
The horses are nominated when they are 1 year old and they must have been
sired by an eligible stallion the previous year. When entering the Stallion
name, the drop-down list should only show the Stallions that were eligible
during the year before the FoalDate.

Yes, the only part of the StallionYear value is the year part.

The rule for selecting an eligible record from the list of eligible
stallions is that they are eligible the year before the year in the DateFoal
field on the current record.

My plan was to take the year from the DateFoal field, subtract one year and
use that result as the parameter entry. That would filter the combo-box
drop-down so it would only show those stallions eligible during a particular
year (FoalDate - 1) That way only foals by a eligible stallion can be entered
in the Stakes program. I get as far as the year result, but have not been
able to figure out how to do the rest.

Does this make any sense?

Thanks

Hi Keith

Well, you've replied with a wealth of information. Perhaps I brought that
on myself ;-)

The important bits are:

1. StallionDate is a date/time field
2. DateFoal is a date/time field

You imply that every StallionYear value is 1 January and that the only part
that is of importance is the year:

WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" & [Year]))

It would therefore simplify things somewhat if you store only the year as an
integer.

What you haven't mentioned is where the value [Year] comes from. Is this
the year part of the DateFoal field, or the year previous to that, or
something else.

I asked for your "business rules". By that, I meant you to describe the
relevant rule(s) for selection of an eligible record...

For example:
"If a Horse has a FoalDate in a particular calendar year then its sire's
Stallion record must have a StallionDate on 1 January of the previous year"

Furthermore, if the RowSource of your combo box is the query
qryMasterListStallionsbyYear, then I reckon you have about 8 fields too
many. All you should normally have in a RowSource query are:
1. a key field
2. a display field (text)
3. any field(s) necessary to filter the rows in the list.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Please see inserted answers to your questions below --

Thanks - Keith

Use the DateDiff("D",startDate,endDate) function for DAYS elapsed, or Y
for
year.

--
David Glienna
MVP - Visual Developer (Visual Basic)
2006 thru 2008
Hi Keith

The Year function will give you the year part only from a given date.
Let's say [SomeDate] contains today's date (13 May 2008).

Year( [SomeDate] ) returns 2008

To get the previous year, simply subtract one from the result.

Year( [SomeDate] ) - 1 returns 2007

Now, I'm not sure what field(s) you want to compare this with in your
Stallions table.

Here is the Stallion Table so we compare with the StallionYear field

HorseID Long Integer
4
StallionYear Date/Time
8
Paid Date Date/Time
8
Paid Amount Currency
8
Paid Yes/No
1

Do you have two fields for when the stallion started and stopped being
eligible?

No - a StallionYear and a Paid

If so, what about a stallion that is currently eligible - is his second
field blank?

I would say yes -

And are they date fields of number fields containing only the year?

Date Fields

And is the Foal's birth field a date or a year?

Foals Birth is a date

Here is the other table (main horse table) and the query that I am using:

HorseId Long Integer
4
HorseName Text
30
HorseStatus Byte
1
Color Long Integer
4
Sex Long Integer
4
Gait Long Integer
4
DateFoal Date/Time
8
StallionID Long Integer
4
TattooNo Text
15
DamID Long Integer
4
BreederID Long Integer
4
OwnerID Long Integer
4
CalBredNo Text
15


Query: qryMasterListStallionsbyYear


SQL

PARAMETERS [Year] Short;
SELECT tblHorses.HorseId, tblHorses.HorseName,
tblHorsesStallionYears.StallionYear,
tblHorsesAttribColors.[Horse Color], tblHorsesAttribSex.Sex,
tblHorsesAttribGaits.[Horse Gait],
tblHorses.DateFoal, tblHorses.CalBredNo, tblHorses_1.HorseName,
tblDams.DamName
FROM ((((((tblHorsesStallionYears INNER JOIN tblHorses ON
tblHorsesStallionYears.HorseID =
tblHorses.HorseId) INNER JOIN tblHorsesAttribColors ON
tblHorses.Color =
tblHorsesAttribColors.ColorID) INNER JOIN tblHorsesAttribSex ON
tblHorses.Sex =
tblHorsesAttribSex.SexID) INNER JOIN tblHorsesAttribGaits ON
tblHorses.Gait =
tblHorsesAttribGaits.GaitID) LEFT JOIN tblDams ON tblHorses.DamID
=
tblDams.DamID) LEFT JOIN
tblHorses AS tblHorses_1 ON tblHorses.StallionID =
tblHorses_1.HorseId) LEFT JOIN tblHorsemen
ON tblHorses.BreederID = tblHorsemen.HorsemanID
WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" & [Year]))
ORDER BY tblHorses.HorseName;
Query Parameters

Name Type
[Year] Integer

Columns

Name Type
Size

HorseId Long Integer
4
tblHorses.HorseName Text
30
StallionYear Date/Time
8
Horse Color Text
15
Sex Text
8
Horse Gait Text
7
DateFoal Date/Time
8
CalBredNo Text
15
tblHorses_1.HorseName Text
30
DamName Text
25

So many questions!

Can you please post back with some more details about your table
structures and "business rules"?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your prompt reply. I think that I was not quite clear on
my
first
input (I am finding it very difficult to explain - maybe thats why I
can't
figure it out) Anyhow, I like the second alternative that you
mentioned,
but
still need to figure out one more part of the problem.

The text box that contains the date formation (this is about horses -
FoalYear), needs to extract the Year and then take off one year (that
is
the
year the horse was bred) This reason is that we have to be sure that
the
Stallion was eligible during the year of breeding. This way the
drop-down
list only contains Stallions that were eligible that year.

I was able to work a a variable to give me the right year for the
parameter,
but could not figure out how to apply it.

I hope this makes sense.

Keith

:

Hi Keith

You should remove the parameter from the query and supply the
parameter
value using a WHERE clause.

For example, the SQL statement in your query probably includes a
WHERE
clause like this:
WHERE Year([DateField]) = [Enter year:]

So you should remove that WHERE condition from your query and, when
you
want
to populate the combo box, use some code like this:

Me.YourComboBoxName.RowSource = _
"Select * from YourQuery where Year([DateField])=" _
& SomeYearValue

Alternatively, is the parameter value in a textbox control on your
form
somewhere? If so, then you could refer to it directly in your query
instead
of using the parameter.

In the criteria cell of your query, instead of:
[Enter year:]
use
Forms![YourFormName]![YourTextboxName]

If the value in the textbox changes, you will need to requery the
combo
box.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having a problem coding events on a form which has one combo
box
that
references a different query with a parameter. Everything works
fine
except
that When the form opens, a dialog box from the parameter query
pops
up.
I
would like to be able to use information contained in the basic
record
to
answer the parameter question without having to enter it manually.

I know just about enough about VBA to work up a variable for the
information

 

[Graham Mandeno]

Hi Keith

OK, then I think this will work for you.

Make a query "qryEligibleStallions" with this SQL:

SELECT tblHorses.HorseId, tblHorses.HorseName,
FROM tblHorsesStallionYears INNER JOIN tblHorses ON
tblHorsesStallionYears.HorseID = tblHorses.HorseId
WHERE Year(tblHorsesStallionYears.StallionYear)
= Year(Forms![YourFormName]![FoalDate]) - 1

Insert the name of your form in the obvious place.

Now, set the RowSource of your combo box to qryEligibleStallions.

Finally, in the AfterUpdate event of the FoalDate textbox, and in the
Current event of the form, requery the combo box:
StallionID.Requery

Let me know how you go.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham - Thanks for all the help you have given me so far. I think we are
getting close to the answer.

Perhaps this might help - the program is for California Sires Stakes
horses.
The horses are nominated when they are 1 year old and they must have been
sired by an eligible stallion the previous year. When entering the
Stallion
name, the drop-down list should only show the Stallions that were eligible
during the year before the FoalDate.

Yes, the only part of the StallionYear value is the year part.

The rule for selecting an eligible record from the list of eligible
stallions is that they are eligible the year before the year in the
DateFoal
field on the current record.

My plan was to take the year from the DateFoal field, subtract one year
and
use that result as the parameter entry. That would filter the combo-box
drop-down so it would only show those stallions eligible during a
particular
year (FoalDate - 1) That way only foals by a eligible stallion can be
entered
in the Stakes program. I get as far as the year result, but have not been
able to figure out how to do the rest.

Does this make any sense?

Thanks

Hi Keith

Well, you've replied with a wealth of information. Perhaps I brought
that
on myself ;-)

The important bits are:

1. StallionDate is a date/time field
2. DateFoal is a date/time field

You imply that every StallionYear value is 1 January and that the only
part
that is of importance is the year:

WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" & [Year]))

It would therefore simplify things somewhat if you store only the year as
an
integer.

What you haven't mentioned is where the value [Year] comes from. Is this
the year part of the DateFoal field, or the year previous to that, or
something else.

I asked for your "business rules". By that, I meant you to describe the
relevant rule(s) for selection of an eligible record...

For example:
"If a Horse has a FoalDate in a particular calendar year then its sire's
Stallion record must have a StallionDate on 1 January of the previous
year"

Furthermore, if the RowSource of your combo box is the query
qryMasterListStallionsbyYear, then I reckon you have about 8 fields too
many. All you should normally have in a RowSource query are:
1. a key field
2. a display field (text)
3. any field(s) necessary to filter the rows in the list.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Please see inserted answers to your questions below --

Thanks - Keith

:

Use the DateDiff("D",startDate,endDate) function for DAYS elapsed, or
Y
for
year.

--
David Glienna
MVP - Visual Developer (Visual Basic)
2006 thru 2008
Hi Keith

The Year function will give you the year part only from a given
date.
Let's say [SomeDate] contains today's date (13 May 2008).

Year( [SomeDate] ) returns 2008

To get the previous year, simply subtract one from the result.

Year( [SomeDate] ) - 1 returns 2007

Now, I'm not sure what field(s) you want to compare this with in
your
Stallions table.

Here is the Stallion Table so we compare with the StallionYear field

HorseID Long Integer
4
StallionYear Date/Time
8
Paid Date Date/Time
8
Paid Amount Currency
8
Paid Yes/No
1



Do you have two fields for when the stallion started and stopped
being
eligible?

No - a StallionYear and a Paid


If so, what about a stallion that is currently eligible - is his
second
field blank?

I would say yes -


And are they date fields of number fields containing only the year?

Date Fields


And is the Foal's birth field a date or a year?

Foals Birth is a date



Here is the other table (main horse table) and the query that I am
using:

HorseId Long Integer
4
HorseName Text
30
HorseStatus Byte
1
Color Long Integer
4
Sex Long Integer
4
Gait Long Integer
4
DateFoal Date/Time
8
StallionID Long Integer
4
TattooNo Text
15
DamID Long Integer
4
BreederID Long Integer
4
OwnerID Long Integer
4
CalBredNo Text
15


Query: qryMasterListStallionsbyYear


SQL

PARAMETERS [Year] Short;
SELECT tblHorses.HorseId, tblHorses.HorseName,
tblHorsesStallionYears.StallionYear,
tblHorsesAttribColors.[Horse Color], tblHorsesAttribSex.Sex,
tblHorsesAttribGaits.[Horse Gait],
tblHorses.DateFoal, tblHorses.CalBredNo, tblHorses_1.HorseName,
tblDams.DamName
FROM ((((((tblHorsesStallionYears INNER JOIN tblHorses ON
tblHorsesStallionYears.HorseID =
tblHorses.HorseId) INNER JOIN tblHorsesAttribColors ON
tblHorses.Color =
tblHorsesAttribColors.ColorID) INNER JOIN tblHorsesAttribSex ON
tblHorses.Sex =
tblHorsesAttribSex.SexID) INNER JOIN tblHorsesAttribGaits ON
tblHorses.Gait =
tblHorsesAttribGaits.GaitID) LEFT JOIN tblDams ON
tblHorses.DamID
=
tblDams.DamID) LEFT JOIN
tblHorses AS tblHorses_1 ON tblHorses.StallionID =
tblHorses_1.HorseId) LEFT JOIN tblHorsemen
ON tblHorses.BreederID = tblHorsemen.HorsemanID
WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" & [Year]))
ORDER BY tblHorses.HorseName;
Query Parameters

Name Type
[Year] Integer

Columns

Name Type
Size

HorseId Long Integer
4
tblHorses.HorseName Text
30
StallionYear Date/Time
8
Horse Color Text
15
Sex Text
8
Horse Gait Text
7
DateFoal Date/Time
8
CalBredNo Text
15
tblHorses_1.HorseName Text
30
DamName Text
25


So many questions!

Can you please post back with some more details about your table
structures and "business rules"?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your prompt reply. I think that I was not quite clear
on
my
first
input (I am finding it very difficult to explain - maybe thats why
I
can't
figure it out) Anyhow, I like the second alternative that you
mentioned,
but
still need to figure out one more part of the problem.

The text box that contains the date formation (this is about
horses -
FoalYear), needs to extract the Year and then take off one year
(that
is
the
year the horse was bred) This reason is that we have to be sure
that
the
Stallion was eligible during the year of breeding. This way the
drop-down
list only contains Stallions that were eligible that year.

I was able to work a a variable to give me the right year for the
parameter,
but could not figure out how to apply it.

I hope this makes sense.

Keith

:

Hi Keith

You should remove the parameter from the query and supply the
parameter
value using a WHERE clause.

For example, the SQL statement in your query probably includes a
WHERE
clause like this:
WHERE Year([DateField]) = [Enter year:]

So you should remove that WHERE condition from your query and,
when
you
want
to populate the combo box, use some code like this:

Me.YourComboBoxName.RowSource = _
"Select * from YourQuery where Year([DateField])=" _
& SomeYearValue

Alternatively, is the parameter value in a textbox control on your
form
somewhere? If so, then you could refer to it directly in your
query
instead
of using the parameter.

In the criteria cell of your query, instead of:
[Enter year:]
use
Forms![YourFormName]![YourTextboxName]

If the value in the textbox changes, you will need to requery the
combo
box.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having a problem coding events on a form which has one combo
box
that
references a different query with a parameter. Everything works
fine
except
that When the form opens, a dialog box from the parameter query
pops
up.
I
would like to be able to use information contained in the basic
record
to
answer the parameter question without having to enter it
manually.

I know just about enough about VBA to work up a variable for the
information

 

[Keith]

Graham -

Thank you very much for your help and patience. Your solution to my problem
works just fine.

Thanks Again - Keith

Hi Keith

OK, then I think this will work for you.

Make a query "qryEligibleStallions" with this SQL:

SELECT tblHorses.HorseId, tblHorses.HorseName,
FROM tblHorsesStallionYears INNER JOIN tblHorses ON
tblHorsesStallionYears.HorseID = tblHorses.HorseId
WHERE Year(tblHorsesStallionYears.StallionYear)
= Year(Forms![YourFormName]![FoalDate]) - 1

Insert the name of your form in the obvious place.

Now, set the RowSource of your combo box to qryEligibleStallions.

Finally, in the AfterUpdate event of the FoalDate textbox, and in the
Current event of the form, requery the combo box:
StallionID.Requery

Let me know how you go.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham - Thanks for all the help you have given me so far. I think we are
getting close to the answer.

Perhaps this might help - the program is for California Sires Stakes
horses.
The horses are nominated when they are 1 year old and they must have been
sired by an eligible stallion the previous year. When entering the
Stallion
name, the drop-down list should only show the Stallions that were eligible
during the year before the FoalDate.

Yes, the only part of the StallionYear value is the year part.

The rule for selecting an eligible record from the list of eligible
stallions is that they are eligible the year before the year in the
DateFoal
field on the current record.

My plan was to take the year from the DateFoal field, subtract one year
and
use that result as the parameter entry. That would filter the combo-box
drop-down so it would only show those stallions eligible during a
particular
year (FoalDate - 1) That way only foals by a eligible stallion can be
entered
in the Stakes program. I get as far as the year result, but have not been
able to figure out how to do the rest.

Does this make any sense?

Thanks

Hi Keith

Well, you've replied with a wealth of information. Perhaps I brought
that
on myself ;-)

The important bits are:

1. StallionDate is a date/time field
2. DateFoal is a date/time field

You imply that every StallionYear value is 1 January and that the only
part
that is of importance is the year:
WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" & [Year]))
It would therefore simplify things somewhat if you store only the year as
an
integer.

What you haven't mentioned is where the value [Year] comes from. Is this
the year part of the DateFoal field, or the year previous to that, or
something else.

I asked for your "business rules". By that, I meant you to describe the
relevant rule(s) for selection of an eligible record...

For example:
"If a Horse has a FoalDate in a particular calendar year then its sire's
Stallion record must have a StallionDate on 1 January of the previous
year"

Furthermore, if the RowSource of your combo box is the query
qryMasterListStallionsbyYear, then I reckon you have about 8 fields too
many. All you should normally have in a RowSource query are:
1. a key field
2. a display field (text)
3. any field(s) necessary to filter the rows in the list.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Please see inserted answers to your questions below --

Thanks - Keith

:

Use the DateDiff("D",startDate,endDate) function for DAYS elapsed, or
Y
for
year.

--
David Glienna
MVP - Visual Developer (Visual Basic)
2006 thru 2008
Hi Keith

The Year function will give you the year part only from a given
date.
Let's say [SomeDate] contains today's date (13 May 2008).

Year( [SomeDate] ) returns 2008

To get the previous year, simply subtract one from the result.

Year( [SomeDate] ) - 1 returns 2007

Now, I'm not sure what field(s) you want to compare this with in
your
Stallions table.

Here is the Stallion Table so we compare with the StallionYear field

HorseID Long Integer
4
StallionYear Date/Time
8
Paid Date Date/Time
8
Paid Amount Currency
8
Paid Yes/No
1



Do you have two fields for when the stallion started and stopped
being
eligible?

No - a StallionYear and a Paid


If so, what about a stallion that is currently eligible - is his
second
field blank?

I would say yes -


And are they date fields of number fields containing only the year?

Date Fields


And is the Foal's birth field a date or a year?

Foals Birth is a date



Here is the other table (main horse table) and the query that I am
using:

HorseId Long Integer
4
HorseName Text
30
HorseStatus Byte
1
Color Long Integer
4
Sex Long Integer
4
Gait Long Integer
4
DateFoal Date/Time
8
StallionID Long Integer
4
TattooNo Text
15
DamID Long Integer
4
BreederID Long Integer
4
OwnerID Long Integer
4
CalBredNo Text
15


Query: qryMasterListStallionsbyYear


SQL

PARAMETERS [Year] Short;
SELECT tblHorses.HorseId, tblHorses.HorseName,
tblHorsesStallionYears.StallionYear,
tblHorsesAttribColors.[Horse Color], tblHorsesAttribSex.Sex,
tblHorsesAttribGaits.[Horse Gait],
tblHorses.DateFoal, tblHorses.CalBredNo, tblHorses_1.HorseName,
tblDams.DamName
FROM ((((((tblHorsesStallionYears INNER JOIN tblHorses ON
tblHorsesStallionYears.HorseID =
tblHorses.HorseId) INNER JOIN tblHorsesAttribColors ON
tblHorses.Color =
tblHorsesAttribColors.ColorID) INNER JOIN tblHorsesAttribSex ON
tblHorses.Sex =
tblHorsesAttribSex.SexID) INNER JOIN tblHorsesAttribGaits ON
tblHorses.Gait =
tblHorsesAttribGaits.GaitID) LEFT JOIN tblDams ON
tblHorses.DamID
=
tblDams.DamID) LEFT JOIN
tblHorses AS tblHorses_1 ON tblHorses.StallionID =
tblHorses_1.HorseId) LEFT JOIN tblHorsemen
ON tblHorses.BreederID = tblHorsemen.HorsemanID
WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" & [Year]))
ORDER BY tblHorses.HorseName;
Query Parameters

Name Type
[Year] Integer

Columns

Name Type
Size

HorseId Long Integer
4
tblHorses.HorseName Text
30
StallionYear Date/Time
8
Horse Color Text
15
Sex Text
8
Horse Gait Text
7
DateFoal Date/Time
8
CalBredNo Text
15
tblHorses_1.HorseName Text
30
DamName Text
25


So many questions!

Can you please post back with some more details about your table
structures and "business rules"?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your prompt reply. I think that I was not quite clear
on
my
first
input (I am finding it very difficult to explain - maybe thats why
I
can't
figure it out) Anyhow, I like the second alternative that you
mentioned,
but
still need to figure out one more part of the problem.

The text box that contains the date formation (this is about
horses -

 

[Graham Mandeno]

That's great, Keith! Glad to help

Graham -

Thank you very much for your help and patience. Your solution to my
problem
works just fine.

Thanks Again - Keith

Hi Keith

OK, then I think this will work for you.

Make a query "qryEligibleStallions" with this SQL:

SELECT tblHorses.HorseId, tblHorses.HorseName,
FROM tblHorsesStallionYears INNER JOIN tblHorses ON
tblHorsesStallionYears.HorseID = tblHorses.HorseId
WHERE Year(tblHorsesStallionYears.StallionYear)
= Year(Forms![YourFormName]![FoalDate]) - 1

Insert the name of your form in the obvious place.

Now, set the RowSource of your combo box to qryEligibleStallions.

Finally, in the AfterUpdate event of the FoalDate textbox, and in the
Current event of the form, requery the combo box:
StallionID.Requery

Let me know how you go.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham - Thanks for all the help you have given me so far. I think we
are
getting close to the answer.

Perhaps this might help - the program is for California Sires Stakes
horses.
The horses are nominated when they are 1 year old and they must have
been
sired by an eligible stallion the previous year. When entering the
Stallion
name, the drop-down list should only show the Stallions that were
eligible
during the year before the FoalDate.

Yes, the only part of the StallionYear value is the year part.

The rule for selecting an eligible record from the list of eligible
stallions is that they are eligible the year before the year in the
DateFoal
field on the current record.

My plan was to take the year from the DateFoal field, subtract one year
and
use that result as the parameter entry. That would filter the
combo-box
drop-down so it would only show those stallions eligible during a
particular
year (FoalDate - 1) That way only foals by a eligible stallion can be
entered
in the Stakes program. I get as far as the year result, but have not
been
able to figure out how to do the rest.

Does this make any sense?

Thanks


:

Hi Keith

Well, you've replied with a wealth of information. Perhaps I brought
that
on myself ;-)

The important bits are:

1. StallionDate is a date/time field
2. DateFoal is a date/time field

You imply that every StallionYear value is 1 January and that the only
part
that is of importance is the year:
WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" &
[Year]))
It would therefore simplify things somewhat if you store only the year
as
an
integer.

What you haven't mentioned is where the value [Year] comes from. Is
this
the year part of the DateFoal field, or the year previous to that, or
something else.

I asked for your "business rules". By that, I meant you to describe
the
relevant rule(s) for selection of an eligible record...

For example:
"If a Horse has a FoalDate in a particular calendar year then its
sire's
Stallion record must have a StallionDate on 1 January of the previous
year"

Furthermore, if the RowSource of your combo box is the query
qryMasterListStallionsbyYear, then I reckon you have about 8 fields
too
many. All you should normally have in a RowSource query are:
1. a key field
2. a display field (text)
3. any field(s) necessary to filter the rows in the list.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Please see inserted answers to your questions below --

Thanks - Keith

:

Use the DateDiff("D",startDate,endDate) function for DAYS elapsed,
or
Y
for
year.

--
David Glienna
MVP - Visual Developer (Visual Basic)
2006 thru 2008
Hi Keith

The Year function will give you the year part only from a given
date.
Let's say [SomeDate] contains today's date (13 May 2008).

Year( [SomeDate] ) returns 2008

To get the previous year, simply subtract one from the result.

Year( [SomeDate] ) - 1 returns 2007

Now, I'm not sure what field(s) you want to compare this with in
your
Stallions table.

Here is the Stallion Table so we compare with the StallionYear field

HorseID Long Integer
4
StallionYear Date/Time
8
Paid Date Date/Time
8
Paid Amount Currency
8
Paid Yes/No
1



Do you have two fields for when the stallion started and stopped
being
eligible?

No - a StallionYear and a Paid


If so, what about a stallion that is currently eligible - is his
second
field blank?

I would say yes -


And are they date fields of number fields containing only the
year?

Date Fields


And is the Foal's birth field a date or a year?

Foals Birth is a date



Here is the other table (main horse table) and the query that I am
using:

HorseId Long Integer
4
HorseName Text
30
HorseStatus Byte
1
Color Long Integer
4
Sex Long Integer
4
Gait Long Integer
4
DateFoal Date/Time
8
StallionID Long Integer
4
TattooNo Text
15
DamID Long Integer
4
BreederID Long Integer
4
OwnerID Long Integer
4
CalBredNo Text
15


Query: qryMasterListStallionsbyYear


SQL

PARAMETERS [Year] Short;
SELECT tblHorses.HorseId, tblHorses.HorseName,
tblHorsesStallionYears.StallionYear,
tblHorsesAttribColors.[Horse Color], tblHorsesAttribSex.Sex,
tblHorsesAttribGaits.[Horse Gait],
tblHorses.DateFoal, tblHorses.CalBredNo,
tblHorses_1.HorseName,
tblDams.DamName
FROM ((((((tblHorsesStallionYears INNER JOIN tblHorses ON
tblHorsesStallionYears.HorseID =
tblHorses.HorseId) INNER JOIN tblHorsesAttribColors ON
tblHorses.Color =
tblHorsesAttribColors.ColorID) INNER JOIN tblHorsesAttribSex
ON
tblHorses.Sex =
tblHorsesAttribSex.SexID) INNER JOIN tblHorsesAttribGaits ON
tblHorses.Gait =
tblHorsesAttribGaits.GaitID) LEFT JOIN tblDams ON
tblHorses.DamID
=
tblDams.DamID) LEFT JOIN
tblHorses AS tblHorses_1 ON tblHorses.StallionID =
tblHorses_1.HorseId) LEFT JOIN tblHorsemen
ON tblHorses.BreederID = tblHorsemen.HorsemanID
WHERE (((tblHorsesStallionYears.StallionYear)="1/1/" &
[Year]))
ORDER BY tblHorses.HorseName;
Query Parameters

Name Type
[Year] Integer

Columns

Name Type
Size

HorseId Long
Integer
4
tblHorses.HorseName Text
30
StallionYear Date/Time
8
Horse Color Text
15
Sex Text
8
Horse Gait Text
7
DateFoal
Date/Time
8
CalBredNo Text
15
tblHorses_1.HorseName Text
30
DamName Text
25


So many questions!

Can you please post back with some more details about your table
structures and "business rules"?
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your prompt reply. I think that I was not quite
clear
on
my
first
input (I am finding it very difficult to explain - maybe thats
why
I
can't
figure it out) Anyhow, I like the second alternative that you
mentioned,
but
still need to figure out one more part of the problem.

The text box that contains the date formation (this is about
horses -