search by 1 of 3 combos

[Guest]

I don't know what I am missing here. I have a form for viewing and editing
contact information, and I would like the user to be able to search by either
bid number (contact ID), phone number or name, then have the record details
displayed on the form.

I have 3 combo boxes, one for each search criteria, and the code for them is
based on a working search combo I have on another form, but they will not
work on this form. I'msure it is in the syntax, but I think I've made such a
mess now that I don't know where to start.


bidder_bidnumber is Text
contactprimphone is Integer
contactlastname is Text


source code:

Option Compare Database
Option Explicit

Private Sub cbo_find_by_bid_number_AfterUpdate()
Dim content
content = Trim(cbo_find_by_bid_number) & "*"
content = "[bidder_bidnumber] like '" & content & "'"
If cbo_find_by_bid_number <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_bid_number_GotFocus()
Me.cbo_find_by_bid_number.Value = ""
End Sub

Private Sub cbo_find_by_name_AfterUpdate()
Dim content
content = Trim(cbo_find_by_name) & "*"
content = "contactlastname like '" & content & "'"
If cbo_find_by_name <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_name_GotFocus()
Me.cbo_find_by_name.Value = ""

End Sub

Private Sub cbo_find_by_phone_AfterUpdate() ' "Find by Phone #" combo box
Dim content
content = Trim(cbo_find_by_phone) & "*"
content = "[contactprimphone] like '" & content & "'"
If cbo_find_by_phone <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_phone_GotFocus()
Me.cbo_find_by_phone.Value = ""

End Sub

 

[strive4peace]

Simplifying Search Comboboxes
---

You have made this more difficult than it needs to be... if you set the
bound column to be the record's primary key, you can use the same code
for each of your search combos.


Make one or more unbound combos on your form. Let the first column be
invisible and be the primary key ID of the recordsource of your form and
then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

~~~~~~~~~

if you want to be able to do wildcard searches on string controls --
presumeably, you will want to set a filter, not find records. One thing
I like to do is have a listbox (or combo)for records matching the search
criteria -- this is where the FindRecord code would run.

As criteria is selected, the SQL for the search list is rewritten so the
user only sees the records to pick that match what they have specified.

In the same way that FindRecord is written for any control, you would
have one procedure to rewrite the search list SQL. If you want more
information, set up your search combo (just list everything for now) and
post the SQL -- we can help you with code to construct the SQL based on
criteria.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I don't know what I am missing here. I have a form for viewing and editing
contact information, and I would like the user to be able to search by either
bid number (contact ID), phone number or name, then have the record details
displayed on the form.

I have 3 combo boxes, one for each search criteria, and the code for them is
based on a working search combo I have on another form, but they will not
work on this form. I'msure it is in the syntax, but I think I've made such a
mess now that I don't know where to start.


bidder_bidnumber is Text
contactprimphone is Integer
contactlastname is Text


source code:

Option Compare Database
Option Explicit

Private Sub cbo_find_by_bid_number_AfterUpdate()
Dim content
content = Trim(cbo_find_by_bid_number) & "*"
content = "[bidder_bidnumber] like '" & content & "'"
If cbo_find_by_bid_number <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_bid_number_GotFocus()
Me.cbo_find_by_bid_number.Value = ""
End Sub

Private Sub cbo_find_by_name_AfterUpdate()
Dim content
content = Trim(cbo_find_by_name) & "*"
content = "contactlastname like '" & content & "'"
If cbo_find_by_name <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_name_GotFocus()
Me.cbo_find_by_name.Value = ""

End Sub

Private Sub cbo_find_by_phone_AfterUpdate() ' "Find by Phone #" combo box
Dim content
content = Trim(cbo_find_by_phone) & "*"
content = "[contactprimphone] like '" & content & "'"
If cbo_find_by_phone <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_phone_GotFocus()
Me.cbo_find_by_phone.Value = ""

End Sub

 

[Guest]

So I used that ,and made one combo that searches all three: last name,
company name or phone number

Now, I was looking into normalization, and now I moved company name to a new
table, since there can be more than one contact for any given company.

Now, in my contacts table, I have a numeric field :
"table_contact_companies_ID", and I have a new table (table_contact
_companies) with an Autonumber primary key, and the "company_name" field.

How should I change my code to reflect this?



My code for the search combo:

Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like ""*" &
strWord & _
"*"") OR ([contactcompany] Like ""*" & strWord & _
"*"") OR ([contactprimphone] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub



Also, how will I change this string, which I use in several of my forms and
reports to display "Company (John Smith)" or "Smith, John":

=IIf(IsNull([contactcompany]),[contactlastname] & ", " &
[contactfirstname],[contactcompany] & " (" & [contactfirstname] & " " &
[contactlastname] & ")")

Simplifying Search Comboboxes
---

You have made this more difficult than it needs to be... if you set the
bound column to be the record's primary key, you can use the same code
for each of your search combos.


Make one or more unbound combos on your form. Let the first column be
invisible and be the primary key ID of the recordsource of your form and
then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

~~~~~~~~~

if you want to be able to do wildcard searches on string controls --
presumeably, you will want to set a filter, not find records. One thing
I like to do is have a listbox (or combo)for records matching the search
criteria -- this is where the FindRecord code would run.

As criteria is selected, the SQL for the search list is rewritten so the
user only sees the records to pick that match what they have specified.

In the same way that FindRecord is written for any control, you would
have one procedure to rewrite the search list SQL. If you want more
information, set up your search combo (just list everything for now) and
post the SQL -- we can help you with code to construct the SQL based on
criteria.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I don't know what I am missing here. I have a form for viewing and editing
contact information, and I would like the user to be able to search by either
bid number (contact ID), phone number or name, then have the record details
displayed on the form.

I have 3 combo boxes, one for each search criteria, and the code for them is
based on a working search combo I have on another form, but they will not
work on this form. I'msure it is in the syntax, but I think I've made such a
mess now that I don't know where to start.


bidder_bidnumber is Text
contactprimphone is Integer
contactlastname is Text


source code:

Option Compare Database
Option Explicit

Private Sub cbo_find_by_bid_number_AfterUpdate()
Dim content
content = Trim(cbo_find_by_bid_number) & "*"
content = "[bidder_bidnumber] like '" & content & "'"
If cbo_find_by_bid_number <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_bid_number_GotFocus()
Me.cbo_find_by_bid_number.Value = ""
End Sub

Private Sub cbo_find_by_name_AfterUpdate()
Dim content
content = Trim(cbo_find_by_name) & "*"
content = "contactlastname like '" & content & "'"
If cbo_find_by_name <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_name_GotFocus()
Me.cbo_find_by_name.Value = ""

End Sub

Private Sub cbo_find_by_phone_AfterUpdate() ' "Find by Phone #" combo box
Dim content
content = Trim(cbo_find_by_phone) & "*"
content = "[contactprimphone] like '" & content & "'"
If cbo_find_by_phone <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_phone_GotFocus()
Me.cbo_find_by_phone.Value = ""

End Sub

 

[strive4peace]

"Now, in my contacts table, I have a numeric field"

very good!

When I have IDs in the table that the form is based on but want to do
wildcard searches, what I do is modify the SQL for a combo or listbox to
FIND records rather than filtering the form.

This has really great flexibility! You can literally search for
anything that you can relate by simply showing record that match the
criteria.

Let the first column of your search combo be invisible and be the
primary key ID of the recordsource of your form and then, on its
AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~

If you need help understanding how to construct SQL statements, send me
an email and request my 30-page Word document on Access Basics (for
Programming) -- it doesn't cover VBA, but prepares you for it because it
covers essentials in Access. I do also send out the first 3 chapters
of a book I am writing on VBA to all who request it.

Be sure to put "Access Basics" in the subject line so that I see your
message...



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

So I used that ,and made one combo that searches all three: last name,
company name or phone number

Now, I was looking into normalization, and now I moved company name to a new
table, since there can be more than one contact for any given company.

Now, in my contacts table, I have a numeric field :
"table_contact_companies_ID", and I have a new table (table_contact
_companies) with an Autonumber primary key, and the "company_name" field.

How should I change my code to reflect this?



My code for the search combo:

Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like ""*" &
strWord & _
"*"") OR ([contactcompany] Like ""*" & strWord & _
"*"") OR ([contactprimphone] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub



Also, how will I change this string, which I use in several of my forms and
reports to display "Company (John Smith)" or "Smith, John":

=IIf(IsNull([contactcompany]),[contactlastname] & ", " &
[contactfirstname],[contactcompany] & " (" & [contactfirstname] & " " &
[contactlastname] & ")")

Simplifying Search Comboboxes
---

