Run a query without opening it Results

I am creating Macros which first run queries and then run the report based on those queries. The problem is that to run the Query it seems that you have to first open it within the macro, run it and then close it after which you run the respective report.
This causes the screen to flash with the opening and closing of queries. Is there any way to run a query without opening it??

The reason that the query has to be run before the report is because there is new data input every day into the data table.

Hi

I have a ListBox on my form that takes its data from a Query. I have been...

opening the query
executing a requery
opening the main form
recalc on the main form

How do I execute a requery and refresh my ListBox WITHOUT opening the query ?

Thanks

Al

May be this has been solved in this forum before but I can't find it. Here it goes.

I have a code I wrote to open a query and use its values to update a table. The query gets its Date range from the StartDate and EndDate controls on the form. The query runs fine if i open it by double-cliking on it while the form is open. But I get errors when I tried opening it from the code eventhough the form is open. I get the following message: TOO FEW PARAMETERS. EXPECTED 2. The query retuns no records. It looks like the code runs the query without picking the values from the date controls on the form. In the query criteria I have the folowing function:

Between [forms]![frmRevReports]![txtBegin] And [forms]![frmRevReports]![txtEnd]

However, when I removed this BETWEEN condition from the criteria in the query, the code runs just fine. That is, when it makes no reference to the date controls on the form.

What do I have to add to get the code to reference the dates on the form and supply them to the criteria of the query, "QRY_REVENUE_MINUTES_OUT_1"

Here is my code. Please help:

	Code:
	    Dim rstOutGoingMin As Recordset
    Dim rstTotalOutMin As Recordset
    Dim rstBalanceConsulting As Recordset
    Dim mMinutes As Double
    Dim mOutgoingCallsCount As Double
    Dim currNumber As String

    Set db = CurrentDb()
    db.QueryTimeout = 0
    
    Set rstOutGoingMin = db.OpenRecordset("QRY_REVENUE_MINUTES_OUT_1", dbOpenForwardOnly)
    With rstOutGoingMin
        Do While Not .EOF
            currNumber = !ANumber
            mOutgoingCallsCount = !OutgoingCallsCount
                Set rstTotalOutMin = db.OpenRecordset("REVENUE_REPORTS_ACCOUNTS", dbOpenTable)
                With rstTotalOutMin
                        .Index = "PrimaryKey"
                        .Seek "=", currNumber
                        If Not .NoMatch Then
                            .Edit
                            !Out_Going_Min = mMinutes
                            !Out_Going_Calls_Count = mOutgoingCallsCount
                            .Update
                        End If
                End With
                .MoveNext
          Loop
                rstTotalOutMin.Close
       End With
    rstOutGoingMin.Close



I get an errormessage in my project I can not solve

It says a table is opened exclusively by onother user or opened with a user interface, the table can not be manipulated via programming

I can open the table,
sort it
do whatever I want
I can run a query without any problem but, in the program iteself when I run following line

DoCmd.RunSQL "UPDATE [BGV Competenties] SET [BGV Competenties].keuze = False;"

or

DoCmd.OpenQuery "Keuze_aanpassen_aan_primair"

I get the same error

What to do?
JP

I have a form called Products. Besides lots of other fields used to capture product specific data, there is a field called Category from which the data is created by means of a populated combo box. The values for the combo box are from a product category list which I typed in.
I have another form which is created to run some preset queries from which the users will run queries by means of a button click
One of the queries is a search by product Category . Another combo box is used with the same values list that is on the Product from. Users select the product category they wish to search on and by clicking the appropriate button the query is run and displayed.

This generally works fine but sometimes I find that after running a category query the category field content for record 1 on the priducts form gets changed to a different category.
I'm trying to find what sequence of events causes this to happen, so far without success.
I think it's something to do with leaving a query open and then running another Category Query from the Query Form
Does anyone have an idea as to what causes this to happen - It's got me beat at present
thanks
swingtones

