to combo box or not to combo box...

[Tcs]

I've been working with Access for a coupe years now. Unfortunately, I've done
virtually NOTHING with forms. (GULP.)

I'm trying to create a small phone directory in Access 2k3. (Mostly for the
learning experience.) I have tables:

tblAreaCodes, tblExchanges, tblNumbers, tblExtensions, tblUsers, tblLocations,
tblFloors, tblDivisions and tblDepartments. Only tblUsers has several fields
(User, Nickname, Username, Sex, PersEmailAddr). All the rest only have a key
and the actual field value. (Like "404", "678" for the AreaCode.)

While I would like to be able to pull up records where they match what I input
into a combo [or whatever] box, I'd *like* to be able to ADD to the respective
tables also. And I can't seem to do that.

If an area code doesn't exist, I want to be able to add it...on the fly. Same
goes for the exchange, number and extension. I've pulled employee data for the
Users table, as well as the Location, Division & Department tables. While I
don't expect to have to make any changes to these last three (3), I may need to
modify the Users records. (Perhaps their name is mispelled, and/or their
username and/or nickname is wrong.)

When my box is bound to the KEY field for my box, then I can't add. When I can
add, it's because my box is bound to the data field ("AreaCode"), as opposed to
the key ("AreaCodeKey"). If I try to remove the bind altogether, Access says I
can't do that at all.

Everywhere I look I read I can do ANYTHING. It's just that it doesn't work when
*I* try to do it. And I can't understand WHY I can't. (If I did, then I'd fix
it!) This is FRUSTRATING.

Does anyone have any recommendations other than to just "give up"?

Thanks in advance,

Tom

 

[Guest]

I think you have more tables than you need, unless, for example, you use the
are code table only to validate an area code. Even then, it is a bit of an
overkill. Also, you mentioned nothing about having relationships set up. I
also notice your users table has no reference to phone numbers at all. How
do you find a phone number for a user?

When considering database design, you try to adhere to common database
design rules. One of which is data redundancy. You want to avoid carrying
the same piece of data more than one time; however, in this case, I think you
have gone a little too granular. I would recommend storing the entire phone
number for an individual. Whether you put it in the users table or a
users/phone table will depend on the business rules. If the rule is a user
has one and only one phone number, put it in the users table. If the rule is
a user may have none or may numbers, then you need a users/phone table. The
users table and users/phone tables should be related by a unique key value.
Here is a suggested layout for the users/phone table:

USR_PHON_ID Autonumber, Primary Key
USR_PHON_USR Long Integer, Foreign key to users table
USR_PHONE_TYPE Long Integer, Foreign Key to phone type table
USR_PHONE-NBR Text length of 10

Now the phone type table which stores the description of the type of phone
(Home, Home2, Work, Cell, Fax, etc) would be:

PHON_TYPE_ID Autonumber, Primary Key
PHONE_TYPE_DESCRIPTON Text, lenght to suit

As to the combo, it is a latin dance that was popular in the mid part of the
20th century.... no wait, that's mombo, lets get back to combo

A combo box needs a row source. That is, a table, query, value list, or
field list that creates the list of items you will see in the combo. If you
are trying to look up users, then the combo would be on a form based on the
users table, and your row source for your combo would be based on a query
that returns the users name, and for best practice the primary key field of
the user table.

Then you can select a user from the combo box. It will take some VBA to
actually locate the user and make his record the current record, but try
using the combo box wizard first to see if it will get you where you need to
go.

Before you start on forms, you really need to sit down and do a good
redesign on your data structure.

good luck.

I've been working with Access for a coupe years now. Unfortunately, I've done
virtually NOTHING with forms. (GULP.)

I'm trying to create a small phone directory in Access 2k3. (Mostly for the
learning experience.) I have tables:

tblAreaCodes, tblExchanges, tblNumbers, tblExtensions, tblUsers, tblLocations,
tblFloors, tblDivisions and tblDepartments. Only tblUsers has several fields
(User, Nickname, Username, Sex, PersEmailAddr). All the rest only have a key
and the actual field value. (Like "404", "678" for the AreaCode.)

While I would like to be able to pull up records where they match what I input
into a combo [or whatever] box, I'd *like* to be able to ADD to the respective
tables also. And I can't seem to do that.

If an area code doesn't exist, I want to be able to add it...on the fly. Same
goes for the exchange, number and extension. I've pulled employee data for the
Users table, as well as the Location, Division & Department tables. While I
don't expect to have to make any changes to these last three (3), I may need to
modify the Users records. (Perhaps their name is mispelled, and/or their
username and/or nickname is wrong.)

When my box is bound to the KEY field for my box, then I can't add. When I can
add, it's because my box is bound to the data field ("AreaCode"), as opposed to
the key ("AreaCodeKey"). If I try to remove the bind altogether, Access says I
can't do that at all.

Everywhere I look I read I can do ANYTHING. It's just that it doesn't work when
*I* try to do it. And I can't understand WHY I can't. (If I did, then I'd fix
it!) This is FRUSTRATING.

