Create new record from combo box in a form

[Cudamike]

I have an input form with a combo box that is filled by a query. I want to
select the results and have it fill in a new record with the selected used
ID, Last,First, middle name and date of hire information into the new record
so it do not have to be retyped. Presently if I select the used ID from the
combo box it will bring up the first record with that information listed in
it. Any ideas were I went wrong?

Thanks in advance

 

[Jeff Boyce]

I don't have a very clear picture of what's happening, but ...

It sounds like you are trying to copy ID, Last, First ,... (several fields)
from one table to another. Why?!

Store ONLY the ID, then use a query to "reconnect" the Last, First, ...
values from the table they are already stored in.

Or am I simply not understanding...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Cudamike]

Hi Jeff,
Thanks for the fast reply. What I am trying to do is create a form were I
have two combo boxes that some one can select user information by Employee ID
or Last name and have that selection of data probagate to a new record with
the user information filled in and just have the user input the rest of the
required information. Let me know if you need any additional informatuion.

 

[Jeff Boyce]

Let me re-state my question ... why? Why do you want to propagate data that
you already have stored?

What will having a duplicate copy of (some/much of) data allow you to do
(i.e., why are you duplicating data)?

I'm not asking out of idle curiosity, but because there may be a better
way... (if we only knew what the business need is...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Cudamike]

Hi Jeff,
What I started to make was a simple DB that we could use to keep track of
PTO (personal time off). They wanted to be able to add full week tracking and
single days scheduled off and query by week, Work Group and several other
criterias. There is about 200 employees that need to be entered into the
database. I am still trying to deside if I should do it in Access with two
tables. One for the repetive user information that does not change and one
just to store the weeks and the single day counts and create a relationship.
The other thought is maybe this would be better suited to be done in Excel. I
invite you thoughts in the matter.

Mike

 

[Jeff Boyce]

Mike

It will surely depend on what skills/experience you have. If you have time
in already on Excel, it might be simpler and faster to do it there.

Access is a relational database, requires a different way of thinking to use
effectively, and has something of a steep learning curve.

You definitely do NOT want to enter "repetitive" data in an Access table
(but I understand that's what you'd be doing in Excel).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Cudamike]

Hi Jeff,
Well I would like to do it in Access and I have tried several different ways
of breaking up the table since I read your last reply but I would like to try
it if you can help point me in the right direction. Firt let me show you what
the table information contains and maybe you can offer a suggestion.

Employee#, Fist, Mid,Last names, Date of Hire, PTO Manager, work group This
information will not change and be repetive. The data fields that will change
will be Vacation week Start Date, Vacation Week end date, and and Single
days throughout the year that they will take off. I made the Employee# the
primery key because it is a non duplicated data. Can you offer an idea which
would be the best way to proceed, I would be greatful.

 

[Jeff Boyce]

Mike

comments in-line below...

Hi Jeff,
Well I would like to do it in Access and I have tried several different
ways
of breaking up the table since I read your last reply but I would like to
try
it if you can help point me in the right direction. Firt let me show you
what
the table information contains and maybe you can offer a suggestion.

Employee#, Fist, Mid,Last names, Date of Hire, PTO Manager, work group
This
information will not change and be repetive.

Date of Hire, Manager, Work Group may apply to a person, but these are NOT
usually attributes of a person in the same way a name or date-of-birth are.

You're claiming that once a person is hired, they are never (re-)hired into
a new work group, and their Manager never changes ... really?! Where do you
work?

The data fields that will change
will be Vacation week Start Date, Vacation Week end date, and and Single
days throughout the year that they will take off.

Why? As in "why would these 'change'?" Are you saying you have no interest
in keeping historical information about when someone was scheduled for
vacation last year? Will the HR folks in your company agree with that?

Also, if the employee isn't there, s/he isn't there. Does it matter whether
they are "vacation" days or "single" days? If is does, (see below)...

I made the Employee# the
primery key because it is a non duplicated data. Can you offer an idea
which
would be the best way to proceed, I would be greatful.

I know that I don't have a very clear picture of your situation yet, so the
following is just a very rough outline...

I suspect you could use tables like:

tblPerson
PersonID
FirstName
LastName
DOB

tlkpRole (e.g. for Managers vs. ...)
RoleID
RoleTitle
RoleDescription

trelEmployee
EmployeeID
PersonID
RoleID
FromDate
ToDate
ManagerID (this would be the PersonID of the person who is this
employee's manager)

trelVacation
VacationID
EmployeeID
StartDate
EndDate
Vacation (a yes/no field, in case you need to differentiate between
vacation and "single")

Now, how does this fit with what you are actually working with?

Regards

Jeff Boyce
Microsoft Office/Access MVP