I've attached a version with a simple search box.
This also has the tables and field names adjusted to remove spaces and other characters that shouldn't be included.
There are many other ways of achieving search functions this is just a simple one. You can program it more elegantly removing the repeating code, however I wanted to show you a simple way of getting to where you want to head.
Hi Minty:
I downloaded the database with the search box that you added and this is exactly what I want, but with a slight modification.
I am retired and keep a membership database as an unpaid volunteer for a non-profit organization (we are all unpaid volunteers).
The only other thing I would like added is a Find Next function on the form.
I sorted A to Z on LastName and if I need to search on FirstName, the Find Next function would be very beneficial as the first name will not be in order on the two lists.
I do not know VBA coding and I tried to copy what I've seen to set up a Find Next function but it does not work. I added a second button for FindNext and I probably need a new section on the VBA coding but have no idea how to do it or how to key it in with the "bookmark" created by the first found selection from the other button, the Find button.
I would be most grateful if you could edit the code which I have pasted here to get the Find Next function to work with the form. I've have made a screen capture of the form in design view (your form, but modified to include the button), and inserted it below.
Thanks for any help.
EDIT: I have deleted the old code and copied new code here -- note, I copied a sample Find (search) next, but it does not work
------------------------------------------------------------------------------------------------------------
| cmdFinder | | Click |
------------------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
-----------------------------------------------------------------------------------------------------------------
Private Sub cmdFinder_Click()
Dim sFind As String
Dim daoRS As DAO.Recordset
If IsNull(Me.txtFinder) Then Exit Sub
sFind = Me.txtFinder
Select Case Me.optFind_Wnat
Case 1 ' Last Name
Set daoRS = Me.RecordsetClone
daoRS.FindFirst ("[LastName] Like '*" & sFind & "*'")
'If we could find the newly added record, jump to it
If Not daoRS.NoMatch Then
Me.Bookmark = daoRS.Bookmark
End If
Case 2 ' First Name
Set daoRS = Me.RecordsetClone
daoRS.FindFirst ("[FirstName] Like '*" & sFind & "*'")
'If we could find the newly added record, jump to it
If Not daoRS.NoMatch Then
Me.Bookmark = daoRS.Bookmark
End If
End Select
End Sub
----------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
| (General) | | cmdSearch_Click |
------------------------------------------------------------------------------------------------------------
Private Sub cmdSearch_Click()
Dim RS As Object
Dim strSearch As String
Set RS = Me.RecordsetClone
strSearch = "LastName=" & Nz(Me.txtID, 0)
With RS
.Bookmark = Me.Bookmark
If Me.cmdSearch.Caption = "Find" Then
.FindFirst strSearch
Else
.FindNext "LastName=" & Me.txtID
End If
If .NoMatch Then
MsgBox "No match"
cmdSearch.Caption = "Find"
Else
Me.Bookmark = .Bookmark
Me.cmdSearch.Caption = "Find Next"
End If
End With
RS.Close
Set RS = Nothing
End Sub
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
| cmdOpenInfo | | Click |
------------------------------------------------------------------------------------------------------------
Private Sub cmdOpenInfo_Click()
Dim iMembershipID As Integer
Dim sWhereCondition As String
iMembershipID = Me.MembershipID
If IsNull(iMembershipID) Then Exit Sub
sWhereCondition = "MembershipID = " & iMembershipID
DoCmd.OpenForm "frmMembershipMembersTransPOSTINGListBoxCONTINUOUS", acNormal, , sWhereCondition, , acWindowNormal
End Sub
------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
| Membership_ID | | DblClick |
------------------------------------------------------------------------------------------------------------
Private Sub Membership_ID_DblClick(Cancel As Integer)
Dim iMembershipID As Integer
Dim sWhereCondition As String
iMembershipID = Me.MembershipID
If IsNull(iMembershipID) Then Exit Sub
sWhereCondition = "MembershipID = " & iMembershipID
DoCmd.OpenForm "frmMembershipMembersTransPOSTINGListBoxCONTINUOUS", acNormal, , sWhereCondition, , acWindowNormal
End Sub