You have made this more difficult than it needs to be... if you set the
bound column to be the record's primary key, you can use the same code
for each of your search combos.


Make one or more unbound combos on your form. Let the first column be
invisible and be the primary key ID of the recordsource of your form and
then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

~~~~~~~~~

if you want to be able to do wildcard searches on string controls --
presumeably, you will want to set a filter, not find records. One thing
I like to do is have a listbox (or combo)for records matching the search
criteria -- this is where the FindRecord code would run.

As criteria is selected, the SQL for the search list is rewritten so the
user only sees the records to pick that match what they have specified.

In the same way that FindRecord is written for any control, you would
have one procedure to rewrite the search list SQL. If you want more
information, set up your search combo (just list everything for now) and
post the SQL -- we can help you with code to construct the SQL based on
criteria.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I don't know what I am missing here. I have a form for viewing and editing
contact information, and I would like the user to be able to search by either
bid number (contact ID), phone number or name, then have the record details
displayed on the form.

I have 3 combo boxes, one for each search criteria, and the code for them is
based on a working search combo I have on another form, but they will not
work on this form. I'msure it is in the syntax, but I think I've made such a
mess now that I don't know where to start.


bidder_bidnumber is Text
contactprimphone is Integer
contactlastname is Text


source code:

Option Compare Database
Option Explicit

Private Sub cbo_find_by_bid_number_AfterUpdate()
Dim content
content = Trim(cbo_find_by_bid_number) & "*"
content = "[bidder_bidnumber] like '" & content & "'"
If cbo_find_by_bid_number <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_bid_number_GotFocus()
Me.cbo_find_by_bid_number.Value = ""
End Sub

Private Sub cbo_find_by_name_AfterUpdate()
Dim content
content = Trim(cbo_find_by_name) & "*"
content = "contactlastname like '" & content & "'"
If cbo_find_by_name <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_name_GotFocus()
Me.cbo_find_by_name.Value = ""

End Sub

Private Sub cbo_find_by_phone_AfterUpdate() ' "Find by Phone #" combo box
Dim content
content = Trim(cbo_find_by_phone) & "*"
content = "[contactprimphone] like '" & content & "'"
If cbo_find_by_phone <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and
purchases on the form
End If
End Sub

Private Sub cbo_find_by_phone_GotFocus()
Me.cbo_find_by_phone.Value = ""

End Sub

 

[strive4peace]

Making SQL for Search Combo
---

I guess I should expand a bit on an example of what you can do with
modifying SQL for a search combo...

"search by either bid number (contact ID), phone number or name, then
have the record details displayed on the form."

Make a control called FindBidder (combo or listbox)

It's AfterUpdate event will be --> =FindRecord()
(BidderID will be the first column and it will be hidden)
FindRecord code is in a previous post to this thread, replace:
IDfield --> BidderID

then, put something like this behind your form to build it's RowSource:

'~~~~~~~~~~~~~~~~~~`
Private Function BuildSearchSQL()

'Crystal
' strive4peace2006 at yahoo dot com

dim s as string _
, mWhere as string

mWhere = ""

if not isnull(cbo_find_by_phone) then
mWhere = "[contactprimphone] like '" & cbo_find_by_phone & "'"
end if

if not isnull(cbo_find_by_CompanyID) then
if len(trim(mWhere)) > 0 then mWhere = mWhere & " AND "
mWhere = "[CompanyID] =" & cbo_find_by_CompanyID
end if

if not isnull(txt_find_by_contactlastname) then
if len(trim(mWhere)) > 0 then mWhere = mWhere & " AND "
mWhere = "[contactlastname] like '" _
& txt_find_by_contactlastname & "*'"
end if

'etc -- for all your criteria

s = "SELECT BidderID, BidderName "
& " FROM Bidders " _
& IIF(len(mWhere) > 0, mWhere, "") _
& ";"

me.FindBidder.RowSource = s
me.FindBidder.requery

end Function

'~~~~~~~~~~~~~~~~``

the cbo_find_by_CompanyID combobox would have properties like this:
RowSource -->
SELECT CompanyID, CompanyName
FROM Companies
ORDER BY CompanyName
Columncount --> 2
ColumnWidths --> 0;2
ListWidth --> 2
AfterUpdate --> =BuildSearchSQL()

on the AfterUpdate event of cbo_find_by_phone AND
txt_find_by_contactlastname -->
=BuildSearchSQL()

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

you said
contactprimphone is Integer

