Display data based on selction from combo box

[dntews]

I have what should be a simple question but have been reading and trying for
days and can't get it to work. I am using access 2007. Have a simple table
with empname and empid. Created a form with empname in unbound combo box with
"SELECT Employees.EmpName, Employees.id FROM Employees; " in rowsource. Form
is bound to employees table with "Select * from Employees" in recordsource.
EmpID is a text box has control source of "ID". AfterUpdate event on combo
is

Me.RecordsetClone.FindFirst "[EmpName] = " & Me![Combo0]
Me.Bookmark = Me.RecordsetClone.Bookmark

When I click on combo and select new name from dropdown the ID text box does
not change

PLEASE help me this silly thing to work. When I select a name in the combo I
want the data on the form to refresh.I plan to expand this but can't even gt
this to work.

Would greatly appreciate any help I can get

 

[Mr. B]

dntews,

You will be better off if you use the "Employees.id" field to locate
records. The reason for this is that this is the unique value for that
record and it is a number type field so you do not have to deal with all of
the problems with text type characters.

Normally we see the Id field as the first column in a combo box. This does
not mean that it has to be that way. If the "Employees.id" field was the
first field in your SELECT statement then you would have the following
properties set for your combo box:
Column Count = 2 'this tells the combo box that is has two fields
Column Widths = 0";2" ' the zero tells the combo box to hide the first
column
Bount Column = 1

If you want to leave your SELECT statment that you are using to produce your
list for your combo box as it is, you can set the properties of your combo
box as follows and it will function just as it would if you changed the
SELECT statment to have the "Employees.id" field as the first column:
Column Count = 2
Column Widths = 2";0"
Bound Column = 2

Next, to locate the selected record, first, I am assuming that you have a
text box type control on your form that is bound to the "Employees.id" field.
This text box should have its Visible property set to No. I would also
suggest that you provide a more descriptive name for your combo box. Combo0
would not mean much if you saw it in a list, but if you renamed it to
something like, "cboEmployees" then you would recogonize that name easier.
For now, I will refer to the combo box using the name you have on it right
now. If you decide to change the name, you will need to change the current
name in the code below to whatever you rename it to.

Use the following code in the After Update event of your combo box:

If Me.Combo0 > 0 then 'makes sure a value has been selected in the combo box
'declare a variable to hold the selected Employee ID value
dim lngEmpId as long
'assign the selected EmployeeID to the variable
lngEmpID = Me.Combo0
'make the EmployeeID field control visible and set focus to it
With Me.NameOfEmployeeIDControl
.visible = true
.setfocus
end with
'actually locate the selected record
docmd.findRecord lngEmpID
'set the focus to the Employee Name field control or some other if you
like
me.NameOfEmployeeNameControl.setfocus
'make the EmployeeId combo box not visible any more
me.Combo0.visible = false
end if

I hope I have not confused you.

HTH

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

I have what should be a simple question but have been reading and trying for
days and can't get it to work. I am using access 2007. Have a simple table
with empname and empid. Created a form with empname in unbound combo box with
"SELECT Employees.EmpName, Employees.id FROM Employees; " in rowsource. Form
is bound to employees table with "Select * from Employees" in recordsource.
EmpID is a text box has control source of "ID". AfterUpdate event on combo
is

Me.RecordsetClone.FindFirst "[EmpName] = " & Me![Combo0]
Me.Bookmark = Me.RecordsetClone.Bookmark

When I click on combo and select new name from dropdown the ID text box does
not change

PLEASE help me this silly thing to work. When I select a name in the combo I
want the data on the form to refresh.I plan to expand this but can't even gt
this to work.

Would greatly appreciate any help I can get

 

[dntews via AccessMonster.com]

Thanks for your help. I understand your naming comments and agree but I have
been working on this so long I just didn't make it "neat".
I restructured the table and made the empid field first, number and primary
key and empname is second and text. I named the combo box cboempid and the
text box txtempname which is bound to employees!empname. Combo has SELECT
Employees.empid, employees.empname FROM Employees; in rowsource, Control
source is blank and bound column =1. column count = 2 and widths are 1"; 0".
Control source on txtempname = =[Employees]![EmpName]. The code on the
AfterUpdate event is now

