I've created several tables each that are linked and use look ups, the lookups are a couple letters followed by a number e.g. HP00001 and they display correctly in the table they originate from but when in other tables it only shows the number part so just 00001,
I don't understand why, any help appreciated... thanks
The issues you are experencing are exactly the reason that most of us do not reccommend defining and using lookups at the table level. They cause more problems than they are worth.
One problem that occurs is that when these lookups are defined at the table level, usesr tend to then try to use the table as a data entry point. Forms should be used to interact with the data and tables should be used just to store the data.
You should create and use lookups in form by using combo boxes or list boxes.
What you are experencing is caused by the fact that your table is actually storing the record ID value from the lookup table in the field of your table. But when you have defined a lookup you then provide a way to see the values from the lookup table but the visible values are not actually in the field.
As to the question about Table Level Inputs Masks and Field Sizes:
Field Size has to be declared at Table Level, AFAIK
Input Masks can be used at either Level, but declaring it at Table Level keeps you from having to do so repeatedly if the data can be entered thru multiple Forms.
Having said that, many experienced developers, myself included, believe Input Masks to be evil! They often cause more problems than they solve.
You have to remember, when using Input Masks, that the user may not always arrive at the control by tabbing to it from the previous control, but may click on the control. If a mask is being used and the user doesn't click to insert the cursor at the very beginning of the text box, he/she may start to enter data without realizing that they're not at the beginning. When they leave the control they'll get an error message because the data wasn't entered as the input mask dictated. They'll then have to go back and re enter the info. The way to avoid this is to use something like this:
Code:
Private Sub YourControlName_Click()
YourControlName.SelStart = 0
End Sub
Even tabbing to the control can be problematic if in the Options for the database the default behavior for "Entering Field" is set to anything other than "Go to start of field." The answer to this is to set the cursor to the beginning of the field when the field is tabbed into:
Code:
Private Sub YourControlName_GotFocus()
YourControlName.SelStart = 0
End Sub
Common sense would dictate that the second snippet of code should work for both cases, since click into the field should mean the textbox has focus, but for some reason this doesn't work for all versions.
Developers who avoid using Input Masks control the formatting of entered data by doing so in the Control's AfterUpdate event.
Ok I tried that and the tables shows up the full ID now including the letters in every table, but it still doesn't show up correctly in the forms some look ups show it correctly in the top box but the drop down options are just the numbers but when you select a number it displays it correctly at the top, and others just have the numbers including the top box and never show the letters, i don't why they are acting differently as done every look up and form with the same method.
I just tried a query and if I type in just the numbers it finds the records but not if I include the letters too even though that is how it is stored in the tables... very confusing
