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 » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Auto Fill



 
 
Thread Tools Display Modes
  #1  
Old February 25th, 2010, 10:46 PM posted to microsoft.public.access.gettingstarted
Amy
external usenet poster
 
Posts: 412
Default Auto Fill

I'm trying to enter employee training information. I want to be able to type
in an identifier (last name or employee number) and it will bring up
information showing what training they received, and the date they received
it. I know I have to make a table with all the information I need, but that's
where I'm stuck. I dont' know what to use (form, query, etc.) and I don't
know how to set any of that up. Basically, I'm totally lost

Any help is GREATLY appreciated!
  #2  
Old February 26th, 2010, 01:35 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Auto Fill

On Thu, 25 Feb 2010 13:46:06 -0800, Amy wrote:

I'm trying to enter employee training information. I want to be able to type
in an identifier (last name or employee number) and it will bring up
information showing what training they received, and the date they received
it. I know I have to make a table with all the information I need, but that's
where I'm stuck. I dont' know what to use (form, query, etc.) and I don't
know how to set any of that up. Basically, I'm totally lost

Any help is GREATLY appreciated!


The data would be *stored* in a table; selected and organized in a Query; and
*displayed* on a Form. You will need at least three tables: Employees (with
the employee ID, last name, first name, and other biographical data); Courses,
a list of all the training courses available, with CourseID, CourseName, and
other info about the course; and CoursesTaken, with fields for the EmployeeID,
CourseID, and other info about this employee's participation in this course
(e.g. date taken, satisfactor/unsatisfactory performance, etc.)

For some resources (including a couple of sample training applications) see
the links at these sites:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
  #3  
Old March 2nd, 2010, 03:34 PM posted to microsoft.public.access.gettingstarted
Amy
external usenet poster
 
Posts: 412
Default Auto Fill

Thank you for helping! Here is my next problem
I have created the tables to store all the information. My question now, is
how do I get it all to connect? I have created a query and a form but I don't
think I did it right. I would like to be able to type in the employee's name
and have all the training they have completed show up.

If I type information such as employee name and course into a form, how is
the data stored? Is it saved somewhere?

"John W. Vinson" wrote:

On Thu, 25 Feb 2010 13:46:06 -0800, Amy wrote:

I'm trying to enter employee training information. I want to be able to type
in an identifier (last name or employee number) and it will bring up
information showing what training they received, and the date they received
it. I know I have to make a table with all the information I need, but that's
where I'm stuck. I dont' know what to use (form, query, etc.) and I don't
know how to set any of that up. Basically, I'm totally lost

Any help is GREATLY appreciated!


The data would be *stored* in a table; selected and organized in a Query; and
*displayed* on a Form. You will need at least three tables: Employees (with
the employee ID, last name, first name, and other biographical data); Courses,
a list of all the training courses available, with CourseID, CourseName, and
other info about the course; and CoursesTaken, with fields for the EmployeeID,
CourseID, and other info about this employee's participation in this course
(e.g. date taken, satisfactor/unsatisfactory performance, etc.)

For some resources (including a couple of sample training applications) see
the links at these sites:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
.

  #4  
Old March 2nd, 2010, 07:41 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Auto Fill

Your form should be based on a query on the CoursesTaken table which
references a combo box of employees on the same form. I'll come back to the
combo box later, but the query would be:

SELECT *
FROM [CoursesTaken]
WHERE [EmployeeID] = Form![cboEmployee];

Make the form a continuous form. If you use the form wizard to create it
select 'tabular' as the layout. You only need controls in the form for the
CourseID and other columns like the date taken etc, not for the EmployeeID.

In the form make the control bound to the CourseID column a combo box set up
like this:

ControlSource: CourseID

RowSource: SELECT [CourseID], [CourseName]FROM [Courses] ORDER BY
[CourseName];

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first dimension
is zero to hide the first column and that the second is at least as wide as
the combo box.

In the form's header add a combo box and set it up like this:

Name: cboEmployee

ControlSource: Leave this blank

RowSource: SELECT [EmployeeID], [FirstName] & " " & [LastName] FROM
[Employees] ORDER BY [LastName], [FirstName];

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

In the AfterUpdate event procedure of this combo box put:

Me.Requery

This requeries the form so it shows only the courses for the selected
employee. If you are unfamiliar with entering code in event procedures you
do this by selecting the cboEmployee combo box in form design view and
opening its properties sheet if its not already open. Then select the After
Update event property in the properties sheet. Click on the 'build' button;
that's the one on the right with 3 dots. Select 'Code Builder' in the
dialogue, and click OK. The VBA window will open at the event procedure with
the first and last lines already in place. Enter the line of code between
these two existing lines.

When the form opens it will not show any records. Select an employee from
the combo box and it should then show any courses taken by the employee.
You can add a new attendance at a course for the current employee by adding a
new record in the blank row below the existing course attendance records
simply by selecting a course in the combo box and entering the date taken etc
as appropriate.

Ken Sheridan
Stafford, England

Amy wrote:
Thank you for helping! Here is my next problem
I have created the tables to store all the information. My question now, is
how do I get it all to connect? I have created a query and a form but I don't
think I did it right. I would like to be able to type in the employee's name
and have all the training they have completed show up.

