Filter on search

[Michael]

Hi

I have a search function on my main form that brings up
anything that matches the same string on 3 fields, ID,
First Name, Surname, the results are then based in a list
box. I then dbl click on the correct record and it brings
up the first record that I want in my form- which works
(but when I click next - it goes to the next record-NOT
the next record for the person I selected)....but what I
want it to do is bring up the first record and apply the
filter so that when I click next on my form it goes to the
next record that I have selected.

This is the code I am using at dbl click. I think that it
is the 'DoCmd.FindRecord strString, acStart, , acDown, ,
acCurrent' that I need to amend....but I can't think what
to do

Private Sub Surname_DblClick(Cancel As Integer)
Dim strString As Double

strString = Forms![FrmCustomersMainMenu]![Surname]

'Open the form for entering information
DoCmd.OpenForm "FrmCustomerQuotes"
Forms![frmcustomerQuotes].[Customer ID].SetFocus
'Attempt to locate a quote and bring it up
DoCmd.FindRecord strString, acStart, , acDown, ,
acCurrent
'DoCmd.Close acForm, "frmcouncilsearch", acSaveNo
Forms![frmcustomerQuotes]![Customer ID].SetFocus
Forms![FrmCustomersMainMenu].[Command0].SetFocus
Forms![FrmCustomersMainMenu].Quote.Visible = False
Forms![FrmCustomersMainMenu].[Quotedetails] = " "
DoCmd.Close acForm, "frmcustomersMainmenu"

End Sub

Thanks for any help..

 

[Jess]

It sounds to me as if you could simplify everything a lot
by using an underlying query to select the data for your
form. This way, you would begin the process with only the
records on the form that you require.

On your main form you are (presumably) entering the ID,
First name, surname you want to find.

If you have a subform which finds the matches, make the
data source of the subform a query which searches the
secondary file using selection criteria from the main
form, e.g. query field surname and in selection criteria
you type
= Forms![FrmCustomersMainMenu]![Surname]
etc. etc.

You need to requery the subform once you have established
the search criteria on main form, to run the query and
retrieve your data. The simplest way you could do this is
to have a command button on the main form which you click
and on the click event of the button you have a piece of
code which reads

me!Subformname.requery

You could also, instead of putting this code behind the
command button, put it on the after update event of the
last of the three inputs.

HTH

Jess

-----Original Message-----
Hi

I have a search function on my main form that brings up
anything that matches the same string on 3 fields, ID,
First Name, Surname, the results are then based in a list
box. I then dbl click on the correct record and it brings
up the first record that I want in my form- which works
(but when I click next - it goes to the next record-NOT
the next record for the person I selected)....but what I
want it to do is bring up the first record and apply the
filter so that when I click next on my form it goes to the
next record that I have selected.

This is the code I am using at dbl click. I think that it
is the 'DoCmd.FindRecord strString, acStart, , acDown, ,
acCurrent' that I need to amend....but I can't think what
to do

Private Sub Surname_DblClick(Cancel As Integer)
Dim strString As Double

strString = Forms![FrmCustomersMainMenu]![Surname]

'Open the form for entering information
DoCmd.OpenForm "FrmCustomerQuotes"
Forms![frmcustomerQuotes].[Customer ID].SetFocus
'Attempt to locate a quote and bring it up
DoCmd.FindRecord strString, acStart, , acDown, ,
acCurrent
'DoCmd.Close acForm, "frmcouncilsearch", acSaveNo
Forms![frmcustomerQuotes]![Customer ID].SetFocus
Forms![FrmCustomersMainMenu].[Command0].SetFocus
Forms![FrmCustomersMainMenu].Quote.Visible = False
Forms![FrmCustomersMainMenu].[Quotedetails] = " "
DoCmd.Close acForm, "frmcustomersMainmenu"

End Sub

Thanks for any help..

.