Cmd Button/ Text box verification

[kaylachris]

I have a Form which I use to access specific records using a text box/ command button combination. The user types in a SSN and then clicks the button and that persons record pulls up in a separate form. Everything works smoothly as long the SSN entered into the text box exist in the table. If the command button is pushed without anything in the text box or a number that cannot be found the SSN field of my Table the first user in the table is pulled up or a blank record.
I wish to create some type of coding/macro that checks to see if the text box matches any of the SSN in the table before it opens a form. If there is not a match then a dialog box will pop-up simply stating that the SSN could not be found.


I've been tinkering with DLookup.. but not even sure if that its capable of such a task. I've listed the names of all the items below just in case someone knows some type of coding to help me out (I'm VB stupid lol).

Text box name: LookUpSSNtext
Command Button: EditProfileClick
Table: SoldierData
Form where text box/cmd button is located: MainPage

Thanks in advance

[Datagopherdan]

You can do something like this.

dim intCount as Integer
intCount=DCount("*","SoldierData","[SSN]=" & Me!LookUpSSNtext)
if intCount < 1 Then
    MsgBox "No records found that match that SSN", vbInformation
          Exit Sub
End If

[kaylachris]

I appreciate the input but gonna post my final code... just incase someone else wants to give it a try.. it does everything I want

Private Sub EditProfileButton_Click()
If (IsNull(LOOKUPSM)) Then
Beep
MsgBox "Please Enter a SSN", vbOKOnly, "SSN not found"
Exit Sub
End If
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "SoldiersSSN = " & Me.LOOKUPSM
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
DoCmd.OpenForm "SMReview", acNormal, "", "[SoldiersSSN] = Forms![MainPage]![LOOKUPSM]", acFormEdit, acNormal
Else
Beep
If (Eval("MsgBox ('Profile Not Found.@Create New Profile?.@@',20,' Profile Not Found!')")) = vbYes Then
DoCmd.OpenForm "SMReview", acNormal, "", "", acAdd, acNormal
End If
End If
Set rst = Nothing
End Sub