vb code for lookup field properties?

[dave k]

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

 

[Duane Hookom]

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 http://www.mvps.org/access/lookupfields.htm.

MVPs are sworn to use the power for good, not evil.

 

[dave k]

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!

 

[Duane Hookom]

You can provide the "lookup" functionality on your forms. Users shouldn't
normally have access to datasheet views of tables and/or queries.

 

[dave k]

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

 

[Duane Hookom]

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.

 

[dave k]

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

 

[Duane Hookom]

I'm not sure on how to get rid of them with code.

 

[dave k]

Thanks.

Dave

I'm not sure on how to get rid of them with code.

 

[Bas Cost Budde]

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 ;-)

 

[dave k]

Thanks! I will check it out tonight.

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 ;-)