Problem with lookups

[gacton]

Access 2003- I put together a PO database some time ago. The other day, after
some macros 'hatled' I found a couple of lines in a macro missing. I replaced
them but found my supplier lookup is malfunctioning. When I click the drop
down in my lookup table, the list of suppliers is supposed to pull down. It's
blank. My suppliers are still in the supplier table, and queries and forms
can display all information. I added more fields to the lookup and noticed it
seems that only numeric data will display. All text fields are blank. I am
baffled. What can I do?

 

[gacton]

More info- The database is stored on our server. This issue only seems to be
with my computer. Other computers in the office can see the data normally in
the pull down list.

 

[Douglas J. Steele]

Sounds as though you probably were hit by the bug in the Office 2003 SP3.

Try installing the hot fix from http://support.microsoft.com/kb/945674

 

[John W. Vinson]

Access 2003- I put together a PO database some time ago. The other day, after
some macros 'hatled' I found a couple of lines in a macro missing. I replaced
them but found my supplier lookup is malfunctioning. When I click the drop
down in my lookup table, the list of suppliers is supposed to pull down. It's
blank. My suppliers are still in the supplier table, and queries and forms
can display all information. I added more fields to the lookup and noticed it
seems that only numeric data will display. All text fields are blank. I am
baffled. What can I do?

Get rid of this lookup field, and preferably all the other lookup fields in
your database:

http://www.mvps.org/access/lookupfields.htm

gives a critique of what many of us consider a misfeature.

Instead, use a Form base on your table, and put a Combo Box on the form to
select the supplier. It gives you a LOT more power and control.

You should probably check that you have a good, working, current backup (if
you don't, MAKE ONE NOW!!) and use Tools... Database Utilities... Compact and
Repair. The lookup probably stopped working because your database has become
corrupted, and there may well be other problems that have not yet become
visible.

 

[gacton]

I have tried your suggestion to no avail. I have also just uninstalled sp3,
and still no change. I am thinking to uninstall and re-install Office, unless
there is more to try.

 

[Douglas J. Steele]

Well, John did raise a good point. I assumed you were talking about a combo
box on a form, but you may be talking about a lookup field in a table. If
you are talking about a lookup field in a table, John's absolutely correct:
get rid of it! See the reference he cited (
http://www.mvps.org/access/lookupfields.htm ) for some of the reasons why.

 

[gacton]

Well, I think that is what I am doin. I have forms that by clicking the down
arrow will give a drop down list to choose from. Once the supplier is
selected from the list, an update query runs to fill in the rest of the
supplier data. I have just created a lookup table to try and troubleshoot.
But, even in the lookup table, text doesn't display. Numeric data will.

 

[John W. Vinson]

Well, I think that is what I am doin. I have forms that by clicking the down
arrow will give a drop down list to choose from. Once the supplier is
selected from the list, an update query runs to fill in the rest of the
supplier data. I have just created a lookup table to try and troubleshoot.
But, even in the lookup table, text doesn't display. Numeric data will.

If you are COPYING data from the supplier table into some other table -
DON'T!!!

Relational databases use the "Grandmother's Pantry Principle": "A place - ONE
place! - for everything, everything in its place". The supplier data should
exist in the Supplier table and *ONLY* in the supplier table. The ONLY
supplier field you need in this table is the unique SupplierID.

You can display it on a Form by pulling it from the combo box, using a textbox
with a control source like

=comboboxname.Column(n)

where n is the zero based index of the field in the combo's row source - e.g.
if the supplier's phone number is the fifth field in the combo's row source
query (and the combo's Column Count is five or larger) use Column(4).

To display supplier data on a Report, use a Query joining the sales table to
the supplier table, by SupplierID.

 

[gacton]

Well, first- I suspect something is amiss with my computer because I can see
all the data on other computers in the office.
Second- I am using a combo box on a form to select the supplier. Only the
supplier name is stored in the actual PO table.
I just created a new form with a new combo box for the supplier name (I've
tried this several times). Additional fields for the lookup include, ID,
phone number, address, city, state, and zip. When I click to open the list of
suppliers, I see all fields, but the data for non-numeric fields is blank. I
can see phone number, zip, and ID.

 

[gacton]

No, it's a combo box on a form. Please see my reply to his post.

 

[John W. Vinson]

Well, first- I suspect something is amiss with my computer because I can see
all the data on other computers in the office.
Second- I am using a combo box on a form to select the supplier. Only the
supplier name is stored in the actual PO table.
I just created a new form with a new combo box for the supplier name (I've
tried this several times). Additional fields for the lookup include, ID,
phone number, address, city, state, and zip. When I click to open the list of
suppliers, I see all fields, but the data for non-numeric fields is blank. I
can see phone number, zip, and ID.

There was a bug in SP3 of Access2003 that caused combos to appear blank.
There's a hotfix: see

http://support.microsoft.com/kb/945280/en-us

 

[gacton]

I tried something- I exported my supplier table to Excel. Then I created a
new blank database and imported that supplier Excel sheet to my supplier
table. I created a combo box on a new blank form, called SUPPLIER NAME. The
pull down works fine.
Now with my existing database (the one with the problem), when I create a
new PO, a PO MAIN form opens. On this form, is a SUPPLIER combo box. When I
click to expand the supplier list, it pulls down, but no names can be seen
(it's blank). This is why I started this post. I can type in the supplier
name (as long as it's excatly like it's stored), and the rest of the supplier
info will come up automatically (like I programmed it to. Mind you, this PO
database has been in use for well over 2 years now before this problem
cropped up.

 

[gacton]

I fixed it. I guess the supplier table somehow got corrupted.
I exported the SUPPLIER table to an Excel file. Since it worked with my test
db, I imported the Excel sheet into my existing db. I renamed the original
SUPPLIER table and named the imported one exactly as it should be. Mind you,
I like to keep my data separate (back end database) from the functional front
end database. Now when I open my front end PO Database and create a new PO,
and click on the SUPPLIER pull down, my suppliers are there.

 

[John W. Vinson]

I fixed it. I guess the supplier table somehow got corrupted.
I exported the SUPPLIER table to an Excel file. Since it worked with my test
db, I imported the Excel sheet into my existing db. I renamed the original
SUPPLIER table and named the imported one exactly as it should be. Mind you,
I like to keep my data separate (back end database) from the functional front
end database. Now when I open my front end PO Database and create a new PO,
and click on the SUPPLIER pull down, my suppliers are there.

Glad you got it fixed.

However, if there is one corrupt table... there is probably more corruption.
I'd really suggest creating a new database and importing everything EXCEPT the
table you've determined to be corrupt. Reestablish relationships and workgroup
security if necessary, and compact and repair the new database.

 

[gacton]

To the best of my knowledge, there was 1 more corrupted table, delivery
address (since we have multiple locations), and I fixed that with the same
corrective action as SUPPLIER. I will work on creating a new db and import as
you suggest. Thanks.

 

[David W. Fenton]

However, if there is one corrupt table... there is probably more
corruption. I'd really suggest creating a new database and
importing everything EXCEPT the table you've determined to be
corrupt. Reestablish relationships and workgroup security if
necessary, and compact and repair the new database.

I think it's much more likely that it was a compiled SQL problem,
and a compact would also have fixed it.