Comparing(Validating) Data Entered In Form To Table

[jmort232]

Hello, This is literally my first project with ACCESS and any help would be appreciated.
First I am creating basically a sign in sheet using ACCESS. I have the form set up with my table, i require last name, first name, last four of ssn, and some other misc info. What i want to do is when they enter in their last 4 of ther SSN in the form i want it to check and see if it matches with their first and last name, maybe on a before update event. If it checks out i just want it to proceed on. If not i would like it to throw an error message to say please enter correct last 4.

For the life of me i can not figure out how to properly validate this. Its probably fairly simple and I am over thinking it but please any help is greatly appreciated.

[twinnyfo]

jmort232,

Welcome to Bytes!

First, we should always keep in mind that a database cannot "validate" anyone's data. The only person who can validate anything concerning the person is the person themselves.

So, keeping that in mind, what you are looking for is basic search criteria, and if those search criteria are not met, then the user must be presented with other options.

Before we proceed too much further with this issue, it would be wise for you to consider several things about this sign in DB. First, will it be possible for "new" people to sign in? If so, then a new person's information will not be found in the Database, so it must be added.

Also, let's say a person enters their information incorrectly, but they think they entered it correctly, would this force a new record (with incorrect information)?

Do you know the attendees of the event(s)? If so, there may be better ways of signing people in.

All of this is to say that allowing a user to type in their own information to be validated can be fertile soil for additional challenges.

To more specifically address your question, yes the solution is relatively simple, but without knowing greater details of your tables and field names, we are all simply guessing. The essence of your criteria would be:

"WHERE [LastName] = '" & Me.txtLastName & _
"' AND [FirstName] = '" & Me.txtFirstName & _
"' AND [LastFour] = '" & Me.txtLastFour & "'"

But, since this is your first foray into Access, my guess is that there is probably more beginner's complexity built into your structure and naming conventions that might cause additional problems.

Without seeing examples of what you have tried so far, it is difficult to provide any greater detail.

[jmort232]

Hello twinnyfo,
Thanks for the input, I will try my best to address your questions.

Basically starting at the beginning I have one table and it has 10 fields. The user will fill out their information such as last name, first name, last four of ssn, and then hit sign in. This will record that data and I will manually review it with a database on a website (that is not maintained by us) that has the customers information in it as well. Basically I mark down that they came in and used our services (I work in a computer lab at our local education center).

Currently the way we do this is by a pen and paper sign in form and i do the same process, they fill out the same info, i go to database online and mark they were here. The reason i want the last 4 checked is because sometimes they provide false numbers and it takes me longer to find them in the database.

My plan with my database is to have them enter in their info and if they exist in the system and their last 4 matches their name then it just simply logs it. If this is their first time logging in, it just simply logs it. If they have been here before and they give me a different last 4 than what it shows with their name it will throw an error stating enter correct last 4.

How will i get their correct last 4 if they enter it wrong in the first place you may be thinking. Well once they enter in their information for the first time, and i access the online database i will correct any information they entered in wrong. That way their record is correct and the next time they come they cannot enter in false information.

I hope this helps address some of your questions. Maybe I am going about this the wrong way, maybe this is more work than what its worth.

[twinnyfo]

Based upon your description, though, you will have serious challenges if you have two users named "John Smith" but with different SSNs. As you can see there are inherent challenges with such a sign in system. It may be wiser to have a "UserID" the the student types in. Then, if the system can't find the User ID, they add a new record. But here, again, if someone enters a wrong UserID you have the same problems.

Customers are notoriously unable to type their names properly....

[NeoPa]

If I understand correctly then, this is no security system. Simply an identification system.

Your database acting a bit like a local cache to ensure that the identification data given matches an existing/validated entry if they've ever been through the process before, and enters into the cache a new entry if not. This new entry then is available for you to get confirmed before next time they try to use it. That makes sense. If there are multiple people with the same names then it will only allow the entry if the data matches any one of those entries.

