473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

writing code for a better find command

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
2 1974
Delerna
1,134 Expert 1GB
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
3,080 Expert 2GB
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

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

Similar topics

8
by: jquest | last post by:
Hi Again; 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...
0
by: Gazelle | last post by:
I have a dilemma, that I am hopping I can find some help with. He is the back story so everyone sort of understands what it is that I am trying to accomplish. My problem: I have multiple...
171
by: tshad | last post by:
I am just trying to decide whether to split my code and uses code behind. I did it with one of my pages and found it was quite a bit of trouble. I know that most people (and books and articles)...
12
by: Chris Springer | last post by:
I'd like to get some feedback on the issue of storing data out to disk and where to store it. I've never been in a production environment in programming so you'll have to bear with me... My...
10
by: cj | last post by:
I'm having a problem writing an insert command to work with a datatable. I've looked at what the sqldataadapter creates and created my insert command to look the same. I have:...
22
by: JoeC | last post by:
I am working on another game project and it is comming along. It is an improvment over a previous version I wrote. I am trying to write better programs and often wonder how to get better at...
1
by: Smita Prathyusha | last post by:
I am facing a problem in writing to COM1. I am using a Win 32 Console mode Program in VC++ the following is the code: If anyone can help me out it will be of great help : // SC_Using_Classes.cpp...
0
by: mcc99 | last post by:
Amazing, I surfed around to find a simple utility that could list files recursively from a given top folder down through it, writing out the path and filename in simple \path\file_name form, without...
118
by: Chuck Cheeze | last post by:
This might be in the wrong group, but... Here is an example of my data: entry_id cat_id 1 20 2 25 3 30 4 25 5 35
1
by: =?Utf-8?B?ZmhpbGxpcG8=?= | last post by:
We have a code snippet that downloads data to Excel. it is writing row by row. This causes a performance issue. Any ideas on how to speed this up will be appreciated. Please find below an...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.