Form problem still not solved

[Guest]

Dear all,

In my database i have a form that i use to enter information. It has fields
like EMPLOYEE ID, NAME, TICKET NUMBER, TICKET COST. When i enter id it
automatically gives me the employees name. I need to add one more column HIRE
DATE. How do i make it to show the hire date automatically as it does with
NAME. The form updates table "tblTicket". The database has a table of all
employees info "ROSTER". I checked the form properties and it shows the same
table ("tblTicket") that it updates.

Your help will be greatly appreciated.

 

[Guest]

Are you entering the ID in a combo box? If not, where are you entering it,
and by what means does it automatically produce the employee's name? You
should probably be storing employee ID only, not employee name in every
record in tblTickets. What happens to your records if an employee's name
changes? It is difficult to be specific without knowing more about your
database structure.
In general, you can add a column to a combo box Row Source query, then
adjust the combo box's column count to change what appears in the combo box.
If you need Hire Date to appear on a form, if it is in the underlying table
or query you just need to add a bound text box to the form. If it is not in
the underlying table or query you have an unusual situation that requires
more explanation.

 

[Jeff Boyce]

Alish

Access considers "Name" a reserved word -- using it as a column title will
confuse both Access and you. Try something a bit more descriptive, perhaps
"EmployeeName"?

Without a bit more description of your underlying table structure, I'm
having trouble visualizing what you are trying to do...

 

[Sandra Daigle]

First, you might want to look at the AfterUpdate event of the EmployeeId
field to see how it is currently getting the the data from the Name field of
your Roster table. There are (at least) three ways to get related data when
you have the value of the join field (EmployeeID).

Method 1 - the easiest
--------------------------------
Use a combo box for EmployeeID and include the Employee Name and Hiredate
fields in the RowSource query of the combo. Make sure that the columnCount
property is at least 3. Now you can add textboxes to your form that have a
reference back to the combo to display the related values. The column
property is indexed starting at 0 so for example, in the ControlSource of
EmployeeName you would have:

= Employeeid.column(1)

Method 2 - ok for one field only

 

[Sandra Daigle]

One other thing, it's a good idea to keep an issue in one thread until the
issue has been resolved. This allows *everyone* to see where you started and
what progress has been made. It also allows someone else who might query the
archives of the newsgroups to see the full history of a thread and possibly
find solutions to their own issues. Most people using the newsgroups use an
offline newsreader and follow the threads in which they participate so it
doesn't really matter whether how long ago the thread was started.

Start new threads when a) you get *No* response after 2 or 3 days b) a new
or different problem develops out of the original issue.

For more guidelines on using the newsgroups please see
http://www.mvps.org/access/netiquette.htm.

 

[Guest]

Sandra, i created a query.in the query i linked the updatable table with the
ROSTER. it is working fine when it comes to showing the info automatically.
But it is not updating the original table. i want it to update the table
simultaniously.

P.S Can i use the same thread tomorrow?

First, you might want to look at the AfterUpdate event of the EmployeeId
field to see how it is currently getting the the data from the Name field of
your Roster table. There are (at least) three ways to get related data when
you have the value of the join field (EmployeeID).

Method 1 - the easiest
--------------------------------
Use a combo box for EmployeeID and include the Employee Name and Hiredate
fields in the RowSource query of the combo. Make sure that the columnCount
property is at least 3. Now you can add textboxes to your form that have a
reference back to the combo to display the related values. The column
property is indexed starting at 0 so for example, in the ControlSource of
EmployeeName you would have:

= Employeeid.column(1)

Method 2 - ok for one field only
-----------------------------------------
In the AfterUpdate event of the control use a dlookup to pull the related
value from the Roster table and assign that value to an unbound textbox.

Method 3 - ok but adds overhead and confusion
-------------------------------------------
Use and Autolookup query as I described in your earlier thread.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Dear all,

In my database i have a form that i use to enter information. It has
fields like EMPLOYEE ID, NAME, TICKET NUMBER, TICKET COST. When i
enter id it automatically gives me the employees name. I need to add
one more column HIRE DATE. How do i make it to show the hire date
automatically as it does with NAME. The form updates table
"tblTicket". The database has a table of all employees info "ROSTER".
I checked the form properties and it shows the same table
("tblTicket") that it updates.

Your help will be greatly appreciated.

 

