VBA Code to run a query

I have a query which prompts the user to enter a date.

Using the following VBA code, which is assigned to a button:


	Code:
	Sub GetMErateData()
'Displays all ME rate data
DoCmd.OpenQuery "qry Divisional Analysis Month End Rate"
End Sub

When the prompt appears and I click cancel rather then enter a date I get an error message:

Runtime Error '2001'
You cancelled the previous operation

Is there anyway I can press cancel and not receive this message?


Post your answer or comment

comments powered by Disqus
I am researching methods to run a query and have the query output fed into a new spreadsheet file.

Is the:

	Code:
	DoCmd.OutputTo

method the preferred way to do such?

If so, where may I find a list of A2007 supported AcFormat constants for the OutputFormat parameter?

Or is there a way to adjust a DAO.QueryDef object to have it output to an Excel file?

I think I would prefer to export based on SQL so that the FE temp table may have "scratch pad" columns which do not get exported to the spreadsheet. I am seeing I am going to need such as I am not able to perform all preparations in the SQL Server Stored Procedure which populates the FE temp table. So, high level steps:
1) Empty FE temp table
2) EXEC SP and download records to FE temp table
3) VBA fix-up of the data in the FE temp table
4) SQL to Excel file export

Hey peeps,

I need some guidance here. I have the following vba code in a On Click event:

	Code:
	Private Sub Command12_Click()
 
Dim intCountryID As Integer
intCountryID = DLookup("CountryID", "qryCompanyFilter", "[CompanyID] = " & Me.Text10)
 
Select Case intCountryID
Case 1
 
 
DoCmd.OpenReport "ReportOne", acViewPreview
Case 2
 
 
DoCmd.OpenReport "ReportThree", acViewPreview
Case 3
 
DoCmd.OpenReport "ReportTwo", acViewPreview
 
End Select
 
End Sub

Now, I want to run a query in the above case statment and display the results in the report. So for example, say Case 1 is true, I want to run a query(which I will add to the code) and display the records in "ReportOne". So something like this:


	Code:
	 
Case 1
mysql = "SELECT Countries"
mysql = mysql & " FROM qryFootballTournament"
mysql = mysql & " qryFootballTournament.Points =" & Me.Points
DoCmd.OpenReport "ReportOne", acViewPreview
 
Case 2
mysql = "SELECT Countries"
mysql = mysql & " FROM qryFootballTournamentTwo"
mysql = mysql & " qryFootballTournament.Points =" & Me.Points
DoCmd.OpenReport "ReportTwo", acViewPreview
 
Case 3
mysql = "SELECT Countries"
mysql = mysql & " FROM qryFootballTournamentThree"
mysql = mysql & " qryFootballTournament.Points =" & Me.Points
DoCmd.OpenReport "ReportThree", acViewPreview

My question is how do I run the query and source the results in a report!

Thanks,
Ket

Hello Friends
Access 2010
I have checking this Forum looking for a code to run a find button based in a Query
I can run this code when the code is like this
Private Sub cmdFind_Click()
DoCmd.OpenForm "FFind", acViewNormal
DoCmd.Close acForm, Me.Name
End Sub

I am trying another way with the following code:
Private Sub cmdFind_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
strSQL = "SELECT OBID, YY, AU, PK, CT, TT, RE, WN From QFind"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
DoCmd.OpenForm "FFind", acViewNormal
DoCmd.Close acForm, Me.Name
Else
MsgBox "No Records"
End If
rs.Close
Set rs = Nothing

End Sub

However, when I click de button to open the form FFind (baed on QFind) to display the records a message Error 3061 is displayed when I open the VBA the code line
Set rs = db.OpenRecordset(strSQL)
The execution stops in the above line and is coloured in Yellow
Could you help me to fix this error?

Many thanks in advance

Dear friends, I am fairly new to VBA coding in Access and I am trying to create a code to run multiple queries using same value.
I have 3 queries for Customer Material and I want to run these queries by passing a value of customer to find all the materials the customer can order.

