Calling Joan Wild

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is a continuation of the post "Best Way to Share". You have been soooo
helpful. However, I am struggling with the Lookup Field issue. If I have a
long list of Items it is much simpler and efficient to have a lookup field
with these items listed. As I understand you and the link to 'The Evils of
Lookup Fields' one should never use them. Are you saying that even if there
are 15 items that a person would choose from that these should not be listed
to choose from? I (think) I understand why using Lookup from other Tables
would be an issue, but not from within the Table. Thank you for you patience
and replies.
 
i know the previous question was not Security related, however, it was a
continuation of a security question. This one is about security...If I split
the database and have only tables on the LAN, how do I prevent certain users
from seeing some info in certain tables. Can I have tables on my PC that
are not on the LAN?
Thanks!
 
Hi Sam,


I wasn't following your previous thread. But the most common issue related
to the use of lookup fields in Access is the use of lookup table in a table
definition versus on the form. The recommended method is to implement the
lookup function on the form. You can still use tables to store your
selections/choices, but avoid "looking up" from a table. Use a form.
As I understand you and the link to 'The Evils of
Lookup Fields' one should never use them.
Click to expand...

This sentence should read:
As I understand you and the link to 'The Evils of Lookup Fields' one should
never use them "in a table definition".

Are you saying that even if there
are 15 items that a person would choose from that these should not be listed
to choose from?
Click to expand...

It is okay to list selections/choices whether using a listbox or a combobox.
And it is okay to store the selections/choices in tables. What's not okay is
implementing them from a table. You should implement them on the form.


Immanuel Sibero
 
Sam said:
This is a continuation of the post "Best Way to Share". You have
been soooo helpful. However, I am struggling with the Lookup Field
issue. If I have a long list of Items it is much simpler and
efficient to have a lookup field with these items listed. As I
understand you and the link to 'The Evils of Lookup Fields' one
should never use them. Are you saying that even if there are 15
items that a person would choose from that these should not be listed
to choose from? I (think) I understand why using Lookup from other
Tables would be an issue, but not from within the Table. Thank you
for you patience and replies.
Click to expand...

Hi Sam,

Immanuel has given the reasons for using lookups on forms, but not lookup
fields in a table. However, upon reading this, it sounds as though you are
talking about using a 'value list', rather than looking up to another table.
(in either case, I'd still do this just on a form, not in the table).

You'd be better off to put your 15 values in another table and relate it to
your existing table. You'll find it much easier to manage if you need to
add values to your value list (just open up the table and add them). If you
are using a value list, you'd have more work to do.
 
Ok, I have created a table with my Prod Type list. I have created a query
and related to my Job table and included it on a form. However, I can see
the list of Prod Type but cannot choose one of them. I have done this on
other forms but the lookup was in the table. I want to be able on the Form to
choose which Prod Type from the list. What am I doing wrong? Please don't
grow weary with me, I really appreciate your help.
 
Sam said:
Ok, I have created a table with my Prod Type list. I have created a
query and related to my Job table and included it on a form.
However, I can see the list of Prod Type but cannot choose one of
them. I have done this on other forms but the lookup was in the
table. I want to be able on the Form to choose which Prod Type from
the list. What am I doing wrong? Please don't grow weary with me, I
really appreciate your help.
Click to expand...

Not to worry, I'll not grow weary! I assume there is a ProdType field in
the Job table that relates to your 'lookup table'. I'll also assume your
Prod Type table has just one field.

You just need to create a form based on the Job table. There are a number
of ways to approach it, but you could then have the form wizard create the
form for the Job table. Then

Option 1: Right-click on the textbox control it created for the ProdType
field, and choose Change To... and choose ComboBox.
Set the properties for the combobox as follows:
Row Source Type - Table/Query
RowSource - <whatever you named your Prod Type table> (there's a dropdown
you can choose it)
Bound Column - 1
Limit to List - Yes
Column Count - 1
Column Widths - 1.5"
(play with this to suit the list you have)

OR
Option 2: Select the textbox control it created for the ProdType field, and
delete it.
Ensure the field list is displayed, and the toolbox is displayed with the
control wizard enabled
Click on the combobox tool on the toolbox; then go to the field list and
drag the ProdType field to the form.
The wizard will guide you through the steps of selecting the row source
(your table name), and setting the size of the column.

How's that?
 
Thank you, again. If I create the Form from the Table it works great. I was
trying to create the Form from a Query of the Table. What is different about
creating a Form from a Query?
 
Sam said:
Thank you, again. If I create the Form from the Table it works
great. I was trying to create the Form from a Query of the Table.
What is different about creating a Form from a Query?
Click to expand...

A query allows you to set criteria - for instance if you had a flag on a
record to indicate 'inactive', you wouldn't want that record to show up in
your form.

Another example (since you're asking in the security newsgroup), is you can
restrict the records that are seen by the currently logged in user by using
the CurrentUser() function (providing you store a field with their
username).

You shouldn't really dish up all records in a table to the user in a form.
They're not likely to be able to deal with more than one at a time anyway -
this is important to consider if you're accessing the data over a network.

Also it is possible that you want to be able to make changes to more than
one table at a time. A query allows you to include fields from more than
one table on a form, although this is often accomplished with a form/subform
interface.

In your case, you just wanted to lookup a value in another table, and store
it in your first table. You don't need to have the table included in the
recordsource of your form. You just reference it in the rowsource of your
combobox.

Finally, if you do implement security, you'll want to base all your
forms/reports on queries so that you can take advantage of the 'with owner
permissions' property. This allows you to deny all permissions on the
tables.
 
Ok, I have created all new Tables and Queries. I created a new Form from the
Query. On the Form I have fields that the Data Entry person needs to choose
from list to make the entry. I have set the field as a Combo Box and chose
the control source to a Query that has only the list. On the Form, the list
is visible but it will not allow me to choose from the list. This works
perfectly is the Lookup Field is in the table, but you have told me not to do
a Lookup Filed in the Table, correct? The reason I am still in 'Security' is
because when I get past this problem I will want to protect my Tables, etc.
FYI, the data entry person is entery data from a Sales Order, we then track
the production of the job with all the details. This is basically an Order
Entry Form and a Status Form so they need access to all Open orders in the
Table.
Thanks, again!
 
Sam said:
Ok, I have created all new Tables and Queries. I created a new Form
from the Query. On the Form I have fields that the Data Entry person
needs to choose from list to make the entry. I have set the field as
a Combo Box and chose the control source to a Query that has only the
list.
Click to expand...

Did you change the control source or the row source? The control source is
the field that the control is bound to.
On the Form, the list is visible but it will not allow me to
choose from the list.
Click to expand...

Be more specific, what happens?

What are the properties for the combobox? What is the SQL of the query your
form is based on?
 
Back
Top