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

[ScubaBart]

Making progress on the Bee application



So, working on the Inspection Log form(s) and attempting to filter for various criteria to show only the desired results on the main Inspection Form.

The filters I want to use are 1) By Apiary 2) Beginning Inspection Date and 3) Ending Inspection Date

I have the Apiary part of the filtering working by itself no problem and I think once I understand how the date filtering works, I can integrate it.

The main form opens with the Apiary filter set to All Apiaries and the End Date as the current date. I want to be able to populate the Begin Date with the actual date from the earliest inspection record AND I need that date value to update if I modify the Apiary filter as different Apiaries will have different inspection dates.

For the initial Begin Date value, I am hoping there is a simple vb function that will search a defined set of records for the earliest date in the Date field.

For updating, the value would have to search that field in a subset of the records as defined by the value of the Apiary filter. No other filter criteria will be applied.

I have searched and everything seems to point to queries and I can't really find anything that is just vba.

Will I have to use a query for this or can I do it relatively simply in vb ?

Once I know for sure which way I have to go and some of the basic syntax I can hopefully bumble my way through it.

If it turns complicated, I can upload my app so my approach is more easily seen.

Thanks !!

Just discovered that I do indeed have one additional criteria and that is that the records need to show only for Active Hives and Apiaries.

Going to do a little cleanup on the forms and such and upload the current version later.

[June7]

Could use a recordset but really, pulling a single value from dataset is what domain aggregate functions are for.

What do you mean by "update" - if you just want the calculation to be dynamic based on some criteria, that is certainly possible.

https://rogersaccessblog.blogspot.co...mystified.html

[ScubaBart]

Could use a recordset but really, pulling a single value from dataset is what domain aggregate functions are for.

What do you mean by "update" - if you just want the calculation to be dynamic based on some criteria, that is certainly possible.

I am updating my original post since as I am working on this, I discovered there is one other criteria that has to be applied and that is that the Apiaries must be tagged as Active. That way I don't show inspections from inactive apiaries. That particular criteria will be a constant

But, to your comments, the filtering is not intended to show a single result but rather a narrowed down set of records from the full list.

And yes, by update, I am wanting the results to update in real time as I adjust the criteria.

Looked at the link you posted but don't think that is going to accomplish what I am looking for.

I think I will need to format a few things in my forms and upload the app for it to make more sense.

[Welshgasman]

You use whatever you need to check as criteria in the domain function.

[orange]

What exactly do you want to update? Just describe the high level in simple English.
Can you give us an example, say with By Apiary?

[June7]

For the initial Begin Date value, I am hoping there is a simple vb function that will search a defined set of records for the earliest date in the Date field.

This indicates you want just a single value - domain aggregate function can accomplish that.

[ScubaBart]

I may have made this request confusing by trying to do two different though semi-related things.

I have downloaded the sample search database by Allen Browne and am looking it over and after a brief initial review, I think I will indeed be able to use this approach for my dynamic search function. While his example is set up to set the criteria then apply the resulting filter, I expect I can use the 'AfterUpdate' event to run the filter dynamically after any given change in the applicable fields.


I am also changing my approach for setting the begin date to the earliest date form the underlying table. I am going to do this only when the form is initially loaded and not try to update it to the earliest date after each change in the Apiary selection filter.

The above comment(s) suggest using the domain aggregate function. I am not familiar with that but will research it and with luck, figure it out. If not, I'll be back.......



Thanks !!

[June7]

Allen Browne's example provides user with button to trigger search. Yes, could trigger search after input into a control. This would most likely use AfterUpdate event calling the search procedure. This requires control to lose focus either with tab or enter or click.

[ScubaBart]

ok, a semi-related issue here. The filter process seems to be working except for one part.

I am using the DLookup function to set the value in a checkbox. Took a while but I got that to work fine. Problem is, when I try to use the value of that checkbox for the filter, it does not work.

I even tried to use just that single part of the filter by itself. I can set the criteria of the filter based on the value of the checkbox (continuous forms) but when I set FilterOn to true, it bugs out but gives no reason.

Is there a limitation that I am not aware of where I can't use the value obtained by a DLookup for a search criteria ?

This seems to be my final stumbling block for the filter operation.

Actually, one more piece of info, When I try to activate the filter with just that one criteria, it asks me to enter the parameter value as if it wasn't provided.

[madpiet]

I'd do this by building my filter string in code and then applying it somewhere (like in the Open event of a form or report).

[ScubaBart]

Building the filter with all the other criteria works and I am updating the filter when I change any of the conditions (in combo or unbound fields).

The problem is, is seems to not accept the value of the checkbox where I am using the DLookUp function.

[Welshgasman]

Is there a limitation that I am not aware of where I can't use the value obtained by a DLookup for a search criteria ?

No, at least not that I am aware of.

Actually, one more piece of info, When I try to activate the filter with just that one criteria, it asks me to enter the parameter value as if it wasn't provided.

That indicates it cannot find that criteria, so check your spelling and even check the value.

Start using Debug.Print to check you have what you think you have. i believe I have said this to you before?
Walk your code with F8 and breakpoints and examine what you actually have, not what you think you have.

See the Debugging link in my signature on how to do this, if you do not know.

[June7]

Does sound like a spelling error.

I have no problem pulling value from checkbox set with DLookup() expression.

[ScubaBart]

That indicates it cannot find that criteria, so check your spelling and even check the value.

Start using Debug.Print to check you have what you think you have. i believe I have said this to you before?
Walk your code with F8 and breakpoints and examine what you actually have, not what you think you have.

See the Debugging link in my signature on how to do this, if you do not know.

I have run into the spelling error before and checked, rechecked, entered, re-entered, copied and pasted the Name of the checkbox at least a dozen times.

This is the filter code

Private Sub Filter_Button_Click()

Me.Filter = "Apiary_Active_LkUp = -1"
Me.FilterOn = True


End Sub

This is the checkbox


I did a step through on the debugging as you have suggested. I did learn that from you from before.

When it steps into the Me.Filter line, that's when this popup shows up asking for the value.


I can enter a value manually and it works but that kind of defeats the purpose.

If I can't figure it out by tomorrow, I'll upload a copy of the file.

Thanks for trying to help.....

[Welshgasman]

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 ?