Search in Form (1 Viewer)

Valentine

Member
Local time
Today, 00:41
Joined
Oct 1, 2021
Messages
198
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:

Code:
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:

Code:
    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

I’m here to help
Staff member
Local time
Yesterday, 21:41
Joined
Oct 29, 2018
Messages
18,972
Hi. I think you're right. Text values need a delimiter. For example:
Code:
rstName.FindFirst "[Last Name] = '" & NameSearch & "'"
Hope that helps...

Edit: Added missing "&". Sorry.
 
Last edited:

Valentine

Member
Local time
Today, 00:41
Joined
Oct 1, 2021
Messages
198
ok I added in the delimiter but code just ends without searching.
Code:
rstName.FindNext "[Last Name] = '" & NameSearch & "'"
I had to add the extra & since it wanted an end of statement without it.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:41
Joined
Oct 29, 2018
Messages
18,972
ok I added in the delimiter but code just ends without searching.
Code:
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

Member
Local time
Today, 00:41
Joined
Oct 1, 2021
Messages
198
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

I’m here to help
Staff member
Local time
Yesterday, 21:41
Joined
Oct 29, 2018
Messages
18,972
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

Super Moderator
Staff member
Local time
Today, 00:41
Joined
Feb 19, 2002
Messages
36,296
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

Member
Local time
Today, 00:41
Joined
Oct 1, 2021
Messages
198
For some reason all of a sudden my search buttons are pulling up this error:
Error.PNG

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

I’m here to help
Staff member
Local time
Yesterday, 21:41
Joined
Oct 29, 2018
Messages
18,972
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.
 

Valentine

Member
Local time
Today, 00:41
Joined
Oct 1, 2021
Messages
198
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

..forever waiting... waiting for jellybean!
Local time
Today, 12:41
Joined
May 7, 2009
Messages
16,394
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

Grover Park George
Local time
Yesterday, 21:41
Joined
Nov 25, 2004
Messages
670
That's often a symptom of a missing or broken reference. Open the VBE and check your references.
 

Users who are viewing this thread

Top Bottom