Finding the solution

[ukdane]

I have a table in my database (tbl_mydata), that contains data.
One of the items is a product number.

I have another table (tbl_products), that lists the product number (as the Primary Key) with the product name.

I also have a form (frm_data). The form shows the table (tbl_mydata). However I need the form to show the product name (as found in tbl_products), and not the product number. I need the user to be able to add/change the product data in the form, by product name, and for the chnages to be recorded in the tabe (tbl_mydata).

Your help is appreciated.
Cheers

[OBP]

Providing you only need the Form to Display the Product Name related to the Product Number use a Combo Box bound to the Product Number on the form (from the tbl_mydata table), in the combo select both Number and Name but set the Number column Width to 0cms.
When you select the Product it will display the Name and will do so for all of the current records.

[ukdane]

Sounds like a plan! :-)

How can I achieve this if I use the form in "Datasheet" view? Is it possible?

Cheers

[OBP]

Not on the form, but you can use the Form in "Continuous Forms" mode, you just have to do a little work arranging the fields and putting the field labels in the form Header.
You can however setup "lookup" a field in the tbl_mydata for the product number that will work with a form in Datasheet view, but I personally hate them as they can lead to difficulties later when using queries.

[ukdane]

I've added a combobox, with the following:
Control Source = productid
Row Source Type = Table/Query
Row Source = SELECT DISTINCTROW tbl_products.productid, tbl_products.productname FROM tbl_products ORDER BY [productname];
Column Count = 2
Bound Column = 1

But when I view the form, the Combobox only shows the productid, the productname column is empty.
What am I missing?

Cheers

[OBP]

Number column Width to 0cms

[ukdane]

Column Widths = 2cm;2cm

The first column shows the product id.
The second column (which should be the product name) is blank.

If I change the column width to:
Column Widths = 0cm; 2cm
then the box is just empty.

I can't understand why the product name is blank (every field in the combobox column 2 is blank). I've looked at the table, which incidentally is a linked Excel table, and the data is there, and the name of the column is correct.

But still no luck.

Edit: Could it be that the id column (which is correct) is a number field, but the name column is a text field?

[hansup]

I can't understand why the product name is blank (every field in the combobox column 2 is blank). I've looked at the table, which incidentally is a linked Excel table, and the data is there, and the name of the column is correct.Have you confirmed the SQL statement for the row source returns what you expect? If not, create a new query, switch to SQL View and paste in that statement. When you run the query does it return values in both the productid and productname columns?

Hans

[CreganTur]

I've only browsed this Thread, so this may have already been covered. If it has, then ignore me :razz2:

For the combobox's properties- have you changes the Column Count property to reflect the number of columns you want? Have you set values for each column width?

[ukdane]

Through a process of elimination....

1) I tried to recreate exactly what I wanted on a new form. Without luck.
2) I then tried with a non-excel linked table. It did work.

So I've copied the data from the excel linked table to the other table, and used that instead.

Is this a general problem with linked-excel tables?

Hansup: When I run the query, it works fine.
CreganTur: Yup I applied the correct number of column rows, and the correct widths.

[hansup]

Through a process of elimination....

1) I tried to recreate exactly what I wanted on a new form. Without luck.
2) I then tried with a non-excel linked table. It did work.

So I've copied the data from the excel linked table to the other table, and used that instead.

Is this a general problem with linked-excel tables?I created a form with a combox which uses a SELECT statement to retrieve values from a linked spreadsheet. It seems to work OK ... with this particular linked sheet. I don't know if there are problems in general.

Hans

[CreganTur]

Is this a general problem with linked-excel tables?


I've never had a problem with linked Excel worksheets. I'm guessing that your linked spreadsheet had column headings set to True.

It could be that there was an unusual column name in the spreadsheet that messed you up.

[ukdane]

I'm guessing that your linked spreadsheet had column headings set to True.

I did/do have column headings set to True. As it's working at the moment with the other table, I won't play around with it, but next time I need to make a change, I'll experiment with it again.

Cheers