If I type information such as employee name and course into a form, how is
the data stored? Is it saved somewhere?

I'm trying to enter employee training information. I want to be able to type
in an identifier (last name or employee number) and it will bring up

[quoted text clipped - 33 lines]
MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201003/1

  #5  
Old March 2nd, 2010, 09:05 PM posted to microsoft.public.access.gettingstarted
Amy
external usenet poster
 
Posts: 412
Default Auto Fill

Thanks for your help. But is there any way you could dumb it down for me?
I am unfamiliar with entering any kind of code. Your help is SOOO much
appreciated!!!

"KenSheridan via AccessMonster.com" wrote:

Your form should be based on a query on the CoursesTaken table which
references a combo box of employees on the same form. I'll come back to the
combo box later, but the query would be:

SELECT *
FROM [CoursesTaken]
WHERE [EmployeeID] = Form![cboEmployee];

Make the form a continuous form. If you use the form wizard to create it
select 'tabular' as the layout. You only need controls in the form for the
CourseID and other columns like the date taken etc, not for the EmployeeID.

In the form make the control bound to the CourseID column a combo box set up
like this:

ControlSource: CourseID

RowSource: SELECT [CourseID], [CourseName]FROM [Courses] ORDER BY
[CourseName];

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first dimension
is zero to hide the first column and that the second is at least as wide as
the combo box.

In the form's header add a combo box and set it up like this:

Name: cboEmployee

ControlSource: Leave this blank

RowSource: SELECT [EmployeeID], [FirstName] & " " & [LastName] FROM
[Employees] ORDER BY [LastName], [FirstName];

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

In the AfterUpdate event procedure of this combo box put:

Me.Requery

This requeries the form so it shows only the courses for the selected
employee. If you are unfamiliar with entering code in event procedures you
do this by selecting the cboEmployee combo box in form design view and
opening its properties sheet if its not already open. Then select the After
Update event property in the properties sheet. Click on the 'build' button;
that's the one on the right with 3 dots. Select 'Code Builder' in the
dialogue, and click OK. The VBA window will open at the event procedure with
the first and last lines already in place. Enter the line of code between
these two existing lines.

When the form opens it will not show any records. Select an employee from
the combo box and it should then show any courses taken by the employee.
You can add a new attendance at a course for the current employee by adding a
new record in the blank row below the existing course attendance records
simply by selecting a course in the combo box and entering the date taken etc
as appropriate.

Ken Sheridan
Stafford, England

Amy wrote:
Thank you for helping! Here is my next problem
I have created the tables to store all the information. My question now, is
how do I get it all to connect? I have created a query and a form but I don't
think I did it right. I would like to be able to type in the employee's name
and have all the training they have completed show up.

If I type information such as employee name and course into a form, how is
the data stored? Is it saved somewhere?

I'm trying to enter employee training information. I want to be able to type
in an identifier (last name or employee number) and it will bring up

[quoted text clipped - 33 lines]
MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201003/1

.

  #6  
Old March 3rd, 2010, 12:21 AM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Auto Fill

I'll try, but to paraphrase Einstein, some things can be made as simple as
possible but no more so. I'm assuming you've created the tables just as John
described, that the Employees table has data, and that the Courses table has
been filled with course data.

1. First you need to create a query, so open the query designed; don't add
any tables and then switch to SQL view. In the SQL window paste in the
following in place of what's the

SELECT *
FROM [CoursesTaken]
WHERE [EmployeeID] = Form![cboEmployee];

2. Save the query under a suitable name. If you try and open it now it will
prompt for a parameter value. Don't worry about that; its just because the
form and its combo box isn't yet created.

3. Create a continuous forms view form based on this query, which you can do
with the form wizard. Again if you try and open the form it will at this
stage prompt for a parameter value, but don't worry about that.

4. Open the form in design view. It will at this stage probably have a text
box as the control bound to the CourseID field. Chage this to a combo box
which you can do by right clicking on the text box and selecting ChangeTo ïƒ*
Combo Box from the short cut menu. Open the combo box's properties sheet if
its not already open and set its properties as I described in my last post.

5. With the form still open in design view add a cboEmployee combo box in
the form header. Set its properties as I described in my last post.

6. Finally add the line Me.Requery to the cboEmployee combo box's
AfdterUpdate event procedure in the way I described.

7. Save the form.

You should then be able to switch the form to form view and select an
employee from the combo box to see any courses they've been assigned to in
the CoursesTaken table, and add any course you wish to assign them to. If
you need to assign an employee to a course which is not yet in the Courses
table there is a way this can be done by typing the course name directly into
the CourseID combo box on the form, but that's something we can come back to
later once you've got the form working correctly otherwise.

Ken Sheridan
Stafford, England

Amy wrote:
Thanks for your help. But is there any way you could dumb it down for me?
I am unfamiliar with entering any kind of code. Your help is SOOO much
appreciated!!!

Your form should be based on a query on the CoursesTaken table which
references a combo box of employees on the same form. I'll come back to the

[quoted text clipped - 76 lines]
MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201003/1

  #7  
Old March 3rd, 2010, 12:23 AM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Auto Fill

If you are seeing a weird symbol in paragraph 4 of my last post its meant to
be a right pointing arrow.

Ken Sheridan
Stafford, England

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201003/1

 




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 05:51 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.