It won't have any element of security within it as it has no password and getting hold of someone else's SSN isn't often that difficult. As long as that's clearly understood we can approach this just for what it is - an identification system.

I would suggest the clicking of a button to trigger the checking code, but an AfterUpdate() event or three can also be used if that's important to you. The logic of the latter would be to have code in the AfterUpdate() events for all three of your controls (We'll call them txtFirstName, txtSurname & txtSSN for now.) which are all very similar in that they just call a separate local procedure that checks that none of these three controls is currently Null. If any are then it exits quietly. If not then it continues to handle the check for a match.

If the cached data is also used to populate other controls on the form then I suggest using a Recordset object. The filter string would be of the form (Again I've used fields named as [FirstName], [Surname] & [SSN] for the three fields.) :

WHERE ([FirstName]='xxx')
  AND ([Surname]='yyy')
  AND ([SSN]='zzz')

Clearly the xxx, yyy & zzz values need to be injected into the string. That would involve code similar to :

Dim strWhere As String
 
strWhere = "WHERE ([FirstName]='%FN')%L" _
         & "  AND ([Surname]='%SN')%L" _
         & "  AND ([SSN]='%SS')"
strWhere = Replace(strWhere, "%FN", Me.txtFirstName)
strWhere = Replace(strWhere, "%SN", Me.txtSurname)
strWhere = Replace(strWhere, "%SS", Me.txtSSN)
strWhere = Replace(strWhere, "%L", vbNewLine)

Good luck.

[jmort232]

twinnyfo
Hey thanks again for your reply. Yeah I have thought of the issues of people with same last/first names causing issues and I was thinking of adding additional fields like maybe middle inital to help narrow it down. Again I know that it is still possible that all match up with two people, but its a risk im willing to take seeing as how I am making this database as a personal tool to help me with my job, not so much a necessary tool for my job. Generally I am sitting there the whole time so if an issue like this were to occur I would simply revert back to having them sign in on the sheet of paper like usual.

NeoPa
You pretty much nailed it on how I want to use it. No its not going to be secure, but it will not contain full socials, just last four and basically as it stands now people are just signing in on a piece of paper so everybody's information is listed right there currently. So no real change in whats available for other customers to see.

Thank you for your tips and I will give them a shot. If I may ask a super NOOB question, the code snipets you wrote, where exactly would I enter this in at. Would I open up my form in design view, click the property sheet info, navigate to the event tab, then go to the after update, and under the options availiable would I just enter this into code builder.

Im completely new to this as I stated in a previous post so please forgive my incompetence. When I decided to start this I had never even opened ACCESS before. So if you also have any good links to some good training's or tutorials (talking about something similar to what I am trying to achieve) I would love that also. Thanks again.

[NeoPa]

Oh how I wish more members were like you and responded appropriately to all comments and suggestions.

Let me start at the end and provide a link I have for tutorials (Microsoft Access Tutorials (Strive4Peace)). Others may wish to share other links. I believe Twinny has a list he shares frequently.

When you don't know what to do with a suggestion then responding and explaining that, and asking for further help on that point, is the perfect response. Let me see if I can pad it out a little further. Before I do so though, let me say that your understanding of how to get to the correct module for the Form is bang on. In the scenario you outlined it would take you straight to the ..._AfterUpdate() event handler, creating a template if one weren't already there. Alt-F11 is another way to switch to the VBA IDE but that won't necessarily leave you in the right place in the associated module.

I'll make it clear that my earlier code suggestions were merely snippets. Not the whole of any code but just bits to copy and paste into other code so that they'll do the work you were most struggling with. What I will include below will be more of the code in the format used within a module. Be careful if you paste it in that you don't lose any other code in the module that does any other work. This should do the work as has been discussed though :

Option Compare Database
Option Explicit
 
'There may be various other procedures in here.
 
'This is one of the Event Procedures that will handle updates to
'   any of your three Controls.
Private Sub txtFirstName_AfterUpdate()
    Call CheckAllEntered()
End Sub
 
Private Sub txtSurname_AfterUpdate()
    Call CheckAllEntered()
End Sub
 
Private Sub txtSSN_AfterUpdate()
    Call CheckAllEntered()
End Sub
 
'CheckAllEntered() first checks if all three Controls have data entered.
'   If No then immediately exits doing nothing.
'   If Yes then it checks if the two Names match anything previously entered.
'       If No then creates a new entry for the new names and SSN, then closes.
'       If Yes then checks if the SSN value matches any for the matching names.
'           If No then it throws a message and requests re-entry.
'           If Yes then it populates other Controls on the Form and closes.
Private Sub CheckAllEntered()
    Dim strWhere As String, strMsg As String
 
    With Me
        'NB. X+Y+Z, where none is numeric, resolves to Null if any is Null.
        If IsNull(.txtFirstName+.txtSurname+.txtSSN) Then Exit Sub
        'Prepare Criteria string to match just records with names as entered.
        strWhere = "WHERE ([FirstName]='%FN')%NL" _
                 & "  AND ([Surname]='%SN')"
        strWhere = Replace(strWhere, "%FN", Me.txtFirstName)
        strWhere = Replace(strWhere, "%SN", Me.txtSurname)
        strWhere = Replace(strWhere, "%NL", vbNewLine)
        If IsNull(DLookup(Expr:="*" _
                        , Domain:="[YourTable]" _
                        , Criteria:=strWhere)) Then
            'At this point we create a new record using data already entered.
            'If the rest all works we'll get to this code later.
            'We'll need some more info from you at that point.
            Exit Sub
        End If
        strWhere = strWhere & "%NL  AND ([SSN]='%SS')"
        strWhere = Replace(strWhere, "%SS", Me.txtSSN)
        strWhere = Replace(strWhere, "%NL", vbNewLine)
        If IsNull(DLookup(Expr:="*" _
                        , Domain:="[YourTable]" _
                        , Criteria:=strWhere)) Then
            'At this point we have a problem so warn the user.
            strMsg = Replace("Invalid SSN for this name.%NL%NL" _
                           & "Please re-enter details." _
                           , "%NL", vbNewLine)
            Call MsgBox(Prompt:=strMsg _
                      , Buttons:=vbOKOnly Or vbExclamation _
                      , Title:=".Name"
        Else
            'At this point we show the record matching data entered.
            'If the rest all works we'll get to this code later.
            'We'll need some more info from you at that point.
        End If
        Exit Sub
    End With
End Sub
 
'There may be various other procedures after here too.

At this point I should make it clear that I don't have your project to hand so I haven't tested this code. This is known as air-code so be prepared to have to test it and find bugs before it just works. Hopefully I have the syntax right at least. You'll almost certainly have to change names in the code to match the objects in your project, or alternatively change them in your project to match the code.

If you don't know about the With statement then look it up. It's extremely useful for making your code shorter and clearer - as well as reducing resource usage.

I do strongly recommend that you study the code I've provided for you. Ask yourself why might I have done things the way I did. Understand as well as you can what it all means and exactly what it's doing.

[jmort232]

NeoPa
Thank you for the reply and sorry for the delay. Had a long weekend. I will take this and begin work on it and hopefully knock it out. Thank you again for the help, apparently I was nowhere near close to figuring it out on my own lol. I will let you know how it goes (Good or bad).

[jmort232]

NeoPa,
Well I feel as if I am getting closer. You can tell me if or where I messed up. I took your code and plugged in my information. I will copy it down below and you can tell me if I messed up any syntax anywhere. I think I chose the msgbox options correctly, and where I enter in the domain information I leave it in the Square brackets correct? for example I have Domain:="[Table2]"

I guess I'm not sure if its working or not, what I did was the alt f11 and copied and pasted straight over after doing my edits in notepad. I then close out the vba application and open my form and enter in data. I use just my first and last and last 4 and then after I had my base line I attempted at giving it a false last 4 and received no errors. I believe the code is running properly as I no longer get any errors while I am entering data into my form. Any idea where I messed this up at. Below is the copy and pasted code I am using.

Option Compare Database
 Option Explicit
 
 'There may be various other procedures in here.
 
 'This is one of the Event Procedures that will handle updates to
 '   any of your three Controls.
 Private Sub txtFirstName_AfterUpdate()
     Call CheckAllEntered
 End Sub
 
 Private Sub txtLastName_AfterUpdate()
     Call CheckAllEntered
 End Sub
 
 Private Sub txtSSN_AfterUpdate()
     Call CheckAllEntered
 End Sub
 
 'CheckAllEntered() first checks if all three Controls have data entered.
 '   If No then immediately exits doing nothing.
 '   If Yes then it checks if the two Names match anything previously entered.
 '       If No then creates a new entry for the new names and SSN, then closes.
 '       If Yes then checks if the SSN value matches any for the matching names.
 '           If No then it throws a message and requests re-entry.
 '           If Yes then it populates other Controls on the Form and closes.
 Private Sub CheckAllEntered()
     Dim strWhere As String, strMsg As String
 
     With Me
         'NB. X+Y+Z, where none is numeric, resolves to Null if any is Null.
         If IsNull(.txtFirstName + .txtLastName + .txtSSN) Then Exit Sub
         'Prepare Criteria string to match just records with names as entered.
         strWhere = "WHERE ([FirstName]='%FN')%NL" _
                  & "  AND ([LastName]='%SN')"
         strWhere = Replace(strWhere, "%FN", Me.txtFirstName)
         strWhere = Replace(strWhere, "%SN", Me.txtLastName)
         strWhere = Replace(strWhere, "%NL", vbNewLine)
         If IsNull(DLookup(Expr:="*" _
                         , Domain:="[Table2]" _
                         , Criteria:=strWhere)) Then
             'At this point we create a new record using data already entered.
             'If the rest all works we'll get to this code later.
             'We'll need some more info from you at that point.
             Exit Sub
         End If
         strWhere = strWhere & "%NL  AND ([SSN]='%SS')"
         strWhere = Replace(strWhere, "%SS", Me.txtSSN)
         strWhere = Replace(strWhere, "%NL", vbNewLine)
         If IsNull(DLookup(Expr:="*" _
                         , Domain:="[Table2]" _
                         , Criteria:=strWhere)) Then
             'At this point we have a problem so warn the user.
             strMsg = Replace("Invalid SSN for this name.%NL%NL" _
                            & "Please re-enter details." _
                            , "%NL", vbNewLine)
             Call MsgBox(Prompt:=strMsg, Buttons:=vbOKOnly)
                       , Buttons:=vbOKOnly Or vbExclamation _
                       , Title:=".Name"
         Else
             'At this point we show the record matching data entered.
             'If the rest all works we'll get to this code later.
             'We'll need some more info from you at that point.
         End If
         Exit Sub
     End With
 End Sub

[jmort232]

NeoPa,
I was going to just edit my last post but decided that I would just create another new post so you can compare the two, maybe I chopped the code up way to much and need to restart. I noticed some more name changes to the code that I needed to make (Or atleast think I needed to make) such as renaming from txtSSN_AfterUpdate() to just SSN_AfterUpdate(). I hope I am correct on this.

After I did that and tried running /debugging I worked through it and my first real issue was the code that said

If IsNull(DLookup(Expr:="'" _
                         , Domain:="[Table2]" _
                         , Criteria:=strWhere)) Then

This would give me the error:
Run-Time error '3075': Syntax error in string in Query Expression ".
So I added another ' so it looked like Expr:="''" and that seemed to work, idk if that was proper or not but it moved me on to the next error which then said
Run-Time error '3085': Undefined function 'Where' in expression.

strWhere = "Where ([FirstName]='%FN')%NL" _
                  & "  AND ([LastName]='%LN')"
         strWhere = Replace(strWhere, "%FN", Me.FirstName)
         strWhere = Replace(strWhere, "%LN", Me.LastName)
         strWhere = Replace(strWhere, "%NL", vbNewLine)
         If IsNull(DLookup(Expr:="''" _
                         , Domain:="[Table2]" _
                         , Criteria:=strWhere)) Then

So where it is showing my problem at is on the , Criteria:=strWhere)) Then