If Me.cboempid > 0 Then
Dim lngEmpID As Long
lngEmpID = Me.cboempid
With Me.cboempid
Visible = True
SetFocus
End With
DoCmd.FindRecord lngEmpID
Me.txtempname.SetFocus
Me.cboempid.Visible = False
End If

When I run this I get the empid number in combo dropdown and "#NAME?" in text
box. When I select a new number the combo box changes but not the empname
I am new to Access but wrote the VB application that the City of Houston
uses to prepare the citywide annual operating budget for every dept, so I'm
not new to programming but can't get this to work. I have literally been
working on this for weeks, have bought 2 manuals, have asked this on 2 forums
and now you and can't get it to work. I REALLY appreciate anything you can do
to help me get over this

dntews,

You will be better off if you use the "Employees.id" field to locate
records. The reason for this is that this is the unique value for that
record and it is a number type field so you do not have to deal with all of
the problems with text type characters.

Normally we see the Id field as the first column in a combo box. This does
not mean that it has to be that way. If the "Employees.id" field was the
first field in your SELECT statement then you would have the following
properties set for your combo box:
Column Count = 2 'this tells the combo box that is has two fields
Column Widths = 0";2" ' the zero tells the combo box to hide the first
column
Bount Column = 1

If you want to leave your SELECT statment that you are using to produce your
list for your combo box as it is, you can set the properties of your combo
box as follows and it will function just as it would if you changed the
SELECT statment to have the "Employees.id" field as the first column:
Column Count = 2
Column Widths = 2";0"
Bound Column = 2

Next, to locate the selected record, first, I am assuming that you have a
text box type control on your form that is bound to the "Employees.id" field.
This text box should have its Visible property set to No. I would also
suggest that you provide a more descriptive name for your combo box. Combo0
would not mean much if you saw it in a list, but if you renamed it to
something like, "cboEmployees" then you would recogonize that name easier.
For now, I will refer to the combo box using the name you have on it right
now. If you decide to change the name, you will need to change the current
name in the code below to whatever you rename it to.

Use the following code in the After Update event of your combo box:

If Me.Combo0 > 0 then 'makes sure a value has been selected in the combo box
'declare a variable to hold the selected Employee ID value
dim lngEmpId as long
'assign the selected EmployeeID to the variable
lngEmpID = Me.Combo0
'make the EmployeeID field control visible and set focus to it
With Me.NameOfEmployeeIDControl
.visible = true
.setfocus
end with
'actually locate the selected record
docmd.findRecord lngEmpID
'set the focus to the Employee Name field control or some other if you
like
me.NameOfEmployeeNameControl.setfocus
'make the EmployeeId combo box not visible any more
me.Combo0.visible = false
end if

I hope I have not confused you.

HTH

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

I have what should be a simple question but have been reading and trying for
days and can't get it to work. I am using access 2007. Have a simple table

[quoted text clipped - 15 lines]

Would greatly appreciate any help I can get

 

[Mr. B]

You have the Column Widths backward.

Because the EmployeeID field is the first field in the select statement you
need a zero width for the first column so the ID field will be the value
returned by the control, but not show in the combobox list. Set the second
value to a wide enough value that you can see all of the data (Employee Name)
that is in it. This might be 2" and it might be 3". It just depends on what
the length of the data is.



-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

Thanks for your help. I understand your naming comments and agree but I have
been working on this so long I just didn't make it "neat".
I restructured the table and made the empid field first, number and primary
key and empname is second and text. I named the combo box cboempid and the
text box txtempname which is bound to employees!empname. Combo has SELECT
Employees.empid, employees.empname FROM Employees; in rowsource, Control
source is blank and bound column =1. column count = 2 and widths are 1"; 0".
Control source on txtempname = =[Employees]![EmpName]. The code on the
AfterUpdate event is now

If Me.cboempid > 0 Then
Dim lngEmpID As Long
lngEmpID = Me.cboempid
With Me.cboempid
.Visible = True
.SetFocus
End With
DoCmd.FindRecord lngEmpID
Me.txtempname.SetFocus
Me.cboempid.Visible = False
End If

When I run this I get the empid number in combo dropdown and "#NAME?" in text
box. When I select a new number the combo box changes but not the empname
I am new to Access but wrote the VB application that the City of Houston
uses to prepare the citywide annual operating budget for every dept, so I'm
not new to programming but can't get this to work. I have literally been
working on this for weeks, have bought 2 manuals, have asked this on 2 forums
and now you and can't get it to work. I REALLY appreciate anything you can do
to help me get over this

