- Joined
- Dec 9, 2010
- Messages
- 1
- Reaction score
- 0
Hello All,
I'm having difficulty setting up a query with the functionality to pass multiple lines of values in a text box on a MS Access Form to find the matches in a SQL Database connected via ODBC.
At a high level, essentially what I'm trying to set up is a user friendly form where someone with no knowledge of access can open up the database and paste in employee numbers (from excel, notepad, etc.) and retrieve all the records tied to that employee number.
Setting this up for searching for one employee number at a time is no problem, but the hiccup for me is figuring out to code this for multi line entry. For example, I want to be able to paste in a list of numbers to an unbound text box on a form like this
21471959
21471960
21471961
21471962
21471963
21471964
21471965
...and retrieve the results. The number of values in the list is not static, so the code needs to be flexible enough to accomodate however many values the user inputs into the text box.
I'm not sure if I will need to use VBA... or, if SQL will work.
The current SQL query I have is based on
table = dbo_empdatacopy
Field = lEmplDeptId
Form text box reference = [Forms]![Emp_Search]![Text3]
SELECT
dbo_empdatacopy.lEmplDeptId, dbo_empdatacopy.EMail
FROM
dbo_empdatacopy
WHERE
(((dbo_empdatacopy.lEmplDeptId) In ([Forms]![Emp_Search]![Text3])));
Can anybody provide a snippit of code to help me loop through every line that is populated? Thanks in adv if you can!
I'm having difficulty setting up a query with the functionality to pass multiple lines of values in a text box on a MS Access Form to find the matches in a SQL Database connected via ODBC.
At a high level, essentially what I'm trying to set up is a user friendly form where someone with no knowledge of access can open up the database and paste in employee numbers (from excel, notepad, etc.) and retrieve all the records tied to that employee number.
Setting this up for searching for one employee number at a time is no problem, but the hiccup for me is figuring out to code this for multi line entry. For example, I want to be able to paste in a list of numbers to an unbound text box on a form like this
21471959
21471960
21471961
21471962
21471963
21471964
21471965
...and retrieve the results. The number of values in the list is not static, so the code needs to be flexible enough to accomodate however many values the user inputs into the text box.
I'm not sure if I will need to use VBA... or, if SQL will work.
The current SQL query I have is based on
table = dbo_empdatacopy
Field = lEmplDeptId
Form text box reference = [Forms]![Emp_Search]![Text3]
SELECT
dbo_empdatacopy.lEmplDeptId, dbo_empdatacopy.EMail
FROM
dbo_empdatacopy
WHERE
(((dbo_empdatacopy.lEmplDeptId) In ([Forms]![Emp_Search]![Text3])));
Can anybody provide a snippit of code to help me loop through every line that is populated? Thanks in adv if you can!