but is the issue more towards the top with my first strWhere ="Where ([FirstName] ='%FN')

I really hope this makes sense and hopefully I am on the right track. Thanks for any help you are able to give, I feel like I am still making this harder than it has to be.

Full updated code listed below

Option Compare Database
 Option Explicit
  'There may be various other procedures in here.
 
 'This is one of the Event Procedures that will handle updates to
 '   any of your three Controls.
 Private Sub FirstName_AfterUpdate()
     Call CheckAllEntered
 End Sub
 
 Private Sub LastName_AfterUpdate()
     Call CheckAllEntered
 End Sub
 
 Private Sub SSN_AfterUpdate()
     Call CheckAllEntered
 End Sub
 
 'CheckAllEntered() first checks if all three Controls have data entered.
 '   If No then immediately exits doing nothing.
 '   If Yes then it checks if the two Names match anything previously entered.
 '       If No then creates a new entry for the new names and SSN, then closes.
 '       If Yes then checks if the SSN value matches any for the matching names.
 '           If No then it throws a message and requests re-entry.
 '           If Yes then it populates other Controls on the Form and closes.
 Private Sub CheckAllEntered()
     Dim strWhere As String, strMsg As String
 
     With Me
         'NB. X+Y+Z, where none is numeric, resolves to Null if any is Null.
         If IsNull(FirstName + LastName + SSN) Then Exit Sub
         'Prepare Criteria string to match just records with names as entered.
         strWhere = "Where ([FirstName]='%FN')%NL" _
                  & "  AND ([LastName]='%LN')"
         strWhere = Replace(strWhere, "%FN", Me.FirstName)
         strWhere = Replace(strWhere, "%LN", Me.LastName)
         strWhere = Replace(strWhere, "%NL", vbNewLine)
         If IsNull(DLookup(Expr:="''" _
                         , Domain:="[Table2]" _
                         , Criteria:=strWhere)) Then
             'At this point we create a new record using data already entered.
             'If the rest all works we'll get to this code later.
             'We'll need some more info from you at that point.
             Exit Sub
         End If
         strWhere = strWhere & "%NL  AND ([SSN]='%SS')"
         strWhere = Replace(strWhere, "%SS", Me.SSN)
         strWhere = Replace(strWhere, "%NL", vbNewLine)
         If IsNull(DLookup(Expr:="*" _
                         , Domain:="[Table2]" _
                         , Criteria:=strWhere)) Then
             'At this point we have a problem so warn the user.
             strMsg = Replace("Invalid SSN for this name.%NL%NL" _
                            & "Please re-enter details." _
                            , "%NL", vbNewLine)
             Call MsgBox(Prompt:=strMsg, Buttons:=vbOKOnly)
 
         Else
             'At this point we show the record matching data entered.
             'If the rest all works we'll get to this code later.
             'We'll need some more info from you at that point.
         End If
         Exit Sub
     End With
 End Sub
 
 
 'There may be various other procedures after here too.

