Zero-Length strings as a primary key 1

[KornGeek]

I'm using Access 2000. In my database, I have some reference tables that are linked to my data tables. They all have a basic structure of 2 text fields, ID and Name. For instance, my tblLocations table has fields LocationID and LocationName. In my data tables, I store the ID, which is a smaller string, and can reference the longer name. Standard one-to-many design with referential integrity.

I tried to be clever by creating a dummy record where the ID field is a zero-length string ("&quot and the Name would be set to something such as "{None}". This is primarily because one of my data tables uses three of these fields as part of the primary key, so they cannot be Null. In this table, I have assets (which may appear more than once in the table), and the Employee, Department, and/or Location who is responsible for the asset.

The problem is that on one of my forms, where the user assigns responsibility for the asset, they can select the Employee, Department, and Location by either ID or Name. I use a text box for the ID, and a Combo Box for the Name. It works quite well, except when the ID = "". Instead of the combo box showing "{None}", it is empty.

Is there some rule against using zero-length strings as the primary key? Is there some reason Access doesn't like this? Is it because Access doesn't like me personally? Please offer whatever help you can. It will be greatly appreciated. Sorry for my long-winded post, and thank you for bearing with me.

 

[Darrylles]

Hi Korn,

I can't understand where this concept of 'zero-length' string primary key came from.

A primary key must be unique and not be null, a zero-length string is really 'null' as far as I'm concerned.

If the ID field is pk, then you can't do this - can you? (Just as you can't have nulls as part of your tri-part pk).

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[KornGeek]

Actually, Access treats "" and Null very differently. The general concept is that Null means "I don't know what the value is", while "" means "I know what the value is, but it's empty." The tables allow me to store "" in the field, and when I use commands such as DLookup, it functions correctly it just on the form with the combo boxes that it fails.

 

[RiverGuy]

I don't quite understand. Do you have them enter an ID, then the combo-box will be populated with names that are applicable to that ID?

And if you leave the ID box blank, you do not get a choice of "{None}"?

If this is the case....for one I would use VBA. But what if the user hits the space bar, and you might have ' ' instead of ' '. Maybe do something like evaluate the first character of the text box, and if it is a space, use the first character, which would be an empty string, and populate the combo box thereafter.

 

[KornGeek]

I appreciate you taking the time to help me. Maybe I can explain it a little better. They can either enter the ID, or select from the combo box. Each Name is indexed (No Duplicates). This means that if a name is selected from the combo box, it changes the ID box, and vice versa. I actually developed this functionality in a previous database.

Hoever, with the zero-length strings, if I leave the ID box blank, the combo box is blank. If I type "" in the ID box, the combo box is blank. If I select "{None}" from the combo box, the ID box is blank, and the combo box goes blank.

I agree that I will need to do checking to strip leading spaces from my ID's, but I'm trying to get it to work under the best of circumstances first. Then, I'll start working on the error conditions.

When you say you would use VBA, what would you do in VBA? I'm not afraid to use code. In fact, I already have several modules, and every form or report has VBA code behind it. I just don't see how I can fix this with VBA.

 

[KornGeek]

To reproduce this quickly and easily, create a table, (tblStuff) with two fields, ID and Name. Make ID the primary key and set Name to Indexed(No Duplicates). Populate the table with the following data
[tt]
ID Name
"1" "Test1"
"2" "Test2"
"" "Now You See Me"
[/tt]

Create a form (frmStuff) that has tblStuff as its datasource. Create a text box linked to the ID field. Copy and paste the control, then convert to a combo box. Keep the ID field as the data source for the combo box, select tblStuff as the RowSource, set BoundColumn to 1, ColumnCount to 2, and ColumnWidths to 0";1".

Open the form, and enter 1 in the text box. The combo box will update. Select Test2 in the combo box, and the text box will update. Select "Now You See Me" in the combo box, and it will mystically disappear.

[insert wizard smiley here]

 

[RiverGuy]

I understand now.

With VBA you might open a recordset for that tblStuff for all of the rows.

Don't set the datasource of the combobox.

When a user changes the id, or maybe when he clicks the combobox, do a .Find on the recorset and use that records value and update the text the combobox.

If the user leaves the box blank, when he clicks the combo box, you would be finding the record that has the ID of your textbox, which should be "".