dntews,

You will be better off if you use the "Employees.id" field to locate
records. The reason for this is that this is the unique value for that
record and it is a number type field so you do not have to deal with all of
the problems with text type characters.

Normally we see the Id field as the first column in a combo box. This does
not mean that it has to be that way. If the "Employees.id" field was the
first field in your SELECT statement then you would have the following
properties set for your combo box:
Column Count = 2 'this tells the combo box that is has two fields
Column Widths = 0";2" ' the zero tells the combo box to hide the first
column
Bount Column = 1

If you want to leave your SELECT statment that you are using to produce your
list for your combo box as it is, you can set the properties of your combo
box as follows and it will function just as it would if you changed the
SELECT statment to have the "Employees.id" field as the first column:
Column Count = 2
Column Widths = 2";0"
Bound Column = 2

Next, to locate the selected record, first, I am assuming that you have a
text box type control on your form that is bound to the "Employees.id" field.
This text box should have its Visible property set to No. I would also
suggest that you provide a more descriptive name for your combo box. Combo0
would not mean much if you saw it in a list, but if you renamed it to
something like, "cboEmployees" then you would recogonize that name easier.
For now, I will refer to the combo box using the name you have on it right
now. If you decide to change the name, you will need to change the current
name in the code below to whatever you rename it to.

Use the following code in the After Update event of your combo box:

If Me.Combo0 > 0 then 'makes sure a value has been selected in the combo box
'declare a variable to hold the selected Employee ID value
dim lngEmpId as long
'assign the selected EmployeeID to the variable
lngEmpID = Me.Combo0
'make the EmployeeID field control visible and set focus to it
With Me.NameOfEmployeeIDControl
.visible = true
.setfocus
end with
'actually locate the selected record
docmd.findRecord lngEmpID
'set the focus to the Employee Name field control or some other if you
like
me.NameOfEmployeeNameControl.setfocus
'make the EmployeeId combo box not visible any more
me.Combo0.visible = false
end if

I hope I have not confused you.

HTH

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

I have what should be a simple question but have been reading and trying for
days and can't get it to work. I am using access 2007. Have a simple table

[quoted text clipped - 15 lines]

Would greatly appreciate any help I can get

 

[dntews via AccessMonster.com]

But the combobox is supposed toi show the ID. The empname is supposed to show
in the textbox. I want to click the dropdown on the combo, pick an ID number
from the list and have that ID number show in the combo bo and the employee
name related to that ID number show in the textbox

Thanks for your help. I understand your naming comments and agree but I have
been working on this so long I just didn't make it "neat".
I restructured the table and made the empid field first, number and primary
key and empname is second and text. I named the combo box cboempid and the
text box txtempname which is bound to employees!empname. Combo has SELECT
Employees.empid, employees.empname FROM Employees; in rowsource, Control
source is blank and bound column =1. column count = 2 and widths are 1"; 0".
Control source on txtempname = =[Employees]![EmpName]. The code on the
AfterUpdate event is now

If Me.cboempid > 0 Then
Dim lngEmpID As Long
lngEmpID = Me.cboempid
With Me.cboempid
.Visible = True
.SetFocus
End With
DoCmd.FindRecord lngEmpID
Me.txtempname.SetFocus
Me.cboempid.Visible = False
End If

When I run this I get the empid number in combo dropdown and "#NAME?" in text
box. When I select a new number the combo box changes but not the empname
I am new to Access but wrote the VB application that the City of Houston
uses to prepare the citywide annual operating budget for every dept, so I'm
not new to programming but can't get this to work. I have literally been
working on this for weeks, have bought 2 manuals, have asked this on 2 forums
and now you and can't get it to work. I REALLY appreciate anything you can do
to help me get over this

dntews,

[quoted text clipped - 67 lines]

 

[Mr. B]

I think I may have misunderstood what you are wanting to do.

If your combo box an unbound control that you want to use to locate a record
in your form that is bound to the Employees table?

In either case, normally you would not expect any use to remember an ID
number but rather you would expect them to remember and select the employee
by name. That is the reason that you have the name show in the combo box but
have the Id as the bound column.

Let me know the answer to my question above and I will try my best to help.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