I know that this has been answered many times but i cannot get it to work. I am wanting the user to run a query and for it to show the results without them actually going into the query design view. At present they can clikc on a command button which will load a query. The only view they get are parameter requests (eg [enter system] [enter start date]. Then the query results will come up in a seperate window. What i would like is a form where they would open it and there would be unbound text boxes where they could enter the criteria for the parameters and the results to come up in a sub-form. Can this be done

Hope you can help. After a field is updated I want to run a select query. However I don't seem to be able to run this query without opening the query and then closing it via a macro. This looks really clumsy to me. Is there anyway of updating the query once a field is updated without having to open and close it?
Many Thanks

Dan

My form has a command button that employees can click to get current efficiency....The command button runs a query and opens a form that shows their name and efficiency with a command button to go back to original form. However, if they put in the wrong id # or hit a + sign or something foreign, it cannot query because the id number is incorrect so it gives them the form without anything on it...it doesnt even have the button to go back to original form...they cant get back.

So, right now the code behind the on click event of the button says:

Private Sub cmdcheckminutes_Click()
DoCmd.OpenForm "frmindividualorderminutes"
end sub

How do I tell it to cancel the command if the id number is incorrect ? (I dont want it to even open the form.)

Also, when the "individualorderminutes" form closes to go back to original form, I want the focus set on me.pickerid field...I couldnt figure out how to reference that field from another form..

Thanks, Noreene

Hello all,
How can I (a very new Access user)print a report based on an open data sheet? Users view a form that contains a datasheet where they do all their inputting and click various buttons that run queries. One particular query is kind of complex and they may run it several times before they find what they want to print. I cannot have a report based on this query because they would have to try and remember all the criteria they used to find what they were looking for. There must be a way to base a report on an open data sheet or somthing. I hope I have provided enough info to get an answer. Please help. This is killing me

Here is a lot more detail I hope it will help:
I have a form: Main Input Screen.
It contains a sub form called: Call Entry.
Call Entry is displayed in the Main Input Screen form as a DataSheet.
Depending on the query that is run the record source for Call Entry changes.
This is accomplished by the following code: (The Qry?????? Of course changes depending on the button they click)

Private Sub cmdP123MS_Click()
Me![Call Entry].Form.RecordSource = "QryP123MS"
Me![Call Entry].SetFocus
DoCmd.GoToRecord , , acFirst
End Sub

Some of the queries are complex. The user can specify criteria on many or a few fields.
Buttons are provided on the Main Input Screen form for the user to click on and run. The only change the user sees are the records in the datasheet.
When a user is satisfied with their search the Print button is then clicked (this button will run more code) and the records will print on a report that is nicely formatted to display the results.

My problem is that I can’t figure out how to print what the users want without running the query (which is NOT known in advance) all over again.

I did have this working once but have no idea how I did it. Someone out there digital space figured it out for me. I applied it and then lost it.

The key field for the report was UNIT
I think the report had a text box that pointed to a field on the open sub form called Unit. It may have looked something like this: Forms![Maint Input Screen]![Call Entry]!Unit
It worked great for a few weeks until I broke it somehow.

I am having a heck of a time explaining to people what I need. I hope this helps.
Running a query again (basing a report on a query WILL NOT DO).
Dimming variables and strings to capture possible input from search criteria may work but is far too complicated and not required. I did have it working once.
I think my report should be based on the Call Entry form (it’s record source is variable) But I don’t know how to make a sub form a record source for a report or if it is even possible.

Oh that I could still use Lotus Approach – is was soooo much easier to work with. But alas, I can’t. I must learn MS Access.

Please help and thanks – may many blessings and riches be to anyone who can solve this for me.


[This message has been edited by koocanusa (edited 05-04-2002).]

I'm a beginner at VB and programming. I have created a button on my form that runs a report whose underlying query prompts the user to enter a case number to print a single record. On button click, I would like the query to use the current record's case number without requiring the user to enter it. I also still need to run the report without opening the form, so I don't want to modify the query unless the user can still set the criteria for the case number when running the report by itself. Any suggestions would be welcome.

Hi,

I am new to access 2003 and have been building a database to track internal projects with the help of books and internet resources.

My current scenario:
I have a report whose source is a query that a user builds on the fly via a form interface that I created. After the user hits submit on the form interface, I have vba code which builds the appropriate SQL, sends it to the stored query to apply the SQL, then closes the query window immediately. Then the report would open based on the stored query, and the user sees the result. So, the user fills out a form, and sees a report at the end with the criterias specified without being bothered by what's going on in the background.

What I would like:
My current scenario is somewhat roundabout and very slow. Is there a way to build the SQL off of the form interface, and set the code as the record source for the report, so that the report would open showing the criteria specified?

Edit: I am looking to make the report's source the SQL statement itself, that is, a way to pass the SQL statement build on the fly into the report's source at runtime.

Thanks for your help!

I have a macro that I would like to open a Query in design view (so that the user may edit the query), and then run the query after the user has edited it (like when the user closes the design window). However, when I use the OpenQuery command to open the Query in design view, the macro just moves on to the next step without waiting for the Query to be modified!

I think that it may be possible to use a Condition to check if the Query is being designed, but I don't know what that check would be.

Any help would be greatly appreciated, thank you!

Please can someone help with this problem. I am trying to Export data from a query which has parameter set for the user to enter StartDate and EndDate. I then would export the data to an Excel file. It works perfectly without the Parameters on the query. How do I set it up to work with the parameter. I keep getting an error that "too few parameters." I get the error in the section which has the "Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)" part. Below is my codes. I read something about QueryDefs, but I do not know how to go about getting this to work. Any help will be greatly appreciated.

Public Function ExportRequest() As String
On Error GoTo err_Handler


' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabOne As Byte = 1
Const cStartRow As Byte = 2
Const cStartColumn As Byte = 1

DoCmd.Hourglass True


' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
sTemplate = CurrentProject.Path & "AOSummaryTemplate.xls"
sOutput = CurrentProject.Path & "AOSummary.xls"
If Dir(sOutput) "" Then Kill sOutput
FileCopy sTemplate, sOutput



' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabOne)
sSQL = "select * from AOSummary Where between [Forms]![AOSummaryReportForm]![StartDate] = ? AND [Forms]![AOSummaryReportForm]![EndDate] = ?"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)


' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow
If Not rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to AOSummary.xls"
Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

ExportRequest = "Total of " & lRecords & " rows processed."
Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."

exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
ExportRequest = Err.Description
Me.lblMsg.Caption = Err.Description
Resume exit_Here

End Function


Below is my sql that I got from the actual query that has the parameter included.


PARAMETERS [Forms]![AOSummaryReportForm]![StartDate] DateTime, [Forms]![AOSummaryReportForm]![EndDate] DateTime;
SELECT DISTINCT [AO SummaryCount].AO, [AO SummaryCount].[Closed/Mc/Bt/SV], [AO SummaryTotal].[Closed/Mc/Bt/SV], [AO SummaryCount].Comp, [AO SummaryTotal].Comp, [AO SummaryCount].ExRefi, [AO SummaryTotal].ExRefi, [AO SummaryCount].IntRefi, [AO SummaryTotal].IntRefi, [AO SummaryCount].[Mcyc/Boat], [AO SummaryTotal].[Mcyc/Boat], [AO SummaryCount].Ncar, [AO SummaryTotal].Ncar, [AO SummaryCount].[New HE], [AO SummaryTotal].[New HE], [AO SummaryCount].ODOT, [AO SummaryTotal].ODOT, [AO SummaryCount].Open, [AO SummaryTotal].Open, [AO SummaryCount].Salo, [AO SummaryTotal].Salo, [AO SummaryCount].Ucar, [AO SummaryTotal].Ucar, [AO SummaryCount].Visa, [AO SummaryTotal].Visa, [AO SummaryTotal].CountOfLOAN_TYPE, [AO SummaryTotal].SumOfAMOUNT1
FROM APPROACH INNER JOIN ([AO SummaryTotal] INNER JOIN [AO SummaryCount] ON [AO SummaryTotal].AO = [AO SummaryCount].AO) ON APPROACH.AO = [AO SummaryTotal].AO
WHERE (((APPROACH.Date_Field) Between [Forms]![AOSummaryReportForm]![StartDate] And [Forms]![AOSummaryReportForm]![EndDate]));

In the Access object pane I have qryDenialsForThisEob. I am trying to run it from Csharp using code something like this:

SQL = "SELECT * FROM qryDenialsForThisEob";

da = new OledDBDataAdapter(SQL, cn)
da.Fill(dataTable1);

This isn't working. I have other queries that work fine from csharp, for instance:

SQL = "SELECT * FROM qryAllDenials"

Anyway the error I'm getting is this:

Undefined function 'GetTheLetters' in expression.

This refers to a UDF used in the query, called Public Function GetTheLetters residing in standard module.

The odd thing is that this query runs fine when run from Acess VBA or the Access object pane. (Literally I can close the .Net app, open the Access object pane, and run the query without incident). It is only when running it from Csharp that I have this error. So the question is whether UDFs can be invoked from VB.Net and Csharp.Net.


Hi All,

I'm not able to run SQL queries from Access 2003.

I've coneected to MSDE (which is on the same machine I'm working on). I've selected a Database 'TastDB'. Now I want to run a script (containing 'Create Table') on TestDB from Access.

