Problem With Datasheet Quick Filters When Form Record Source is SQL (1 Viewer)

Aaron5714

New member
Local time
Today, 03:33
Joined
Oct 17, 2013
Messages
1
In a datasheet form I am usually able to use the "quick filters" built into each column heading to filter specific values for multiple fields. My problem is when I replace the Record Source of the datasheet form with a SQL statement instead of a table or query name. Then I can set a quick filter on one field, but after I do so, if I select another field the quick filter menu no longer shows a list of the values in the field. I have been searching trying to figure out what I might be doing wrong. The only tip I found was to go to Access Options > Current Database and select all three check boxes under Filter Lookup Options. Unfortunately that did not seem to resolve the issue.

Any suggestions would be much appreciated. Also, I'm curious if others have the same issue? You can test this by creating a datasheet form from a table, then in the Properties replace the Record Source with SELECT * FROM [table name]. Then try to use the quick filter on multiple fields and see if you can see the check boxes.
 

bikcina

Registered User.
Local time
Today, 09:33
Joined
Jun 3, 2015
Messages
15
I suppose that Aaron5714 find some workaround solution to this problem.
Searching for a solution for same or similar problem I bumped on this post, and decided to ask if maybe someone know is it somehow possible to solve this kind of a problem.

In my application I have a lot of datasheet sub-forms and in almost every case I set Record Source from a code. And on those sub-forms I can only apply one quick filter (when I try to set second quick filter, quick filter list disappears).
On those sub-forms where I collect data directly from table I can apply multiple quick filters.
I'm not sure is Record Source property making the problem.

Any ideas?

Thank you in advance.
 

bikcina

Registered User.
Local time
Today, 09:33
Joined
Jun 3, 2015
Messages
15
Actually I did specify them. I never used SELECT * when setting Record Source.
This is one of my Record Sources:
SELECT tblProjects.ID, tblProjects.ProjectName, tblProjects.InternalStatus, tblProjects.ProjectType, tblProjects.Author, tblProjects.CustomerName, "Open" AS [Open], "Copy" AS Copy, "Delete" AS [Delete], tblProjects.CountryName, tblProjects.TurbineType, tblProjects.PowerPerUnit, tblProjects.NumberOfUnits, tblProjects.CSTenderLocation, tblProjects.TG1, tblProjects.TG2, tblProjects.TG3, tblProjects.BidReview, tblProjects.TotalCost, tblProjects.ControlRoomEquipmentCost, tblProjects.CellsMaterialCost, tblProjects.SparePartsCost, tblProjects.OtherMaterialCosts, tblProjects.TrainingCost,
FROM tblProjects
ORDER BY tblProjects.CreatedAt DESC;

There is a lot more fields but I deleted them because query would be too long.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:33
Joined
May 7, 2009
Messages
19,169
create a temporary query, then when time to change recordsource, you assign the sql statement to the temp query's sql.
set your recordsource to temp query

sample:

private sub testbutton_click()
dim db as dao.database
dim qd as dao.querydef
dim strSQL as string
set db=currentdb
on error resume next
set qd = db.querydefs("TempQry")
'if error occurs, query is not yet created, create one
if (err.number <> 0) then set qd = db.createquerydef("TempQry")
'build your select statement,
'NOTE: you must specify each fieldname to include. dont use wildcard (*)
strSQL = "select [field1],[field2],.... from table1"
qd.SQL = strSQL
set qd=nothing
set db=nothing
'now set your recordsource
me.recordsource = "TempQry"
end sub

''========
this wont work if you have multi-form open and using just one temp query, you need to create temp query for each form that you need to set the recordsource through vba.
 

bikcina

Registered User.
Local time
Today, 09:33
Joined
Jun 3, 2015
Messages
15
I'll try to do that.

Thank you. I'll inform you whether is working or not.
 

bikcina

Registered User.
Local time
Today, 09:33
Joined
Jun 3, 2015
Messages
15
I have one more question that is not exactly related to this topic.
Maybe you noticed, that in my query I have:
SELECT ..., ..., ..., "Open" AS [Open], "Copy" AS Copy, "Delete" AS [Delete], ...,

And when I try to Open, Copy or Delete some element of datasheet view, I have to get ID of that element somehow.
Until now I've done that with Me.ID.Value, but now when I implemented your idea, it seams that Me.ID.Vale isn't working.
Any suggestions?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:33
Joined
May 7, 2009
Messages
19,169
have you included the ID field in your select statement on changing recordsource?
 

bikcina

Registered User.
Local time
Today, 09:33
Joined
Jun 3, 2015
Messages
15
Yes I have. In post #3 you can see that first field is ID
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:33
Joined
May 7, 2009
Messages
19,169
in datasheet view, can you see the ID?
test on Current Event of your form:

private sub form_current()
debug.print Me.ID.Value
end sub

does, it print the ID in immediate window?
if this is the subform, and your button in main form, you reference the id in subform by:

me.subformname.form!ID
 

Users who are viewing this thread

Top Bottom