this is not good -- it should either be a Long Integer or Text
Integers are limited to 32K

In the above example, I assumed it was text and used quote delimiters


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

"Now, in my contacts table, I have a numeric field"

very good!

When I have IDs in the table that the form is based on but want to do
wildcard searches, what I do is modify the SQL for a combo or listbox to
FIND records rather than filtering the form.

This has really great flexibility! You can literally search for
anything that you can relate by simply showing record that match the
criteria.

Let the first column of your search combo be invisible and be the
primary key ID of the recordsource of your form and then, on its
AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~

If you need help understanding how to construct SQL statements, send me
an email and request my 30-page Word document on Access Basics (for
Programming) -- it doesn't cover VBA, but prepares you for it because it
covers essentials in Access. I do also send out the first 3 chapters
of a book I am writing on VBA to all who request it.

Be sure to put "Access Basics" in the subject line so that I see your
message...



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

So I used that ,and made one combo that searches all three: last name,
company name or phone number

Now, I was looking into normalization, and now I moved company name to
a new table, since there can be more than one contact for any given
company.
Now, in my contacts table, I have a numeric field :
"table_contact_companies_ID", and I have a new table (table_contact
_companies) with an Autonumber primary key, and the "company_name" field.

How should I change my code to reflect this?



My code for the search combo:

Private Sub cmd_search_Click()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([contactlastname] Like
""*" & strWord & _
"*"") OR ([contactcompany] Like ""*" & strWord
& _
"*"") OR ([contactprimphone] Like ""*" &
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub



Also, how will I change this string, which I use in several of my
forms and reports to display "Company (John Smith)" or "Smith, John":

=IIf(IsNull([contactcompany]),[contactlastname] & ", " &
[contactfirstname],[contactcompany] & " (" & [contactfirstname] & " "
& [contactlastname] & ")")

Simplifying Search Comboboxes
---

You have made this more difficult than it needs to be... if you set
the bound column to be the record's primary key, you can use the same
code for each of your search combos.


Make one or more unbound combos on your form. Let the first column
be invisible and be the primary key ID of the recordsource of your
form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

~~~~~~~~~

if you want to be able to do wildcard searches on string controls --
presumeably, you will want to set a filter, not find records. One
thing I like to do is have a listbox (or combo)for records matching
the search criteria -- this is where the FindRecord code would run.

As criteria is selected, the SQL for the search list is rewritten so
the user only sees the records to pick that match what they have
specified.

In the same way that FindRecord is written for any control, you would
have one procedure to rewrite the search list SQL. If you want more
information, set up your search combo (just list everything for now)
and post the SQL -- we can help you with code to construct the SQL
based on criteria.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



miss031 wrote:
I don't know what I am missing here. I have a form for viewing and
editing contact information, and I would like the user to be able to
search by either bid number (contact ID), phone number or name, then
have the record details displayed on the form.
I have 3 combo boxes, one for each search criteria, and the code for
them is based on a working search combo I have on another form, but
they will not work on this form. I'msure it is in the syntax, but I
think I've made such a mess now that I don't know where to start.


bidder_bidnumber is Text
contactprimphone is Integer
contactlastname is Text


source code:

Option Compare Database
Option Explicit

Private Sub cbo_find_by_bid_number_AfterUpdate()
Dim content
content = Trim(cbo_find_by_bid_number) & "*"
content = "[bidder_bidnumber] like '" & content & "'"
If cbo_find_by_bid_number <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information
and purchases on the form
End If
End Sub

Private Sub cbo_find_by_bid_number_GotFocus()
Me.cbo_find_by_bid_number.Value = ""
End Sub

Private Sub cbo_find_by_name_AfterUpdate()
Dim content
content = Trim(cbo_find_by_name) & "*"
content = "contactlastname like '" & content & "'"
If cbo_find_by_name <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information
and purchases on the form
End If
End Sub

Private Sub cbo_find_by_name_GotFocus()
Me.cbo_find_by_name.Value = ""

End Sub

Private Sub cbo_find_by_phone_AfterUpdate() ' "Find by Phone #"
combo box
Dim content
content = Trim(cbo_find_by_phone) & "*"
content = "[contactprimphone] like '" & content & "'"
If cbo_find_by_phone <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information
and purchases on the form
End If
End Sub

Private Sub cbo_find_by_phone_GotFocus()
Me.cbo_find_by_phone.Value = ""

End Sub