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:
Here is the one for last name which doesn't work:
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.
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.