Access 2003 "IF" function question

[Nadihaha]

I'm not sure where to post this so if it's in the wrong place please let me
know and I will repost.

How do I enter an "IF" function into a form?

For example If the surname from a particular record is selected the first
name auto populates.

I currently have a table with Employee Details in it and one with Computer
details in it, I want another table with the Employees name and which
computer they have. So I want a form where I can pick the last name (from
combo box, which I can do) the first name is auto populated and then I can
pick which computer. I have a number of other places in my database that
this would prove useful.

Thanks

 

[Wayne-I-M]

Hi Nadihaha

You don't want to use an "IF" for this.

You need (as I think you have already) 2 tables.
1 = Employee details
2 = Computor details

There are a number of reasons for this. 1st you may have more than 1
employee with the same name, some employees may have the same type of
computor.

It may be that you have an inventory number for each computor. This will
still need 2 tables.

You need to lin the tables like this.

tblEmployees
EmployeeID = AutoNumber
ComputorID = Number
Employee1stName = Text
Employee2Name = Text
EmployeeAddressLine1 = Text
EmployeeAddressLine2 = Text
EmployeeAddressLine3 = Text
etc
etc

tblComputors
ComputorID = AutonNumber
ComputorType = Text
ComputorMake = Text
ComputorOperatingSystem = Text
ComputorPurchasedDate = Date/Time
ComputorSerialNumber = Text
etc
etc