[twinnyfo]

jmort232,

Your strWhere String should NOT have the word "Where" in it, as it is the criteria string for a Domain Aggregate Function.

Line 1 of your second block of Code (Line 33 of your final block of code) whould simply be:

strWhere = "([FirstName]='%FN')%NL" _

Hope this hepps!

[jmort232]

twinnyfo,
Hey thank you that took care of the issue, good catch. Now it is not throwing any error when running, but still don't understand why when i enter my last name and first name and last 4 of my social correctly the first time, then wrong the second time, it is not giving me the error stating incorrect last 4

[twinnyfo]

Personally, I don't like using DLookup() without a specific value that it is looking for. If you are looking for "any record that meets the search criteria", then you might better use DCount(). But, if you are looking for "data that is specific to the search criteria" then you use DLookup(), but tell it specifically which Field to return. Either way, you should always use DLookup() with a specific Field being queried.

In your case, rather than trying to find a "non-Null" result, look for something specific--or count the records and if the result is 0 then the match has not been found. So, I'll make a few minor modificaitons to your code to help you see what I am trying to do (since we know the code "works"):

'CheckAllEntered() first checks if all three Controls have data entered.
'   If No then immediately exits doing nothing.
'   If Yes then it checks if the two Names match anything previously entered.
'       If No then creates a new entry for the new names and SSN, then closes.
'       If Yes then checks if the SSN value matches any for the matching names.
'           If No then it throws a message and requests re-entry.
'           If Yes then it populates other Controls on the Form and closes.
Private Sub CheckAllEntered()
    Dim strWhere    As String
    Dim strMsg      As String
    Dim intRcds     As Integer
 
    With Me
        'NB. X+Y+Z, where none is numeric, resolves to Null if any is Null.
        If IsNull(FirstName + LastName + SSN) Then Exit Sub
        'Prepare Criteria string to match just records with names as entered.
        strWhere = "([FirstName]='%FN')%NL" _
                 & "  AND ([LastName]='%LN')"
        strWhere = Replace(strWhere, "%FN", Me.FirstName)
        strWhere = Replace(strWhere, "%LN", Me.LastName)
        strWhere = Replace(strWhere, "%NL", vbNewLine)
        Debug.Print strWhere
        intRcds = DCount(Expr:="*" _
                       , Domain:="[Table2]" _
                       , Criteria:=strWhere)
        If intRcds = 0 Then
            'At this point we create a new record using data already entered.
            'If the rest all works we'll get to this code later.
            'We'll need some more info from you at that point.
            Exit Sub
        End If
        strWhere = strWhere & "%NL  AND ([SSN]='%SS')"
        strWhere = Replace(strWhere, "%SS", Me.SSN)
        strWhere = Replace(strWhere, "%NL", vbNewLine)
        Debug.Print strWhere
        intRcds = DCount(Expr:="*" _
                       , Domain:="[Table2]" _
                       , Criteria:=strWhere)
        If intRcds = 0 Then
            'At this point we have a problem so warn the user.
            strMsg = Replace("Invalid SSN for this name.%NL%NL" _
                           & "Please re-enter details." _
                           , "%NL", vbNewLine)
            Call MsgBox(Prompt:=strMsg, Buttons:=vbOKOnly)
 
        Else
            'At this point we show the record matching data entered.
            'If the rest all works we'll get to this code later.
            'We'll need some more info from you at that point.
            Call MsgBox(Prompt:=intRcds & " records found")
        End If
        Exit Sub
    End With
