query work but datasheet shows no records (1 Viewer)

I have a query that includes wild cards and it runs fine when it has to. /the only problem I'm having is that the forms datasheet isn't returning the records. I see them in the query and on the form a textbox I have set up to show the number of records is showing the correct number of records but just nothing appears on the datasheet, this is my query and my VBA code for the form :
_query________________________________________________________

Like [Forms]![TblPurchases].[CboMaterial] & "*"

_____________________________________________________________
Private Sub cmdSearchMaterial_Click()
Dim task2 As String

task2 = "select * from TblPurchases order by ([ Material]) "
Me.Filter = "[Material ] = '" & Me.CboMaterial & " '"
Me.TxtTotal = Format(DCount("Material", "QueryMaterials"), "0")

End Sub

I have a similar one running for "Vendors" which works perfectly the query and records appearing on the datasheet, just cant seem to see why this one isn't working.:banghead:

Well I would have thought that you would need to set the recordsource in the form property sheet or by VBA.?

Also you would need to switch on the filter?, it is not enough to just set it.?

Go back and inspect your Vendor method and see the differences.

Gasman said:

Well I would have thought that you would need to set the recordsource in the form property sheet or by VBA.?

Also you would need to switch on the filter?, it is not enough to just set it.?

Go back and inspect your Vendor method and see the differences.

Click to expand...

That's not the solution as this is the VBA for "Vendor" and it works perefectly

Private Sub CmdSearchVendors_Click()
Dim task As String

task = "select * from TblPurchases order by [vendors] "
Me.Filter = "[Vendor] = '" & Me.cbovendors & "'"
Me.TxtTotal = Format(DCount("vendor", "TblPurchases Query"), "0")

End Sub


As I had mentioned before the query runs and the records appear in the query datasheet just not in the forms datasheet.

Ok, as my signature shows, I'm not an expert, but the only line I can see working is the Me.txtTotal.
The filter is not set on? How does that work?
What is task meant to do?
Without a record source, I cannot see how a form would work.?

So I am not able to help, sorry.

Gasman said:

Ok, as my signature shows, I'm not an expert, but the only line I can see working is the Me.txtTotal.
The filter is not set on? How does that work?
What is task meant to do?
Without a record source, I cannot see how a form would work.?

So I am not able to help, sorry.

Click to expand...

My code is not too long I will share it, I'm no expert as well as you see.


Option Compare Database



Private Sub CmdSearch_Click()
'Search button

Call Search

End Sub
Sub Search()
Dim strCriteria, task As String

Me.Refresh
If Me.TxtPurchaseDateFrom.Value = "" Or Me.TxtPurchaseDateTo.Value = "" Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.TxtPurchaseDateFrom.SetFocus
Else
strCriteria = "([Date of Purchase] >= #" & Nz(Me.TxtPurchaseDateFrom) & "# And [Date of Purchase] <=#" & Nz(Me.TxtPurchaseDateTo) & "#)"
task = "select * From TblPurchases Where( " & strCriteria & ") order by [Date of Purchase] "

DoCmd.ApplyFilter task
'DoCmd.ApplyFilter criteria
Me.TxtTotal = FindRecordCount(task)


End If

End Sub

Private Sub cmdSearchMaterial_Click()
Dim task2 As String

task2 = "select * from TblPurchases order by ([ Material]) "
Me.Filter = "[Material ] = '" & Me.CboMaterial & " '"
Me.TxtTotal = Format(DCount("Material", "QueryMaterials"), "0")


End Sub

Private Sub CmdSearchVendors_Click()
Dim task As String

task = "select * from TblPurchases order by [vendors] "
Me.Filter = "[Vendor] = '" & Me.cbovendors & "'"
Me.TxtTotal = Format(DCount("vendor", "TblPurchases Query"), "0")

End Sub

Private Sub CmdClear_Click()

Dim task As String


