Table Structure

[jzwp22]

OK, I'm a little confused. You said the following which is exactly what you want:

If I understand this part, I want to show all field/plot/crop combinations created without an end date. So, when I go to frmHarvestDates to enter a harvest event, my Field/Plot/Crop Harvested combo box will have any filed/plot/crop combination with no EndDate to choose from.

If the query is giving you what you want which it appears to be the case based on the following statement, then I think you are all set.

The query results are now returning the 1 field/plot/crop combination that does not have an EndDate.

When it is time to enter the harvest information, you only want those fields(plots/crops) that have not yet been harvested, so the query is giving you exactly what you need.

I had suggested that a nested query might be necessary but that was before you added the end date field. Having that end date eliminated the need for the nested query. You will probably need nested queries at some point, so you have not wasted any effort in learning about them.

 

[hotrodsue]

I'm sorry for the confusion. I know I'm confused. Thank you for your kindness and continued help.

I guess where I'm confused is the frmHarvestDates (attached screen cap). Prior to updating qryFieldPlotCropList, the Field/Plot/Crop Harvested combo box had a drop down list of 5 test combinations. The first harvest event being Henderson Field | Plot A | Corn.

After updating qryFieldPlotCropList, I wondered if I did something wrong. With HarvestEndDate Criteria set to Is Null, the query returns the only combination without an end date. So, when I go back to frmHarvestDates the first harvest event with an EndDate has nothing in the Field/Plot/Crop Harvested drop down box. Is that right?

When I go to enter a new harvest event, the Field/Plot/Crop Harvested combo box does not display the test combinations that have not been used. It only shows the one combination without an EndDate. I guess I'm thinking the Field/Plot/Crop Harvested would display any unused combinations to select for the harvest event. Does that make sense?

Thanks again for your help.

 

[hotrodsue]

After I posted, I realized it may help to look at the database. Attached here for reference.

Many thanks!

 

[hotrodsue]

After working on it some more it seems I'm there. Attached is the current version of the database.

On qryFieldPlotCropList I changed 2 of the table joins. Now the query is returning the FULL list of test combinations already entered into the tblFieldPlotCrop. So when I go to enter a new harvest event, I have ALL combinations that do not have an EndDate. Woo Hoo! That's pretty exciting.

When I look at the first harvest event, the Field/Plot/Crop Harvested drop combo box is empty as this harvest event has an EndDate. When I look at the underlying table, it appears the data is stored as it should be. I was confused about the combox box being empty. Do you think that will be confusing for the end user?

Again, I can't begin to express how grateful I am for your ongoing input. Thank you.