Check if Value exists in a table

[Perlyman]

Heres the scoop..

Form with a text box([PartFind} - User enters a part number and the onUpdate Event - verify if the part number exists in "Main" Table Field "PN". I need some type of return (true or false) if the value exists in the table, if not MsgBox (Would you like to add the part).

My first attempt was to add a combobox based on the "PN" Field to the form. Then added event for "NOT IN LIST"
It worked (if not in list- msgbox (would u like to add a part?)the problem being I was unable to trap the default Access error msg "The Item you entered was not an item in the list" .

Any thoughts appreciated - Rob

 

[DreamerZ]

Here's a sample of a NotInList function:

Private Sub GameSite_NotInList(NewData As String, Response As Integer)

If MsgBox("Game Site not in list. Add it?", vbQuestion + vbYesNo, _
"Game Site&quot = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "AddGameSite"
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If

End Sub

I would recommend making the PartNumber find a combo box with a row source of the table (or a query of the table). Then add the code above to the OnNotInList property. The code checks for the entry in the list and if it's not, asks if they want it added. Clicking yes opens another form with a single text box allowing for the adding of the new part number (the text box control source is the part number in the table). Clicking okay on that form closes it and requeries the original combo box to add the new part number.

Hope that helps.

DreamerZ

 

[Perlyman]

Great!

It worked no Access Error!

Thanks DreamerZ

Rob