[Guest]

according to my timezone it is reaching evening so i will have to check
tomorrow.

thanks a lot

First, you might want to look at the AfterUpdate event of the EmployeeId
field to see how it is currently getting the the data from the Name field of
your Roster table. There are (at least) three ways to get related data when
you have the value of the join field (EmployeeID).

Method 1 - the easiest
--------------------------------
Use a combo box for EmployeeID and include the Employee Name and Hiredate
fields in the RowSource query of the combo. Make sure that the columnCount
property is at least 3. Now you can add textboxes to your form that have a
reference back to the combo to display the related values. The column
property is indexed starting at 0 so for example, in the ControlSource of
EmployeeName you would have:

= Employeeid.column(1)

Method 2 - ok for one field only
-----------------------------------------
In the AfterUpdate event of the control use a dlookup to pull the related
value from the Roster table and assign that value to an unbound textbox.

Method 3 - ok but adds overhead and confusion
-------------------------------------------
Use and Autolookup query as I described in your earlier thread.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Dear all,

In my database i have a form that i use to enter information. It has
fields like EMPLOYEE ID, NAME, TICKET NUMBER, TICKET COST. When i
enter id it automatically gives me the employees name. I need to add
one more column HIRE DATE. How do i make it to show the hire date
automatically as it does with NAME. The form updates table
"tblTicket". The database has a table of all employees info "ROSTER".
I checked the form properties and it shows the same table
("tblTicket") that it updates.

Your help will be greatly appreciated.

 

[Sandra Daigle]

You really shouldn't be copying data from one table to another without a
*very* compelling reason. If you already have EmployeeID in the Tickets
table, then you can (and should) always link back to the Roster table to get
the related Name and HireDate. This is basic normalization of data. Here is
one justification for doing it this way, when the Employee gets married and
changes their last name, do you want to go to one table to change it or to
every table and change it. When properly normalized, the change is made to
one table.

It is possible that I am still not understanding your issue. When you post
back please include the SQL of your Recordsource query for the form, and
list any controls on your form plus the Controlsources of those controls.
Also include any code that might be in use on your form.

Absolutely you *can* use the same thread tomorrow. I've participated in
threads that went on for weeks. In fact I just replied to one thread that
had been stagnat for over a month.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sandra, i created a query.in the query i linked the updatable table
with the ROSTER. it is working fine when it comes to showing the info
automatically. But it is not updating the original table. i want it
to update the table simultaniously.

P.S Can i use the same thread tomorrow?

First, you might want to look at the AfterUpdate event of the
EmployeeId field to see how it is currently getting the the data
from the Name field of your Roster table. There are (at least) three
ways to get related data when you have the value of the join field
(EmployeeID).

Method 1 - the easiest
--------------------------------
Use a combo box for EmployeeID and include the Employee Name and
Hiredate fields in the RowSource query of the combo. Make sure that
the columnCount property is at least 3. Now you can add textboxes to
your form that have a reference back to the combo to display the
related values. The column property is indexed starting at 0 so for
example, in the ControlSource of EmployeeName you would have:

= Employeeid.column(1)

Method 2 - ok for one field only
-----------------------------------------
In the AfterUpdate event of the control use a dlookup to pull the
related value from the Roster table and assign that value to an
unbound textbox.

Method 3 - ok but adds overhead and confusion
-------------------------------------------
Use and Autolookup query as I described in your earlier thread.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Dear all,

In my database i have a form that i use to enter information. It has
fields like EMPLOYEE ID, NAME, TICKET NUMBER, TICKET COST. When i
enter id it automatically gives me the employees name. I need to add
one more column HIRE DATE. How do i make it to show the hire date
automatically as it does with NAME. The form updates table
"tblTicket". The database has a table of all employees info
"ROSTER". I checked the form properties and it shows the same table
("tblTicket") that it updates.

Your help will be greatly appreciated.

 

[Sandra Daigle]

No problem - until then.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

according to my timezone it is reaching evening so i will have to
check tomorrow.

thanks a lot

First, you might want to look at the AfterUpdate event of the
EmployeeId field to see how it is currently getting the the data
from the Name field of your Roster table. There are (at least) three
ways to get related data when you have the value of the join field
(EmployeeID).

