Hi George. I'm going to guess that Mr. Kallal is trying to take the
weekend off, so I'll jump in here and see if I can help out a bit.
Next I used ACCESS to create an entry form from the query. I just
highlighted my query and then went to "create form" and voila, a form
was created.
And I used the wrong terminology. It created a lot of entry boxes on
the form, but they default to text box or whatever??? I figured out
how to change the employee ID box to be a combo box. That's what I
meant.
Terminology: Access created a "form" that contains "controls." "Text
box" is one type of control that allows you to type text into the
field -- hence the name Text Box. (When you are typing, the digits and
the letters are all text.) A "Combo Box" combines (combination,
shortened to Combo) functionality of a list box (which allows you to
choose from a list, but not to type into it) and a text box. So: the
entry form you are wnating to create is, in fact, an Access form. The
"entry boxes" are Access form controls.
As you can see, an employee is fixed assigned a Department. Only one
department.
I want to create an entry form for each department in my company. I
don't need to have a department table, althought I could, because the
employee file is populated (downloaded) from another source - so the
employee table is always FIXED data, including the department.
As the employee table is fixed I would agree that there seems to be no
need for a department table. However, as I read down your description I
would suggest that you build a Department Query against your Employee
Table.
Ok. With Access, you don't need a different form for each department;
what you need is a method for Access to know which department is loading
the form. (This raises another question: is each department working from
a different computer? We'll come back to this question later.)
However, I want to create a separate entry for each department so they
can only enter Punches for employees that belong to that deparment.
In other words, so department 35 can only enter punches for employees
who are desigated as department 35.
So, If a department is "35" that entry form can only see employees
from department 35. Right now, on the form, if I enter employee 25,
and press enter, it brings in their name and department and city
automagically. However, I want to create an entry form so the only
employee #'s the person can enter are employees who have been assigned
department 35.
This can be accomplished by the SQL restriction that Albert is
describing to you. However, before he, or I can help you do that you
need to let us know more about how Access is supposed to know which
department it is supposed to be working with.
Assuming that the user is going to somehow choose the department, you
can use the unbound control (unbound text box) that Albert describes.
Personally, I would reccomend using a Combo Box that is bound to the
Department Query I mentioned earlier. This way the user can either enter
the department number or choose it from a list.
I changed the employee ID textbox on my form to be a combo box
(remember, I let access create the form for me by highlighting the
query and clicking create form).
Okay, I changed the box to be a combo box, and I can see it's going to
let me scroll down, but where I get lost is how to enter the code you
are giving me. I go into design view, and then click build and then
code builder and it defaults to "before update". I changed the
before update to after update.
It puts a private sub and end sub in automatically, and I tried to
emulate the code gave me ending up with this:
Private Sub Department_AfterUpdate()
Question: You just referred to "employee ID combo box" and this code is
in the "Department" control after update event. I'm going to assume
that this is the Employee Department text box created by Access when you
had it build the form for you. You need to add a new control to your
form, and your code will attach to this new control. I'll assume your
new control will be called "cboChooseDepartment" so the code will change
to:
Private Sub cboChooseDepartment_AfterUpdate()
Dim strSql As String
strSql = "select [Punches Employee ID], [Employee Name], [Employee City]
from [Employee Query Name Here] where [Employee Department] = " &
Me.cboChooseDepartment
Me.[Employee ID].RowSource = strSql
End Sub
Note that everything between "strSql=..." and "Me.[Employee
ID].RowSource = strSql" is all one line, so if you're doing a copy and
paste you will probably need to put it back together after you paste.
There are a total of five (5) lines in this subroutine.
Also, everything I put inside of square brackets are names that come
from the query you built ... you need to replace my suggestions with
your actual names. The square brackets are necessary anytime there is a
space in the name.
I save that and then open it. I try to scroll down on the combo
employee id box, but no employees appear. I am trying to figure out
how to code this strsql statement. I only want employees with a
department of "35" to show in the scroll down combo box.
Unfortunately, I'm getting no employees now.
When you open the form, you need to choose a department from the Choose
Department Combo Box before you try to select an employee. After you
choose a department, you should find a list of employees in your
Employee ID Combo Box.
Obviously, I have gone awry...I have to be making this way more
complicated than it needs to be. I am guessing I need to make this
strsql something like:
Select employeeid, name, city from employee where deparment = " &
me.35 or something like that??? Or am I totally way, way off base.
I'd call this the learning process <grin>. I think I can follow your
thinking here, and I'd say that the "off base" is mostly just not being
familiar with how Access is put together. Keep at it, try to make sense
out of what comes up when you hit F1 (built in help) -- use F1
frequently! -- keep coming back here, and you'll find that it will come
together for you.
I confess I am a novice, but I've worked with data bases on other
computers (IBM AS/400 - don't laugh) so I'm not without some basic
understanding here.
So as you begin to learn Access' ways things will begin to fall into
place because you have something to relate to! said:
One other question on the form: Field Inout -
Lets say I want to validate Inout to either be "IN", "OUT" or (blank).
I tried to do that through validation data. I made that to be = "IN"
or "OUT" or " ".
But if I try to press enter, leaving it blank, it doesn't like it. IN
works and OUT works, but just pressing enter (leaivng it blank) gives
me my validation message which I put in which says "must be in, out or
blank"
Did you try pressing a single space before you hit enter? I'm not sure
if Access will recognize that or not. Access recognizes Null as a valid
value -- that's null, as in "nothing at all", and when you press enter
in an empty field Access wants to leave it Null which violates your
validation rule. Nulls can be usefil, but they can also cause problems
if you don't handle them properly so you likely do want to stay with " "
as the third value.
You might try putting " " as the default value, then just pressing enter
will leave the single space.
Whew...that's a lot of typing.
Yup. But that's how problems get solved said:
If you read all that and it makes
sense, I'll be surprised.
Why? You have that little faith in your level of knowledge? Your ideas
/ questions do make sense.
But I'm hopeful you can glean something out
of my rambling. I also understand maybe I could be doing with split
database or front end/back end, but I'm not interested in going there
right now. I want to get the basics and then graduate to something
else.
Good place to come back to my earlier question about how the different
departments will access the database. If you are going to have multiple
(as in simultaneous) users, then you will be going to a FE/BE (that is
what split databse means) eventually. As long as this is strictly a
development / testing database and you are the only one touching it that
can wait until you're further along the learning curve <g>. In fact,
that's where I am with my first Access application. It's nowhere near
done, but it's time to delve into splitting and multi-user capability.
I suggested that you build an Employee Department Query. I know of two
ways to do that but I don't know what the differences are in terms of
performance impact. Build a Query against your Employee Table that
contains only the Department field (sorted ascending.) In Design View,
Select View | Totals from the menu to add the "Total" property to your
design grid. You want "Group By" (the default on my computer.)
The second method does not use the Totals property. Instead, from the
Design View, Select View | SQL View and change "SELECT" to "SELECT
DISTINCT". As I said, I don't know which to prefer; I mention this
only in case Albert (or someone else) wants to comment on the
differences. The first method might be preferable for a beginner simply
because it uses built-in design tools and does not require editing the
SQL.
Am I on the right track there