Solved Between Dates On Keyword Search

[MajP]

Can you screen shot your references on this file and the one that is working? Is the error still the same?

Thing is, the sample file you gave me doesn't give the error and it's running on the same machine ?

The reference issue would likely be File specific and not machine/application specific that is why one might work and the other not. If it is a reference issue.

 

[Emma35]

References from the non working file on the left

 

[MajP]

Microsoft Access 16.0 Object Library is all the frontend stuff in Access - forms, reports, etc.
In any normal Access project, you can't even unselect it, since it is always used.

Microsoft Office 16.0 Access Database Engine Object Library is the, well, database engine.
Also known as ACE, or the successor of DAO (including all of DAO functionality). You need it to work with any kind of DAO objects, like your regular DAO.Recordset.

In the original error you posted you did not have the second reference. The code uses DAO so it gave you the error you posted. Why it does not work now I do not know. Not that it should matter (I think), but you can move the priority up and down to match the working version. Give it a try, but not hopeful.

Often when I am developing a database I create a brand new database. Then I import all the existing tables first. Compact and repair. Then I import all the forms, reports, queries, modules, and macros. Add any missing references. Compact and repair one more time and compile. This can help to clean out any problems. If you are like me you sometimes start a new database from a copy of something else. This can perpetuate issue down the line. If you cannot figure it out this is a good option to try. If not it is a good thing to do anyways to help eliminate potential corruption.

 

[Emma35]

I created a brand new database and imported everything from the non working file. Did a compact and repair and even compiled it but no luck. Also tried the long shot with the priorities but still nothing. I think at this point it'd be unfair to ask anything else of you MajP so thanks again for your help....hopefully i'll get to the bottom of it eventually.

 

[Josef P.]

Simplify the problem ... try create a simple example file. Then you can upload this one and we can look into why it's not working as it should.

 

[Gasman]

I am unable to replicate that issue. It works fine for me. Maybe delete the version you have and download again.
View attachment 109969

Late coming to this @MajP but I was keen to see what you did for this thread.
However if I just enter the begin date I get

at

However as the form appears to be unbound, I commented out that line and the filter works for me now.

 

[MajP]

@Gasman,
I must have posted a version of that code and then replaced it. I do not remember, but I do not think that line is in the current version.

@Emma35,
If you can post the non-working version it may be important to understand why it fails to work on your machine, it it works other places. This problem could manifest in other things you do.

To troubleshoot. Create a brand new database. In a module create this method and try to run it. If that does not run there is something bigger going on.

Public Sub TestRef
dim rs as dao.database
end sub

 

[Emma35]

Alright here's a version that doesn't work. Maybe you might notice something

 

[MajP]

It is still a reference issue. The ActiveX Data Objects and Access Database Engine objects are missing. I do not understand why it is not including these references. This will be a problem other places.

 

[Emma35]

I can't understand why these references are unticking themselves ??. You can see from the screenshots in post 22 i had them ticked as you recommended

 

[Josef P.]

Missing reference: DAO (ACEDAO) lib
=> Application.References.AddFromGuid "{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}", 0, 0

Missing function in form class: filterList
Example see #11 (but you have to adapt this for your requirements)
I'm wondering how you're going to implement a filter between dates with just one control.

 

[MajP]

Missing reference: DAO (ACEDAO) lib
=> Application.References.AddFromGuid "{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}", 0, 0

Missing function in form class: filterList
Example see #11 (but you have to adapt this for your requirements)
I'm wondering how you're going to implement a filter between dates with just one control.

But that does not explain what is happening. A new DB should load
Microsoft Access Database Engine Objects
AND
Microsoft ActiveX Data Objects

DAO in the prior ADO in the latter. If it is not loading these or removing these something is going on that I do not know.

Is it possible the libraries are corrupted?

@Emma35, FYI that last example does not include all the fixes to the cmbobox, the addition of the to from date fields, or the code to filter. But if you import the form I sent, my updated query, and add the references it should work.

 

[Gasman]

I cannot find filterList in the Test db uploaded.?
It exists in MajP version?

It seems none or hardly any of MajP's code is in that Test db? :-(

 

[Emma35]

Hi All,
The file i uploaded is not the exact one that MajP sent me originally. That one works fine and is not the issue. The file attached above is one where i tried to implement MajP's code myself....i need to be able to do this if i'm to use this code in future projects rather than the method i was using previously.
@MajP The To/From date fields aren't there because i hadn't got that far yet. I was trying to get the Reported By control to work first. Regarding the combo, i changed the Row Source to the one you recommended and added the FilterList() command to the After Update event. Is there something else i should have changed ?

@Gasman The two modules are both there in the Test database along with the other changes MajP suggested with the Form and query. If i've missed something then i'm not sure what it is and that might be the problem here ?

Thanks all

 

[Emma35]

Missing reference: DAO (ACEDAO) lib
=> Application.References.AddFromGuid "{4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}", 0, 0

Missing function in form class: filterList
Example see #11 (but you have to adapt this for your requirements)
I'm wondering how you're going to implement a filter between dates with just one control.

Thanks Josef......i know what you're saying but i didn't edit the modules at all as i wouldn't know where to start. Maybe this is causing the issue ?

 

[Gasman]

The two modules might well be there, but filterlist is not in any of them?
I have to go out now, so will have to look at it later.
However you should be able to debug that db.
If I add the filterlist from MajP's db, then it complains about not having the and/or option which is not in your form.
So you would need to comment that out or implement it all in one go.

 

[Gasman]

OK, in the house for a few minutes.
I removed the modules and reimported from MajP's DB.
I had to comment out some code in FilterList as those controls are not on your form.
If MajP took your form and added those controls, I would just use his form.

I had to change the control names from SearchX to those that MajP chose, more meaningful names like cmbLocation.

So look at that code and see if you can understand it.
The reset button does not work, no code behind that. Another reason to take MajP's version?

Edit: I also had to add a DAO reference?
Edit:Edit: The filterlist function is meant to be in the form, not the modules?

 

[Emma35]

Hi Gasman....i'm sorry i've been off work for a week with friggin covid. Thought we were done with all that.
I just tried your file and i'm still getting the same error when i try to use the filter ?