VBA Query Help

[pundabuyer]

Hi all,

I am just trying to understand the whole querydef thing! I don't have a problem as such. I have read a lot on the internet but it is all contradictory and confusing!

What are the following used for exactly? :

QueryDefs
CreateQueryDef

To get the grasp of this i simply want to be able to runsql from vba declaring a parameter from an input box in the most simplist form!!!

For example:

Append all fields from customers table to customersappended table where the customers.customerID is a parameter - FROM RUNSQL

Thankyou!! :whistle:

[pundabuyer]

Hi,

Could someone take a look at the code below and tell me where i am going wrong?

You should be able to see i am trying to run sql code with a parameter and define the parameter from an input box... i know i can do this with a regular query but i want to do this from vba...




Private Sub Command0_Click()

Dim Db As Database
Dim Qd As QueryDef
Set Db = CurrentDb
Set Qd = Db.CreateQueryDef("Q1", "PARAMETERS EnterCriteria String;INSERT INTO tbl_Append ( CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax ) SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.ContactTitle, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, Customers.Phone, Customers.Fax FROM Customers WHERE (((Customers.CustomerID)=EnterCriteria));")

Qd!EnterCriteria = InputBox("Enter Name ID")

Qd.Close

End Sub

[CreganTur]

QueryDef is the collection that Access uses to house all of your queries.

CreateQueryDef creates a new query that is added to the QueryDef collection. It doesn't run the query, just creates a new one.

Now, you don't have to use a DAO connection to run a query that exists in the same database as your code. There are simpler ways that will probably work better for you: the OpenQuery method and the RunSQL method; details below:

1) RunSQL Method:
DoCmd.RunSQL. This method allows you to define a SQL statement as a string value directly in the VBE and run it. If you want to provide a parameter then you would generally use this behind a form where you have a textbox or combobox that defines your parameter. The VBA would be something like:
DoCmd.RunSQL "INSERT INTO tbl_Append ( CustomerID) " _
"SELECT Customers.CustomerID WHERE Customers.CustomerID = '" & Me.txtBox & "';"
Me.txtbox refers to the textbox on your form where your desired parameter is.

You could use an input box if you really wanted to, like this:
Dim strParam As String
strParam = InputBox("Enter your parameter here")
DoCmd.RunSQL "INSERT INTO tbl_Append ( CustomerID) " _
"SELECT Customers.CustomerID WHERE Customers.CustomerID = '" & strParam & "';"
Honestly using a form Object would be better because you can use data validation on what the User enters. An input box is harder to validate... the user could easily input invalid data.

2) DoCmd.OpenQuery:
This method will run an existing query that exists in your database. You use it by referencing the name of the query you want to run:
DoCmd.OpenQuery "QueryName"
Replace QueryName with the actual name of the query. To use this with a parameter you will need to make some changes to the query itself. The easiest way to do this is, again, have the parameter provided by an object on a form.

Open your query in Design View and click on the Criteria column for the desired field. Use Build. Now, you will click on Forms, All Forms, and drill down to the desired object on the desired form... the result will look something like: Forms![FormName]![Object], where FormName is the form's actual name, and Object is the object's actual name.

What this does: when the query launches it will grab the object's value and automatically use it as the parameter for the query.

HTH:thumb

[pundabuyer]

Thanks so much for taking the time to explain, i really appreciate it and understand!!

Thanks Again

P