Search from Form

[patricespencer]

I am writing a Pension Program for my company. I am extracting the data from our HR system (.dbf) the has the Employee info in one table and the spouse info in an other table.

In order to populate or grab the employee we want to run a calculation for I have a pop up box that asks for the SSN. I am using the following code to do that:

Private Sub cmdGetnewData_Click()
'On Err GoTo Proc_Err
If IsNull(Me.txtSSN) Then
MsgBox "Please enter a SNN", vbOKOnly, "Error - Missing SSN"
Exit Sub
End If

'Create the query to append a record from FOX into Access
Dim strSQL As String
strSQL = "" & _
"INSERT INTO PMain ( ESSN, ELastName, EFirstName, EMi, EAddress1, EAddress2, ECity, EState, EZip, EDOB, DOH, SSSN, SLastName, SFirstName, SMi, SAddress1, SAddress2, SAddress3, SDOB ) " & _
"SELECT A.P_SSN, A.P_LNAME, A.P_FNAME, A.P_MI, A.P_HSTREET1, A.P_HSTREET2, A.P_HCITY, A.P_HSTATE, A.P_HZIP, A.P_BIRTH, A.P_ORIGHIRE, B.D_SSNO, B.D_LNAME, B.D_FNAME, B.D_MI, B.D_ADDRESS1, B.D_ADDRESS2, B.D_ADDRESS3, B.D_BIRTH " & _
"FROM HRPERSNL AS A LEFT JOIN HDepend AS B ON A.P_EMPNO = B.D_EMPNO " & _
"WHERE B.D_RELATION ='Spouse' AND A.P_SSN='" & Me.txtSSN & "'"
'Debug.Print strSQL

' Execute the append query using the Run Command.
' We can only execute ACTION queries using the RUN COmmand - Not Select Queries
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

' Now, Open the EMployee form based on the SSN
'We chose to use the Where clause because it means they have to cloe
' and then reopen the form to anybody else.
' We could have used the Filter insted, that way they can unfilter to see other (all_ employees)
DoCmd.OpenForm FormName:="Main", _
WhereCondition:="ESSN = '" & Me.txtSSN & "'"
'Debug.Print "[E SSN] = '" & Me.txtSSN & "'"
DoCmd.Close acForm, "frmLookForSSN"
'Proc_Err:
'MsgBox Err.Number & " " & Err.Description
Exit Sub
End Sub

My problem is that not all employee's have spouses or maybe the HR department didn't enter a spouse for that employee. So when I try to get those employees the record comes up blank.

How can I write this so it will get the employee regardless if they have a spouse or not.

Any help would be very much appreciated.

Thanks in advance.