Unbound Search textbox

[Alex Martinez]

Hello,


I have a form that has the following fields "PolicyNumber" - text field size
is 10, "Insured"- text field size is 50, "Audit" Yes/No field. All three
fields appear in the form in separate text box and drop downbox (Audit).
What I like to do is have a fourth text box/combobox that is can be bound or
not bound when I type the first 4 digits of the policy number the
PolicyNumber that match will show up. Now I can do a Control F and type in
the PolcyNumber, but when you have 100 policies that can be time consuming.
I have no clue how to do this. I still want to see the records as I scroll
using my mouse wheel or the navitagion button. Any tips or website to visit
I will appreciate the help. Thank you in advance.

 

[Allen Browne]

An unbound combo that lists the possible policies will be the quickest
interface to find the record. Use its AfterUpdate event to find the matching
record.

Details in:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

 

[tina]

it's not clear whether you want to filter the records, or do a Find. in
either case, you can add an unbound textbox control (i'll call it txtPol) to
the form's Header section. add code to the control's AfterUpdate event. to
Find a record, use the following code, as

Me.Recordset.FindFirst "PolicyNo Like '" & Me!txtPol & "*'"
If Me.Recordset.NoMatch Then
Me!txtPol = Null
End If

to filter the records, use

Me.Filter = "PolicyNo Like '" & Me!txtPol & "*'"
Me.FilterOn = True

note the with either solution, you can enter anywhere from one to all ten
digits of the policy number, rather than specifically and only four digits.
if it's possible that more than one policy number may have the same first
four digits, recommend you use the Filter code rather than the Find code.
also note that if you use the Filter code, you may want to add a command
button to the Header section, to remove the filter and show all records, as

Me.FilterOn = False

hth

 

[Crystal]

Hi David,

As Allen said, the best way for the user to specify what
they are looking for is with an unbound combobox -- or, if
your form has room, an unbound listbox

Here is an expansion of what Allen posted

You can actually do the find without setting an object to
the recordset clone (thanks freakazeud!)

I like to make a private function behind the form and have
several lookups -- or one lookup and change the lookup SQL
based on an option group selection so the user has more than
one way to find the record -- in any case, the bound column
is always a hidden ID, so it can use the same code:

AfterUpdate --> =FindRecord()
(you can also use the BeforeUpdate event)

Private Function FindRecord()

'thanks for ideas, freakazeud

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mRecordID As Long
mRecordID = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
End If

End Function

~~~

and, the long version:

Private Function FindRecord()

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mRecordID As Long, mPassNumber as integer
Dim mRecordIDcurrent As Long

mRecordIDcurrent = nz(IDfield)
mPassNumber = 0
mRecordID = Me.ActiveControl

'set lookup combo to be empty
Me.ActiveControl = Null

FindRecord_FindIt:
mPassNumber = mPassNumber + 1
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
Else
select case mPassNumber
case 1
'remove the filter and find the record
me.filterOn = false
me.Requery
goto FindRecord_FindIt
case 2
'filter was already removed and record wasn't found
'set record back to where it was
mRecordID = mRecordIDcurrent
msgbox "Record not found",,"Record not found"
goto FindRecord_FindIt
end select
End If

End Function


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
(e-mail address removed)