On running the code, I would like to input customer number and want Query1 to run and
if the Query1 is success then display all records and stop
else run Query2 using the same parameter value (in this case Customer # so that I don't have to enter the same customer# again for the 2nd query) and if the Query2 is success then display all records and stop
else run Query3 using the same parameter value and if the Query3 is success then display all records and stop
else display records from a table tbl_material.

I tried DoCmd.OpenQuery command but the if elseif statement is not working and it run all the 3 queries and table and display results in 4 separate tabs and if I use parameter in each of the queries as criteria then the code ask me customer # every time a query execute.
I would really appreciate all your help

Thank you very much in advance!!!

Hello Brethren,
I seiously l need your help urgently. I'm trying to run a query on seemingly unrelated tables. I used the query wizard but the wizard returned empty fields. I tried to manipulate the SQL code using RIGHT JOIN, LEFT JOIN OR OUTER JOIN, but to no avail.
Your suggestions will be very much appreciated.
joe

I need the code or any help to be able to get a combobox to run a query
based apon the value selected within the combobox, i also dont know how to make the query variable and linked to the combobox so that i dont have to produce loads of queries.

I so far have this SQL code

SELECT NRTS.Item, NRTS.Description, NRTS.[NRTS No], NRTS.[Serial Number], NRTS.[Date Issued], NRTS.[Cal Due], NRTS.[Allocated To], NRTS.[Pat due Date], NRTS.Comments
FROM NRTS
WHERE [Allocated To]=Forms![Main]![Combo5];

And in the afterupdate event of the combobox,
DoCmd.OpenQuery "MyQuery"

But when i select anything in the combobox the query runs, but dosent return any records.

Any help would be great.

Hey guys, I have a Process Change Form that has a bunch of yes/no check mark boxes on it. Is there a way depending which boxes are check marked for it to run a query and change another table to display only the valid information?

I already have the table created that has the Names of the employees listed and their position. I also have a query table created that when I put in the position it will only display those employees of that position I want.

What I am looking for though is depending which boxes are checked In the Process Change Form that it will give back only the employees needed on the Query table.

ex. 5 yes/no check boxes for Driver, Packer, Operator, Maintenance and Supervisor. I have information that only the Driver, Packer and Supervisor needs to see, so only their yes/no boxes are checked. I need to have the Query table then print with only their names. This is a check off sheet that they sign off on saying they read the information.

I am sure this is something that would need to be done in code.

Any help would be appreciated?

Brad

I would like to run a query where I have a comboBox that carries ID's, if I select an ID, then I want to be able to use that selected ID to perform a Select query which will show the results in a textbox.

Iím trying to run a query from within a query "But Run it Last".

Basically Iím wondering if I can run a query or sql in a order so that all filters and formulas in a query will run, but before itís finished it will look at the last formula

This is the statement I would like to run at the end, after all the filters and stuff has run. If you know that this is something I canít do for, please let me know that also and I will move on and try something different

this is the statment I would like to run last:
[sql]Master_Table_Join_to_Peer_Groups_tbl.[Past_12_Months$], Master_Table_Join_to_Peer_Groups_tbl.[Past_12_Months#], 1+DCount("*","([Master_Table_Join_to_Peer_Groups_tbl])","([Past_3_Month#])

I tried a search but wasn't able to find what I was looking for. I would like to be able to use a form to run a query in my db. I need to use name, begin date and end date and then have a button to run my query. My problem is that I can't get the unbound text boxes (for the dates) to use a date format. Have I taken the wrong approach?

Code: DoCmd.OpenForm stDocName, acFormPivotChart I am using this statement to open a form in pivotchart view.

Also, in the pivotchart view of the form,I dragged the columns that I needed for the graph and when I close the form and reopen the same form with a button click,the form opens up with empty barchart in pivotchart view. Is there a way to populate the graph based on the query while the form is opened in pivot chart view?
My question is: How to create Pivot chart with graphs populated from a query using vba .Could you please provide any sample vba code that runs a query ,creates a pivot chart in a form and then opens the form as a report on a button click. Thanks for your help !

Hi,

I am really new to Access and my boss has asked me to help out with an issue he is having. We used to use the sendmail macro command but we cant do this anymore due to the pop up that Outlook gives. I need to automate the entire process.

What I am trying to achieve:

1.) run a vb script
2.) vbscript runs a query called "VMCR"
3.) results of query are saved as xls format with filename "VMCR" + current date/time so that every time this is run a unique file is created
4.) the attachment is then emailed to one individual