Method 1 - the easiest
--------------------------------
Use a combo box for EmployeeID and include the Employee Name and
Hiredate fields in the RowSource query of the combo. Make sure that
the columnCount property is at least 3. Now you can add textboxes to
your form that have a reference back to the combo to display the
related values. The column property is indexed starting at 0 so for
example, in the ControlSource of EmployeeName you would have:

= Employeeid.column(1)

Method 2 - ok for one field only
-----------------------------------------
In the AfterUpdate event of the control use a dlookup to pull the
related value from the Roster table and assign that value to an
unbound textbox.

Method 3 - ok but adds overhead and confusion
-------------------------------------------
Use and Autolookup query as I described in your earlier thread.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Dear all,

In my database i have a form that i use to enter information. It has
fields like EMPLOYEE ID, NAME, TICKET NUMBER, TICKET COST. When i
enter id it automatically gives me the employees name. I need to add
one more column HIRE DATE. How do i make it to show the hire date
automatically as it does with NAME. The form updates table
"tblTicket". The database has a table of all employees info
"ROSTER". I checked the form properties and it shows the same table
("tblTicket") that it updates.

Your help will be greatly appreciated.

 

[Guest]

ok, as agreed below is the following info of the form:

RecordSource......tblTickets
Filter...................((tblTickets.Name="Joyce, Thomas"))

text box where i enter badge number
ControlSource......BadgeNumber

text box of name
ControlSource......Name

All fields are coming from the same table tblTickets. it has no links with
other queries or tables. Is there any way i can show the database to you so
you have clearer picture of what's going on?

thanks.
alish

You really shouldn't be copying data from one table to another without a
*very* compelling reason. If you already have EmployeeID in the Tickets
table, then you can (and should) always link back to the Roster table to get
the related Name and HireDate. This is basic normalization of data. Here is
one justification for doing it this way, when the Employee gets married and
changes their last name, do you want to go to one table to change it or to
every table and change it. When properly normalized, the change is made to
one table.

It is possible that I am still not understanding your issue. When you post
back please include the SQL of your Recordsource query for the form, and
list any controls on your form plus the Controlsources of those controls.
Also include any code that might be in use on your form.

Absolutely you *can* use the same thread tomorrow. I've participated in
threads that went on for weeks. In fact I just replied to one thread that
had been stagnat for over a month.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sandra, i created a query.in the query i linked the updatable table
with the ROSTER. it is working fine when it comes to showing the info
automatically. But it is not updating the original table. i want it
to update the table simultaniously.

P.S Can i use the same thread tomorrow?

First, you might want to look at the AfterUpdate event of the
EmployeeId field to see how it is currently getting the the data
from the Name field of your Roster table. There are (at least) three
ways to get related data when you have the value of the join field
(EmployeeID).

Method 1 - the easiest
--------------------------------
Use a combo box for EmployeeID and include the Employee Name and
Hiredate fields in the RowSource query of the combo. Make sure that
the columnCount property is at least 3. Now you can add textboxes to
your form that have a reference back to the combo to display the
related values. The column property is indexed starting at 0 so for
example, in the ControlSource of EmployeeName you would have:

= Employeeid.column(1)

Method 2 - ok for one field only
-----------------------------------------
In the AfterUpdate event of the control use a dlookup to pull the
related value from the Roster table and assign that value to an
unbound textbox.

Method 3 - ok but adds overhead and confusion
-------------------------------------------
Use and Autolookup query as I described in your earlier thread.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


alish wrote:
Dear all,

In my database i have a form that i use to enter information. It has
fields like EMPLOYEE ID, NAME, TICKET NUMBER, TICKET COST. When i
enter id it automatically gives me the employees name. I need to add
one more column HIRE DATE. How do i make it to show the hire date
automatically as it does with NAME. The form updates table
"tblTicket". The database has a table of all employees info
"ROSTER". I checked the form properties and it shows the same table
("tblTicket") that it updates.

Your help will be greatly appreciated.

 

[Guest]

ok, as agreed below is the following info of the form:

RecordSource......tblTickets
Filter...................((tblTickets.Name="Joyce, Thomas"))

text box where i enter badge number
ControlSource......BadgeNumber

text box of name
ControlSource......Name

All fields are coming from the same table tblTickets. it has no links with
other queries or tables. Is there any way i can show the database to you so
you have clearer picture of what's going on?

