How to display query results in a subform

[dougmeece]

Good day everyone,

I have a database with 2 main forms. The first form is used to add records to the database and contains a command button that opens the 2nd form for records searching.

On the second form I have tow combo boxes that I would like to search from. Currently, I just have the search button run a query which opens in a separate screen. This is the same for both combo boxes (they are independent of each other). I would like to have the results open on the form in a subform but before I can do that I need to be able to view only certain results. What I want is for the command button to run a query that only displays results that match the text in the combo box. When I try to limit that results to that I get no results returned. If I just run the query without any qualifying where clause I get everything in the query just fine.

After I get that fixed I want to display the results in a subform on my search form. Is any of this possible?

Any help would be greatly appreciated.

Thank you,
Doug

[NeoPa]

See if this tutorial (Example Filtering on a Form) helps with the concepts Doug. It should.

[dougmeece]

Thank you. I will check it out and let you know.

See if this tutorial (Example Filtering on a Form) helps with the concepts Doug. It should.

[dougmeece]

Hello NeoPa,

I looked at your code and tried to make it work for my database but I was not able to do so. I am a long time removed from any code work (and that wasPerl) so I am afraid I probably did not follow it very well.

I would be happy to send you my database if you wnated to look at it. I hope it is not too screwed up right now.

Thanks,
Doug

See if this tutorial (Example Filtering on a Form) helps with the concepts Doug. It should.

[NeoPa]

A kind offer Doug, but I'll take a pass on that one ;)

I will try to help if you can provide a specific question ("My thing doesn't work - please fix it" isn't).

I'm happy to help you along, educating where possible, but if the questions are not specific enough it's just doing things for you which doesn't help you much in the long run, and takes up much more time and effort on my part (which isn't sensible use of my time).

To make it clear, I don't want to be unhelpful, but I need something more precise to work with.

[dougmeece]

I understand. I was hoping you may look at it and advise me where I went wrong so I could fix it not fix it yourself. Sorry if that was unclear.

OK, so here is what I have...

I have a database that is being used to chronicle writings that have been sent to publishers and whether they have been accepted or not.

The first form is called CreatedWorks and is used to update an append query. This one works fine and updates as expected.

The second form is called Records_Search and is used for exactly what the name suggests.

I have two combo boxes on the second form to use as independent criteria to search on.

Combo box one is called CboTitleSearch and should be searching by the title selected in the combo box compared to the append query used for the search for all records of the specific title submitted to a publisher.

Combo box two is called CboPublisherSearch and should be searching for all titles submitted to a specific publisher from the same append query (which should match the publisher name in the combo box).

Problem with the search:
I have tried setting the criteria in the query (Records_Search_Query) to only include the records where [Forms]![Records_Search]![Title] are equal to the records in the append query(Created_Submitted.Title). When I run this query it runs but returns no data. If I take the Where clause out of the equation it runs but obviously returns everything in the append query.

I have tried setting this up in the Records_Search_Query itself without success. I have also tried adding an If statement (If [forms]![Records_Search]![Title] = (Created_Submitted.Title) Then DoCmd.OpenQuery stDocName, acNormal, acEdit) to the code directly which also has not helped. If there were no matching records then I was trying to get a msgbox to display that.

How can I limit the data returned to match only what is displayed in the combo box when the search records command button is pressed?

I am sure I am doing something entirely idiotic here but like I said, I am far removed from coding and have lost practically all of it. I know what I want to do but I don't know how to get there. I tried to use the example you created but I just could not get it to work. I was able to follow what you were doing but I could not apply it to my work. I'm sorry if you get frustrated trying to help me but please know that I come to this forum only after hours and hours and days of trying to figure things out myself using all resources available (help, manuals and Internet searches).

Thanks again for any assistance you can provide.

A kind offer Doug, but I'll take a pass on that one ;)

I will try to help if you can provide a specific question ("My thing doesn't work - please fix it" isn't).

I'm happy to help you along, educating where possible, but if the questions are not specific enough it's just doing things for you which doesn't help you much in the long run, and takes up much more time and effort on my part (which isn't sensible use of my time).

To make it clear, I don't want to be unhelpful, but I need something more precise to work with.

[NeoPa]

OK Doug. You've got my attention.

I need one thing explained though before I can make much sense of all this :
You refer to forms based on, and updates applied to, an "Append Query" (Created_Submitted?). These are impossible, so I suspect we have a terminology problem somewhere. Append queries cannot be used to return recordsets (unlike tables and SELECT queries for instance) so I'm wondering what you're meaning here.

[dougmeece]

You're right, I misspoke. The append query updates the Created_Submitted query which is the one I am using. I meant to say appended query.

OK Doug. You've got my attention.

I need one thing explained though before I can make much sense of all this :
You refer to forms based on, and updates applied to, an "Append Query" (Created_Submitted?). These are impossible, so I suspect we have a terminology problem somewhere. Append queries cannot be used to return recordsets (unlike tables and SELECT queries for instance) so I'm wondering what you're meaning here.