End Sub

This will tell you in the Immediate Window the value of strWhere. Once this value is listed, copy and paste that value into your response and we can see what might be going on.

[NeoPa]

Hi JMort.

Thanks due to Twinny for spotting my error of including the "WHERE " string in the Criteria argument. I had started with the idea of using a Recordset then changed to using DLookup() without properly changing that part.

The other issue you had was a transposition error on your part. My code never had Expr:="'". It was Expr:="*", which is another error in itself (Fine in DCount() and other Domain level functions but not so much in DLookup()). As we're only interested in determining whether or not any record has been found we only need to select a field we know cannot be Null. This is why I always use Expr:="*" when I can. It makes it clear I'm checking the record as a whole rather than the specific field mentioned. It makes the code more easily understood which is always a good thing. Unfortunately, as in this case, I sometimes overlook the fact that it doesn't make quite so much sense in DLookup() as it would for the other Domain functions :-( In this situation, as we have already checked that both [FirstName] & [LastName] are not Null, we can use either (Expr:="[FirstName]").

To emphasise why sometimes Expr:="*" can be so important, consider needing to count records within a data set that match some criteria or other. Many use a field in there instead of the "*" string. This often results in false results when the contents of that one field is Null. Essentially you're counting the number of records where that field is non-null rather than the number of records full stop (Exactly as designed in fact). Using a field name there should only really be done when you're specifically working on non-null values for that field (But NOT in DLookup() of course ;-)).

