Z
zmerz
Hello All,
I have a function running behind my form. In the form you can double click
on any field to run a search on that field. The problem I'm having is if
more than one record meets the search criteria, only the first record is
shown and you will get a message on each successive attempt "Record not
found." For instance, if more than one Smith family exists in the underlying
table, only the first Smith family is shown and I can not view the next Smith
family. I think I need to reset my record pointer but can't figure out where
to do this. I'm including my code so you can see what I've done. Any help
would be greatly appreciated.
Function RecordSearch(controlname As control)
Dim db As Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim sqlstr As String
Dim sqlstrnames As String
Dim inputstring As String
Dim strsearch As String
Dim ctlcontrol As control
Dim strcontrol As String
Dim strreccount As Integer
Dim intaddid As Integer
On Error GoTo ErrRecordSearch
sqlstr = "SELECT tblAddress.AddressID, tblPeople.FirstName,
tblPeople.LastName, tblPeople.EmailAddress, " & _
"tblPeople.Birthday, tblPeople.Anniversary, tblNotes.Notes,
tblPhone.PhoneNumber " & _
"FROM ((tblAddress LEFT JOIN tblPeople ON tblAddress.AddressID =
tblPeople.AddressID) " & _
"LEFT JOIN tblPhone ON tblPeople.PeopleID = tblPhone.PeopleID) LEFT JOIN
tblNotes ON " & _
"tblPeople.PeopleID = tblNotes.PeopleID "
Set ctlcontrol = controlname
strcontrol = ctlcontrol.Name
inputstring = InputBox("Find:", "Search")
If inputstring <> "" Then
Select Case strcontrol
Case FirstName.ControlSource
sqlstr = sqlstr & "WHERE (((tblPeople.FirstName)= '" &
inputstring & "'));"
Case LastName.ControlSource
sqlstr = sqlstr & "WHERE (((tblPeople.LastName) like '*" &
inputstring & "'));"
Case EMailAddress.ControlSource
sqlstr = sqlstr & "WHERE (((tblPeople.EmailAddress)='" &
inputstring & "'));"
Case Anniversary.ControlSource
sqlstr = sqlstr & "WHERE (((tblPeople.Anniversary)='" &
inputstring & "'));"
Case Birthday.ControlSource
sqlstr = sqlstr & "WHERE (((tblPeople.Birthday)='" &
inputstring & "'));"
Case Forms![Neighborhood Input Form]![Neighbors
Subform].Form![Phone]!PhoneNumber.ControlSource 'this is on a subform
sqlstr = sqlstr & "WHERE (((tblPhone.PhoneNumber)='" &
inputstring & "'));"
Case Forms![Neighborhood Input Form]![Neighbors
Subform].Form![NotesSubform]!Notes.ControlSource 'this is located on a subform
sqlstr = sqlstr & "WHERE (((tblNotes.Notes)='" & inputstring
& "'));"
End Select
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstr)
rs.MoveLast
strreccount = rs.RecordCount
rs.MoveFirst
'Debug.Print inputstring
'Debug.Print strreccount
intaddid = rs(0)
If Not rs.BOF And Not rs.EOF Then
Set rs1 = Forms![Neighborhood Input Form].RecordsetClone
If inputstring = controlname Then 'looking for the next record
with the same criteria
rs1.FindNext "AddressID = " & intaddid
If rs1.NoMatch Then
MsgBox "Record not found."
Else
Forms![Neighborhood Input Form].bookmark = rs1.bookmark
End If
Else 'looking for a new record. recordset
goes back to the first record
rs1.MoveNext
rs1.FindNext "AddressID = " & intaddid
If rs1.NoMatch Then
MsgBox "Record not found."
Else
Forms![Neighborhood Input Form].bookmark = rs1.bookmark
End If
End If
rs1.Close
rs.Close
Set rs1 = Nothing
Set rs = Nothing
End If
End If
Exit_RecordSearch:
Set ctlcontrol = Nothing
Exit Function
ErrRecordSearch:
MsgBox Err.Description
Resume Exit_RecordSearch
End Function
I have a function running behind my form. In the form you can double click
on any field to run a search on that field. The problem I'm having is if
more than one record meets the search criteria, only the first record is
shown and you will get a message on each successive attempt "Record not
found." For instance, if more than one Smith family exists in the underlying
table, only the first Smith family is shown and I can not view the next Smith
family. I think I need to reset my record pointer but can't figure out where
to do this. I'm including my code so you can see what I've done. Any help
would be greatly appreciated.
Function RecordSearch(controlname As control)
Dim db As Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim sqlstr As String
Dim sqlstrnames As String
Dim inputstring As String
Dim strsearch As String
Dim ctlcontrol As control
Dim strcontrol As String
Dim strreccount As Integer
Dim intaddid As Integer
On Error GoTo ErrRecordSearch
sqlstr = "SELECT tblAddress.AddressID, tblPeople.FirstName,
tblPeople.LastName, tblPeople.EmailAddress, " & _
"tblPeople.Birthday, tblPeople.Anniversary, tblNotes.Notes,
tblPhone.PhoneNumber " & _
"FROM ((tblAddress LEFT JOIN tblPeople ON tblAddress.AddressID =
tblPeople.AddressID) " & _
"LEFT JOIN tblPhone ON tblPeople.PeopleID = tblPhone.PeopleID) LEFT JOIN
tblNotes ON " & _
"tblPeople.PeopleID = tblNotes.PeopleID "
Set ctlcontrol = controlname
strcontrol = ctlcontrol.Name
inputstring = InputBox("Find:", "Search")
If inputstring <> "" Then
Select Case strcontrol
Case FirstName.ControlSource
sqlstr = sqlstr & "WHERE (((tblPeople.FirstName)= '" &
inputstring & "'));"
Case LastName.ControlSource
sqlstr = sqlstr & "WHERE (((tblPeople.LastName) like '*" &
inputstring & "'));"
Case EMailAddress.ControlSource
sqlstr = sqlstr & "WHERE (((tblPeople.EmailAddress)='" &
inputstring & "'));"
Case Anniversary.ControlSource
sqlstr = sqlstr & "WHERE (((tblPeople.Anniversary)='" &
inputstring & "'));"
Case Birthday.ControlSource
sqlstr = sqlstr & "WHERE (((tblPeople.Birthday)='" &
inputstring & "'));"
Case Forms![Neighborhood Input Form]![Neighbors
Subform].Form![Phone]!PhoneNumber.ControlSource 'this is on a subform
sqlstr = sqlstr & "WHERE (((tblPhone.PhoneNumber)='" &
inputstring & "'));"
Case Forms![Neighborhood Input Form]![Neighbors
Subform].Form![NotesSubform]!Notes.ControlSource 'this is located on a subform
sqlstr = sqlstr & "WHERE (((tblNotes.Notes)='" & inputstring
& "'));"
End Select
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstr)
rs.MoveLast
strreccount = rs.RecordCount
rs.MoveFirst
'Debug.Print inputstring
'Debug.Print strreccount
intaddid = rs(0)
If Not rs.BOF And Not rs.EOF Then
Set rs1 = Forms![Neighborhood Input Form].RecordsetClone
If inputstring = controlname Then 'looking for the next record
with the same criteria
rs1.FindNext "AddressID = " & intaddid
If rs1.NoMatch Then
MsgBox "Record not found."
Else
Forms![Neighborhood Input Form].bookmark = rs1.bookmark
End If
Else 'looking for a new record. recordset
goes back to the first record
rs1.MoveNext
rs1.FindNext "AddressID = " & intaddid
If rs1.NoMatch Then
MsgBox "Record not found."
Else
Forms![Neighborhood Input Form].bookmark = rs1.bookmark
End If
End If
rs1.Close
rs.Close
Set rs1 = Nothing
Set rs = Nothing
End If
End If
Exit_RecordSearch:
Set ctlcontrol = Nothing
Exit Function
ErrRecordSearch:
MsgBox Err.Description
Resume Exit_RecordSearch
End Function