Re: search by 1 of 3 combos

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
*



strive4peace wrote:
"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
*



miss031 wrote:
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] & ")")


"strive4peace" wrote:

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
.

Relevant Pages

  • Project Error
    ... Private Declare Sub Sleep Lib "Kernel32" ... Dim strDataSrc As String ...
    (microsoft.public.vb.bugs)
  • Re: Is there a way to prevent a RichTextBox from scrolling?
    ... Private _isRegex As Boolean ... Public Sub New(ByVal thispattern As String, ... Dim entry As tDict ...
    (microsoft.public.dotnet.framework.windowsforms.controls)
  • Excel Listing tool using VB
    ... Sub ListFiles2() ... Dim directories() As String, CurrentDirectory As String ... Dim dirtopaste, dirok ...
    (microsoft.public.vb.general.discussion)
  • Form Error
    ... SMSDS_CallerID As String ... Private Declare Sub Sleep Lib "kernel32" ... Dim ComString As String ... Dim AppPath As String, FreeFileNo% ...
    (microsoft.public.vb.bugs)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)