Validate partnumber before lose focus

[Cherry2005]

I have a field "Part#" (text)in the daily transactions table. When data entry person enters Part # in the form, I want to make sure the part # is a valid Part # according to a field called "Part#" in the table called Parts.
If it does not exist in the Parts talble, I want a message to pop up.
Is a Dlookup the way I want to tackle this?
I'm very new at this stuff..
Thanks,

 

[gol4]

You could use dlookup or dcount or one of the aggreate functions another option is to use a combox and set it to limit to list.

 

[missinglinq]

I prefer writing my own verification routine using DCount because if you set the property to Limit To List you get a grungy Access errror message. Rolling my own means I can have a custom Messagebox telling the user that there is no such parts # and even offer to let him enter it!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[Cherry2005]

Parts is the Table
Daily Transactions is the data entry form
Is this close?

Thanks for all your help!!


Private Sub Part_Number_LostFocus()
Dim Part_No As String

Part_No = DLookup("[Part#]", "Parts", "[Part#]= " _
& [Forms]![Daily Transactions]![Part#] & "'")
If IsNull(Part_No)

MsgBox "Contact the Industrial Engineering Department for Part# Confirmation", 1, "Unrecognized Part Number"

End If
End Sub

 

[ZmrAbdulla]

Something like this.. Need to test it..

Private Sub Part_Number_LostFocus()
If IsNull(Trim(Me.Part_No)) Then
MsgBox "Please enter a Part #", 1, "No Part Number"
    Me.Part_No.SetFocus
Exit Sub
Else
If DLookup("[Part#]", "Parts", "[Part#]<> " _
    & [Forms]![Daily Transactions]![Part_Number] & "'") Then
MsgBox "Contact the Industrial Engineering Department for Part# Confirmation", 1, "Unrecognized Part Number"
    Me.Part_No.SetFocus
Exit Sub

Else
'Do what to do...

End If
End If
End Sub

See the changes and make sure field name etc...

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.

 

[ZmrAbdulla]

Here is another update..

Private Sub txtPartNumber_Exit(Cancel As Integer)
Dim TxtToCheck As String
TxtToCheck = Me.txtPartNumber.Value
If IsNull(Trim(Me.txtPartNumber)) Then
    MsgBox "Please enter a PartNumber"
        Me.txtAnyOther.SetFocus
        Me.txtPartNumber.SetFocus
Exit Sub
Else

If DLookup("[Part#]", "tableName", "Part#='" & Forms!FormName!txtPartNumber & "'") = TxtToCheck Then
MsgBox "PartNumber matching"
'Do what you want to do

Else
MsgBox "PartNumber not matching"
        Me.txtAnyOther.SetFocus
        Me.txtPartNumber.SetFocus
Exit Sub
End If
End If
End Sub

________________________________________________________________________
Zameer Abdulla
Visit Me
Children are poor men's riches.