Thread: access query displaying in the background

What line is highlighted when it says "Invalid SQL statement..."?

It can be done in a single form.

Originally Posted by davegugg

What line is highlighted when it says "Invalid SQL statement..."?

It can be done in a single form.

It doesn't highlight any line. it just pops up a window with the message in it when I click save.

This happens AFTER removing the select line and then clicking save.

You need to bind your Results form to a table, then fill the table with the results of a query each time you open it. I can't seem to save your database, so I'll have to give you step by step instructions.

1) Create a new table. I'll call mine tbl_QryResults. The table should have all the same fields as your Movie Database List table.
2) In the properties of the Results form, change the Record Source to tbl_QryResults. Now the form will show all the records in the tbl_QryResults table.
3) Next we need to get the latest/chosen records into the tbl_QryResults table. This will need two steps.
a) Clear the old results out of the tbl_QryResults table. You can do this with a single line of VBA code:

DoCmd.RunSQL "DELETE * FROM tbl_QryResults

b) Fill the tbl_QryResults with data you want. You can do this with a query you set up. You'll want it to be an append query. For "View All Movies", it would be something like :

INSERT INTO tbl_QryResults SELECT * FROM [Movie Database List]

.

That should get you going. If you need more help on append querys, Google is a great tool.

I cannot find a record source option in the properties of my form ... is the control source the same thing ?

Never Mind - I found it lol

I don't know if I have done this right or not.

I changed the vba code on the view all button to this:

Private Sub View_All_Movies_Click()
On Error GoTo Err_View_All_Movies_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    
        stDocName = "Results"
       DoCmd.OpenForm stDocName, , , stLinkCriteria
    CurrentDb.QueryDefs("search").Execute
    

Exit_View_All_Movies_Click:
    Exit Sub

Err_View_All_Movies_Click:
    MsgBox Err.Description
    Resume Exit_View_All_Movies_Click
    
End Sub

And now when I try and run it I get a popup box with the following error:

Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 3 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.


With the actual append query - I just changed the original query from what it was to the append query that you posted for me ... with adjustments for the table name of course.

You need to run the query before opening the form.
Again, don't forget you'll want to delete out the previous data from the qry_Results table.

Correct! .

It still wasn't working properly.
It ran and asked me to confirm that I wanted to delete the entries and then it displayed the QueryResults form however said form was empty even though the actual table holds 3 entries in it.

So I thought maybe there was something wrong with the queries so I have deleted the queries and I have made them all again using the design wizard in access ... the problem is that the wizard is making them all as SELECT queries. I do not know how to change this ....

I have been looking for help via google as well but I don't really understand all of what I see.


Sofor example the View All Movies button :
button code looks like this:

Private Sub View_All_Movies_Click()
On Error GoTo Err_View_All_Movies_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    
        stDocName = "Results"
        DoCmd.RunSQL "DELETE * FROM QueryResults"
        CurrentDb.QueryDefs("view all movies").Execute
       DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    

Exit_View_All_Movies_Click:
    Exit Sub

Err_View_All_Movies_Click:
    MsgBox Err.Description
    Resume Exit_View_All_Movies_Click
    
End Sub

and the Query code looks like this:

SELECT [Movie Database List].Volume, [Movie Database List].[Movie Title], [Movie Database List].Rating, [Movie Database List].[Media Type], [Movie Database List].Collection
FROM [Movie Database List];

I found an article that helpedme to understand the append query and to create the queries that I wanted.

I now have 2 queries - the first one is to find the duplicates however it is still a SELECT query and will not execute.

The second one is an APPEND query to show all results and when I run it it deletes the data that was in the queryreults table but then it displays a BLANK reults form instead of bringing across the rata from the table as it should.

have uploaded a copy of the latest database for you to have a look at.

When you open the form are there records in the table that the form is bound to?

I have just opened the database and opened the form named queryresults which is bound to the movie list table and there are records in both the queryresults table and the movie list table.
however the end result of the query is a blank results form instead of one with the data in it.

Ok, you correctly bound your form to the table. Now you need to set up your controls on the form to show whichever fields from the tables apply to them. Go to design mode with the Results form. Click on one of the text boxes that you set up. Bring up the text box's properties. There is one called Control Source. If you click it, it will give you a list of fields from the table that is bound to that form. Select the field you want to bound to that textbox. Repeat for each textbox.

I'd strongly recommend doing some Access tutorials as these will give you a much better understanding of how to use tables, forms, and querys.