Me.CboMaterial = ""
cbovendors = ""
Me.TxtPurchaseDateFrom = ""
Me.TxtPurchaseDateTo = ""
strCriteria = "(TblPurchases.[Date of Purchase]>=#" & Me.TxtPurchaseDateFrom & "# And (TblPurchases.[Date of Purchase])<=#" & Me.TxtPurchaseDateTo & "#)"
task = "select * From TblPurchases Where [Primary Key] is null"
DoCmd.ApplyFilter task
Me.TxtTotal = FindRecordCount(task)


End Sub


Private Sub CmdShowAll_Click()
Dim task As String

Me.TxtPurchaseDateFrom = ""
Me.TxtPurchaseDateTo = ""
task = "select * from TblPurchases order by [Date of Purchase] "
Me.RecordSource = task
Me.TxtTotal = FindRecordCount(task)

End Sub

I have only one module that runs to give recordcount and when I run show all records, which all works fine, selection of vendors and the filtering of purchases by date range.

Try clicking CmsShowAll first, as at least that is the only place a recordsource is set that I can see.
Then also try your method of filtering with


and see what that does.

I don't see the point of selecting every record and then filtering for certain types.
Why not use the criteria in the recordsource each time.?

I still cannot see how that all works correctly?

Gasman said:

Try clicking CmsShowAll first, as at least that is the only place a recordsource is set that I can see.
Then also try your method of filtering with

Code:Copy to clipboard

DoCmd.ApplyFilter task

and see what that does.

I don't see the point of selecting every record and then filtering for certain types.
Why not use the criteria in the recordsource each time.?

I still cannot see how that all works correctly?

Click to expand...

DoCmd.Apply/filter task used here triggers a dialog box asking for a parameter value which is unnecessary since it's set already by the cbomaterial control. the reason for show all records is to be able to scan the database to see what records(vendors,dates, ) at a glance you maybe interested in seeing you can then further drill down by material or vendor and I intend to add category as another option to drill down into. It;s basically a Materials Purchased for a house.If you have made numerous purchases of material during the build, you can search for which vendors you used to supply the materials and what amounts and cost, you can also see what was bought from which vendor ,when and cost at that time. A precise description of the item is then known in case you need or want to order more and know who had the better price. I made the same app in Excel that does everything that I need but wanted to make it more robust by making it in access.

Wait for an expert.

One last thought.
This is how I would do it.

As you are changing the order all the time, you need to refresh the recordsource.
In that case I would be coding
as you have done for cmdSearch
Then there is no need to filter, you have filtered via the WHERE clause.

If you were bring in the data in the same order all the time for the form, then I would go the filter route, but I do not think you can do that due to the way you are bringing in the data.

HTH

One point to consider is that syntactically, you are showing us evidence that both a form and a table are named TblPurchases:

Form-syntax: Like [Forms]![TblPurchases].[CboMaterial] & "*"

Table-syntax: task2 = "select * from TblPurchases order by ([ Material])"

It is entirely possible that Access doesn't like this even though the two objects can exist (since it didn't throw an immediate error). But references to the objects might very well get confused. Consider changing the name for one of the two objects.

the forms datasheet isn't returning the records

Click to expand...

So what is the .Recordsource on the form in question? I don't care about the query because you said it works. But if the form DOESN'T work then let's focus there. Just remember, even in datasheet view, a form has to have a valid .Recordsource in order to be able to show records, and you have not told us that tidbit yet.

I'm a little bit leery of the .ApplyFilter syntax you used because I can't make sense out it. I think you are missing a comma somewhere because you are supplying SQL as a filter but from my reading, the WHERE-clause (fragment) is supposed to be the 2nd argument of .ApplyFilter. The 1st argument is a filter name and you have that defined as a complex string, so I'm surprised it didn't blow up in your face with a run-time error. Or do you have notifications disabled? If so, bad move during debugging.

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.applyfilter

Please post lengthy code between CODE tags to retain indentation and readability.

I agree the ApplyFilter FilterName argument should be a query name, not SQL statement. Try the WhereCondition argument.
DoCmd.ApplyFilter , criteria