I will then schedule a macro to run the vbscript.

If anyone could help me out that would be really great. I am stuck!

We have an exchane server which will allow receinving smtp mail from the vbscript

thanks in advance

Paul

Other than SQL, what are my options for running a query using VBA?

I have always used this...

DoCmd.OpenQuery "MyQueryNameHere"

But today I have a db that does not like to run a query using the above DoCmd command. But I can run it manually.

Any suggestions? Thanks!

I have what Microsoft call a "Navigation Form" as the main form in my application. Users just click tabs to move about the application and it all works very well, looks good and was almost no work to produce.
Now one of the forms that opens when you click a tab shows a summary of overdue matters ie a continuous form of a few fields from a record in text boxes. The boxes are populated from a query that pulls out those records that are overdue for attention.
Ideally there should be a button beside the text boxes and when one is clicked it should open a form where the updating of information is needed. This happens to be the form that is opened by the second tab in the "Navigation Form".
So the VBA code behind such a button needs to be along the lines (in Psuedo VBA) of :-

Global VarID as integer 'In a Module
'and then
Sub Button1_Click
VarID = Me.ID
Click (navigation form.tab2) ' I hope its as easy as this
End Sub

'and some way of filtering the form with VarID so that it shows the right record.

This baffles me. Over to you O Great Ones!

Hi

i've managed to create a macro that opens a query into design view but can't seem to figure out how to get the macro to run the query

Hi,

I wasn't sure whether to put this in the query section or Macro section of the forums, so forgive if not in the right one.

I have a macro that runs a delete query, and that works ok, but could be improved if it could automatically answer yes to any confirmation boxes that appear (the data being deleted is not needed and is just used to run a query, so I am confident there is no need for a manual confirmation).

Any help on whether this is possible would be gratefully received.

Thanks,

Robert.

First off, PLEASE bear with me. I am a total NEWBy to access and know ALMOST nothing about what I am about to ask.

I am in the process of building a database to track publications (529 total). I have 3 different ways to allow users to find a pub. the first 2 involve unique numbers that the users will know, and I have queries set up for those. The third way involves having the user "walk through" a sub-categorized series of links to find the exact pub they are looking for. When the user reaches the final link and clicks on it, that link SHOULD run a query that compiles info from various tables onto a form.

I have designed the form and the query to get the info from the various tables, with the criteria set to prompt the user for the ID (AutoNumber)but need a way for each of the "final links" to run the query with the ID already set, not requiring the user to enter the ID number: that number would already be "embedded" in the link, and the query would run with no input required from the user.

Also need that info to be displayed on a pretty little form... PLEASE HELP!!!

I am having a problem trying to find VBA code to import an excel 2007 file into my access 2007 dao database. My problem is that with each excel record I need to create 2 different records which will be written into different tables in my database.

Guys,

For whatever reason I have three queries (different) that I want to use to populate one Word template. I know the code to open the document and do the word bit but I dont know the code to run a query and then access the fields in it.... any help much appreciated.

What code can you use to run a query that's in a different ms access database?

hi, picture this
i have a form, on this form this is what i would like to happen
there is a small drop down list of field, example - a list of countries - uk,usa,wales etc
i would like the user to be able to select one of these options from the drop down list, and then click a button (macro possibly) that says search.
i would like this button to then run a query, based on the name the user has just chosen on the form and bring up
so if they choose wales, and click search, the records that have the word 'wales' in them are displayed.

I have set up a command button within a form and wish to output results from a query for a specific company. The query has been set up.

Can anyone help?

Thanks

I am having a very tough time working on this, any help/idea on this problem will be greatly appreciated.

Basically, I am looking for an access VBA code, which runs a macro when a cell in excel file is clicked. Thanks a million in advance.

I have searched the internet a lot and lost hope!

Is it possible to run a query/filter in order to hide/show a field in a record instead of it affecting the entire record.

What I'm trying to do is, in a form the user can enter information about a service call and they can enter a recommendation for the next visit. When they enter a recommendation they are able to select which month the recommendatin should pop up. This Service report get's printed on a monthly basis as a report. Now the recommendation field should only pop up if that particular month which was selected earlier is true. I've tried different methods, but it would affect the entire record as opposed to just one field.

Thanks.


Not finding an answer? Try a Google search.