A simple question

[Edward]

Hi Everybody,
In Access 2007 , when we have tewo tables (Customers, orders) and in Orders
table I want to use a field "customer" to build a relationship with customers
table I use lookupwizard. In lookupwizard I chose lastname and first name na
dI think Access automatically addes primary key of customers table as well.
Now during data entry in orders table when I get to customers field and click
on dropdown list I see fistname,lastname of existing customer whiich I can
pick one. My question is :although this dropdown shows both first and last
names when I select one from the list it only enters the first name, is there
a way to enter both first,last name combination? second question is : because
the primary key in customers table is autonumber when I use this lookupwizard
to establish a relationship between orders and customers table although the
field is showimg first name, its data type is numebr. My assumption is Access
regardless what it shows in the connected field ( in this case text)
acctually it keeps the primary key of customers field and that's why it
changes the text data type to number. am i correct?

 

[Ken Sheridan]

Edward:

Firstly the look-up wizard is best avoided. See the following link for the
gory details:


http://www.mvps.org/access/lookupfields.htm


You are correct; the data type of your Customer column in the Orders table
is a number, and its actual values are numbers, not what you see. The names
are 'looked up' from the Customers table and are not held in the Orders table
at all. This is as it should be as names are unsuitable for keys, being
duplicated.

To enable you to see both names in the column you need to amend its
properties in table design view. Make a copy of the table first as a back
up, then:

1. Open the table in design view and select the Customer field.

2. In its properties sheet select the 'lookup' tab. It should show the
Diusplay Control property as 'Combo box'.

3 Select the RowSource property. It should look something like this:

SELECT Customer, FirstName, LastName FROM Customers ORDER BY LastName,
FirstName;

Change it to:

SELECT Customer, FirstName & " " & LastName FROM Customers ORDER BY
LastName, FirstName;

making sure that the field names are unchanged from the originals.

4. Change the ColumnCount property to 2.

5. Change the ColumnWidths property to 0cm;8cm (if your units are not
metric then Access will automatically convert the values).

You should now see the full name in the column in datasheet view of the
table. However, its far better not to do this in the table at all but only
in a form based on the table, using a combo box set up as above as the
control on the form. Data should always be entered via forms, not in raw
datasheet view, and showing the values 'looked up' from the Customers table
in datasheet view of the table serves no real purpose and only causes
confusion, as you've demonstrated by your post. Not to mention the other
problems described at the link I gave you!

Ken Sheridan
Stafford, England

 

[Edward]

Thank you very helpful. I have one more question . I use a form to enter data
but when lookupwizard is bases on another table user can not enter anything
but those items in the dropdown list which is good. the problem is when I use
a typed item in lookupwizard in this case dropdown list still shows my typed
items but alos gives the user to enter new items not in the dropdown list. Is
there anyway to avaoid this( user only able to select one from the dropdown
list and not be able to enter his own item)?
-
Best regards,
Edward

Edward:

Firstly the look-up wizard is best avoided. See the following link for the
gory details:


http://www.mvps.org/access/lookupfields.htm


You are correct; the data type of your Customer column in the Orders table
is a number, and its actual values are numbers, not what you see. The names
are 'looked up' from the Customers table and are not held in the Orders table
at all. This is as it should be as names are unsuitable for keys, being
duplicated.

To enable you to see both names in the column you need to amend its
properties in table design view. Make a copy of the table first as a back
up, then:

1. Open the table in design view and select the Customer field.

2. In its properties sheet select the 'lookup' tab. It should show the
Diusplay Control property as 'Combo box'.

3 Select the RowSource property. It should look something like this:

SELECT Customer, FirstName, LastName FROM Customers ORDER BY LastName,
FirstName;

Change it to:

SELECT Customer, FirstName & " " & LastName FROM Customers ORDER BY
LastName, FirstName;

making sure that the field names are unchanged from the originals.

4. Change the ColumnCount property to 2.

5. Change the ColumnWidths property to 0cm;8cm (if your units are not
metric then Access will automatically convert the values).

You should now see the full name in the column in datasheet view of the
table. However, its far better not to do this in the table at all but only
in a form based on the table, using a combo box set up as above as the
control on the form. Data should always be entered via forms, not in raw
datasheet view, and showing the values 'looked up' from the Customers table
in datasheet view of the table serves no real purpose and only causes
confusion, as you've demonstrated by your post. Not to mention the other
problems described at the link I gave you!