[dougmeece]

I believe the problem is in my query, Records_Search_Query, but I am not 100% sure. I call that query when I click the search button, however, I have been unable to get the desired expression to work in the query.

You're right, I misspoke. The append query updates the Created_Submitted query which is the one I am using. I meant to say appended query.

[dougmeece]

Well guess what. I just stumbled into a fix. It may not be the best way but it seems to work now.

Here is the SQL Statement in my query. I changed from trhe Created_Submitted query to the actual table and added the WHERE clause again. I don't know why it worked this time but I am not complaining.

SELECT Created_Submitted.Title, Created_Submitted.[Year Created], Created_Submitted.Submitted, Created_Submitted.[Submitted To], Created_Submitted.Website, Created_Submitted.Type, Created_Submitted.Accepted, Created_Submitted.[Date Submitted]
FROM Created_Submitted
WHERE ((([Forms]![Records_Search]![TxtTitleSearch])=([Created_Submitted].[Title])))
GROUP BY Created_Submitted.Title, Created_Submitted.[Year Created], Created_Submitted.Submitted, Created_Submitted.[Submitted To], Created_Submitted.Website, Created_Submitted.Type, Created_Submitted.Accepted, Created_Submitted.[Date Submitted];
 

OK Doug. You've got my attention.

I need one thing explained though before I can make much sense of all this :
You refer to forms based on, and updates applied to, an "Append Query" (Created_Submitted?). These are impossible, so I suspect we have a terminology problem somewhere. Append queries cannot be used to return recordsets (unlike tables and SELECT queries for instance) so I'm wondering what you're meaning here.

[NeoPa]

You're right, I misspoke. The append query updates the Created_Submitted query which is the one I am using. I meant to say appended query.

There's still a little bit of confusion here.

Queries (any queries) don't contain data. They can select and show data from an underlying record source, but in effect, they are similar to filters.

It is of course possible that the append query can update (add records to) the underlying table, such that when the SELECT query (Created_Submitted) is run those records are now included.

PS. I will try to catch up with your other posts too - time allowing.

[NeoPa]

... I changed from trhe Created_Submitted query to the actual table and ...

That's great news Doug :)
I'll just post a slightly reorganised version of the SQL which will hopefully make what's going on a little easier to read and understand. What I'm not clear on at the moment is what type of item [Created_Submitted] is? From your explanation I'm almost sure it's the table.

SELECT [Title],
       [Year Created],
       [Submitted],
       [Submitted To],
       [Website],
       [Type],
       [Accepted],
       [Date Submitted]
FROM Created_Submitted
WHERE [Title]=[Forms]![Records_Search].[TxtTitleSearch]
GROUP BY [Title],
         [Year Created],
         [Submitted],
         [Submitted To],
         [Website],
         [Type],
         [Accepted],
         [Date Submitted]

[NeoPa]

Now I can see the SQL more clearly, it's almost certain that you can lose the whole of the GROUP BY clause in your SQL. Unless you have some extraordinary situation where you have multiple records which duplicate ALL the specified fields exactly, it is superfluous and may slow down performance (on top of making the SQL more complicated to digest).

See if this modified version works as well as your previously working one.

SELECT [Title],
       [Year Created],
       [Submitted],
       [Submitted To],
       [Website],
       [Type],
       [Accepted],
       [Date Submitted]
FROM Created_Submitted
WHERE [Title]=[Forms]![Records_Search].[TxtTitleSearch]

[dougmeece]

Yes, that worked very nicely. I am now going to attempt to have it display in either a list box or subform within the Records_Search form as opposed to opening a separate screen with the results.

I will try to do it without having to ask for more help. Hopefully I won't need to come back for more assistance but don't be shocked if I do. Of course, if you know of an easy way to do it off the top of your head I am always willing to take advice and try it.

Thanks for all of your help.

Now I can see the SQL more clearly, it's almost certain that you can lose the whole of the GROUP BY clause in your SQL. Unless you have some extraordinary situation where you have multiple records which duplicate ALL the specified fields exactly, it is superfluous and may slow down performance (on top of making the SQL more complicated to digest).

See if this modified version works as well as your previously working one.

SELECT [Title],
       [Year Created],
       [Submitted],
       [Submitted To],
       [Website],
       [Type],
       [Accepted],
       [Date Submitted]
FROM Created_Submitted
WHERE [Title]=[Forms]![Records_Search]![TxtTitleSearch]

[NeoPa]

To set up a ListBox with the data simply set the RecordSource of the ListBox to the SQL required.

[dougmeece]

Got it with a subform by entering the code below to the CmdSearch_OnClick Event.

Forms![Records_Search]![subfrmTitleSearch].Requery

Thanks for everything,
Doug

To set up a ListBox with the data simply set the RecordSource of the ListBox to the SQL required.

[NeoPa]

Good for you Doug. Nice going :)