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.