Lookup Field and Input Masks

[tbaxter]

I have my database done, for the most part. But I want to add a lookup to a field in my Component table so that I can associate a particular number (paragraph number in a pub) to a failure mode description. In the Failure Mode table, I have associated a paragraph with a failure mode:

Example: 6-66.2 Visual
6-66.8 Continuity
6-66.8 Insulation

Yes, some paragraphs have more than one failure mode. The primary key for this table is an autonumber which allows me to do this with minimal headache.

When I created a lookup within the appropriate field in the Component table, it erases my format/input mask and makes it look like this:

6662 Visual
6668 Continuity

I wasn’t able to put an input mask in the General tab of the FailureMode field in the Component table – I guess because it has the paragraph AND the failure mode in the lookup and 9-99.99;;_ just won’t cover the text for the failure mode description. The input mask is present in the Failure Mode table but isn't "crossing over" to the lookup!

I REALLY need those “masks” in the paragraph callout to help me distinguish and match the failure mode! Can someone please explain how to do this?

Thanks!

 

[boblarson]

Okay, this may sound harsh, but DO NOT USE LOOKUPS IN TABLES!!!!!! Microsoft should be beaten up for allowing that. They will only cause you grief and suffering, even though on first glance they look attractive.

See more info about the evils of lookup fields in tables here:
http://www.mvps.org/access/lookupfields.htm

You should be using FORMS for table input and not direct access in the tables. If that is the case then you can use combo boxes or list boxes as lookups for your fields.

 

[tbaxter]

Looks like I need to pull out my old textbook or Access Bible (it's a 97 Bible and my Access version is 2003 but the method is probably the same, right?) as I don't remember how to do this (so that it will work, that is)! That old saying "if you don't use it you lose it" is definitely true . . .

I did try with a combo box in my input form but the paragraph numbers, again, showed up as 6662, etc., vice 6-66.2. I'll look at it again next week - I'm taking some time off today for fun in the sun!

 

[boblarson]

I did try with a combo box in my input form but the paragraph numbers, again, showed up as 6662, etc., vice 6-66.2.

If you need to format them like that you will probably need to store them as TEXT because numbers really wouldn't store that way. You could store them as numbers and format them to show as you wish when displayed (by setting the format "0-00.0" but what if there are 3 digits after the dash and one after the decimal, so as an example, what if you had 6-100.3? If you stored the digits 61003 then the format would show as 61-00.3 and not 6-100.3

So, I think in your case I would store them as text, exactly as entered.

 

[tbaxter]

Actually, I do have that field set as a text field. Was my mistake in setting up the input mask? By using the mask, does that do away with the dashes and periods?

The data type is text, field size is 8, input mask is #9\-99.99;;_. I thought I had set it up to store the dashes, etc., in the field but I may not have.

 

[boblarson]

It looks, to me, like the input mask is set properly, so I'm not sure why the dash wouldn't be saving and then displaying.

 

[tbaxter]

In the table, queries, form and reports, it shows up as 6-66.2, etc. It's just in the drop-down list (combo box, whatever it's called) when I'm trying to associate the paragraph with a particular failure mode on the input form. I want a quick recognition - I don't want to have to stop and think, "okay, that shows 6662, so that means it's 6-66.2." But that might be what I have to go with.

Thanks for trying to help, though.

 

[boblarson]

What is the underlying rowsource of the combo box? It really shouldn't be showing it that way, so I'm wondering what it is. Can you either
1. Post the SQL behind the combo box
2. or post the db

 

[tbaxter]

Well, here's an odd development. I made a minor adjustment and now the failure mode field is populated, based on the paragraph numbers, I suspect.

But I need to update them to further identify them. As I mentioned, one paragraph may have both insulation and continuity checks in it and I want to differentiate between the 2. I think I need to tweak the failure mode table, as a result, to reflect the subparagraph number. For example, if 6-66.8.c is insulation and 6-66.8.f is continuity, I want to "separate" them so I'll know which units failed one test and which units failed the other.

Let me work on it another day or two - if that fails, I'll depopulate the database and post it.

Thanks!