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.
Can you post the code you use to run the query?
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
Here is the entire code for the button:
Please Login or Register to view this content.
The following line of code opens your query:
If you don't want the query to open, simply remove that line of code.Please Login or Register to view this content.
If you want to run the query without opening it, use:
Please Login or Register to view this content.
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:
Query 2 - to search for an entry in the database - this query is supposed to be usable with both partial AND full search criteria.Please Login or Register to view this content.
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.Please Login or Register to view this content.
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.
What was your query accomplishing in your original code? Since it was just a SELECT query, all it did was open the query. If you didn't want it to be opened, just remove that line of code.
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:
and as soon as I tried to save it I get an error message window popup that says:Please Login or Register to view this content.
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?
What was the query doing??? You're code opened a query, but you say you didn't want the query opened. Just remove the line as shown below and it won't open the query.
Please Login or Register to view this content.
Yes these things can be done using VBA coding.Originally Posted by vampyr07au
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.
but can this all be done using just one form for results to be displayed or do I need seperate forms ?
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.
Last edited by vampyr07au; 10-18-2011 at 06:12 PM.
I am attaching copies of both the current database ( october 5th in name) and a copy of the old database that I have been referring to (titled complete and working) for you to hvae a look at so you can see what I have been talking about in my posts.
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:
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 :Please Login or Register to view this content..Please Login or Register to view this content.
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
Last edited by vampyr07au; 10-20-2011 at 12:57 AM. Reason: updated details.
I don't know if I have done this right or not.
I changed the vba code on the view all button to this:Please Login or Register to view this content.
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.
Last edited by vampyr07au; 10-20-2011 at 05:08 AM. Reason: further details.
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.
the delete line first followed by the execute query and then the open form command ?
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:
and the Query code looks like this:Please Login or Register to view this content.
Please Login or Register to view this content.
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.
Last edited by vampyr07au; 10-21-2011 at 06:00 AM. Reason: New information
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.
Last edited by vampyr07au; 10-22-2011 at 07:27 AM.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks