writing code for a better find command

[rvinodk]

Hi,

I saw this thread on the above Topic. Did it work? I tried the same thing and it is not populating the list Can anyone help me please?
A solution was given by PCDatasheet.as mentioned below in the code;

This was the original question
I have had help from this group before and want to thank everyone,
especially PCDatasheet.
My database includes a field called HomePhone, it uses the (xxx)xxx-xxx
format to include area code.
When a customer calls, I currently use Ctrl F with the HomePhone field
highlighted. Then I enter the last 4 digits and use the find next option.
This is cumbersome, so I have tried several methods (including a macro)
using comand buttons, but nothing seems quite right.

The future of this function in my database is more complicated than just
using Ctrl F function and using find next repeatedly. I am hoping to
incorporate a list or combo box.

I would like to click a command button, have it search and find all
occurrences of the least 4 digits of the phone number (based on the 4 digits
I input), and then present a list or combo box that displays the entire
phone number, customer name and address of each record that contains the 4
digits as the LAST 4 digits of the phone number, not the first 4 or any 4.

Is this possible? and can anyone recommend code that would work

The code was
ou need tables that look like:
TblCustomer
CustomerID
FirstName
Lastname
Address
City
State
Zipcode

TblPhoneNum
PhoneNumID
CustomerID
HomePhone
WorkPhone
CellPhone

The tables need a relationship on CustomerID.

Create a query named QryFindCustomer that includes both tables. Put the
following fields in the query in the order shown below:
CustomerToFind: TblCustomer.Cus tomerID
Customer:[LastName] & ", " & [[Firstname] <<Sort Accending>>
Address
CityStateZip:[City] & ", " & [State] & " " & [Zipcode]
HomePhone
LastFourDigits: Right([HomePhone],4)
Set the criteria for LastFourDigits as:
Forms!FrmSearch ForCustomer!Fou rDigitNum

Create a search form named FrmSearchForCus tomer. Add an unbound textbox
named FourDigitNum. Add a listbox named CustomersWithFo urDigits with the
following properties:
RowSource <<Leave Blank>>
Bound Column 1
Column Count 5
Column Width 0;1.5;1.5;1.5;1
Note: You will need to adjust the widths to suit.

Put the following code in the OnChange event of the textbox, FourDigitNum:
If Len(Me!FourDigi tNum) = 4 Then
Me!CustomersWit hFourDigits.Row Source = "QryFindCustome r"
End If

The listbox will have the value CustomerID. You will need to put code in the
listbox's AfterUpdate event so when you select a customer, code will give
you what you want.

[Delerna]

Use a query that includes a field calculated from the phone number field.
I assume it will be of type text because area codes can start with zero.

CalcFieldName:r ight(trim(Phone Field),4)

Link the criteria for that field to a textbox on your form
(where you enter the 4digits.. Forms!FrmSearch ForCustomer!Fou rDigitNum
in your post)

Put a button on the form that opens the query.


Something like that anyway. Hope there are enough hints to get you going.
Use your imagination :)

[nico5038]

Create a query (qryFourDigit) with the Right([HomePhone],4) as an additional column.
Next use this query for a subform on your find form.
Next add a combobox based on a query like:

select distinct FourDigit from qryFourdigit order by 1;

Finally use the linkage fields to have the combo's value from the master form linked to the FourDigit field of the subform.
Now the value of the combo will filter the subform to show all numbers with those trailing digits.

Nic;o)