Full Version: Combo Box issue
UtterAccess Forums > Microsoft® Access > Access Forms
szickgraf
I have a report generator form that generates SS reports. There are a few combo boxes for particulars such as dates, etc; well, the combo box for an "area" is populated by a query (this works fine), but when I try to generate a report, the file (save as) box appears and the "center" I've chosen disappears. What can I do about this? Thanks.
Jack Cowley
Welcome to Utter Access Forums!
Are you using the data in the multiple combo boxes as criteria to filter a query that is the Record Source for you Report? If the form with the combo boxes is as I described then I would suggest you look at this article and consider using it as your 'search' form.
If this is not what you are after then more details would be helpful so that someone here can assist you.
Jack
szickgraf
The original developer used the choices within some of the combo boxes as 'criteria' in queries for sub reports, such as: [Forms]![Frm_Review_Status]![center]. This report generator form has a combo box for 'center', which was never used, and this report has always included all centers (locations), so the box was always null. Powers that be now request to have this same report broken down by individual or all 'centers'. I've amended queries and reports to render accordingly.
When I run the report in design mode (no file 'save as:' box appears) it works fine, but when I do it as a regular user would, the file 'save as:' box appears and the chosen info in the 'center' combo box vanishes (lost focus?), thus giving me a report for all 'centers' instead of the one I specified.
Anyway, I've been searching through the properties of the combo box to find a solution to this problem, unsuccessfully, and I'm not sure what to do next. Thanks for helping out! and I hope I'm not too confusing.
Scott
Jack Cowley
At this point there is not much that I can suggest. It will be, I think, a matter of going though the various parts of the form with the combo boxes and the related query to find the problem. You might want to delete the combo box all together and and see what happens. Be sure that the control names in the criteria line of the query are spelt correctly as a misspelled work can be the downfall of the process.
Good luck!
Jack
szickgraf
I'll try. thanks again.
dashiellx2000
Jack:
Is it possible to base a report on a dynamic query? If so, How?
Thanks.
Jack Cowley
Sure. Just use the Dynamic_Query as the Record Source for the Report. If you have a QBF form then you can have a command button that creates the query then in a line of code after the Dynamic_Query has been created use code like this to open or print the Report:
oCmd.OpenReport "ReportNameHere"
hth,
Jack
dashiellx2000
Thanks Jack. I really hope this is what I've been looking for. It seems like it is.
Jack Cowley
I have a suspicion that this is exactly what you have been looking for... Good luck!!
ack
dashiellx2000
Well, of course, this would be too easy. I'm getting a Run Time Error '2001': You canceled the previous operation.
Here's the code I typed, I can't seem to find where I made my mistake:
<
This line : DoCmd.OpenQuery "Dynamic_Query" is highlighted in the debug window.
If anyone could please point out where I would be greatful.
Thanks.
Jack Cowley
Do you have you Microsoft DAO 3.6 Object Library checked in Reference (I assume you do)? Add the DAO reference in your first line of code just to be safe:
im db as DAO.Database
Is the code creating the Dynamic_Query? If so then the code is working.
If you want to open your report then use the code I suggested earlier in place of the code to open the query.
hth,
Jack
dashiellx2000
I'll check the DAO issue. Of course I'm using A97, so I hope this isn't an issue. I was trying to test it by seeing if it would make the query first, before I bound my table to it.
Thanks.
Jack Cowley
If you are using Access97 then you do not need to check your references as your code should be OK as it stands. I would suggest you put a stop in your code and step through it as it executes to be sure that you are getting the values you expect in your Where clause....
ack
dashiellx2000
I had to restart my computer and then I looked at the example that you can download from the Microsoft website again and there were more code lines then in the print out. I added:
If ObjectExists("Queries", "Dynamic_Query") = True Then
MyDatabase.QueryDefs.Delete "Dynamic_Query"
MyDatabase.QueryDefs.Refresh
End If
Now I'm getting an error saying ObjectExisits is not a Function....
If I take this out, it says that the Object already exists. I looked at the Queries and low and behold it was there, so I guess my computer had gas or something and my restarting helped. Anyway, I need to figure out how to get rid of the query to create another one now.
Thanks.
Jack Cowley
This line of code before your Where clauses should delete the current Dynamic_Query:
On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0
hth,
Jack
dashiellx2000
Thank's Jack. Took out the If part and it works.
Thanks.
dashiellx2000
Just one more question. Is there a way to referance another table. i.e. We'd like each tech's manager name to appear on the reports as well. In fact, some reports are grouped by manager. The manager's ID is not on the tblwriteoffdata, it is on tblManagers and as a one to many relationship to tblTechs.
Thanks.
Jack Cowley
Without knowing your structure I would suggest using DLookup() or if the table you are using in your QBF form is joined to the Manager table that base your QBF form on a query using your table and the manager table.
th,
Jack
dashiellx2000
Here's the SQL from the Query I was using to run this report before, but I'm trying to make the database more dynamic and give people more options for running reports:
SELECT tblTechs.TechName, tblWriteoffData.Amount, tblWriteoffCDMs.CDM, tblManagers.Manager
FROM tblWriteoffCDMs INNER JOIN (tblManagers INNER JOIN (tblTechs INNER JOIN tblWriteoffData ON tblTechs.ClockNum = tblWriteoffData.TechID) ON tblManagers.ManagerID = tblTechs.ManagerID) ON tblWriteoffCDMs.CDMID = tblWriteoffData.CDMID;
Now the Closesest I can get is:
SELECT *
FROM tblWriteoffData
WHERE (((tblWriteoffData.CDMID)=1) AND ((tblWriteoffData.Completed)=-1));
I was hoping to be able to bound a number of different report to the Dynamic Query created by the form. I've successfully hard code some of the criteria for static differences that are represented on the tblWriteoffData form (i.e. if it was rejected or completed, etc...)
I'm going to struggle with this, but any suggestions you may have would be greatly appreciated.
Thanks.
Jack Cowley
Base your Dynamic_Query on the query...
SELECT *
FROM NameOfYourQueryHere
WHERE (((tblWriteoffData.CDMID)=1) AND ((tblWriteoffData.Completed)=-1));
Now your Dynamic_Query will show all the fields from your query rather than just the record from the table.
Jack
dashiellx2000
Thanks Jack!!!
I have to do some more test, but your suspicion was correct: this is exactly what I was looking for!!!
Thanks again!!!
Jack Cowley
William -
You are welcome and continued success with the project...
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.