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
*
Laura83 wrote:
[Quoted Text] > 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
|