Next link these 2 tables in the relationship window.
Next either create a query based on 2 tables and then a form based on that
or Create a form based on tblEmplyees and another based on tblComputor
(ensure that the employeeID is on both forms.
Open the form based on tblEmployees in design view and add the other form a
subform - use the employeeID and the linking field

When you look at a record of an employee the subform will show the computor
that person has.

hope this helps

 

[Wayne-I-M]

Just had a thought - I forgot to say.
On your form you don't need to have all the fields from the table(s) shown.
You could just a have
EmployeeID, Name, Deptartment, etc
ComputorID, EmployeeID, ComputotrType, etc

If you add a combo (use the wizard to crete it for you) then you could just
have a very simple form. It "may" be a good idea to have an option after the
you have selected the Emplyee (so you can see the comptutor detals) that you
can go to the full form which will show more details - ComputorType,
ComputorMake, ComputorOperatingSystem, ComputorPurchasedDate, etc etc

 

[Nadihaha]

The Problem is still if I select Employee ID in my field I have no idea who
that relates to, likewise with Computer ID.

I still need an If function to say if I select this ID auto populate the
Employee Name fields appropriately.

I'm trying to make this so that others can use the database, If I use the
Employee ID as an identifying feature I may as well not use the form as they
are going to have to use the table to figure out what the Employee Numbers
relate to.

 

[Nadihaha]

That and this is only part of my database.

I currently have a Hardware table - Containing all the Computer related
Hardware.

And a Software table with all of our software.

I am going to have a table that combines the software with the hardware (ie
what software's installed on which PC)

So what I'm aiming for is a report that states who has what software.

 

[John W. Vinson]

The Problem is still if I select Employee ID in my field I have no idea who
that relates to, likewise with Computer ID.

Not if you use the tools that Access provides.

I still need an If function to say if I select this ID auto populate the
Employee Name fields appropriately.

No, you do not.

I'm trying to make this so that others can use the database, If I use the
Employee ID as an identifying feature I may as well not use the form as they
are going to have to use the table to figure out what the Employee Numbers
relate to.

There is a tool called a Combo Box that you can use on a Form.

This can (and will, by default, if you use the toolbox wizard to create it)
*DISPLAY* the person's name, but *STORE* the ID.

See Crystal's video and the other resources here. Access will do a LOT
(sometimes even too much!) to help you; you don't need to program *anything*
to get a lot of user-friendliness built in.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/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]

That and this is only part of my database.

I currently have a Hardware table - Containing all the Computer related
Hardware.

And a Software table with all of our software.

I am going to have a table that combines the software with the hardware (ie
what software's installed on which PC)

So what I'm aiming for is a report that states who has what software.

Since a given piece of Software could be installed on many Computers, and each
computer will have many different pieces of software, you need three tables
for this:

Computers
ComputerID
<information about the computer as a thing in itself>

Software
SoftwareID
ProgramName
Manufacturer
<other information about the program itself>

SoftwareInstalled
ComputerID <what computer was it installed in>
SoftwareID <what got installed>
InstallDate
<other info about this installation, e.g. version, employee ID of installer,
etc>

 

[Nadihaha]

Hi John Yes this is what I have, as well as a few others. I'm using this
learning curve to get rid of a few unmanageable spreadsheets.

 

[Nadihaha]

Thanks John,

This will work for now we don't have any last name duplicates. However I
forsee a problem. I currently have the last name and the first neame stored
in separate fields (everything I've read so far about access says this is
what I should do) However what will happen when we get bigger and do
encounter name duplicates?

I would rather counteract this problem now. My thinking is to select by last
name and if there is only one with this last name store the ID but if there
is more than one record give me a choice of first names....is this at all
possible?

The other problem I have is when I run my report it displays the ID's not
the name so the report is useless without being able to access the database
to check the ID's.

Gah I feel like this is a bit to hard, but I know it will be better in the
end.

I just want it to be basic to use, so that it will be utilised!!

 

[John W. Vinson]

Thanks John,

This will work for now we don't have any last name duplicates. However I
forsee a problem. I currently have the last name and the first neame stored
in separate fields (everything I've read so far about access says this is
what I should do) However what will happen when we get bigger and do
encounter name duplicates?

Store a unique ID, the last name, the first name, and some other field or
fields that would let you identify the person: nickname, department, position,
phone number, whatever works in your situation. You can display up to ten
fields in a combo box; and you can and should concatenate the actual name
fields into one, e.g. a query like

SELECT PersonID, [LastName] & ", " & [FirstName] & (" " + [MiddleName]) AS
FullName, [Department], [Position] FROM peopletable ORDER BY LastName,
FirstName, MiddleName;

Adjust the field and table names and use this as the rowsource of a combo box.

I would rather counteract this problem now. My thinking is to select by last
name and if there is only one with this last name store the ID but if there
is more than one record give me a choice of first names....is this at all
possible?

The above is simpler and lets the user see and select the firstname.

The other problem I have is when I run my report it displays the ID's not
the name so the report is useless without being able to access the database
to check the ID's.

Do not base the report on the Table. Instead base it on a Query joining your
table to the "people" table by ID. Pull the person's name from the people
table, and the other data from your main table.

You're using a relational database, not a spreadsheet! You DO NOT need to (nor
should you) put everything in one table to display it; use Queries to combine
data from all of the relevant tables, and base your report *on that query*.

Gah I feel like this is a bit to hard, but I know it will be better in the
end.

I just want it to be basic to use, so that it will be utilised!!

To a certain extent, the more work you do up front in the design of the
database, the less work your users will need to do in using it. And you'll
only be building the database once, and your users will hopefully use it for a
long time.

 

[Nadihaha]

Many Thanks John. Thankyou soo much for being Patient.

That works like a dream. I have a query pulling all the information I need
to figure out what to pick and then a drop down box linked to that query

Thanks John,

This will work for now we don't have any last name duplicates. However I
forsee a problem. I currently have the last name and the first neame stored
in separate fields (everything I've read so far about access says this is
what I should do) However what will happen when we get bigger and do
encounter name duplicates?

Store a unique ID, the last name, the first name, and some other field or
fields that would let you identify the person: nickname, department, position,
phone number, whatever works in your situation. You can display up to ten
fields in a combo box; and you can and should concatenate the actual name
fields into one, e.g. a query like

SELECT PersonID, [LastName] & ", " & [FirstName] & (" " + [MiddleName]) AS
FullName, [Department], [Position] FROM peopletable ORDER BY LastName,
FirstName, MiddleName;

Adjust the field and table names and use this as the rowsource of a combo box.

I would rather counteract this problem now. My thinking is to select by last
name and if there is only one with this last name store the ID but if there
is more than one record give me a choice of first names....is this at all
possible?

The above is simpler and lets the user see and select the firstname.

The other problem I have is when I run my report it displays the ID's not
the name so the report is useless without being able to access the database
to check the ID's.

Do not base the report on the Table. Instead base it on a Query joining your
table to the "people" table by ID. Pull the person's name from the people
table, and the other data from your main table.

You're using a relational database, not a spreadsheet! You DO NOT need to (nor
should you) put everything in one table to display it; use Queries to combine
data from all of the relevant tables, and base your report *on that query*.

Gah I feel like this is a bit to hard, but I know it will be better in the
end.

I just want it to be basic to use, so that it will be utilised!!

To a certain extent, the more work you do up front in the design of the
database, the less work your users will need to do in using it. And you'll
only be building the database once, and your users will hopefully use it for a
long time.

That's what I'm hoping!! They use a hideously coloured spreadsheet at the
moment!!