But the combobox is supposed toi show the ID. The empname is supposed to show
in the textbox. I want to click the dropdown on the combo, pick an ID number
from the list and have that ID number show in the combo bo and the employee
name related to that ID number show in the textbox

Thanks for your help. I understand your naming comments and agree but I have
been working on this so long I just didn't make it "neat".
I restructured the table and made the empid field first, number and primary
key and empname is second and text. I named the combo box cboempid and the
text box txtempname which is bound to employees!empname. Combo has SELECT
Employees.empid, employees.empname FROM Employees; in rowsource, Control
source is blank and bound column =1. column count = 2 and widths are 1"; 0".
Control source on txtempname = =[Employees]![EmpName]. The code on the
AfterUpdate event is now

If Me.cboempid > 0 Then
Dim lngEmpID As Long
lngEmpID = Me.cboempid
With Me.cboempid
.Visible = True
.SetFocus
End With
DoCmd.FindRecord lngEmpID
Me.txtempname.SetFocus
Me.cboempid.Visible = False
End If

When I run this I get the empid number in combo dropdown and "#NAME?" in text
box. When I select a new number the combo box changes but not the empname
I am new to Access but wrote the VB application that the City of Houston
uses to prepare the citywide annual operating budget for every dept, so I'm
not new to programming but can't get this to work. I have literally been
working on this for weeks, have bought 2 manuals, have asked this on 2 forums
and now you and can't get it to work. I REALLY appreciate anything you can do
to help me get over this

dntews,

[quoted text clipped - 67 lines]

Would greatly appreciate any help I can get

 

[dntews via AccessMonster.com]

Sorry if I didn't make myself clear. I am using this simple database and form
to try to get this to work and will expand it to more fields later.
What I am trying to do is have a form that will display all the info for a
particular record. In my test case, I want to display both the employee ID
and name on a form. I put a combo box on the form to hold the field I will
use for lookup - in this case the empname. When I click on the dropdown on
the CB, all the employee names appear. When I click on "Bill" from the list,
I want Bill to show in the CB and his ID number in the textbox. When I click
the CB again and click on "Mary", her name will show in the CB and her ID
will show in the textbox. I want to be able to select whomever I want and be
able to change data pertaining to them, but of course not the key field which
is the lookup field. I have been told to use the wizard to create the CB and
choose the third option from the wizard but my wizard only offers 2 options -
look up data in a table or I will enter the data myself. Is it possible there
is a 'bug' in 2007 or is there maybe something in the setup that I missed?
Thanks again for all your help. I really appreciate it

But the combobox is supposed toi show the ID. The empname is supposed to show
in the textbox. I want to click the dropdown on the combo, pick an ID number
from the list and have that ID number show in the combo bo and the employee
name related to that ID number show in the textbox

Thanks for your help. I understand your naming comments and agree but I have
been working on this so long I just didn't make it "neat".

[quoted text clipped - 31 lines]

 

[Mr. B]

dntews,

I have put together a small demo of using combo boxes to find and/or filter
records in a form. Because I cannot attach any files here, I have placed
this demo database file on the Download page of my website at:
http://www.askdoctoraccess.com/DownloadPage.htm

Scroll down to the bottom of the page and you will find where you can
download the "DemoFindAndfilterUsingComboBoxes.zip" file.

HTH


-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

Sorry if I didn't make myself clear. I am using this simple database and form
to try to get this to work and will expand it to more fields later.
What I am trying to do is have a form that will display all the info for a
particular record. In my test case, I want to display both the employee ID
and name on a form. I put a combo box on the form to hold the field I will
use for lookup - in this case the empname. When I click on the dropdown on
the CB, all the employee names appear. When I click on "Bill" from the list,
I want Bill to show in the CB and his ID number in the textbox. When I click
the CB again and click on "Mary", her name will show in the CB and her ID
will show in the textbox. I want to be able to select whomever I want and be
able to change data pertaining to them, but of course not the key field which
is the lookup field. I have been told to use the wizard to create the CB and
choose the third option from the wizard but my wizard only offers 2 options -
look up data in a table or I will enter the data myself. Is it possible there
is a 'bug' in 2007 or is there maybe something in the setup that I missed?
Thanks again for all your help. I really appreciate it

