Setting Parameter to Query in VBA Code

[Jonny]

Hello Friends,
I have Query1 that should get one parameter , how to set this parameter in a code.

Below the code.

Private Sub Command0_Click()

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Query1")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
'
Do While Not MyRS.EOF

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailName]

With objOutlookMsg
.To = TheAddress
.Display
End With

MyRS.MoveNext
Loop 

Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

 

[Alisa]

Private Sub Command0_Click()

Dim MyDB As Database
[COLOR="red"]Dim MyRS As DAO.Recordset
Dim MyQDF as DAO.Querydef[/COLOR]
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
[COLOR="Red"]Set MyQDF=MyDB.QueryDefs("Query1")[/COLOR]
[COLOR="red"]MyQDF.Parameters(0)= your parameter[/COLOR]
Set MyRS = MyQDF.OpenRecordset
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
'
Do While Not MyRS.EOF

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailName]

With objOutlookMsg
.To = TheAddress
.Display
End With

MyRS.MoveNext
Loop 

Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

 

[Jonny]

Hi Alisa,
I did something similar:
Added to first line of Query1:

PARAMETERS PName Text ( 255 );

to last line of Query1:

WHERE (((tblProducts.ProductName)=PName));

and to code:

    Dim qd As DAO.QueryDef
    Set qd = CurrentDb.QueryDefs("Query1")
    qd.Parameters("PName").Value = InputBox("Enter product to search:")
    Set MyRS = qd.OpenRecordset()

Thank you.