Table combobox hell

[JS]

I created a table with a combo-box lookup rather than
having the lookup on the form(bad design I now know). The
lookup was to a table with an Autonumber field and a
MaterialName field. My combo box row source is: SELECT
[tblMaterials].[MaterialID], [tblMaterials].[MaterialName]
FROM tblMaterials; with Bound Column being 1. I am now
trying to import/append data from Excel and can't because
(I'm guessing) the spreadsheet contains the MaterialName
and it's expecting the Autonumber for the Name. Please
tell me there's a way to remove this combobox and
import/append data that contains the MaterialName. I have
already built forms/queries, etc off this setup. Will I
have to redo them all?

 

[JS]

No there aren't many types so I guess I could do that but
what about removing the combobox from the table
altogether. Any thoughts on that?

-----Original Message-----
Unless you have way too many types of "Materials" I would

think you could just run "Find and Replace" a few times in
your spreadsheet, changing the material names to the
material IDs the database has for them. After that, you
shouldn't have any problems importing the data.

I created a table with a combo-box lookup rather than
having the lookup on the form(bad design I now know). The
lookup was to a table with an Autonumber field and a
MaterialName field. My combo box row source is: SELECT
[tblMaterials].[MaterialID], [tblMaterials]. [MaterialName]
FROM tblMaterials; with Bound Column being 1. I am now
trying to import/append data from Excel and can't because
(I'm guessing) the spreadsheet contains the MaterialName
and it's expecting the Autonumber for the Name. Please
tell me there's a way to remove this combobox and
import/append data that contains the MaterialName. I have
already built forms/queries, etc off this setup. Will I
have to redo them all?

.

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

I created a table with a combo-box lookup rather than
having the lookup on the form(bad design I now know).

You can fix this by simply change the Field Type from "LookUp" Field to the
same type of the MaterialID (using Long if the MaterialTypeID is an
AutoNumber).

Whether you want to keep the "LookÜp" ComboBox is immaterial since you
already *knew* that what you see may not be what is actually stored under
the hood. You can change the display in the LookUp tab of the lower left
pane of the Table DesignView.

There may be duplicate relationships because of the old LookUp Field. Use
the Relationship window to check and delete if necessary.



The

lookup was to a table with an Autonumber field and a
MaterialName field. My combo box row source is: SELECT
[tblMaterials].[MaterialID], [tblMaterials].[MaterialName]
FROM tblMaterials; with Bound Column being 1. I am now
trying to import/append data from Excel and can't because
(I'm guessing) the spreadsheet contains the MaterialName
and it's expecting the Autonumber for the Name. Please
tell me there's a way to remove this combobox and
import/append data that contains the MaterialName. I have
already built forms/queries, etc off this setup. Will I
have to redo them all?

No. Storing the MaterialID as the ForeignKey in the "other" Table is the
right approach. All you need to do is to link the Excel spreadsheet to the
database so that it appears as a Linked Table. Create a Select Query that
combines the linked Excel Table and the tblMaterial (using MaterialName as
the linking Field) which should give you the MaterialID. Convert this to a
Make-Table or an Append Query as required.

 

[Allen Browne]

To remove the combo:
1. Open your table in design view.
2. Select the problem field.
3. In the lower pane, choose the Lookup tab, and change the DisplayControl
property to Textbox.

To export the material name:
1. Create a query that contains both the main table and tblMaterials.
2. Drag the fields you want to export into the query grid.
3. Export the query.

From the query design interface, you should be able to save the
spreadsheeting using: File | Export. To export programmatically, use the
TransferSpreadsheet action.