recordset error

[MysticElaine]

I am trying to add a recordset to my button to see if a client exists. A client exists in the table Clients if all three fields (First Name, Last Name and DOB) are identical to what the user is typing into the text boxes (named the same) to add a client.

My recordset comes up with the error: the rst.FindFirst that the Microsoft Access database engine does not recognize 'Me.First Name' as a valid field name or expression.

Private Sub AddNewClient_Click()
If IsNull([First Name]) Or IsNull([Last Name]) Or IsNull(DOB) Then
MsgBox "All fields are required", vbOKOnly, "Error"
Else
    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim str As String
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("Clients", dbOpenDynaset)
    rst.FindFirst "[First Name] = Me.[First Name] AND [Last Name] = Me.[Last Name] AND [DOB] = Me.[DOB]"
    If Not rst.NoMatch Then
    MsgBox "Client exists"
    GoTo Cleanup:
    Else
If Me.First_Name <> "" And Me.Last_Name <> "" And Me.DOB <> "" Then
Dim ClName As Variant
Dim ClientID As Integer
ClientID = Me.Client_ID.Value
ClName = Me.Client_Name.Value
Forms![Case]![Client ID].Value = ClientID
Forms![Case]![Client].Value = ClName
Forms![Case]![Date Requested].Enabled = True
Forms![Case]![Staff Requesting].Enabled = True
Forms![Case]![Language Requested].Enabled = True
Forms![Case]![Service Requested].Enabled = True
Forms![Case]![Service Date].Enabled = True
Forms![Case]![Outcome].Enabled = True
Forms![Case]![Date Requested].SetFocus
DoCmd.Close acForm, "Client", acSaveYes
End If
End If
End If
Cleanup:
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

Thanks

 

[RainLover]

You have both First Name and First_Name.

They are not the same.

 

[MysticElaine]

I wanted it to find the record in which the First Name, Last Name, and DOB fields in the table are identical to what the user typed in the First Name, Last Name and DOB textboxs on the form. Is that not possible? Sorry, this is my first time trying to do a recordset.

 

[namliam]

You need to substitute in your typed values into the string, you cant just splash them in.

For text
"SomeField = """ & YourVariable & """"

Date
"SomeField = #" & YourVariable & "#"
Please note that YourVariable must be in US Format or be forced to be formatted that way using the format function

Number
"SomeField = " & YourVariable & ""

Good luck out there.

Edit, please o please do indent your code....
Dont be lazy and use the tab-key

 

[MysticElaine]

Thanks for your response!

Edit, please o please do indent your code....
Dont be lazy and use the tab-key

I thought tab-key did indent...what do you mean please?

 

[namliam]

I thought tab-key did indent...what do you mean please?

Yes it does indent... that is what I mean!

 

[BlueIshDan]

Private Sub AddNewClient_Click()

    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim str As String
    
    Dim ClName As Variant
    Dim ClientID As Integer
        
    If IsNull([First Name]) Or IsNull([Last Name]) Or IsNull(DOB) Then
        MsgBox "All fields are required", vbOKOnly, "Error"
    Else
    
        Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset("Clients", dbOpenDynaset)
        rst.FindFirst "[First Name] = Me.[First Name] AND [Last Name] = Me.[Last Name] AND [DOB] = Me.[DOB]"
        
        If Not rst.NoMatch Then
        
            MsgBox "Client exists"
            GoTo Cleanup:
            
        Else
        
            If Me.First_Name <> "" And Me.Last_Name <> "" And Me.DOB <> "" Then
            
                ClientID = Me.Client_ID.Value
                ClName = Me.Client_Name.Value
                Forms![Case]![Client ID].Value = ClientID
                Forms![Case]![Client].Value = ClName
                Forms![Case]![Date Requested].Enabled = True
                Forms![Case]![Staff Requesting].Enabled = True
                Forms![Case]![Language Requested].Enabled = True
                Forms![Case]![Service Requested].Enabled = True
                Forms![Case]![Service Date].Enabled = True
                Forms![Case]![Outcome].Enabled = True
                Forms![Case]![Date Requested].SetFocus
                DoCmd.Close acForm, "Client", acSaveYes
                
            End If
            
        End If
        
    End If
    
Cleanup:

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    
End Sub

 

[MysticElaine]

Ahh, okay. I normally do indent everything, but I was just trying the inserted recordset code this morning and so didn't want to indent everything if I was just going to have to pull the code out. I have been having so many issues with getting this button to do what I want. You should be glad I deleted all of the code that I just made inactive with the ' before posting.