Search in Form

[Valentine]

I have a form that i want my users to be able to search by certain things. I have a search by ID # and a search by last name. The ID # works perfectly but the name is giving me an error " Run-Time error '3070' The Microsoft Access database engine does not recognize 'name' as a valid field name or expression"

This is the one for the ID# which works:

Dim dbCurr As DAO.Database
    Dim rstDod As DAO.Recordset
    Dim DodSearch As String
    
    'Check for input
    If IsNull(Me.TxtFindDoD) Or Me.TxtFindDoD = "" Then
        MsgBox "Input a Valid DoD ID#.", vbOKOnly, "DoD ID Value Needed"
        Me.TxtFindDoD.BackColor = vbYellow
        Me.TxtFindDoD.SetFocus
    Else
        Set dbCurr = CurrentDb()
        Set rstDod = Me.Recordset
        DodSearch = Me.TxtFindDoD.Value
        rstDod.FindFirst "[DoD ID] = " & DodSearch
            If rstDod.NoMatch Then
                MsgBox "Record not found for DoD ID# " & DodSearch
            End If
        Me.TxtFindDoD.BackColor = vbWhite
        Me.TxtFindDoD.Value = ""
        Set rstDod = Nothing
        Set dbCurr = Nothing
    End If

Here is the one for last name which doesn't work:

    Dim dbCurr As DAO.Database
    Dim rstName As DAO.Recordset
    Dim NameSearch As String
    Dim strName As String
        
    'Check for input
    If IsNull(Me.TxtFindName) Or Me.TxtFindName = "" Then
        MsgBox "Input a Valid Last Name.", vbOKOnly, "Last Name Needed"
        Me.TxtFindName.BackColor = vbYellow
        Me.TxtFindName.SetFocus
    Else
        Set dbCurr = CurrentDb()
        strName = "SELECT Roster.[Last Name] FROM Roster"
        Set rstName = dbCurr.OpenRecordset(strName)
        NameSearch = Me.TxtFindName.Value
        rstName.FindNext "[Last Name] = " & NameSearch
            If rstName.NoMatch Then
                MsgBox "Record not found for Last Name " & NameSearch
            End If
        Me.TxtFindName.BackColor = vbWhite
        Me.TxtFindName.Value = ""
        Set rstName = Nothing
        Set dbCurr = Nothing
    End If

I think it has something to do with being able to find a number vs finding letters but i cant figure out how to make them both work.

 

[theDBguy]

Hi. I think you're right. Text values need a delimiter. For example:

rstName.FindFirst "[Last Name] = '" & NameSearch & "'"

Hope that helps...

Edit: Added missing "&". Sorry.

 

[Valentine]

ok I added in the delimiter but code just ends without searching.

rstName.FindNext "[Last Name] = '" & NameSearch & "'"

I had to add the extra & since it wanted an end of statement without it.

 

[theDBguy]

ok I added in the delimiter but code just ends without searching.

rstName.FindNext "[Last Name] = '" & NameSearch & "'"

I had to add the extra & since it wanted an end of statement without it.

Yeah, sorry for my typo. I missed it. Quick question, just wondering why you're only using FindNext, where is FindFirst?

 

[Valentine]

yeah thats my mistake, I changed to findfirst afterwards and took out the extra steps of making a different recordset with name as well. Now both searches are exactly the same just in the name one I added the delimiter part. Thank you it all works now.

 

[theDBguy]

yeah thats my mistake, I changed to findfirst afterwards and took out the extra steps of making a different recordset with name as well. Now both searches are exactly the same just in the name one I added the delimiter part. Thank you it all works now.

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

Single quotes won't work with last names because certain countries have names that contain a single quote such as O'Neil. You would need to surround names with double quotes or use some other method.

 

[Valentine]

For some reason all of a sudden my search buttons are pulling up this error:

If I go into the VBA code of the buttons and close it, the buttons mysteriously work but i dont want to do that EVERY time I open the database. Is there something I need to do so I dont get this error?

 

[theDBguy]

For some reason all of a sudden my search buttons are pulling up this error:
View attachment 99991
If I go into the VBA code of the buttons and close it, the buttons mysteriously work but i dont want to do that EVERY time I open the database. Is there something I need to do so I dont get this error?

First thing you could try is do a C&R. If that doesn't work, create a new empty DB file and import all your objects into it.

 

[arnelgp]

Is there something I need to do so I dont get this error?

re-create the form.

 

[Valentine]

ok I started with just copying the code deleting the code saving the DB then closing and reopening DB and pasting the code back in. So far that is working I will keep you posted if i have to do anything else.

 

[arnelgp]

no, i mean create a New form and add New Controls.
the code, you can then copy from the old one.

also did you try first to Find 'name' on your code, maybe it
is being used "somewhere" in your db and you forget to declare it.

 

[GPGeorge]

That's often a symptom of a missing or broken reference. Open the VBE and check your references.