can you use select with DoCmd.RunSQL?

[Sam Y.]

From the Access help, it says that you can use a select statement in RunSQL.
My question is, how do you get the values of that select statement? For
instance if I select a dropdownlist which lists columns "ID, first name,
middle name, last name", is there a way I can make all those values show up
in the dropdownlist.text property? Or would it be the .value property?

To try to work around this problem, I want to do RunSQL to do a select
statement: "select first name, last name from employees where '" & empid =
dropdownlist.value & "'". The problem is, I don't know where the values are
returned after the RunSQL statement is executed. So I'm assuming you don't
get any returned values.

*This is for Access 2003. I'm using Access 2007, but saving as a 2003 mdb
file since not all the computers that will use this database do not have
Access 2007.

 

[Klatuu]

If you are trying to create a dropdown list, you mean a combo box or a list
box. In either case, you don't use a RunSQL command for this. you can
either use a query or an SQL statment or a table. It goes in the Row Source
property of the control.

 

[John W. Vinson]

From the Access help, it says that you can use a select statement in RunSQL.
My question is, how do you get the values of that select statement? For
instance if I select a dropdownlist which lists columns "ID, first name,
middle name, last name", is there a way I can make all those values show up
in the dropdownlist.text property? Or would it be the .value property?

To try to work around this problem, I want to do RunSQL to do a select
statement: "select first name, last name from employees where '" & empid =
dropdownlist.value & "'". The problem is, I don't know where the values are
returned after the RunSQL statement is executed. So I'm assuming you don't
get any returned values.

*This is for Access 2003. I'm using Access 2007, but saving as a 2003 mdb
file since not all the computers that will use this database do not have
Access 2007.

You don't need RunSQL *AT ALL* for this purpose. Executing RunSQL on a Select
query just opens a query datasheet - and generally that's not a good idea,
users shouldn't see table or query datasheets at all.

Instead, if you want a combo box ("dropdownlist" by its proper name) to
display the results of a query, use that Query as the combo box's RowSource
property. If you want one combo box to be dependent on another combo box, just
include that combo box in the rowsource query:

select[first name], [last name] from employees where empid =
[Forms]![YourFormName]![dropdownlist] ORDER BY [First Name], [Last Name];

 

[Sam Y.]

What I mean is, I'm trying to basically get the supervisor's first and last
name to show up in a textbox after you choose from the combobox. The
dropdownlist is a combobox that has a list of supervisors but the bound
column is the empid. As far as I know, you can't show all the values in a
combobox dropdownlist (employee id, first name, last name) once you choose
from it. It will only show the employee id. That is why I want a textbox next
to it to display the full name.

 

[John W. Vinson]

What I mean is, I'm trying to basically get the supervisor's first and last
name to show up in a textbox after you choose from the combobox. The
dropdownlist is a combobox that has a list of supervisors but the bound
column is the empid. As far as I know, you can't show all the values in a
combobox dropdownlist (employee id, first name, last name) once you choose
from it. It will only show the employee id. That is why I want a textbox next
to it to display the full name.

You are mistaken! The combo box's bound column (the ID) need not be the
visible column. In fact it's typical to set the column widths property of that
column to zero, so that it's not displayed at all.

Try this: use a Query

SELECT EmpID, [FirstName] & " " & [LastName]
FROM Employees
WHERE Supervisor=True
ORDER BY FirstName, LastName;

(using your own tablename and appropriate criteria of course).

Set the ColumnCount property to 2 to include both columns, and the
ColumnWidths property to

0;1.5

to give zero width to the empid column and 1.5 inches to the concatenated
name.

Now the combo will STORE the ID but DISPLAY the supervisor's full name.

 

[Sam Y.]

Ok. I know that the column values can all be shown. But the full name will
only show when the combobox's dropdownlist is engaged. Is there a way to make
the bound value the ID and the displayed value the name? So if I selected a
person from the dropdownlist, the person's name will show up in the combobox,
but at the same time, the person's ID will be the value put into the actual
table.

What I mean is, I'm trying to basically get the supervisor's first and last
name to show up in a textbox after you choose from the combobox. The
dropdownlist is a combobox that has a list of supervisors but the bound
column is the empid. As far as I know, you can't show all the values in a
combobox dropdownlist (employee id, first name, last name) once you choose
from it. It will only show the employee id. That is why I want a textbox next
to it to display the full name.

You are mistaken! The combo box's bound column (the ID) need not be the
visible column. In fact it's typical to set the column widths property of that
column to zero, so that it's not displayed at all.

Try this: use a Query

SELECT EmpID, [FirstName] & " " & [LastName]
FROM Employees
WHERE Supervisor=True
ORDER BY FirstName, LastName;

(using your own tablename and appropriate criteria of course).

Set the ColumnCount property to 2 to include both columns, and the
ColumnWidths property to

0;1.5

to give zero width to the empid column and 1.5 inches to the concatenated
name.

Now the combo will STORE the ID but DISPLAY the supervisor's full name.

 

[Sam Y.]

I figured it out! I read the help description in the Access Developer
Reference for the
ComboBox.BoundColumn Property and it says that the leftmost column is the
value that shows up in the textbox portion of the combobox, so I just had to
flip your suggestion around. I made the name portion the first column and
then the emp_id the second column but hid that and then the bound column
value is set to 2. So the name will be selected and show, but the emp_id will
is stored in the actual table. Thanks for all the help and guiding me to my
solution!

Ok. I know that the column values can all be shown. But the full name will
only show when the combobox's dropdownlist is engaged. Is there a way to make
the bound value the ID and the displayed value the name? So if I selected a
person from the dropdownlist, the person's name will show up in the combobox,
but at the same time, the person's ID will be the value put into the actual
table.

What I mean is, I'm trying to basically get the supervisor's first and last
name to show up in a textbox after you choose from the combobox. The
dropdownlist is a combobox that has a list of supervisors but the bound
column is the empid. As far as I know, you can't show all the values in a
combobox dropdownlist (employee id, first name, last name) once you choose
from it. It will only show the employee id. That is why I want a textbox next
to it to display the full name.

You are mistaken! The combo box's bound column (the ID) need not be the
visible column. In fact it's typical to set the column widths property of that
column to zero, so that it's not displayed at all.

Try this: use a Query

SELECT EmpID, [FirstName] & " " & [LastName]
FROM Employees
WHERE Supervisor=True
ORDER BY FirstName, LastName;

(using your own tablename and appropriate criteria of course).

Set the ColumnCount property to 2 to include both columns, and the
ColumnWidths property to

0;1.5

to give zero width to the empid column and 1.5 inches to the concatenated
name.

Now the combo will STORE the ID but DISPLAY the supervisor's full name.

 

[John W. Vinson]

I figured it out! I read the help description in the Access Developer
Reference for the
ComboBox.BoundColumn Property and it says that the leftmost column is the
value that shows up in the textbox portion of the combobox, so I just had to
flip your suggestion around. I made the name portion the first column and
then the emp_id the second column but hid that and then the bound column
value is set to 2. So the name will be selected and show, but the emp_id will
is stored in the actual table. Thanks for all the help and guiding me to my
solution!

Actually if you had tried my solution, and/or read the help description more
carefully, you would have seen that it DOES work. It's the leftmost *non zero
width* column which is displayed when the combo is not dropped down. The bound
column can be to the left or right of the first visible column; so long as it
is concealed by setting its width to zero it won't be visible, dropped down or
not.

 

[Sam Y.]

Yes, you are right. I guess what you already knew didn't click in my brain
until I did it my way, which was basically your method, just flipped around a
bit. Thanks for the help MVP!