Validating only a portion of a field on a form

G

Guest

I have a table that the first field is a lot number. The lot number is a
concatonation of several items, date, part ID, operator ID, etc.

I have a second table that contains the operator ID values with their
repective details, name, address etc.

What I would like to do is check when a lot number is entered that the
portion that is the operator ID can be found in the second table. The
operator ID is always TWO characters long, and always the same number of
characters from the begining so I can pull the string pretty easy. How do I
compare this value to another table?
 
G

Guest

For example, we will assume operator ID is the 10th and 11th character of the
lot number. You can use the DLookup to validate it.

If IsNull(DLookup("[operator Id]", "OperatorTable", "[operator id] = '" _
& Mid(Me.LotNumber, 10, 2) & "'") Then
MsgBox "Operator " & Mid(Me.LotNumber, 10, 2) & " Not Fond"
End If
 
G

Guest

Form is named M-3003 PRODUCITON INFO
Field is named M-3003 LOT
table with operator data is called OPERATOR CODES
field in operator codes to check lot against is OPERATOR ID

ALL FIELDS ARE TEXT

I put the following in the afterupdate event
Private Sub M_3003_LOT_AfterUpdate()
If IsNull(DLookup("[OPERATOR ID]", "OPERATOR CODES", "[OPERATOR ID] =" &
Mid(Me.M_3003_LOT, 7, 2))) Then
MsgBox "Operator " & Mid(Me.LotNumber, 10, 2) & " Not Found"
End If
End Sub

Each time I run the form I get a type mismatch error. What have i messed up?

Klatuu said:
For example, we will assume operator ID is the 10th and 11th character of the
lot number. You can use the DLookup to validate it.

If IsNull(DLookup("[operator Id]", "OperatorTable", "[operator id] = '" _
& Mid(Me.LotNumber, 10, 2) & "'") Then
MsgBox "Operator " & Mid(Me.LotNumber, 10, 2) & " Not Fond"
End If

ED007 said:
I have a table that the first field is a lot number. The lot number is a
concatonation of several items, date, part ID, operator ID, etc.

I have a second table that contains the operator ID values with their
repective details, name, address etc.

What I would like to do is check when a lot number is entered that the
portion that is the operator ID can be found in the second table. The
operator ID is always TWO characters long, and always the same number of
characters from the begining so I can pull the string pretty easy. How do I
compare this value to another table?
Click to expand...
Click to expand...
 
G

Guest

It depends on what the data types involved are. If OPERATOR ID is a numeric
field, it should work. If it is a text field (I think it is based on the
error you are getting) then it needs to be in quotes for the DLookup:
If IsNull(DLookup("[OPERATOR ID]", "OPERATOR CODES", "[OPERATOR ID] ='" &
Mid(Me.M_3003_LOT, 7, 2) & "'")) Then
Click to expand...


ED007 said:
Form is named M-3003 PRODUCITON INFO
Field is named M-3003 LOT
table with operator data is called OPERATOR CODES
field in operator codes to check lot against is OPERATOR ID

ALL FIELDS ARE TEXT

I put the following in the afterupdate event
Private Sub M_3003_LOT_AfterUpdate()
If IsNull(DLookup("[OPERATOR ID]", "OPERATOR CODES", "[OPERATOR ID] =" &
Mid(Me.M_3003_LOT, 7, 2))) Then
MsgBox "Operator " & Mid(Me.LotNumber, 10, 2) & " Not Found"
End If
End Sub

Each time I run the form I get a type mismatch error. What have i messed up?

Klatuu said:
For example, we will assume operator ID is the 10th and 11th character of the
lot number. You can use the DLookup to validate it.

If IsNull(DLookup("[operator Id]", "OperatorTable", "[operator id] = '" _
& Mid(Me.LotNumber, 10, 2) & "'") Then
MsgBox "Operator " & Mid(Me.LotNumber, 10, 2) & " Not Fond"
End If

ED007 said:
I have a table that the first field is a lot number. The lot number is a
concatonation of several items, date, part ID, operator ID, etc.

I have a second table that contains the operator ID values with their
repective details, name, address etc.

What I would like to do is check when a lot number is entered that the
portion that is the operator ID can be found in the second table. The
operator ID is always TWO characters long, and always the same number of
characters from the begining so I can pull the string pretty easy. How do I
compare this value to another table?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks that got it working in the form. You were a great help. While you
are being helpfull one more question.

I set up a query and used
Expr2: DLookUp("[OPERATOR NAME]","OPERATOR CODES","[OPERATOR ID] =" & "Mid(
[M-3003 PRODUCTION INFO]![M-3003 LOT] , 7, 2)")

as one of the fields.

I keep getting an error that [M-3003 PRODUCTION INFO]![M-3003 LOT] can not
be found. This is even though I have this set to the first item to show in
the query.

What have I missed this time?

Again thanks for all your help.

Klatuu said:
It depends on what the data types involved are. If OPERATOR ID is a numeric
field, it should work. If it is a text field (I think it is based on the
error you are getting) then it needs to be in quotes for the DLookup:
If IsNull(DLookup("[OPERATOR ID]", "OPERATOR CODES", "[OPERATOR ID] ='" &
Mid(Me.M_3003_LOT, 7, 2) & "'")) Then
Click to expand...


ED007 said:
Form is named M-3003 PRODUCITON INFO
Field is named M-3003 LOT
table with operator data is called OPERATOR CODES
field in operator codes to check lot against is OPERATOR ID

ALL FIELDS ARE TEXT

I put the following in the afterupdate event
Private Sub M_3003_LOT_AfterUpdate()
If IsNull(DLookup("[OPERATOR ID]", "OPERATOR CODES", "[OPERATOR ID] =" &
Mid(Me.M_3003_LOT, 7, 2))) Then
MsgBox "Operator " & Mid(Me.LotNumber, 10, 2) & " Not Found"
End If
End Sub

Each time I run the form I get a type mismatch error. What have i messed up?

Klatuu said:
For example, we will assume operator ID is the 10th and 11th character of the
lot number. You can use the DLookup to validate it.

If IsNull(DLookup("[operator Id]", "OperatorTable", "[operator id] = '" _
& Mid(Me.LotNumber, 10, 2) & "'") Then
MsgBox "Operator " & Mid(Me.LotNumber, 10, 2) & " Not Fond"
End If

:

I have a table that the first field is a lot number. The lot number is a
concatonation of several items, date, part ID, operator ID, etc.

I have a second table that contains the operator ID values with their
repective details, name, address etc.

What I would like to do is check when a lot number is entered that the
portion that is the operator ID can be found in the second table. The
operator ID is always TWO characters long, and always the same number of
characters from the begining so I can pull the string pretty easy. How do I
compare this value to another table?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You have to refer to the form. It thinks it is looking for a table named
[M-3003 PRODUCTION INFO] with a field named [M-3003 LOT]. Also remember we
need the results inside quotes, but the expression outside the quotes. With
the expression inside the quotes, it will be looking at it as a literal value
rather than the results of the expression.
Try:
Expr2: DLookUp("[OPERATOR NAME]","OPERATOR CODES","[OPERATOR ID] = '" &
Mid(Forms![M-3003 PRODUCTION INFO]![M-3003 LOT] , 7, 2) & "'")

ED007 said:
Thanks that got it working in the form. You were a great help. While you
are being helpfull one more question.

I set up a query and used
Expr2: DLookUp("[OPERATOR NAME]","OPERATOR CODES","[OPERATOR ID] =" & "Mid(
[M-3003 PRODUCTION INFO]![M-3003 LOT] , 7, 2)")

as one of the fields.

I keep getting an error that [M-3003 PRODUCTION INFO]![M-3003 LOT] can not
be found. This is even though I have this set to the first item to show in
the query.

What have I missed this time?

Again thanks for all your help.

Klatuu said:
It depends on what the data types involved are. If OPERATOR ID is a numeric
field, it should work. If it is a text field (I think it is based on the
error you are getting) then it needs to be in quotes for the DLookup:
If IsNull(DLookup("[OPERATOR ID]", "OPERATOR CODES", "[OPERATOR ID] ='" &
Mid(Me.M_3003_LOT, 7, 2) & "'")) Then
Click to expand...


ED007 said:
Form is named M-3003 PRODUCITON INFO
Field is named M-3003 LOT
table with operator data is called OPERATOR CODES
field in operator codes to check lot against is OPERATOR ID

ALL FIELDS ARE TEXT

I put the following in the afterupdate event
Private Sub M_3003_LOT_AfterUpdate()
If IsNull(DLookup("[OPERATOR ID]", "OPERATOR CODES", "[OPERATOR ID] =" &
Mid(Me.M_3003_LOT, 7, 2))) Then
MsgBox "Operator " & Mid(Me.LotNumber, 10, 2) & " Not Found"
End If
End Sub

Each time I run the form I get a type mismatch error. What have i messed up?

:

For example, we will assume operator ID is the 10th and 11th character of the
lot number. You can use the DLookup to validate it.

If IsNull(DLookup("[operator Id]", "OperatorTable", "[operator id] = '" _
& Mid(Me.LotNumber, 10, 2) & "'") Then
MsgBox "Operator " & Mid(Me.LotNumber, 10, 2) & " Not Fond"
End If

:

I have a table that the first field is a lot number. The lot number is a
concatonation of several items, date, part ID, operator ID, etc.

I have a second table that contains the operator ID values with their
repective details, name, address etc.

What I would like to do is check when a lot number is entered that the
portion that is the operator ID can be found in the second table. The
operator ID is always TWO characters long, and always the same number of
characters from the begining so I can pull the string pretty easy. How do I
compare this value to another table?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto-update a field based on input from previous field 0
Auto fill fields in sub form 1
Read Only Form 1
Filling in fields to a partially complete record in a table using a form 0
Open another form if field checked in one form 1
recall of infor on a form 2
Validation Question 4
Filter Frustration 6

Top