By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

writing code for a better find command

P: 3
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.CustomerID
Customer:[LastName] & ", " & [[Firstname] <<Sort Accending>>
Address
CityStateZip:[City] & ", " & [State] & " " & [Zipcode]
HomePhone
LastFourDigits:Right([HomePhone],4)
Set the criteria for LastFourDigits as:
Forms!FrmSearchForCustomer!FourDigitNum

Create a search form named FrmSearchForCustomer. Add an unbound textbox
named FourDigitNum. Add a listbox named CustomersWithFourDigits 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!FourDigitNum) = 4 Then
Me!CustomersWithFourDigits.RowSource = "QryFindCustomer"
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.
Jan 19 '10 #1
Share this Question
Share on Google+
2 Replies


Delerna
Expert 100+
P: 1,134
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:right(trim(PhoneField),4)

Link the criteria for that field to a textbox on your form
(where you enter the 4digits.. Forms!FrmSearchForCustomer!FourDigitNum
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 :)
Jan 20 '10 #2

nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. select distinct FourDigit from qryFourdigit order by 1;
  2.  
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)
Jan 22 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.