But the combobox is supposed toi show the ID. The empname is supposed to show
in the textbox. I want to click the dropdown on the combo, pick an ID number
from the list and have that ID number show in the combo bo and the employee
name related to that ID number show in the textbox

Thanks for your help. I understand your naming comments and agree but I have
been working on this so long I just didn't make it "neat".

[quoted text clipped - 31 lines]

Would greatly appreciate any help I can get

 

[dntews via AccessMonster.com]

I have donwloaded this file and opened the database. When I click on the
dropdown of any of the combo boxes and select a name or city or state,
nothing happens. The name in the box I click on changes but none of the info
in the text boxes changes nor can I edit any of the data that is there

Sorry if I didn't make myself clear. I am using this simple database and form
to try to get this to work and will expand it to more fields later.
What I am trying to do is have a form that will display all the info for a
particular record. In my test case, I want to display both the employee ID
and name on a form. I put a combo box on the form to hold the field I will
use for lookup - in this case the empname. When I click on the dropdown on
the CB, all the employee names appear. When I click on "Bill" from the list,
I want Bill to show in the CB and his ID number in the textbox. When I click
the CB again and click on "Mary", her name will show in the CB and her ID
will show in the textbox. I want to be able to select whomever I want and be
able to change data pertaining to them, but of course not the key field which
is the lookup field. I have been told to use the wizard to create the CB and
choose the third option from the wizard but my wizard only offers 2 options -
look up data in a table or I will enter the data myself. Is it possible there
is a 'bug' in 2007 or is there maybe something in the setup that I missed?
Thanks again for all your help. I really appreciate it

But the combobox is supposed toi show the ID. The empname is supposed to show
in the textbox. I want to click the dropdown on the combo, pick an ID number

[quoted text clipped - 6 lines]

 

[Mr. B]

I just downloaded the file from the website just like you did and it works
just as designed for me here.

Are you perhaps opening it with Access 2007? If so, make sure that you have
sesignated the folder where you have the file as a "Trusted Location".

That is the only thing that I can think of that would cause anything like
this.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

I have donwloaded this file and opened the database. When I click on the
dropdown of any of the combo boxes and select a name or city or state,
nothing happens. The name in the box I click on changes but none of the info
in the text boxes changes nor can I edit any of the data that is there

Sorry if I didn't make myself clear. I am using this simple database and form
to try to get this to work and will expand it to more fields later.
What I am trying to do is have a form that will display all the info for a
particular record. In my test case, I want to display both the employee ID
and name on a form. I put a combo box on the form to hold the field I will
use for lookup - in this case the empname. When I click on the dropdown on
the CB, all the employee names appear. When I click on "Bill" from the list,
I want Bill to show in the CB and his ID number in the textbox. When I click
the CB again and click on "Mary", her name will show in the CB and her ID
will show in the textbox. I want to be able to select whomever I want and be
able to change data pertaining to them, but of course not the key field which
is the lookup field. I have been told to use the wizard to create the CB and
choose the third option from the wizard but my wizard only offers 2 options -
look up data in a table or I will enter the data myself. Is it possible there
is a 'bug' in 2007 or is there maybe something in the setup that I missed?
Thanks again for all your help. I really appreciate it

But the combobox is supposed toi show the ID. The empname is supposed to show
in the textbox. I want to click the dropdown on the combo, pick an ID number

[quoted text clipped - 6 lines]

Would greatly appreciate any help I can get

 

[dntews via AccessMonster.com]

I-T W-O-R-K-S!!!!
Can't thank you enough. I have literally been working this thing over 2 weeks.
Apparently whole problem was Trust not set right. I had the Access folder
trusted but didn't know I needed to trust subfolders. Trusted all of them,
copied your code to my cbo, edited it to my control names and it actually
worked. I moved the CB to the form header and when I left the form and came
back it was gone. So when I went to recreate it, the wizard came up with the
third option I'd never seen before but kept being told I should use. Anyway,
I'm off again, so thanks very, very much

I have donwloaded this file and opened the database. When I click on the
dropdown of any of the combo boxes and select a name or city or state,
nothing happens. The name in the box I click on changes but none of the info
in the text boxes changes nor can I edit any of the data that is there

Sorry if I didn't make myself clear. I am using this simple database and form
to try to get this to work and will expand it to more fields later.

[quoted text clipped - 18 lines]