It there a way to auto populate the department field?
:
Not really, because as soon as you open a form, it is positioned on
the first
record in the recordset.
:
Since that is the first field that they enter information into, I
was just
thinking that could be used to do the auto populate the department
with the
department name. guess maybe that wasn't such a good idea
Those two
fields really don't relate at all except they are in the same table
:
Well, this is a new wrinkle. So what is account number and how
does that
relate to the department name. Where will they be typing in an
account
number?
:
I dont' think that is going to work - I want the department to
auto populate
"CITS" once someone begins typing in the account number
:
Okay, I think I got it now.
For each form, set the Filter property of the form to the
department for
that form and set the FilterOn Property to Yes. For example:
Filter [Department] = 'Some DepartmentName'
:
No problem - I am talking about 1 form for each department
still -
This is what I want to do and maybe this will help
Right now each form is being run off of one table
"tblCorrHist" - Once the
informationis loaded into that table via the form there is
a query that
appends the query into the correct department table
depending on the form you
are using.
What I was thinking about doing, if management doesn't want
to change it to
1 form for all departments is making an append query that
will append all the
tables into one table so that I can run reports off of the
that.
In order to do this I want to be able to have the
department as part of the
table so I know which table it has come from.
So what I was thinking, instead of having the rep choose
the department,
making it an auto field for my purposes only. I would want
it to populate
with the name of the department that corresponds with the
form being used.
I hope this helps.
:
Sorry, we are not connecting. Are you talking about 1
form for all
departments or 1 form for each department. Or fooling
management so they
still click a button for a department and it loads one
form with the correct
department?
Please give me as much detail as you can. Sorry I am not
catching on more
quickly.
:
But I want it to autopopulate with the name of the
department - I am probably
not explaining it correctly sorry
:
If you add a department field to the tables and a
department control on the
form and the form is using that department's table as
it's record source,
then it will contain the department code in the
table.
Maybe I am still not understanding.
:
If the database stays in the current condition that
it is in. What I want to
do is create a department field in the table. I
want this field to
automatically be filled with the department on the
form
:
If you mean using the design I propose, you could
set the Default Value of
the combo box to a specific department.
If this is not what you are asking, please post
back with more detail.
:
i am going to work on changing thier minds on
the way that this is
structured. But I do have one other question.
On the form is there anyway
that I can have a default value for a field
that I name department and have
that automatically have the name of the
department in there?
:
Your first sentence contains an error:
"upper management thinks"
Actually they are totally wrong. This will
actually increase the chance of
errors.
What a nightmare. This means every time any
change is made, you have
multiple forms to update. To fix that, the
correct way would be to filter
one form's record source on the department
you want to see. You could use a
combo box for that to allow the users to
select a department and in the
combo's After Update event, set the form's
filter to include only the
selected department.
If you can't convince them the design needs
to be corrected to prevent
problems just like this, then the only thing
you can do is create a Union
query to union all the departments together
and do the Dcount on this query.
:
The reason that it is set up this way is
because upper management thinks that
this will cause a lot less errors. They
use separate buttons to go into the
specific forms. I was not sure how to work
around this so that is why it is
this way.
:
You are suffering from Bad Database
Design. All the department tables should
be combined into one table. If there is
not one already, create a field that
contains the deptment code, name, or
however you identify a department. This
will make your life much easier. Not
only will it be difficult to do this
simple task, everywhere else you need
department info will be harder to
control.
No "Yes, But"s, just do it. You will be
happier in the long run.
:
I have a few different tables that all
have the same information in them but
the tables are split up by different
departments. I want to do a total
count
of the records in each table that have
nothing in the Date Resolved field. I
know this is possible within access but
I am not sure how to combine those
tables. Any help would be greatly
appreciated.