Let me explain why I use DLookup() rather than, for instance, DCount(), as suggested by Twinny. Another approach that does work. When a DLookup() finds its first match it knows the job is done and stops. DCount() on the other hand has to process through either the entire data set or, if you're lucky, just to the end of the relevant part of a usable index. The former would take considerably longer in relative terms (as would be the case here I suspect) and the latter be much more marginal, but still necessarily longer. As we're dealing with domains (The D refers to Domain level functions for both.) then these delays are less than trivial and should be avoided where possible.

Here's my suggested code again. This time with adjustments made for your Control names (though do read my comment on that at the bottom). You should probably take the opportunity to update those names as soon as you're happy the code logic is working as required.

Option Compare Database
Option Explicit
 
' *** There may be various other procedures in here. ***
 
'This is one of the Event Procedures that will handle updates to
'   any of your three Controls.
Private Sub FirstName_AfterUpdate()
    Call CheckAllEntered()
End Sub
 
Private Sub LastName_AfterUpdate()
    Call CheckAllEntered()
End Sub
 
Private Sub SSN_AfterUpdate()
    Call CheckAllEntered()
End Sub
 
'CheckAllEntered() first checks if all three Controls have data entered.
'   If No then immediately exits doing nothing.
'   If Yes then it checks if the two Names match anything previously entered.
'       If No then creates a new entry for the new names and SSN, then closes.
'       If Yes then checks if the SSN value matches any for the matching names.
'           If No then it throws a message and requests re-entry.
'           If Yes then it populates other Controls on the Form and closes.
Private Sub CheckAllEntered()
    Dim strWhere As String, strMsg As String
 
    With Me
         'NB. X+Y+Z, where none is numeric, resolves to Null if any is Null.
         If IsNull([FirstName] + [LastName] + [SSN]) Then Exit Sub
         'Prepare Criteria string to match just records with names as entered.
         strWhere = "([FirstName]='%FN') AND ([LastName]='%LN')"
         strWhere = Replace(strWhere, "%FN", .FirstName)
         strWhere = Replace(strWhere, "%LN", .LastName)
         If IsNull(DLookup(Expr:="[FirstName]" _
                         , Domain:="[Table2]" _
                         , Criteria:=strWhere)) Then
             'At this point we create a new record using data already entered.
             'If the rest all works we'll get to this code later.
             'We'll need some more info from you at that point.
             Exit Sub
         End If
         strWhere = strWhere & " AND ([SSN]='%SS')"
         strWhere = Replace(strWhere, "%SS", .SSN)
         If IsNull(DLookup(Expr:="[FirstName]" _
                         , Domain:="[Table2]" _
                         , Criteria:=strWhere)) Then
             'At this point we have a problem so warn the user.
             strMsg = Replace("Invalid SSN for this name.%NL%NL" _
                            & "Please re-enter details." _
                            , "%NL", vbNewLine)
             Call MsgBox(Prompt:=strMsg, Buttons:=vbOKOnly)
         Else
             'At this point we show the record matching data entered.
             'If the rest all works we'll get to this code later.
             'We'll need some more info from you at that point.
         End If
         Exit Sub
    End With
