Attempting to use vba to find earliest date in a record-set (not from query)

[ScubaBart]

You need to check the source field, NOT the form control.
What is Apiary_Active_LkUp mapped to, what is it's control source.

Could also try True instead of -1 ?

Here is the control field (Note, I originally had it set to True with the same outcome)

=DLookUp("[Apiary_Active_Ap]","[T_Log_Apiary]","[Log_Apiary_ID] = " & [Forms]![F_Log_Inspection_Main]![Link_to_Log_Apiary_ID_Is])

I have always understood that the filed Name was the controlling identification versus the Control Source.

Is it different in this case ? I'll get a chance to try it later

[Welshgasman]

https://learn.microsoft.com/en-us/of...lter(property)

So you should be filtering on
Apiary_Active_Ap = -1 (or True) ?

[ScubaBart]

https://learn.microsoft.com/en-us/of...lter(property)

So you should be filtering on
Apiary_Active_Ap = -1 (or True) ?

I tried that, still not working. Even changed the name of the checkbox control to match. Still displays the popup looking for the value as if it doesn't recognize the checkbox control.

Will clean up and upload file later when I get more time.

[Welshgasman]

You do not use the control to compare against. You use the field in the table.
You use the control value as criteria.

Did you even look at the link I posted?
Upload your DB with enough to see the issue and instructions on how to recreate.

[ScubaBart]

You do not use the control to compare against. You use the field in the table.
You use the control value as criteria.

Did you even look at the link I posted?
Upload your DB with enough to see the issue and instructions on how to recreate.

Yes, I did read the link.

Current version dated today is attached.

To recreate, open the primary form F_Main_Overview
Click on the button Edit/Add Inspections - This closes the main form and opens the Apiary Inspection Log.
If you look at the checkbox on the right hand side of the form, Apiary_Active_LkUp, you will see that the first few return as checked True. If you scroll down, you will see some that are not checked.

The Filter icon button is tied to the event to filter the results based on the value in that checkbox.
Clicking on that button recreates the problem.

[Welshgasman]

Ok, I think it is because you do not have Apiary_Active_Ap in your Apiary table? or the source for that form.? Why is that? I would expect whether an apiary is active or not would be held in the Apiary table?
TBH, I am not sure how you can compare against a DLookup() value. never done it, and I cannot get the syntax correct.

I would link to that table and bring in Active in the recordsource, then it is a simple filter as described.
That means you need a query for that form, not a simple table.

I do not know if you are keeping the ID and name just to make sure it all works, but you do not need to store the name. You get the name from the table that stores that ID as PK.

See if the attached file works for you.

[ScubaBart]

Ok, I think it is because you do not have Apiary_Active_Ap in your Apiary table? or the source for that form.? Why is that? I would expect whether an apiary is active or not would be held in the Apiary table?
TBH, I am not sure how you can compare against a DLookup() value. never done it, and I cannot get the syntax correct.

I would link to that table and bring in Active in the recordsource, then it is a simple filter as described.
That means you need a query for that form, not a simple table.

I do not know if you are keeping the ID and name just to make sure it all works, but you do not need to store the name. You get the name from the table that stores that ID as PK.

Apiary_Active_Ap is in the Apiary table but that table is not part of the form's source. That's why I was using the DLookUp. Comparing against the DLookUp is what I have been trying to figure out so it does appear that it will not work.

There are two levels to the Inspections, the Apiary level which covers date, time and other general factors and then the Hive level which covers the nitty gritty details for each Hive. Those two tables are linked. I had originally thought to link the Apiary level Inspection table to the Apiary Log table and the Hive Inspection table to the Hive Log table but it started creating weird conflicts so I use the Link_xxxxx_ID values in the assorted tables to make the connection.

I will explore creating a query to drive the Apiary Inspection form and see how that works out.

Any controls in the form that are not formatted are just to monitor how things work. They will go invisible once everything is working.

I use the "ID" terminology as part of both the PK's and the friendly name's and that may be confusing when looking at the table(s). Just kind of the way my system of naming fields worked out.

I will look at the file you uploaded.

Thanks !!

[Welshgasman]

Look at that form first, as I have done just that, what I said I believe you need to do, if you leave your tables, as they are.

[madpiet]

Why not do something really reckless like base the form on a query that gets the column from the other table?

We don't need your entire database. Honest. It would be helpful if you removed everything that was not pertinent to the question, because if I can't even find the form etc your asking about, I'm not going to waste my time trying to answer your question.

[Welshgasman]

Why not do something really reckless like base the form on a query that gets the column from the other table?

We don't need your entire database. Honest. It would be helpful if you removed everything that was not pertinent to the question, because if I can't even find the form etc your asking about, I'm not going to waste my time trying to answer your question.

That is what I did.

[madpiet]

no, you haven't. Sorry.

I'd say list the tables and forms I absolutely have to have to answer the question and then delete the rest. That's what I meant. I really don't care about the rest of the stuff in there. To me it's just noise. Guess I'll just ignore the whole thread.

[ScubaBart]

Why not do something really reckless like base the form on a query that gets the column from the other table?

We don't need your entire database. Honest. It would be helpful if you removed everything that was not pertinent to the question, because if I can't even find the form etc your asking about, I'm not going to waste my time trying to answer your question.

In my comment marked as #20, I provided very clear instructions on reproducing the problem I am having. And I replied to Welshgasman's suggestion that I was going to give the query approach a try. I think my question concerning the use of the DLookUp result in a filter was valid as I could not find a reference to that in my other online searches.

[Welshgasman]

Whilst I created the query for the form, I forgot to remove the DlookUp() and use the actual field in the source. So you will need to change that, if you go by that method.

Corrected in this version, plus two tweaks to make life easier.

[ScubaBart]

Took a quick look at your modified file. Pretty straightforward on the query with the linked fields. I had figured I could keep it simple with just using the DLookUp() control but apparently it will not work for filters.

I'm guessing the other tweaks are the auto open and auto close on exit. I was going to get there eventually but it's easier while developing without that.

Once I know everything is the way I want it, I will probably put the tables and maybe the queries in one file, a 'data' file essentially and put the front end into a standalone executable.

Thanks for working on this.

[Welshgasman]

Data goes in the back end. Everything else in front end.