Thread: access query displaying in the background

I have an access database and some designed user forms for use with the database.

two of the form buttons run queries when clicked.

the query window is popping up in the background behind the userforms as it runs and i do not want this to happen however I do not know how to stop it from happening.
I am talking about the query window in standard access table view (if that makes sense)

Can someone help me please.

Here is the entire code for the button:

Private Sub Find_Entry_Click()
On Error GoTo Err_Find_Entry_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    DoCmd.OpenQuery "search"

    stDocName = "Results"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Find_Entry_Click:
    Exit Sub

Err_Find_Entry_Click:
    MsgBox Err.Description
    Resume Exit_Find_Entry_Click
    
End Sub

cheers,

thanks for that

hey dave,

I changed the code as you suggested however I now have another problem.

When I click on the button that is supposed to execute the query and then open the results form I am getting an error message as follows:
"Cannot execute a select query"

Here is the 2 sets of query code:

Query 1 - to find all duplicate entries in the database and display them:

SELECT [Movie Database List].[Movie Title]
FROM [Movie Database List]
WHERE ((([Movie Database List].[Movie Title]) In (SELECT [Movie Title] FROM [Movie Database List] As Tmp GROUP BY [Movie Title] HAVING Count (*)>1)));

Query 2 - to search for an entry in the database - this query is supposed to be usable with both partial AND full search criteria.

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]
WHERE ((([Movie Database List].Volume) Like "*" & Forms!search.volume & "*") And (([Movie Database List].[Movie Title]) Like "*" & Forms!search.title & "*") And (([Movie Database List].Rating) Like "*" & Forms!search.rating & "*") And (([Movie Database List].[Media Type]) Like "*" & Forms!search.[media type] & "*") And (([Movie Database List].Collection) Like "*" & Forms!search.collection & "*"));

Looking at the SQL code view that I can now see here I assume this has something to do with the "SELECT" at the beginning of the queries however I do not know what to do with this now.

These sets of query code came from the original project that I was doing that had some issues and I have now began to re-design it.
The main design is still basically the same so the coding should still work, however I did not copy the code from within an SQL code view, I copied it from the design view window in office 2003 where u can see the check box and the criteria field etc.

in the original code it was accomplishing the exact same thing as it is now supposed to do.

OK I will try removing the select line and see if it works then.

OK

I removed the select line from the code so it now looks like this:

FROM [Movie Database List]
WHERE ((([Movie Database List].Volume) Like "*" & Forms!search.volume & "*") And (([Movie Database List].[Movie Title]) Like "*" & Forms!search.title & "*") And (([Movie Database List].Rating) Like "*" & Forms!search.rating & "*") And (([Movie Database List].[Media Type]) Like "*" & Forms!search.[media type] & "*") And (([Movie Database List].Collection) Like "*" & Forms!search.collection & "*"));

and as soon as I tried to save it I get an error message window popup that says:
Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'.

I have no idea what is going on. I never had these problems wit the code from the original project at all but the original project was giving me out of memory erros and things like that while trying to open forms and perform actions AFTER I had added more elements to the project.

I have a further query as well.

There are 3 different searches possible within my program.

1. Entering search criteria to search for individual entries.

2. Clicking a button that will show only duplicated entries within the database.

3. Clicking a button that then displays ALL the entries in the database.

When clicking on these buttons it opens a form that displays entries in continuous view down the page.

My query is whether or not I can have the one form for all 3 different search types and also whether or not that form can be made lockable and unlockable and also whether or not edit and delete buttons can be added to this form?
I need the database to be non-editable unless specified otherwise but also need to be able to edit or delete records at various times.

anyone with any ideas at all about this problem?

Dave,

I already removed the open query line and changed it to the execute line as per your previous instructions.

After I did this the project then told me I cannot execute a "select" query.

so then - as suggested by you I removed the "select" line and then I get the following error as previously stated:
"and as soon as I tried to save it I get an error message window popup that says:
Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'. "

I have no idea what this problem is or whats causing it.

Originally Posted by davegugg

Yes these things can be done using VBA coding.

but can this all be done using just one form for results to be displayed or do I need seperate forms ?