Populate a combo box via Qry

[HotRodSue]

My table structure is completed. Now, I’m working on forms and queries. I
have 2 forms with sub forms, frmHarvestDates and frmFields. A dairy farm has
a variety of fields harvested, with varying acre size. The database is
structured such that a harvest is an event for the whole farm and in each
harvest event are multiple fields/plots/crops being harvested.

For the frmHarvestDates I identified which field/plot/crop is being
harvested with a query (qryFieldPlotCropList) to bring all of those pieces of
info together and then used that query as the row source to a combo box bound
to fkFieldPlotCropID.

The same field/plot/crop combination will be planted exactly the same in
more than 1 season. Access will uniquely identify the records, but they will
look the same to the user. So, I chose to filter the field/plot/crop listing
by placing a HarvestEndDate in tblFieldPlotCrop. l modified
qryFieldPlotCropList to limit the list to only those (field/plot/crop
combinations) that have not yet been harvested, HarvestEndDate Is Null.

When I go to frmHarvestDates to enter a new harvest event, my
Field/Plot/Crop Harvested combo box has any field/plot/crop combination with
no EndDate to choose from. However, when I look at the first harvest event
WITH an EndDate, the Field/Plot/Crop Harvested drop combo box is EMPTY as
this harvest event has an EndDate. When I look at the underlying table, the
data is stored as it should be. I was confused about the combo box being
empty, and think it will be confusing for the end user.

How can I better approach this to give the end user a clear data entry form?

 

[Miranda]

If this is a data entry form to enter new harvest dates, then you don't
really want them to see dates that are complete as the user might make
unwanted changes. What about modifying your if statement to add if the
EndDate is not null then return "This harvest is complete.", or whatever
statement you need.

 

[HotRodSue]

Miranda:

That makes perfect sense. Wow, did I make a mountain out of a mole hill.

Typically when putting finishing touches on my forms, I have them open to a
NEW blank record. You're right, we don't want the end user to erroneously
enter data into a completed harvest event. I like you're if statement
modification. "This harvest is complete.", and will use that.

Many thanks,

Sue