End Sub
 
' *** There may be various other procedures after here too. ***

NB. Indenting is very important so please take care to maintain the indenting when using/posting the code.
Also, those comments with *** in them are instructions to you and not simple code comments. They needn't be left in the code.

As a general rule, it's inadvisable to leave the Control names on the Form as the same as the Field names in the record, which is the default. This can lead to ambiguities and hard to figure problems. Preceding the names with the three-letter prefixes that indicate the Control type is a common and reliable way to avoid this while, at the same time, giving the reader an indication of the Control type (Bonus). It makes it very clear that the reference is to a Control and not the Field the Control is bound to.

[jmort232]

twinnyfo,
Hey thank you so much, I put in your code and it worked for me first go and did what I needed it to do. Thank you for an alternate approach I have a lot to learn on this and will probably be asking more questions in the near future as my bosses have now seen the project and are asking for more stuff because they like it so that is great! Thank you for all the help.

NeoPa,
Your updated code worked perfect as well. Thank you for sticking with me on this. You are right you did not have the ' in the quotes you did have the *. I forgot I put in the '' because the * would throw an error also but you explained that to me in your response. Thank you for your detailed answers as it has helped me gain more knowledge with Access. This is something I will continually be working on to improve and hopefully add more features so you will probably see me on these forms more.
Thank you for the tips on the naming convention also. The reason I took off the txt before some of the names is because I thought it was not working because it was not the same name as on the table. Just a newbie mistake and thank you for clearing that up. When my next issue arises I hope I get somebody that helps me through like the both of you did. Thanks again!