However, I have never used ApplyFilter. I use Filter and FilterOn properties.

The_Doc_Man said:

One point to consider is that syntactically, you are showing us evidence that both a form and a table are named TblPurchases:

Form-syntax: Like [Forms]![TblPurchases].[CboMaterial] & "*"

Table-syntax: task2 = "select * from TblPurchases order by ([ Material])"

It is entirely possible that Access doesn't like this even though the two objects can exist (since it didn't throw an immediate error). But references to the objects might very well get confused. Consider changing the name for one of the two objects.



So what is the .Recordsource on the form in question? I don't care about the query because you said it works. But if the form DOESN'T work then let's focus there. Just remember, even in datasheet view, a form has to have a valid .Recordsource in order to be able to show records, and you have not told us that tidbit yet.

I'm a little bit leery of the .ApplyFilter syntax you used because I can't make sense out it. I think you are missing a comma somewhere because you are supplying SQL as a filter but from my reading, the WHERE-clause (fragment) is supposed to be the 2nd argument of .ApplyFilter. The 1st argument is a filter name and you have that defined as a complex string, so I'm surprised it didn't blow up in your face with a run-time error. Or do you have notifications disabled? If so, bad move during debugging.

Click to expand...

First Let me say a Hugh THANK YOU for taking the time to look at my issue and messy code attempts. I am not well versed in Access and struggle to make my Excel app, which works perfectly into a access app. The form .Recordsource is currently TblPurchases, but I have tried QueryMaterial which first prompts for a value with the same results I am experiencing. Tried other ones without success. As for the ApplyFilter syntax I was just following my source for the usage of this form formt which was from a YouTube video:

How to Create Search by Date Range: MS Access by creator: Nana Sara

I'm using his method of the date range filtering and trying to expand on it to include other portions of the records to search. In it he uses the .ApplyFilter syntax with just the filter name. I have attached my forms to see if you my be able to spot the issue if you could be so kind, then point me in the direction I should follow.

Again Thanks for your help.

I have a query that includes wild cards and it runs fine when it has to. /the only problem I'm having is that the forms datasheet isn't returning the records.

Click to expand...

It seems to me that in general if a query is showing the records you want then all you need for a form to also show those same records is to set the form's RecordSource property to be that query and no other code or filters etc on the form would even be needed...

Bullschmidt said:

It seems to me that in general if a query is showing the records you want then all you need for a form to also show those same records is to set the form's RecordSource property to be that query and no other code or filters etc on the form would even be needed...

Click to expand...

I would agree as it seems logical,however there are other queries in the program I'm trying to create. First there is the filter by date ranges(working), the filter by Vendors(working) and in conjunction with each other. my plan is to introduce one more filter("Category") and have them all work in conjunction with each other. Ex. If I filter a material say "Lumber", I then can filter a specific vendor(there will be more than one). then I can filter the rang of dates from that vendor for that item. I am thinking that subqueries is the solution for that ,but I need to get this one(the materials) working first. I tried the "queryMaterial" as the .recordsource,but for some reason as before the query works but the form's datasheet is blank. I uploaded the form with the records for a example to see what I'm trying to achieve.

WayneSeymour said:

I would agree as it seems logical,however there are other queries in the program I'm trying to create. First there is the filter by date ranges(working), the filter by Vendors(working) and in conjunction with each other. my plan is to introduce one more filter("Category") and have them all work in conjunction with each other. Ex. If I filter a material say "Lumber", I then can filter a specific vendor(there will be more than one). then I can filter the rang of dates from that vendor for that item. I am thinking that subqueries is the solution for that ,but I need to get this one(the materials) working first. I tried the "queryMaterial" as the .recordsource,but for some reason as before the query works but the form's datasheet is blank. I uploaded the form with the records for a example to see what I'm trying to achieve.

Click to expand...