Ken Sheridan
Stafford, England

 

[Edward]

One more thing, you said we shouldnt use lookup wizard and also I read the
article you sent but what is the althernative? I mean when I want to relate
orders table with customes how can find proper cutomer ID to enter in a
proper field order's table ?
--
Best regards,
Edward

Edward:

Firstly the look-up wizard is best avoided. See the following link for the
gory details:


http://www.mvps.org/access/lookupfields.htm


You are correct; the data type of your Customer column in the Orders table
is a number, and its actual values are numbers, not what you see. The names
are 'looked up' from the Customers table and are not held in the Orders table
at all. This is as it should be as names are unsuitable for keys, being
duplicated.

To enable you to see both names in the column you need to amend its
properties in table design view. Make a copy of the table first as a back
up, then:

1. Open the table in design view and select the Customer field.

2. In its properties sheet select the 'lookup' tab. It should show the
Diusplay Control property as 'Combo box'.

3 Select the RowSource property. It should look something like this:

SELECT Customer, FirstName, LastName FROM Customers ORDER BY LastName,
FirstName;

Change it to:

SELECT Customer, FirstName & " " & LastName FROM Customers ORDER BY
LastName, FirstName;

making sure that the field names are unchanged from the originals.

4. Change the ColumnCount property to 2.

5. Change the ColumnWidths property to 0cm;8cm (if your units are not
metric then Access will automatically convert the values).

You should now see the full name in the column in datasheet view of the
table. However, its far better not to do this in the table at all but only
in a form based on the table, using a combo box set up as above as the
control on the form. Data should always be entered via forms, not in raw
datasheet view, and showing the values 'looked up' from the Customers table
in datasheet view of the table serves no real purpose and only causes
confusion, as you've demonstrated by your post. Not to mention the other
problems described at the link I gave you!

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

To prevent a user entering a value not in the list set the combo box’s
LimitToList property t o True (Yes). If the user enters a new name they’ll
get an error message. In fact there is away a new name can be entered and a
new record automatically entered into the ‘look-up’ table, but it requires
some VBA code to be written in the control’s NotInList event procedure.

If you don’t use the ‘look-up field wizard’ in table design you can get
exactly the same effect in a form by using a combo box bound to the foreign
key field in the Orders table. In fact it sound like you are already doing
that a your form has ‘inherited’ the combo box from the table design. Its
very easy to set up the combo box independently however, either by using the
combo box wizard when designing the form or by manually setting its
properties in the way I described in my first reply.

Ken Sheridan
Stafford, England

Thank you very helpful. I have one more question . I use a form to enter data
but when lookupwizard is bases on another table user can not enter anything
but those items in the dropdown list which is good. the problem is when I use
a typed item in lookupwizard in this case dropdown list still shows my typed
items but alos gives the user to enter new items not in the dropdown list. Is
there anyway to avaoid this( user only able to select one from the dropdown
list and not be able to enter his own item)?

One more thing, you said we shouldnt use lookup wizard and also I read the
article you sent but what is the althernative? I mean when I want to relate
orders table with customes how can find proper cutomer ID to enter in a
proper field order's table ?

 

[Albert D. Kallal]

the problem is when I use
a typed item in lookupwizard in this case dropdown list still shows my
typed
items but alos gives the user to enter new items not in the dropdown list.
Is
there anyway to avaoid this( user only able to select one from the
dropdown
list and not be able to enter his own item)?

Actually if the combo box or list box is based on another table, you'll find
my default a lot see the little smart tag appeared that lets you edit the
list. however, to disable the smart tag appearing, simply take the combo box
a list box property sheet and the other tab, and you'll see a setting
called:

Allow Value List Edits.

So, just set the above setting to no, and you'll not get the smart tag that
appears that allows to edit the list of items. Note that if you're basing
the combo box or list box on a table, then you have to define an actual form
to be used or the above setting will not do anything. So, in fact as a
general rule, you really want to base that list of items on the table, and
not an embedded value list in the actual form itself. There are many
reasons, but one simple reason is that then to add to the list you are not
actually modifying a value list in the form, but have a nice ready to use
list in a table somewhere (which facilitates editing, printing out, and
other aspects of your application design such as building another form that
uses same list to prompt the user for filters on a report for example).

Anyway, if you set the above allow value list edits to "no", then the user
will not be able to edit or modify the list presented to them. As an
addition, you should probably set the "limit to list" properity = yes as
this will restrict them to only entering values that are legal in the list.