Lookup Column not extracting complete data

[accUser2010]

Lookup Column not extracting complete data(Solved)

Hi Everyone,
First of I would like to thank all the experts who are doing an excellent job in helping others. I hope my problem gets resolved soon.

I am having problem while using lookup column.

I am refering some other table values by using lookup column, its working but its not displaying the complete data. For example, I want to connect Table1.Column1 to Table2.Column1 whose data type - text. It has values like M0001, M0002. When I look in datasheet view of Table1.Column1, its displyaing a drop-down list of 0001, 0002.

Note: In Table2.Column1 I am using inputmask property : "M"0000. That means it will always display M and this should be followed by 4 digits. So in this case user is only entering 0001 and it displays M0001.

I am not sure if this is of any importance or not but still thought it might give you guys a clear idea.

I want to use the complete M0001 as the first letter supposed to be the distinguishing factor between the IDs of several tables.

Any help will be much appreciated.
Thanks in advance

 

[Kryst51]

You should not use lookups at the table level, see my signature for an article that explains why. You should have an FK reference to the ID of the other field, then use a combo box to do the lookup at form level or a query which has both tables in it.

Edit: @ Bob,

 

[boblarson]

1. You should NOT be using lookups at TABLE level. You use them at FORM Level.

2. Read here to see why.

3. Get rid of the lookups in the tables and use them only with combos or listboxes on FORMS.

 

[boblarson]

Oops, I was too slow on this one.

 

[accUser2010]

Thanks a lot Bob and Krst51. You guys are awesome.

I never knew that lookup column can be such a big problem at table level. Initially I made PK and FK but I thought using lookup column was cool and I messed up the whole thing.

I will make appropriate changes to all my tables.

One more thing I think there is also problem if we use inputmask. Can you please tell me if we write "M"0000, in the datasheet view it displays M______. And when user enters 0001 then the value look like M0001. But i am not sure if it is actually storing the First Alaphabet along with the digits. Does Access store only digits if Inputmask is used??

Thanks

 

[boblarson]

You have to TELL it to store the text as well.

For example - the last 0 after the semi-colon tells it to store the literal characters as well as the numbers. And the last one - the underscore is what is the placeholder.

You also have to use \ for the literal character.

So,

\M0000;0;_

Should get you what you want.

 

[accUser2010]

Thanks so Much Bob. I am changing the whole thing.

I am so glad that I found this forum and my problem got solved.

Thanks Once again