To give you a further example of whats working aside from the material filter I filter for a vendor "Citihardware" I receive 127 records(correct and working) I then filter for a date range of 12/16/2016 and receive 12 records again fine. From this point I want to filter the material from those 12 records(not working) I filter "tools" What I should get is 3 records but what I get is the query datasheet works showing 64 records, on the forms datasheet I get the correct record number from the record number textbox,but no records shown on the datasheet. why does the date range and vendor filters work well in conjunction with each other and the material fail in showing the records as well as incorrect record count?

WayneSeymour said:

my plan is to introduce one more filter("Category") and have them all work in conjunction with each other. Ex. If I filter a material say "Lumber", I then can filter a specific vendor(there will be more than one). then I can filter the rang of dates from that vendor for that item. I am thinking that subqueries is the solution for that...

Click to expand...

Have a look at my two example databases Multiple Group and Filter

These show how you can build a SQL string for one or many different filters as in your description. There is no need for subqueries.

I've never used ApplyFilter but found the following in Access help

To run a macro or event procedure when this event occurs, set the OnApplyFilter property to the name of the macro or to [Event Procedure].

You can use the ApplyFilter event to:

Make sure the filter that is being applied is correct. For example, you may want to be sure that any filter applied to an Orders form includes criteria restricting the OrderDate field. To do this, check the form's Filter or ServerFilter property value to make sure this criteria is included in the WHERE clause expression.
Change the display of the form before the filter is applied. For example, when you apply a certain filter, you may want to disable or hide some fields that aren't appropriate for the records displayed by this filter.
Undo or change actions you took when the Filter event occurred. For example, you can disable or hide some controls on the form when the user is creating the filter, because you don't want these controls to be included in the filter criteria. You can then enable or show these controls after the filter is applied.
The actions in the ApplyFilter macro or event procedure occur before the filter is applied or removed; or after the Advanced Filter/Sort, Filter By Form, or Server Filter By Form window is closed, but before the form is redisplayed. The criteria you've entered in the newly created filter are available to the ApplyFilter macro or event procedure as the setting of the Filter or ServerFilter property.

Note
The ApplyFilter event doesn't occur when the user does one of the following:

Applies or removes a filter by using the ApplyFilter, OpenReport, or ShowAllRecords actions in a macro, or their corresponding methods of the DoCmd object in Visual Basic.
Uses the Close action or the Close method of the DoCmd object to close the Advanced Filter/Sort, Filter By Form, or Server Filter By Form window
Sets the Filter or ServerFilter property or FilterOn or ServerFilterByForm property in a macro or Visual Basic (although you can set these properties in an ApplyFilter macro or event procedure).

Click to expand...

Personally I would scrap this ApplyFilter code and either use a SQL string to filter data (as in my examples) or FilterOn/FilterOnBy properties as already suggested.

In general, when you start stacking criteria like this, you do better to try a dynamic .RecordSource approach. You define the SELECT and FROM clauses normally in a string but terminate your string there AND you don't close it with a semicolon. Next, define any ORDER BY statements in a third string that has a semicolon at the end. (No, "third" is not a miscount...)

Then when you want to add criteria, you build a second string that starts with the word "WHERE" and you tack on the things you are filtering. End each "tack on" with " AND " and wait until you have added everything you wanted to add as a filter. Then using the LEFT function, remove the leftmost 5 characters (i.e. the last space-AND-space sequence.)

In these examples, I am presuming that you get your selection criteria from the form you are on. If not, use the actual data source you were intending to use. But this should give you the idea.


Watch out because a couple of those have apostrophes and double-quote marks adjacent to each other, necessary for dealing with text string substitution in the way I am showing here.
Now build the final string as


That leading "IF" removes the empty where clause if you didn't add any criteria to your filter - i.e. an empty filter. Now set the form's .RecordSource to FinalSQL. When you update a form's .RecordSource it does an automatic Me.Requery. And that should give you your filtered recordset.

EDIT: I see that Colin also suggested a dynamic SQL string. Obviously, our posts crossed each other.