Passing Form Value to a Query in VB

[thefonz37]

Here's a code fragment I've written to try to pull a control value from a
form and use it in a query to link the control value back to an employee
e-mail address. I think I've asked a similar question to this before, but I
can't remember what the answer was and now I can't find the topic. Is there
a way to do what I'm trying to do without redesigning major pieces?

Dim outEmail As DAO.Recordset
Dim query As String

query = "SELECT tbl_Employee_Data.[E-Mail]FROM tbl_Archived_Requests INNER
JOIN tbl_Employee_Data ON tbl_Archived_Requests.EmpID =
tbl_Employee_Data.EmpID WHERE
(((tbl_Archived_Requests.ID)=[Forms]![frm_Modify_Approval]![List1]));"

Set outEmail = CurrentDb().OpenRecordset(query)

 

[Allen Browne]

Concatenate the value into the query string:

Dim strSql As String
If Not IsNull(Forms!frm_Modify_Approval!List1) Then
strSql = "SELECT ... FROM ... WHERE tbl_Archived_Requests.ID = " & _
Forms!frm_Modify_Approval!List1 & ";"
etc.

(I have suggested you use a different name for the string, as Query is an
Access reserved word.)

 

[thefonz37]

Rock on, man, worked like a charm. Thanks.

Concatenate the value into the query string:

Dim strSql As String
If Not IsNull(Forms!frm_Modify_Approval!List1) Then
strSql = "SELECT ... FROM ... WHERE tbl_Archived_Requests.ID = " & _
Forms!frm_Modify_Approval!List1 & ";"
etc.

(I have suggested you use a different name for the string, as Query is an
Access reserved word.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Here's a code fragment I've written to try to pull a control value from a
form and use it in a query to link the control value back to an employee
e-mail address. I think I've asked a similar question to this before, but
I
can't remember what the answer was and now I can't find the topic. Is
there
a way to do what I'm trying to do without redesigning major pieces?

Dim outEmail As DAO.Recordset
Dim query As String

query = "SELECT tbl_Employee_Data.[E-Mail]FROM tbl_Archived_Requests INNER
JOIN tbl_Employee_Data ON tbl_Archived_Requests.EmpID =
tbl_Employee_Data.EmpID WHERE
(((tbl_Archived_Requests.ID)=[Forms]![frm_Modify_Approval]![List1]));"

Set outEmail = CurrentDb().OpenRecordset(query)