[NeoPa]

I'm very happy to hear we were both helpful. I'm also happy to hear you'll be posting again. Interesting thread and perfect handled by you - responding to everything and everyone.

I expect I'm most happy though, to see that you're using this experience to grow your understanding. Never hold back from asking why in your questions.

[NeoPa]

I should probably explain two things from the code that may help to understand it better.

1. Something I use quite a lot in my code is the Continuation Character ( _). When this is found at the right-hand end of a line of VBA (and the separating space is always necessary.) it means that the following line is interpreted as a continuation of the one with this on.
   EG.
   

   strWhere = "SELECT * " _
            & "FROM [Table]"

   is exactly equivalent to
   

   strWhere = "SELECT * " & "FROM [Table]"

   I often use this to ensure that code is more readable and more easily understood. With ever widening screens so much more can be seen on a single line nowadays, but not everyone has the same width screens and many forum sites where code is shared and shown suffer from quite narrow windows to display the code in. Thus it makes sense, and is considerate, to ensure the lines never get too long.
2. Lines #37 through #39 of post #14 should be considered as a whole. As explained above these are multiple lines but with a single VBA syntax line only.
   

   If IsNull(DLookup(Expr:="[FirstName]" _
                   , Domain:="[Table2]" _
                   , Criteria:=strWhere)) Then

   For now we'll show this as a single line to illustrate :
   

   If IsNull(DLookup(Expr:="[FirstName]", Domain:="[Table2]", Criteria:=strWhere)) Then

   If you're wondering why only that one Field ([FirstName]) is being returned then let me try to explain.
   Remember, what we're interested in here is finding out if the record exists or not. Our only interest in the Field [FirstName] is whether or not a value is returned or not. When no record is found to match the criteria the value for [FirstName] that's returned is actually Null. Hence - the whole line is saying "Does a record matching our criteria even exist?". This would be more clearly and easily understood if the "*" version could be used but that would make no real sense in a DLookup() call. Thus we have to use a Field we know will exist in all records.

I hope that clarifies the situation for any that read this thread and wonder why it is that the code is written as it as and why it works.

[jmort232]

Awesome, Thanks so much for the clear response. Now I feel much more comfortable with understanding the flow of this.

[NeoPa]

It's always a pleasure dealing with someone, like yourself JMort, who clearly engages. You're a perfect example of someone with limited understanding of the subject matter who, nevertheless, engages fully in the thread. The two shouldn't affect each other, and you've illustrated that point well.