Similarily, when the combo box is validated, do a find on that recordset and update the text box with the id of that recordset, this time, the find would be based on the name. But, I think the combo box would need to lose focus to validate.

I'll have to try it out.

 

[RiverGuy]

I tried all sorts of things. I couldn't get the recordset to behave properly or even open up in VBA. I'm sure there is a way, but I did see your problem and replicate it.

 

[KornGeek]

I'm beginning to think I may have actually discovered a [gasp] bug in Access. But I thought Microsoft software was always bug-free. That must mean that this is an undocumented feature. :->

The part that I'm really having trouble believing is that I'm the first Tek-Tips user to discover this. Somebody has to have tried this before. Or so I would think.

Thanks for your effort reproducing and testing this. Please let me know if you have any other ideas. I may just have to create a dummy ID and use that. It's not as clean-looking, but will work better.

 

[Transcend]

Hi there

you can't[/b/] have a zero length string as a primary key

this isn't a bug .. why would you ever want "" (in other words nothing as your primary key! "" Doesn't identify anything!

Transcend

 

[KornGeek]

What it allows me to do is create a None of the Above type of option. The zero-length string is a unique value, although it is an unusual one. It then ties to a more descriptive name field. What this allows me to do is to allow my users to view it as selecting nothing, when in fact they are selecting a unique record.

Access supports "" as an entry in a primary key field.
Access does not support having Null in the primary key.

One way to view the difference is to write a small portion of code and walk through it.

dim a as long
dim b as long

a = len(Null)
b = len("&quot

If you step through the code and check the value of each variable, a will be Null, while b will be 0.

&quot;&quot; <> Null

 

[Transcend]

Hmmm ok .. have you tried &quot;0&quot; instead of &quot;&quot; ?

Transcend

 

[Darrylles]

Hi again Korn,

My first response was accurate as far as Access is concerned: you will have problems comparing Access's interpretation of NULL and 'empty string' with 'real' programming language's interpretations.

Access does not work to standard, and it's wound me up no-end trying to get a definitive answer to this.

In some instances you MUST use len() to get the response that you expect, dependant on the data-type. It's disgraceful that Access works to a different standard.

Regards,

Darrylle





you will have problems &quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk

 

[KornGeek]

Transcend,
As far as using &quot;0&quot;, that kind of defeats the purpose of what I want to do. I want to make it appear that the field is blank. Also, my app appends leading zeros to make it a 10-character ID field. This would mean that when a user wants the field to be blank, they get &quot;0000000000&quot;. Unfortunately, this is also my current best workaround. I may end up going this route, but I hope I can think of something else.

Hmmm... I just had the idea that since my app doesn't add leading zeros unless the string is all numeric, maybe I can add spaces instead if it is alphanumeric. That way, I would be dealing with a string of &quot; &quot;. This would still appear blank to the user, but should keep Access happy.

Thanks for the suggestions and ideas everybody. I'll let you know how this idea works.

 

[KornGeek]

Well, it sure seemed like a good idea at the time. Unfortunately, Access interprets &quot; &quot; as Null, so it wouldn't even let me add the record to the table.

I may just have to give in and go with Transcend's suggestion of using zero as the ID.

 

[KornGeek]

I finally got this figured out. For those of you who are interested, it took a little bit of work, but it actually works nicely now.

First of all, I set the default value of these controls to &quot;&quot;. Then, in the AfterUpdate event, I added code like this:
Me.controlname = Nz(Me.controlname, &quot;&quot

This ensures the value will never be Null.

As for the display, I noticed that combo boxes didn't display correctly, but text boxes do. What I did was overlay a text box over the top of the combo box leaving only the down arrow button showing, and I shrunk the combo box so that the width is 0.1667&quot; (the width of the arrow button). I expanded the data source of the form to include the &quot;Name&quot; data fields, and set the text fields' data sources to these Name fields.

Using code, I set it so when the user clicks on the arrow button, the text box turns invisible, and the combo box grows to cover the same area. When the user clicks on the text box, I change the focus to the combo box, which triggers the above code. After the combo box is updated, I make the text box visible again, and reshrink the combo box.

It has the effect of making it look like the combo box, except that it can now handle &quot;&quot; as the ID field. It's a lot of work for a little feature, but if the feature is important, this is a way to do it.