Lookup Lists - allow non list items

[Ian]

I hope this is simple,

I've been using the lookup list wizard but want to allow
other items (better yet I'd like the other items entered
to be entered into the list table!)

Anyway I when into properties and changed the limit to
list to YES
The colomn width to 2;1 (I can't really remember what I
changed the first number to but I remember changing it
from 0;1)
The field type is stuck on interger so now if I don't
choose something from the list it only accepts a number.
I'm sure there's a simple solution (possible to both
problems?). Please help.

Ian.

 

[Wayne Morgan]

You would normally leave the bound column at zero width. It will contain the
data that Access needs and the visible column should contain the data
meaningful to the operator. When the user enters an item that is not in the
list, the NotInList event will fire. You would then use code in this event
to add the item to the list. Here is an example of code that will do that.

http://www.mvps.org/access/forms/frm0015.htm

 

[Guest]

Thank you very much Wayne. Since I posted I read that a
lot of MVP's hate the look-up wizard (I've already run
into problems creating queries with it). Would I be
better to a combobox in the form that is dependent on the
lookup table? What's your opinion on it -- FYI in case
you haven't figured it out I'm a slightly above novice
user.

Thanks

 

[John Vinson]

Would I be
better to a combobox in the form that is dependent on the
lookup table?

I'd say so! On the Form you can set the "Limit to List" property of
the combo to False, allowing you to pick existing items or type in an
item not on the list, as you choose.

 

[Guest]

ok - I'm slowly starting to understand gettin rid of
my "look up wizrds" (god help me -- I've got 3 db''s with
about a 1000 of them but I now am seeing the error of my
ways).

So the better design is to:
1. set the combobox row source to my LU_tbl
2. ??Presumably I want to record the choice in a field
somewhere with the main record set -- how do I get the
value selected to be entered into the main tbl?

After that I can play with all the notinlist stuff

Thanks

 

[John Vinson]

So the better design is to:
1. set the combobox row source to my LU_tbl
2. ??Presumably I want to record the choice in a field
somewhere with the main record set -- how do I get the
value selected to be entered into the main tbl?

Well, once you've gotten the combo box onto a Form (where it belongs)
- even if it was put there with the help of the Lookup Wizard - you
should be able to alter its properties; in particular you can put code
in its NotInList event.

The combo box on the Form has a rowsource (the table or - better -
query from which it obtains its data) and a Control Source (the main
table field into which it puts the value in its Bound Column). The
wizard generated combo should already have these set up correctly.

 

[Guest]

Your a saint John. Thank you so much.

No more jerking off with relationships every time I
change a form or weird query results.

Last two questions:
Is there any point to creating a relationship between my
LU_Query and main table now?

How do I email Bill Gates?

Thanks.

 

[John Vinson]

Your a saint John. Thank you so much.

No more jerking off with relationships every time I
change a form or weird query results.

Last two questions:
Is there any point to creating a relationship between my
LU_Query and main table now?

No. Using the Lookup Wizard creates a relationship all by itself
(*even if a relationship already exists*). Do go to the relationships
window, View All, and make sure that you don't have redundant,
duplicated relationships - they'll just slow updates and cause bloat.
To delete an unneeded relationship, select the join line (not the
table icon) and click Delete.

How do I email Bill Gates?

bgates <at> microsoft <dot> com

though he's not the right person to complain to about this (the right
people got their ears thoroughly toasted about this issue recently).

 

[Guest]

Ok - I've deleted all relationships to the look up lists
and tried to email bill but I guess his antispam stuff
has shut down the address because it got bounced. Wanted
to send a compliment -- we've gone totally MS (against a
lot of advice) but I like the integration.

Thanks again.

Ian.

 

[Wayne Morgan]

For complaints/suggestions about Office programs, try the following link:
http://register.microsoft.com/mswish/suggestion.asp

Also, there may be a Contact Us option in the Help menu.