-1

I am trying to add a pull-down combo box to a form in MSAccess. The strange part is that the (email) column is missing. I have performed the following checks/actions

  1. Validated the SQL table (SUP_CONTACTS) has the desired column
  2. Validated MS Access has the linked table (SUP_CONTACTS) linked and contained the desired column.
  3. Deleted and relinked the table (several times), and repaired the data base (several times)

No luck, and not sure what is causing this defect.

Any help is greatly appreciated. Thanks.

FIG A: SQL Table (SUP_CONTACTS) with email column

enter image description here

FIG B: MSAccess Linked Table (SUP_CONTACTS) with email column

enter image description here

FIG C: Combobox missing email column

enter image description here

NanoNet
  • 218
  • 3
  • 11
  • 2
    Have you tried changing the type to Short Text? I don't understand why email needs to be Long Text – Jonathan Willcock Jun 30 '21 at 18:57
  • @JonathanWillcock That seemed to work actually. Thanks – NanoNet Jun 30 '21 at 19:31
  • Combo boxes truncate text at 255 characters, so it seems the wizard is wise enough to not even offer a long text column. – Andre Jun 30 '21 at 19:33
  • 1
    IN MSAccess anything greater than 255 chars is treated as a blob. Its a huge pain in the ass and forces you to design data structure in dumb and annoying ways – Doug Coats Jun 30 '21 at 22:24
  • @DougCoats A lesson I had to learn the hard way indeed. I've converted the columns and only one rediculously long email was lost.. good riddance. We are good now :) – NanoNet Jul 01 '21 at 12:57

1 Answers1

2

I would try changing the data type from long text to short text. Short text allows 255 characters which is more than enough for an email address. If this gives you an error for data type mismatch then you are going to have to get creative. Here’s one way to handle an error from the date type mismatch. Create a new local table containing only the email address from the linked table, change the data type to short text and include that field in your combo box. The downside of this is that you’ll need to repeat this process whenever you have to refresh the emails. But there are ways to streamline such a task.

Lukemorgan75
  • 44
  • 3