thanks.
alish

One other thing, it's a good idea to keep an issue in one thread until the
issue has been resolved. This allows *everyone* to see where you started and
what progress has been made. It also allows someone else who might query the
archives of the newsgroups to see the full history of a thread and possibly
find solutions to their own issues. Most people using the newsgroups use an
offline newsreader and follow the threads in which they participate so it
doesn't really matter whether how long ago the thread was started.

Start new threads when a) you get *No* response after 2 or 3 days b) a new
or different problem develops out of the original issue.

For more guidelines on using the newsgroups please see
http://www.mvps.org/access/netiquette.htm.


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Dear all,

In my database i have a form that i use to enter information. It has
fields like EMPLOYEE ID, NAME, TICKET NUMBER, TICKET COST. When i
enter id it automatically gives me the employees name. I need to add
one more column HIRE DATE. How do i make it to show the hire date
automatically as it does with NAME. The form updates table
"tblTicket". The database has a table of all employees info "ROSTER".
I checked the form properties and it shows the same table
("tblTicket") that it updates.

Your help will be greatly appreciated.

 

[Sandra Daigle]

Sorry for not getting back to you sooner. It sounds to me as though you
should change the textbox for BadgeNumber to a combo box which has a query
against the Roster Table as the rowsource. This query can include all the
fields that you want to see on this form including Hiredate. Then you can
set the ControlSource of the Name textbox to refer to a column in the combo,
and do likewise with the HireDate field.

This way the fields that belong in the Roster table, stay in the roster
table.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

ok, as agreed below is the following info of the form:

RecordSource......tblTickets
Filter...................((tblTickets.Name="Joyce, Thomas"))

text box where i enter badge number
ControlSource......BadgeNumber

text box of name
ControlSource......Name

All fields are coming from the same table tblTickets. it has no links
with other queries or tables. Is there any way i can show the
database to you so you have clearer picture of what's going on?

thanks.
alish

You really shouldn't be copying data from one table to another
without a *very* compelling reason. If you already have EmployeeID
in the Tickets table, then you can (and should) always link back to
the Roster table to get the related Name and HireDate. This is basic
normalization of data. Here is one justification for doing it this
way, when the Employee gets married and changes their last name, do
you want to go to one table to change it or to every table and
change it. When properly normalized, the change is made to one table.

It is possible that I am still not understanding your issue. When
you post back please include the SQL of your Recordsource query for
the form, and list any controls on your form plus the Controlsources
of those controls. Also include any code that might be in use on
your form.

Absolutely you *can* use the same thread tomorrow. I've participated
in threads that went on for weeks. In fact I just replied to one
thread that had been stagnat for over a month.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sandra, i created a query.in the query i linked the updatable table
with the ROSTER. it is working fine when it comes to showing the
info automatically. But it is not updating the original table. i
want it to update the table simultaniously.

P.S Can i use the same thread tomorrow?

:

First, you might want to look at the AfterUpdate event of the
EmployeeId field to see how it is currently getting the the data
from the Name field of your Roster table. There are (at least)
three ways to get related data when you have the value of the join
field (EmployeeID).

Method 1 - the easiest
--------------------------------
Use a combo box for EmployeeID and include the Employee Name and
Hiredate fields in the RowSource query of the combo. Make sure that
the columnCount property is at least 3. Now you can add textboxes
to your form that have a reference back to the combo to display the
related values. The column property is indexed starting at 0 so for
example, in the ControlSource of EmployeeName you would have:

= Employeeid.column(1)

Method 2 - ok for one field only
-----------------------------------------
In the AfterUpdate event of the control use a dlookup to pull the
related value from the Roster table and assign that value to an
unbound textbox.

Method 3 - ok but adds overhead and confusion
-------------------------------------------
Use and Autolookup query as I described in your earlier thread.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


alish wrote:
Dear all,

In my database i have a form that i use to enter information. It
has fields like EMPLOYEE ID, NAME, TICKET NUMBER, TICKET COST.
When i enter id it automatically gives me the employees name. I
need to add one more column HIRE DATE. How do i make it to show
the hire date automatically as it does with NAME. The form
updates table "tblTicket". The database has a table of all
employees info "ROSTER". I checked the form properties and it
shows the same table ("tblTicket") that it updates.

Your help will be greatly appreciated.