Find Record

L

Laura83

Hi,
I have a main form and another form(1). They both work from the same primary
key(application number).
When I navigate from the main form to form(1) and then back, the data
displayed is filtered by the same application number as I want to be able to
display specific data on all forms for the same candidate.

The problem comes when I go back to the main form I can't search through all
records as it says it is filtered. I have tried this before the find function
but it still can't find any other records: DoCmd.GoToRecord , , acFirst

Can anybody help please?
Thanks,
Lau
 
S

strive4peace

Hi Laura,

in the search combobox, make the bound field the (autonumber) ID field
of your table

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

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()

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

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

WHERE
IDfield is the name of your unique ID field. If it is not a long
integer, adjust data types accordingly...


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Top