How can it be done? I've searched this forum as well as the Access help but couldn't do this.

I found following steps, but couln't found step 4 mentioned below (i.e. On the Query menu, point to SQL Specific). I don't see SQL Specific button there:In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Queries under Objects, and then click New on the Database window toolbar. In the New Query dialog box, click Design View, and then click OK. Without adding tables or queries, click Close in the Show Table dialog box. On the Query menu, point to SQL Specific, and then click Data Definition. Enter the SQL statement for your data-definition query. Each data-definition query consists of just one data-definition statement. Can anybody help???

Hi,

I linked table rawdata from a database called competitor from a ODBC Database. I run a query with to make a table with a criteria where it retrieves roughly 10 columns with 719,000 rows. And it gives me this error.

(Cannot open database". It may not be a database that your application recognizes, or the file may be corrupt.

I asked the creator of the database and they said the database grows automatically and it was created with the same Access version as i am using to run the query.

Also when I do a RUN without creating a table and just selecting and displaying the data it does not give me any issues.

Any help will be appreciated.

Hey all,

Have a very annoying issue that has had me stumped for the last two days; whenever I use a reference to a control in another form as a criteria for my query, it wont recognise it. The form is always open as you cannot access the query without it.

I am using;


	Code:
	[Forms]![Candidate]![WorkEmail] Or [Forms]![Candidate]![PrivateEmail]

as the criteria in the Query. When I run it the Query prompts the user to enter a value for the criteria instead of detecting the existing strings (just like a parameter Query). Can anyone explain why it isn't working? I run the Query from another control using a simple 'Requery'. Ideally I want it to run on load but I believe it is trying to pull information from a form not fully loaded and so wont work properly.

So my main issue is why my criteria isn't recognising the form control as a reference. The control is in a Tab, but searches assured me that is no different from being in Detail.

Anyone that can shed some light on my problem?

Thanks,
Tom

Here is my issue. Attached image is a mock example of what I am working with.

A user will select a product to review (Example is product F-12). The subform will fill with all of the pieces that were used to make this process. Now if the user wants to review all pieces in say Step B they will click the button for 'Step B Report(s)' in which then run a query to look up all data for those pieces listed in Step B and then open the results in a report.

The issue I am having is the reports do not open for all of the pieces in Step B. Instead it will only load for the current record which is selected (defaults to record 1) in the subform.

I am trying to pass the list in the field to the report query by using the [Forms]![MainForm]![SubForm].[Form]![FieldName] without success.

Any suggestions?

I have populated an append query and now need to run it without opening it. Then I want the data deleted so it can be run again with a different set of numbers. How to do this with vba code?

thanks

I am using Access 2007. I have an append query that adds a new class to a table named Class_catalog when the Save cmd button is clicked. I have now decided I need to make a field in this table a multi-valued field. The field is named Job_title. I have a form Class_New that lets you make selections and then click the save button to save to the Class_catalog table. On there form there is a combobox to choose Job_title. Now that I have changed this field to a multivalue field, I get an error when the append query is run. When the error occurs, this is the code that is the problem:


	Code:
	Private Sub CmdSave_Click()
    
    If Me.CboJtitle = "All" Then
    DoCmd.OpenQuery "New_Class_All_append"
    DoCmd.GoToRecord , , acNewRec
    Else: If Me.CboJtitle  "All" Then DoCmd.OpenQuery "New_Class_Jtitle_append"
    
    DoCmd.GoToRecord , , acNewRec
    
        
    End If
    
        
End Sub

This is the SQL for the query "New_Class_All_append"

	Code:
	INSERT INTO Classes_taken ( Emp_ID, Class_ID )
SELECT Emp.Emp_ID, [Forms]![Class_New]![TxtCID] AS Class_ID
FROM Emp
WHERE ((([Forms]![Class_New]![CboJtitle])="All"));

The other query "New_Class_Jtitle_append" is this:

	Code:
	INSERT INTO Classes_taken ( Emp_ID, Class_ID )
SELECT Emp.Emp_ID, [Forms]![Class_New]![TxtCID] AS Class_ID
FROM Emp
WHERE ((([Forms]![Class_New]![CboJtitle])=[Emp]![Job_Title]))
GROUP BY Emp.Emp_ID, [Forms]![Class_New]![TxtCID];

I wouldn't ask, but I haven't worked with multivalued fields before. If there is a way without using it, I'm open to that as well.
Thank you!


Not finding an answer? Try a Google search.