Non combo lookup validated by table

[Mike Smith]

I have a form where the user enters a 10 digit project number in an unbound control, presses a button, and the project data appears--no problem. If the user enters a 10 digit number that DOES NOT exist, I get either a completely blank project data form (even without controls/labels) or I can set it up to jump into a new form, data entry mode. What I need is a popup form which tells the user the that number they entered is invalid and lets them choose to re-enter or to exit. The list of valid project numbers is found in the first column of the Primary Table. How do I get the "Entry Form" to look to the table and accept or reject the number the user entered and then respond with either the "OK Form" or the "Invalid Form"? I don't want to use a combo box in this case.

 

[Abby N]

I was working on a very similar problem just yesterday. Here's the solution I came up with. Perhaps you can adapt this to your own situation. I coded the unbound control's 'After Update' event with the following. Hope this helps.

Dim strResponce As String
Me.RecordsetClone.FindFirst "[ProjectNo] = '" & Me![UnboundProjectNo] & "'"
If Me.RecordsetClone.NoMatch Then
DoCmd.GoToRecord acDataForm, "FormName", acNewRec
strResponce = MsgBox("This project number you entered does not exist. Would you like to create a new project?", vbYesNo + vbExclamation, "New Project?")
If strResponce Then
Me.ProjectNo = Me.UnboundProjectNo
Else
Me.UnboundProjectNo = ""
DoCmd.GoToRecord acDataForm, "FormName", acNewRec
End If
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If


~Abby