Learn Parameter Queries

[lawdy]

Hi everyone, I am trying to learn parameter queries. I have read extensively on the internet and found nothing on how to get or use the data in those queries. I have always just made a table. But it looks like I could eliminate the make table using parameter queries. This is probably simple to you.

Anyway, I found the following code at https://learn.microsoft.com/en-us/of...oft-access-sql.

Sub ParametersX()

Dim dbs As Database, qdf As QueryDef
Dim rst As Recordset
Dim strSql As String, strParm As String
Dim strMessage As String
Dim intCommand As Integer

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("NorthWind.mdb")

' Define the parameters clause.
strParm = "PARAMETERS [Employee Title] CHAR; "



' Define an SQL statement with the parameters
' clause.
strSql = strParm & "SELECT LastName, FirstName, " _
& "EmployeeID " _
& "FROM Employees " _
& "WHERE Title =[Employee Title];"

' Create a QueryDef object based on the
' SQL statement.
Set qdf = dbs.CreateQueryDef _
("Find Employees", strSql)

Do While True
strMessage = "Find Employees by Job " _
& "title:" & Chr(13) _
& " Choose Job Title:" & Chr(13) _
& " 1 - Sales Manager" & Chr(13) _
& " 2 - Sales Representative" & Chr(13) _
& " 3 - Inside Sales Coordinator"

intCommand = Val(InputBox(strMessage))

Select Case intCommand
Case 1
qdf("Employee Title") = _
"Sales Manager"
Case 2
qdf("Employee Title") = _
"Sales Representative"
Case 3
qdf("Employee Title") = _
"Inside Sales Coordinator"
Case Else
Exit Do
End Select

' Create a temporary snapshot-type Recordset.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

' Populate the Recordset.
rst.MoveLast

' Call EnumFields to print the contents of the
' Recordset. Pass the Recordset object and desired
' field width.
EnumFields rst, 12

Loop

' Delete the QueryDef because this is a
' demonstration.
dbs.QueryDefs.Delete "Find Employees"

dbs.Close

End Sub



I don't have NorthWind.mdb. I redid the code using items that I have.
Sub ParametersX()

Dim dbs As Database, qdf As QueryDef
Dim rst As Recordset
Dim strSql As String
Dim strParm As String
Dim strMessage As String
Dim intCommand As Integer


Set dbs = OpenDatabase("C:\Users\Loy\Documents\TimeMaterial. accdb")

' Define the parameters clause.
strParm = "PARAMETERS [strTitle] CHAR; "

' Define an SQL statement with the parameters
' clause.
strSql = strParm & "SELECT LastName, FirstName, " _
& "EmployeeID " _
& "FROM Employees " _
& "WHERE Title =[strTitle];"

' Create a QueryDef object based on the
' SQL statement.
Set qdf = dbs.CreateQueryDef _
("fTxtFN", strSql)

Do While True
strMessage = "Find Employees by Job " _
& "title:" & Chr(13) _
& " Choose Job Title:" & Chr(13) _
& " 1 - Supervisor" & Chr(13) _
& " 2 - Crew Chief" & Chr(13) _
& " 3 - Striper"

intCommand = Val(InputBox(strMessage))

Select Case intCommand
Case 1
qdf("strTitle") = _
"Supervisor"
Case 2
qdf("strTitle") = _
"Crew Chief"
Case 3
qdf("strTitle") = _
"Striper"
Case Else
Exit Do
End Select

' Create a temporary snapshot-type Recordset.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

' Populate the Recordset.
rst.MoveLast

' Call EnumFields to print the contents of the
' Recordset. Pass the Recordset object and desired
' field width.
EnumFields rst, 12


Loop

' Delete the QueryDef because this is a
' demonstration.
dbs.QueryDefs.Delete "fTxtFN"

dbs.Close
End Sub
On the line 'EnumFields rst, 12' an error "Sub or Function not defined" There is no error number. Other that the error, I need to know how to use the data that the query gets. Thanks in advance for your help.

[Welshgasman]

So you have missed the function/sub called Enumfields from that link?
Quick look shows no code for that?
However I have google on this phone.

https://www.google.com/search?q=enum...obile&ie=UTF-8

[orange]

You missed this line when creating your code.

It calls the EnumFields procedure, which you can find in the SELECT statement example.

[lawdy]

Ok, I am still very lost on this. I don't know what to do.

[xps35]

What is your ultimate goal? A (parameter) query is normally only an intermediate step on the way to a report or a form.
It is often much easier to enter the parameters on a form instead of working with a parameter query.

[lawdy]

My ultimate goal is to learn a better method of running queries. Currently I run make table queries. Then another procedure to open the record set to get the data from the table. I have countless 'make table' tables. The parameter query seems like a better way.

[xps35]

Those tables cannot be the ultimate goal. What do you with them?

[lawdy]

My ultimate goal is simply to learn parameter queries.

[Welshgasman]

My ultimate goal is simply to learn parameter queries.

So a simple Google would do that?

[orange]

See this Access series by Steve Bishop. You would probably benefit from all the modules, but #7 and 8 may be helpful based on your interest in Queries.

[lawdy]

Ok, I got it worked out. Thank all of you very Muck.