A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to use a combo box in a bound form?



 
 
Thread Tools Display Modes
  #1  
Old May 2nd, 2008, 05:33 PM posted to microsoft.public.access.forms
Billy Smith
external usenet poster
 
Posts: 25
Default How to use a combo box in a bound form?

In an Access 2000 application, I have a table of records and I want to
create a
form that is bound to a query of that table such that the user can edit the
records.
Specifically, one field is EmployeeID, a numeric field.

I want the user to be able to change the employee ID in the record only by
selecting the employee name from a combobox. I can easily get the employee
names and IDs from a table and put them into a combobox.

If I were to let the user change the EmployeeID via a text box it would be
easy
to bind the text box to the EmployeeID field. But using a combo box with
names
linked to employee IDs is indirect. Giving the EmployeeID field in the
table a
lookup control is not an option because I'm afraid that would mess up the
many other
queries and forms that currently use the field as it is.

I know how to use an invisible bound column in a combobox so I know I can
get the ID
when the user selects an employee in the combobox. But how can I link the
ID value to
the database table so the value gets changed in the record?

I'm fluent in VBA for Access. Thanks in advance.


  #2  
Old May 2nd, 2008, 05:44 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default How to use a combo box in a bound form?

I would suggest you use an unbound combo to look up the employee name and ID
and a bound text box to allow the user to change the employee id.

Since the text box is bound, it will contain the current employee id and the
user can then change it.

If the employee id is the record's primary key and there are any child
tables like Payroll info, etc. This could be very dangerous.
--
Dave Hargis, Microsoft Access MVP


"Billy Smith" wrote:

In an Access 2000 application, I have a table of records and I want to
create a
form that is bound to a query of that table such that the user can edit the
records.
Specifically, one field is EmployeeID, a numeric field.

I want the user to be able to change the employee ID in the record only by
selecting the employee name from a combobox. I can easily get the employee
names and IDs from a table and put them into a combobox.

If I were to let the user change the EmployeeID via a text box it would be
easy
to bind the text box to the EmployeeID field. But using a combo box with
names
linked to employee IDs is indirect. Giving the EmployeeID field in the
table a
lookup control is not an option because I'm afraid that would mess up the
many other
queries and forms that currently use the field as it is.

I know how to use an invisible bound column in a combobox so I know I can
get the ID
when the user selects an employee in the combobox. But how can I link the
ID value to
the database table so the value gets changed in the record?

I'm fluent in VBA for Access. Thanks in advance.



  #3  
Old May 2nd, 2008, 08:10 PM posted to microsoft.public.access.forms
Billy Smith
external usenet poster
 
Posts: 25
Default How to use a combo box in a bound form?

Klatuu,

Thanks for you reply. The employee id is not the primary key of the table
I'm changing so that is not a problem

It sounds like you're suggesting that the user get the ID from a combo box
and then manually transfer it to the bound text box. I was hoping for an
automated procedu the combo box comes up with the current employee name
selected. If the user selects another employee from the list in the combo
box then the ID in the record is changed with no other action by the user.
Is there not a way to connect the combo box and the text box so that this
happens?

I've thought about using an invisible text box bound to the ID and then, in
the combo box event procedure code write the ID to the invisible text box.
I think that will work but I was hoping for something built in that would do
this.

Thanks.

"Klatuu" wrote in message
...
I would suggest you use an unbound combo to look up the employee name and
ID
and a bound text box to allow the user to change the employee id.

Since the text box is bound, it will contain the current employee id and
the
user can then change it.

If the employee id is the record's primary key and there are any child
tables like Payroll info, etc. This could be very dangerous.
--
Dave Hargis, Microsoft Access MVP


"Billy Smith" wrote:

In an Access 2000 application, I have a table of records and I want to
create a
form that is bound to a query of that table such that the user can edit
the
records.
Specifically, one field is EmployeeID, a numeric field.

I want the user to be able to change the employee ID in the record only
by
selecting the employee name from a combobox. I can easily get the
employee
names and IDs from a table and put them into a combobox.

If I were to let the user change the EmployeeID via a text box it would
be
easy
to bind the text box to the EmployeeID field. But using a combo box with
names
linked to employee IDs is indirect. Giving the EmployeeID field in the
table a
lookup control is not an option because I'm afraid that would mess up the
many other
queries and forms that currently use the field as it is.

I know how to use an invisible bound column in a combobox so I know I can
get the ID
when the user selects an employee in the combobox. But how can I link
the
ID value to
the database table so the value gets changed in the record?

I'm fluent in VBA for Access. Thanks in advance.





  #4  
Old May 5th, 2008, 03:14 PM posted to microsoft.public.access.forms
Klatuu
external usenet poster
 
Posts: 7,074
Default How to use a combo box in a bound form?

Use the After Update event of the combo to populate the text box:

Me.MyTextBox = Me.MyComboBox
--
Dave Hargis, Microsoft Access MVP


"Billy Smith" wrote:

Klatuu,

Thanks for you reply. The employee id is not the primary key of the table
I'm changing so that is not a problem

It sounds like you're suggesting that the user get the ID from a combo box
and then manually transfer it to the bound text box. I was hoping for an
automated procedu the combo box comes up with the current employee name
selected. If the user selects another employee from the list in the combo
box then the ID in the record is changed with no other action by the user.
Is there not a way to connect the combo box and the text box so that this
happens?

I've thought about using an invisible text box bound to the ID and then, in
the combo box event procedure code write the ID to the invisible text box.
I think that will work but I was hoping for something built in that would do
this.

Thanks.

"Klatuu" wrote in message
...
I would suggest you use an unbound combo to look up the employee name and
ID
and a bound text box to allow the user to change the employee id.

Since the text box is bound, it will contain the current employee id and
the
user can then change it.

If the employee id is the record's primary key and there are any child
tables like Payroll info, etc. This could be very dangerous.
--
Dave Hargis, Microsoft Access MVP


"Billy Smith" wrote:

In an Access 2000 application, I have a table of records and I want to
create a
form that is bound to a query of that table such that the user can edit
the
records.
Specifically, one field is EmployeeID, a numeric field.

I want the user to be able to change the employee ID in the record only
by
selecting the employee name from a combobox. I can easily get the
employee
names and IDs from a table and put them into a combobox.

If I were to let the user change the EmployeeID via a text box it would
be
easy
to bind the text box to the EmployeeID field. But using a combo box with
names
linked to employee IDs is indirect. Giving the EmployeeID field in the
table a
lookup control is not an option because I'm afraid that would mess up the
many other
queries and forms that currently use the field as it is.

I know how to use an invisible bound column in a combobox so I know I can
get the ID
when the user selects an employee in the combobox. But how can I link
the
ID value to
the database table so the value gets changed in the record?

I'm fluent in VBA for Access. Thanks in advance.






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.