@davek: "I have a field that I added to a table.  It is just another field CWho1 (in 
the same table) with a new name COwner.  All other attributes are the same.  
I can't figure out how to create a field in code that has "lookup" properties 
and was wondering if there is a way to copy the field definition from one 
field to another and rename it in code. (It is in a backend that I don't have 
access to, thus I need to perform the addition in vb code).
If not, is there a way to set the lookup properties in the field?  I know 
there are drawbacks to the lookup properties, but what is done is done for 
now.
Thanks for the help.
Dave"
    @duanehookom: "I can only assume that anyone smart enough to provide the code is wise 
enough to not use the code. Consider reading this link about the evils of 
lookup fields <url>
MVPs are sworn to use the power for good, not evil.
Duane Hookom
MS Access MVP
"dave k" <email_address> wrote in message 
news:<email_address>
<quote>"
    @davek: "Yeah, I know.  The problem is it is already implemented that way in the 
front-end and on many other fields.  Let's just suppose I get rid of it, what 
would be the work around to keep functionality?
Thanks!
<quote>"
    @duanehookom: "You can provide the "lookup" functionality on your forms. Users shouldn't 
normally have access to datasheet views of tables and/or queries.
Duane Hookom
MS Access MVP
"dave k" <email_address> wrote in message 
news:<email_address>
<quote>"
    @davek: "Okay, just to confirm how I should get rid of my lookup fields.  I should 
create a new field type Long, copy the data from the lookup field into this 
new field.  Delete the lookup field, rename the new field to the lookup field 
name.
Then, just create a one to one link between the two tables OR just put the 
lookup functionality on the form?
Thanks in advance for the help.  I want to clean up the database soon.
Dave
<quote>"
    @duanehookom: "I'm not sure how you are setting up your lookup fields. If you are using 
Value Lists, create a small table of the values. Use this new "lookup" table 
as the row source of a combo box on your form.
You shouldn't need to add any new fields to your table, just make sure you 
have a lookup table that can be used as the Row Source for a combo box on a 
form. Then remove the "lookup" properties from the field definition.
Duane Hookom
MS Access MVP
"dave k" <email_address> wrote in message 
news:<email_address>
<quote>"
    @davek: "Thanks, my problem is that I don't have access to some of the backend 
databases.  Some of them are in Europe and I need to do it through functions 
in the frontend.  I am not sure how to delete the lookup properties through 
vb code.  Thus, the delete and recreate field in vb code.  Is there a way to 
delete the lookup definitions in code?  By the way, all the lookup values 
come from tables already so that they can be changed easily.
Thank you for the help, I really appreciate it!
Dave
<quote>"
    @duanehookom: "I'm not sure on how to get rid of them with code.
Duane Hookom
MS Access MVP
"dave k" <email_address> wrote in message 
news:<email_address>
<quote>"
    @bcostbudde: "A lookup field is most likely formatted as combobox. This setting 
resides in the DisplayControl property of the field. If you have a 
DAO.Field variable which is named fd, then this line
       fd.Properties("DisplayControl") = acTextBox
will remove the lookup functionality.
If the property cannot be found, you have to "create" it first.
    Case er3270PropertyDoesntExist
       Set pr = fd.CreateProperty("DisplayControl", dbLong)
       fd.Properties.Append pr
       Resume
There may be a hidden relationship between the table where the field 
resides, and the table that is looked up into. You can find this 
relationship in the Relations collection of the current database object. 
I hope ;-)
<quote>
Bas Cost Budde, Holland
<url>
For human replies, replace the queue with a tea"
{'source': 'USENET', 'other_information': '{"reply_count": 8, "forum_name": "microsoft.public.access.modulesdaovba.20140618.mbox"}', 'file': 'data/output/usenet-microsoft.public.parquet'}