Does anyone have any recommendations other than to just "give up"?

Thanks in advance,

Tom

 

[Tcs]

While it may not be as *good* as you describe, I don't think it's quite as *bad*
as you think...

I think you have more tables than you need, unless, for example, you use the
are code table only to validate an area code. Even then, it is a bit of an
overkill. Also, you mentioned nothing about having relationships set up. I
also notice your users table has no reference to phone numbers at all. How
do you find a phone number for a user?

Tables:

tblPhoneNumbers

tblAreaCodes - just area codes
tblExchanges - just exchanges
tblNumbers - just numbers
tblExtensions - just extensions
tblUsers - just users, including user's name, nickname, username
tblRooms - just rooms
tblFloors - just floors
tblLocations - just street addresses
tblDivisions - just divisions
tblDepartments - just departments

-----

Relationships:

Include ALL records from 'tblPhoneNumbers' and only those records from
'tblAreaCodes' where the joined fields are equal.

All table relationships are the same.

ALL tables have AutoNumber field as record key. I'm wanting to store this value
in assiciated field of PhoneNumbers records. For example, AreaCode "404" has a
key of 1. I want to store "1" in the AreaCode field of the PhoneNumbers table.
And *this* at least, I've been able to do.

-----

Table Defs:

(typical for all tables except Phone Numbers)

AreaCodeKey AutoNumber
AreaCode Number
TimestampCreated Date/Time
TimestampUpdated Date/Time

-----

Table Def for tblPhoneNumbers:

PNKey AutoNumber
AreaCode Number
Exchange Number
Number Number
Extension Number
User Number
Room Number
Floor Number
Location Number
Division Number
Department Number
TimestampCreated Date/Time
TimestampUpdated Date/Time

The table tblPhoneNumbers is the ACTUAL phone number record.

-----

When considering database design, you try to adhere to common database
design rules. One of which is data redundancy. You want to avoid carrying
the same piece of data more than one time; however, in this case, I think you
have gone a little too granular. I would recommend storing the entire phone
number for an individual. Whether you put it in the users table or a
users/phone table will depend on the business rules. If the rule is a user
has one and only one phone number, put it in the users table. If the rule is
a user may have none or may numbers, then you need a users/phone table.
tblPhoneNumbers

The
users table and users/phone tables should be related by a unique key value.
Here is a suggested layout for the users/phone table:

USR_PHON_ID Autonumber, Primary Key
USR_PHON_USR Long Integer, Foreign key to users table
USR_PHONE_TYPE Long Integer, Foreign Key to phone type table
USR_PHONE-NBR Text length of 10

Now the phone type table which stores the description of the type of phone
(Home, Home2, Work, Cell, Fax, etc) would be:

PHON_TYPE_ID Autonumber, Primary Key
PHONE_TYPE_DESCRIPTON Text, lenght to suit

As to the combo, it is a latin dance that was popular in the mid part of the
20th century.... no wait, that's mombo, lets get back to combo

A combo box needs a row source. That is, a table, query, value list, or
field list that creates the list of items you will see in the combo. If you
are trying to look up users, then the combo would be on a form based on the
users table, and your row source for your combo would be based on a query
that returns the users name, and for best practice the primary key field of
the user table.

Then you can select a user from the combo box. It will take some VBA to
actually locate the user and make his record the current record, but try
using the combo box wizard first to see if it will get you where you need to
go.

Before you start on forms, you really need to sit down and do a good
redesign on your data structure.

good luck.

I've been working with Access for a coupe years now. Unfortunately, I've done
virtually NOTHING with forms. (GULP.)

I'm trying to create a small phone directory in Access 2k3. (Mostly for the
learning experience.) I have tables:

tblAreaCodes, tblExchanges, tblNumbers, tblExtensions, tblUsers, tblLocations,
tblFloors, tblDivisions and tblDepartments. Only tblUsers has several fields
(User, Nickname, Username, Sex, PersEmailAddr). All the rest only have a key
and the actual field value. (Like "404", "678" for the AreaCode.)

While I would like to be able to pull up records where they match what I input
into a combo [or whatever] box, I'd *like* to be able to ADD to the respective
tables also. And I can't seem to do that.

If an area code doesn't exist, I want to be able to add it...on the fly. Same
goes for the exchange, number and extension. I've pulled employee data for the
Users table, as well as the Location, Division & Department tables. While I
don't expect to have to make any changes to these last three (3), I may need to
modify the Users records. (Perhaps their name is mispelled, and/or their
username and/or nickname is wrong.)

When my box is bound to the KEY field for my box, then I can't add. When I can
add, it's because my box is bound to the data field ("AreaCode"), as opposed to
the key ("AreaCodeKey"). If I try to remove the bind altogether, Access says I
can't do that at all.

Everywhere I look I read I can do ANYTHING. It's just that it doesn't work when
*I* try to do it. And I can't understand WHY I can't. (If I did, then I'd fix
it!) This is FRUSTRATING.

Does anyone have any recommendations other than to just "give up"?

Thanks in advance,

Tom