Check TEXT BOX entry agains table info or EXCEPTION values

[kealaz]

I have the following code being used on the After Update Event Procedure for
a text box.

**********************************************

If DCount(1, "tblDWGLOG", "PART_NO = '" _
& Me!USED_ON & "'") < 1 Then
MsgBox "Invalid part number. Please re-enter.", _
vbOK, "Missing Data"
Cancel = True
Me.USED_ON = Null
Me.QTY_USED.SetFocus
Me.USED_ON.SetFocus


**********************************************


This code is supposed to check the information entered against records in
the field PART_NO in my table tblDWGLOG. All of the part numbers are
[xxxx-xxxx-A] (where x is a number and A is a rev letter). If the part
number is found, it accepts the entry. If the part number is not found, it
gives an error message, and prompts the user to try again.

When I initially set this up, I forgot about a few exceptions, and need this
text box to also accept the following values, in addition to the values in
the PART_NO field of my table. (but error message on any other values)

ASSEMBLY
CONFIG
CUSTOMER
TOP ASSEMBLY

How do I change the above code to include these as acceptable entries in
this field, and still check the tblDWGLOG table for any other input?


Thank you so much for any assistance you can provide.

 

[Piet Linden]

I have the following code being used on the After Update Event Procedure for
a text box.

**********************************************

If DCount(1, "tblDWGLOG", "PART_NO = '" _
& Me!USED_ON & "'") < 1 Then
MsgBox "Invalid part number. Please re-enter.", _
vbOK, "Missing Data"
Cancel = True
Me.USED_ON = Null
Me.QTY_USED.SetFocus
Me.USED_ON.SetFocus

**********************************************

This code is supposed to check the information entered against records in
the field PART_NO in my table tblDWGLOG.  All of the part numbers are
[xxxx-xxxx-A] (where x is a number and A is a rev letter).  If the part
number is found, it accepts the entry.  If the part number is not found, it
gives an error message, and prompts the user to try again.  

When I initially set this up, I forgot about a few exceptions, and need this
text box to also accept the following values, in addition to the values in
the PART_NO field of my table. (but error message on any other values)

ASSEMBLY
CONFIG
CUSTOMER
TOP ASSEMBLY

How do I change the above code to include these as acceptable entries in
this field, and still check the tblDWGLOG table for any other input?

Thank you so much for any assistance you can provide.

Why not use a combo box with the limit to list property set to True?
If you wrote the other 4 values to a table, you could just union the
two sets together... then your problem should be solved, right?

 

[Duane Hookom]

I'm not sure why you would not use a combo box for the user to select a part
number from tblDWGLOG. You could use a union query as the Row Source to add
ASSEMBLY
CONFIG
CUSTOMER
TOP ASSEMBLY

 

[kealaz]

Hi Duane,

Thank you very much for your feedback. I went ahead and changed the text
box to a combo box, per your suggestion, and it works great! I don't know
why I didn't do that from the beginning. It seems much simpler. Thanks for
the suggestion.

I don't know how to write a Union query that would incorporate the extra
words as allowable values. I have created a query and have that as the row
source for the combo box. Here is the code.

SELECT tblDWGLOG.PART_NO
FROM tblDWGLOG;

How do I change this to include the values
ASSEMBLY
CONFIG
CUSTOMER
TOP ASSEMBLY

Thank you so much for your help!!!

 

[Duane Hookom]

Try this union query SQL as you Row Source:

SELECT "ASSEMBLY" as PartNo
FROM tblDWGLOG
UNION
SELECT "CONFIG"
FROM tblDWGLOG
UNION
SELECT "CUSTOMER"
FROM tblDWGLOG
UNION
SELECT "TOP ASSEMBLY"
FROM tblDWGLOG
UNION
SELECT tblDWGLOG.PART_NO
FROM tblDWGLOG;

 

[kealaz]

This worked great Duane! Thanks a bunch!!!