Problem with Subform not displaying correct data

P

PeteC

Hi,

I have a problem with data in a subform which I'm using to resolve a
many-many relationship.

The system has a table called 'Products', containing the following fields
(amongst others)

ProductID (Number, Primary key)
Product Name (Text)

The second table is called 'OpPack' (Operator Packs)

OpPackID (Number, Primary key)
Description (Text)

Each OpPack is made up of several Products, and the same product can be
present in none, one or more OpPacks. To resolve this, I have a resolution
table called OpPackItem

OpPackID (Number, Primary Key)
ProductID (Number, Primary Key)
Quantity (Number - default 1, shows how many of each product is in this
line)

The table OpPackItem is set up with Lookups on both OpPackID and ProductID,
so that instead of seeing the number, you see the Description (from OpPack,
using OpPackID) or the Product Name (from Products, using ProductID)


So, for example, an OpPack of 'Retailer Request' may contain the products
'Counter Top Display' and 'Consumer leaflets' ( with quantity 100)

To construct the Op Packs, I created a form based on the table 'OpPack'.
Within this form I created (using the form wizard) a subform for OpPackItem,
which would have two columns - ProductID, and Quantity.

Now the bit that's not working.

When I look at the subform for the OpPackItem, it shows me the correct
contents of the OpPackItem table, but it's showing me the ProductID *NUMBER*
rather than the text of the 'Product Name'. It is a drop-down list, and when
I drop that list, it does show me the 'Product Names', but when I select one
it puts the Product ID in the field.

I need to see in this subform the Product Name for this form to work.

The row source for the subform is

SELECT Products.ProductID, Products.ProductName FROM Products;

The column count is 2, the column widths are 0;3cm, and the bound column is
1.

Any ideads on why this isn't working? If you need more information, please
let me know. If you would recommend going about it another way, I'm open to
that as well.

Regards,

Pete.
 
T

tina

well, from the information you posted, seems your combo box is set up
correctly to display the ProductName in the combo box control while saving
the ProductID value in the underlying field. so my guess is that the problem
is caused by the Lookup fields in the tables. suggest you remove all Lookups
from all your table fields - see http://www.mvps.org/access/lookupfields.htm
for reasons why. while you're at it, suggest you turn OFF the Name
AutoCorrect option in your db (if it isn't off already); see Tools | Options
| General tab. after doing both, compact/repair your db.

after doing the above and testing your form/subform, post back if the
problem persists, and we'll try to figure out what's causing the problem.

hth
 
P

PeteC

tina said:
well, from the information you posted, seems your combo box is set up
correctly to display the ProductName in the combo box control while
saving the ProductID value in the underlying field. so my guess is
that the problem is caused by the Lookup fields in the tables.
suggest you remove all Lookups from all your table fields - see
http://www.mvps.org/access/lookupfields.htm for reasons why. while
you're at it, suggest you turn OFF the Name AutoCorrect option in
your db (if it isn't off already); see Tools | Options
Click to expand...

Hi,

Problem is sorted now (and I've taken your advice in the Lookups).

The problem was that the control was a ListBox, when it should have been a
ComboBox. Changing it to Combo was all I needed to do to make it work. It
was my fault, because I'd always used Listboxes where I needed just a list
of values, and Combo where I wanted the user to be able to extend the list
as well (I last used them about 10 years ago when I was a programmer....!).

Many thanks for your help; it was the repeated use of the phrase 'Combo Box'
that eventually drilled it into me that I wasn't actually using them!

Regards,

Pete.
 
A

Arvin Meyer [MVP]

Actually, your problem was that you were displaying the wrong column. You
can do that with both List boxes and Combo boxes. Typically you bind the
data to column 1 (Column 0 if using the index) and you set the column width
to 0", which displays column 2 as the first column. You can change those
values, and I believe that's what happened in your case. Tina's suggestion
against using lookup fields (not lookup tables, which are fine) helps to
avoid the problem.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
T

tina

Actually, your problem was that you were displaying the wrong column. You
can do that with both List boxes and Combo boxes. Typically you bind the
data to column 1 (Column 0 if using the index) and you set the column width
to 0", which displays column 2 as the first column.
Click to expand...

i agree with you, Arvin, i would normally think the problem was that the
first column's ColumnWidth was not set to zero, or the wrong column was
bound, or both. but the settings that were posted sounded correct to me.

*****
The row source for the subform is

SELECT Products.ProductID, Products.ProductName FROM Products;

The column count is 2, the column widths are 0;3cm, and the bound column is
1.
*****

seems like those settings should return the correct result in a combo box OR
a listbox. am i missing something obvious here? <cogs in brain make grinding
noise - looks frantically for WD-40> if not, the result seems inexplicable,
so i figured it had to be because of the Lookup fields in the tables. at
least i hoped so! <g>
 
A

Arvin Meyer [MVP]

i agree with you, Arvin, i would normally think the problem was that the
first column's ColumnWidth was not set to zero, or the wrong column was
bound, or both. but the settings that were posted sounded correct to me.
Click to expand...

The settings that were posted do seem correct, but the symptoms of the
problem are in direct conflict with those settings. Perhaps they were read
incorrectly?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top