Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-03-2006, 03:18 PM   #1
anonymous user
Registered User
 
Join Date: Nov 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
anonymous user is on a distinguished road
Search box keeps bringing up errors

I'm trying to modify an existing database to make it easier and faster to search for homeowners. Currently, there is a search window that has two radio buttons, and a list box. one radio button is to search by address and another by last name. When you click one of these, it shows all the records in the list box. The problem is that 1. this is time consuming scrolling through over 300 records, and 2. more search options are needed, to include tag numbers of vehicles, and also phone numbers of residents. What Im trying to do is replace the radio buttons with a text box that will search all of the wanted fields, and produce a list of results in the list box. I'd rather not use a search button and just have the list box filter out as you type, but if a button must be used then thats fine. I've tried different ways, and searched all over the new for weeks, but cannot find the solution to my problem. Here is the current and original code. Thanks in advance for your help....Mike

Type of Search = Radio Buttons
By Search Type = List Box
HOMEOWNERS = table where all the info is located
GET = cmd button to open form with results selected in list box

---------
Private Sub Option35_GotFocus()

End Sub

Private Sub Option37_GotFocus()

End Sub


Private Sub Type_of_Search_AfterUpdate()
With CodeContextObject
If .[Type of Search] = 1 Then
.[By Search Type].RowSource = ""
.[Search Text].Caption = "Select the Last Name to Search for"
.[By Search Type].ColumnCount = 3
.[By Search Type].ColumnWidths = "1.5 in;1 in;0 in"
.[By Search Type].BoundColumn = 3
.[By Search Type].RowSource = "SELECT DISTINCTROW HOMEOWNERS.LastName, HOMEOWNERS.FirstName, HOMEOWNERS.Address FROM HOMEOWNERS WHERE ((Not (HOMEOWNERS.LastName) Is Null)) ORDER BY HOMEOWNERS.LastName, HOMEOWNERS.Address;"
ElseIf .[Type of Search] = 2 Then
.[By Search Type].RowSource = ""
.[Search Text].Caption = "Select the Address to Search for"
.[By Search Type].ColumnCount = 2
.[By Search Type].ColumnWidths = "1.5 in;1 in"
.[By Search Type].BoundColumn = 1
.[By Search Type].RowSource = "SELECT HOMEOWNERS.Address, HOMEOWNERS.LastName FROM HOMEOWNERS WHERE ((Not (HOMEOWNERS.Address) Is Null)) ORDER BY HOMEOWNERS.Address;"
End If
End With
End Sub
Private Sub Get_Click()
On Error GoTo Get_Click_Err
Dim Criteria As String
Dim MyRS As DAO.Recordset

Set MyRS = Forms![BASIC DATA].RecordsetClone
Criteria = "[Address] = """ & Me![By Search Type] & """"
MyRS.FindFirst Criteria
If Not MyRS.NoMatch Then
Forms![BASIC DATA].Bookmark = MyRS.Bookmark
End If
MyRS.close
Set MyRS = Nothing

DoCmd.close acForm, "Homeowners Search Dialog"

Get_Click_Exit:
Exit Sub

Get_Click_Err:
MsgBox Err.Description
Resume Get_Click_Exit

End Sub
Private Sub close_button_Click()
On Error GoTo Err_close_button_Click


DoCmd.close

Exit_close_button_Click:
Exit Sub

Err_close_button_Click:
MsgBox Err.Description
Resume Exit_close_button_Click

End Sub
Private Sub close_Click()
On Error GoTo Err_close_Click


DoCmd.close

Exit_close_Click:
Exit Sub

Err_close_Click:
MsgBox Err.Description
Resume Exit_close_Click

End Sub

anonymous user is offline   Reply With Quote
Old 11-03-2006, 04:06 PM   #2
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,768
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
try docmd.findrecord - you can take a single search string (therefore address or name) and search records in a variety of ways to find what you are after. I tend to use a findfirst, and a findnext button, as one of the options is search the datset from the top, or continuing down the file.

This will automatically place you in the correct record., with hardly any code
gemma-the-husky is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
combo box errors oxfordian3 Forms 3 07-17-2006 02:48 PM
Combo Box Search Question sadlerj Forms 7 10-12-2005 09:30 AM
Search pop-up dialog box for a report mare Reports 2 09-03-2004 06:21 AM
Newbie's combo box/text box question etalent Forms 0 04-01-2004 05:26 AM
Search and replace within a text box DJ_DEF_CON_1 Forms 1 07-02-2003 03:23 PM




All times are GMT -8. The time now is 06:40 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World