Change table design

[Sam]

I originally created a table with LookUp tables/fields. I have queries and
forms based on this table. Now I have seen the light and need to get rid of
the LookUp's in the table and put them in the form (Ilearned how!).
Question: what is the best way to get my data into a reformed table (no
LookUps)??
Thanks!

 

[John Vinson]

I originally created a table with LookUp tables/fields. I have queries and
forms based on this table. Now I have seen the light and need to get rid of
the LookUp's in the table and put them in the form (Ilearned how!).
Question: what is the best way to get my data into a reformed table (no
LookUps)??
Thanks!

If you just want to remove the Lookup combo boxes from table datasheet
view, you can simply open the Table in design view; select the Lookup
tab in the field properties on the lower left of the screen; and
select Textbox.

If you're actually changing what data is stored in the table, you'll
need to explain your old and new table structures. An Append query
will probably be the solution, but not knowing how the data needs to
be transformed, I can't be specific.

John W. Vinson[MVP]

 

[Sam]

Thanks! I did just what you suggested and changed the LookUp to text and
everything works fine, EXCEPT my report. One field gives me an error
message. It says that my control is wrong. I have looked at my original
table and everything is exactly the same. Any idea?

 

[John Vinson]

Thanks! I did just what you suggested and changed the LookUp to text and
everything works fine, EXCEPT my report. One field gives me an error
message. It says that my control is wrong. I have looked at my original
table and everything is exactly the same. Any idea?

It doesn't "say my control is wrong". It gives you an error of some
kind. If you'll be so kind, please post the Control Source property of
the textbox on your report, and the actual error you're getting.

John W. Vinson[MVP]

 

[Sam]

The eror message is: The value you entered isn't valid for this field. It is
a text field with 50 spaces. The data is simply a Wire# "2006-111". I have
looked at my orginal report and can't see anything different and it works
fine.Thank you for your continued efforts.

 

[John Vinson]

The eror message is: The value you entered isn't valid for this field. It is
a text field with 50 spaces. The data is simply a Wire# "2006-111". I have
looked at my orginal report and can't see anything different and it works
fine.Thank you for your continued efforts.

Check the Table definition again. I suspect that the Wire# was
originally a lookup, and that what is in the table is a numeric ID
linked to the lookup table.

Do you really mean a Report? You can't enter data into a Report, and I
cannot see how a Report could give you this particular error! Is it
perhaps a Form?

John W. Vinson[MVP]

 

[Sam]

It is a Report based on a Query of my Table. In the original table this
field was a LookUp. It is now a text field. I have even deleted this field
from my report and still get the message. I can open the report but have to
keep hiting the enter key through each set of data on the page before the
report will open. I suspect this has something to do with how the report was
created with Rpt Wizzard. I have compared it to the original
table/query/report that worked and everything appears the same. It must be
somehting in the background but I don't know what.

 

[John Vinson]

It is a Report based on a Query of my Table. In the original table this
field was a LookUp. It is now a text field. I have even deleted this field
from my report and still get the message. I can open the report but have to
keep hiting the enter key through each set of data on the page before the
report will open. I suspect this has something to do with how the report was
created with Rpt Wizzard. I have compared it to the original
table/query/report that worked and everything appears the same. It must be
somehting in the background but I don't know what.

Check the Sorting and Grouping property of the report.

John W. Vinson[MVP]

 

[Sam]

I have. It has to be something from the original setting of the report. Even
when I delete this field fromt he Group/Sort it still give the error. I can
create a new report from the same query and everything works fine. It has to
be something from the Wizzard setup in the report. I just didn't want to
have to design a new report!