Search record form

[San]

Hey,

I need to create a form with several text boxes in which users type in
key words, press a command button on the form and it opens a matching
record. Thanking you in advance.

[Cilla]

Re: Search record form


San wrote:[color=blue]
> Hey,
>
> I need to create a form with several text boxes in which users type in
> key words, press a command button on the form and it opens a matching
> record. Thanking you in advance.[/color]

Are you trying to create a filter to a form. You can use the Filter by
Form option in the form itself. It will show all the fields from the
original form which allows the user to enter any key words with
wildcards etc. This is located under Record/Filter/Filter by Form.
The user after entering the keywords in the filter form, right clicks
the form and clicks apply filter. You can add these menu options to
command buttons as well. If you do not want the user to see a record
before using the filter have the form open in a new record first then
use the apply filter by form.

[RobK]

Re: Search record form

I got the same problem. I need a textbox where I can typ a name, and
when I press enter or a command button it should show me the matching
record in the form (so without opening any other tables or queries or
w/e)
San schreef:

[Cilla]

Re: Search record form


RobK wrote:[color=blue]
> I got the same problem. I need a textbox where I can typ a name, and
> when I press enter or a command button it should show me the matching
> record in the form (so without opening any other tables or queries or
> w/e)
> San schreef:[/color]

If you are trying to just do a search for a form you should:

1. use a combo box instead of a text box that will allow the user to
choose the data not enter the data. this will stop data entry issues.

2. If you are trying to locate a record that is unique you can attach
this to the combo on the after update property after you change it to
match your form:

On Error GoTo xxx
Dim rs As Object
DoCmd.ShowAllRe cords
Set rs = Forms![FormName].Recordset.Clon e
rs.FindFirst "[Title] = '" & Forms![FormName]![ComboName] & "'"
If Not rs.EOF Then Forms![FormName].Bookmark = rs.Bookmark
Forms![Form]![ComboName] = ""
Exit Function
xxx:
MsgBox "There was an error executing the command.", vbCritical
Exit Function

3. If you need to apply a filter so multiple records appear from one
choice:

a. Create the combo that shows the data the user will search by then
attach this statement modified to your form on the after update
property of the combo

On Error GoTo ZZZ
DoCmd.ShowAllRe cords
Dim SrcSQL As String
SrcSQL = "SELECT TableName.* FROM TableName WHERE_
(((TableName.Fi eldName)=[Forms]![FormName]![ComboName]))"
DoCmd.ApplyFilt er SrcSQL
[Forms]![FormName]![ComboName] = ""
Exit Function
ZZZ:
MsgBox "There was an error executing the command.", vbCritical
Exit Function

Note: [Forms]![FormName]![ComboName] = "" is to make the combo become
blank after the search is complete so the user can search again. It
makes a clean search process.

You can also change your error loop message to match an access error
message

MsgBox err.description

4. For both combo search types you need to create a requery for the On
Enter Property to allow the combo to be required each time the user
enters it. This will stop it from droping data that has been added
since the form was opened.

On Error Resume Next
[Forms]![FormName]![ComboName].Requery
Exit Function

[San]

Re: Search record form

Hi Cilla,

That sounds pretty easy and logical. I think it is not going to work if
I need to open a matching record from a switchboard?

Cilla wrote:[color=blue]
> RobK wrote:[color=green]
> > I got the same problem. I need a textbox where I can typ a name, and
> > when I press enter or a command button it should show me the matching
> > record in the form (so without opening any other tables or queries or
> > w/e)
> > San schreef:[/color]
>
> If you are trying to just do a search for a form you should:
>
> 1. use a combo box instead of a text box that will allow the user to
> choose the data not enter the data. this will stop data entry issues.
>
> 2. If you are trying to locate a record that is unique you can attach
> this to the combo on the after update property after you change it to
> match your form:
>
> On Error GoTo xxx
> Dim rs As Object
> DoCmd.ShowAllRe cords
> Set rs = Forms![FormName].Recordset.Clon e
> rs.FindFirst "[Title] = '" & Forms![FormName]![ComboName] & "'"
> If Not rs.EOF Then Forms![FormName].Bookmark = rs.Bookmark
> Forms![Form]![ComboName] = ""
> Exit Function
> xxx:
> MsgBox "There was an error executing the command.", vbCritical
> Exit Function
>
> 3. If you need to apply a filter so multiple records appear from one
> choice:
>
> a. Create the combo that shows the data the user will search by then
> attach this statement modified to your form on the after update
> property of the combo
>
> On Error GoTo ZZZ
> DoCmd.ShowAllRe cords
> Dim SrcSQL As String
> SrcSQL = "SELECT TableName.* FROM TableName WHERE_
> (((TableName.Fi eldName)=[Forms]![FormName]![ComboName]))"
> DoCmd.ApplyFilt er SrcSQL
> [Forms]![FormName]![ComboName] = ""
> Exit Function
> ZZZ:
> MsgBox "There was an error executing the command.", vbCritical
> Exit Function
>
> Note: [Forms]![FormName]![ComboName] = "" is to make the combo become
> blank after the search is complete so the user can search again. It
> makes a clean search process.
>
> You can also change your error loop message to match an access error
> message
>
> MsgBox err.description
>
> 4. For both combo search types you need to create a requery for the On
> Enter Property to allow the combo to be required each time the user
> enters it. This will stop it from droping data that has been added
> since the form was opened.
>
> On Error Resume Next
> [Forms]![FormName]![ComboName].Requery
> Exit Function[/color]

[Cilla]

Re: Search record form


San wrote:[color=blue]
> Hi Cilla,
>
> That sounds pretty easy and logical. I think it is not going to work if
> I need to open a matching record from a switchboard?
>
> Cilla wrote:[color=green]
> > RobK wrote:[color=darkred]
> > > I got the same problem. I need a textbox where I can typ a name, and
> > > when I press enter or a command button it should show me the matching
> > > record in the form (so without opening any other tables or queries or
> > > w/e)
> > > San schreef:[/color]
> >
> > If you are trying to just do a search for a form you should:
> >
> > 1. use a combo box instead of a text box that will allow the user to
> > choose the data not enter the data. this will stop data entry issues.
> >
> > 2. If you are trying to locate a record that is unique you can attach
> > this to the combo on the after update property after you change it to
> > match your form:
> >
> > On Error GoTo xxx
> > Dim rs As Object
> > DoCmd.ShowAllRe cords
> > Set rs = Forms![FormName].Recordset.Clon e
> > rs.FindFirst "[Title] = '" & Forms![FormName]![ComboName] & "'"
> > If Not rs.EOF Then Forms![FormName].Bookmark = rs.Bookmark
> > Forms![Form]![ComboName] = ""
> > Exit Function
> > xxx:
> > MsgBox "There was an error executing the command.", vbCritical
> > Exit Function
> >
> > 3. If you need to apply a filter so multiple records appear from one
> > choice:
> >
> > a. Create the combo that shows the data the user will search by then
> > attach this statement modified to your form on the after update
> > property of the combo
> >
> > On Error GoTo ZZZ
> > DoCmd.ShowAllRe cords
> > Dim SrcSQL As String
> > SrcSQL = "SELECT TableName.* FROM TableName WHERE_
> > (((TableName.Fi eldName)=[Forms]![FormName]![ComboName]))"
> > DoCmd.ApplyFilt er SrcSQL
> > [Forms]![FormName]![ComboName] = ""
> > Exit Function
> > ZZZ:
> > MsgBox "There was an error executing the command.", vbCritical
> > Exit Function
> >
> > Note: [Forms]![FormName]![ComboName] = "" is to make the combo become
> > blank after the search is complete so the user can search again. It
> > makes a clean search process.
> >
> > You can also change your error loop message to match an access error
> > message
> >
> > MsgBox err.description
> >
> > 4. For both combo search types you need to create a requery for the On
> > Enter Property to allow the combo to be required each time the user
> > enters it. This will stop it from droping data that has been added
> > since the form was opened.
> >
> > On Error Resume Next
> > [Forms]![FormName]![ComboName].Requery
> > Exit Function[/color][/color]

Sure it will. Heres a simple example you can use

Dim xxx As String
xxx = [FieldNameFromSw itchboard]
DoCmd.OpenForm "FormToApplySea chTo"
DoCmd.SelectObj ect acForm, "FormToApplySea rchTo"
Dim rs As Object
DoCmd.ShowAllRe cords
Set rs = Forms![FormToApplySear chTo].Recordset.Clon e
rs.FindFirst "[FieldFromFormTo ApplySearchTo] = '" & xxx & "'"
If Not rs.EOF Then Forms![FromToApplySear chTo].Bookmark =
rs.Bookmark
DoCmd.SelectObj ect acForm, "Switchboar d", False
DoCmd.Close
Exit Sub


Note: I am closing my search form (Switchboard) after making my
selection. You don't have to. Just quote out the 'DoCmd.SelectOb ject
acForm, "Switchboar d", False

[Cilla]

Re: Search record form


Cilla wrote:[color=blue]
> San wrote:[color=green]
> > Hi Cilla,
> >
> > That sounds pretty easy and logical. I think it is not going to work if
> > I need to open a matching record from a switchboard?
> >
> > Cilla wrote:[color=darkred]
> > > RobK wrote:
> > > > I got the same problem. I need a textbox where I can typ a name, and
> > > > when I press enter or a command button it should show me the matching
> > > > record in the form (so without opening any other tables or queries or
> > > > w/e)
> > > > San schreef:
> > >
> > > If you are trying to just do a search for a form you should:
> > >
> > > 1. use a combo box instead of a text box that will allow the user to
> > > choose the data not enter the data. this will stop data entry issues.
> > >
> > > 2. If you are trying to locate a record that is unique you can attach
> > > this to the combo on the after update property after you change it to
> > > match your form:
> > >
> > > On Error GoTo xxx
> > > Dim rs As Object
> > > DoCmd.ShowAllRe cords
> > > Set rs = Forms![FormName].Recordset.Clon e
> > > rs.FindFirst "[Title] = '" & Forms![FormName]![ComboName] & "'"
> > > If Not rs.EOF Then Forms![FormName].Bookmark = rs.Bookmark
> > > Forms![Form]![ComboName] = ""
> > > Exit Function
> > > xxx:
> > > MsgBox "There was an error executing the command.", vbCritical
> > > Exit Function
> > >
> > > 3. If you need to apply a filter so multiple records appear from one
> > > choice:
> > >
> > > a. Create the combo that shows the data the user will search by then
> > > attach this statement modified to your form on the after update
> > > property of the combo
> > >
> > > On Error GoTo ZZZ
> > > DoCmd.ShowAllRe cords
> > > Dim SrcSQL As String
> > > SrcSQL = "SELECT TableName.* FROM TableName WHERE_
> > > (((TableName.Fi eldName)=[Forms]![FormName]![ComboName]))"
> > > DoCmd.ApplyFilt er SrcSQL
> > > [Forms]![FormName]![ComboName] = ""
> > > Exit Function
> > > ZZZ:
> > > MsgBox "There was an error executing the command.", vbCritical
> > > Exit Function
> > >
> > > Note: [Forms]![FormName]![ComboName] = "" is to make the combo become
> > > blank after the search is complete so the user can search again. It
> > > makes a clean search process.
> > >
> > > You can also change your error loop message to match an access error
> > > message
> > >
> > > MsgBox err.description
> > >
> > > 4. For both combo search types you need to create a requery for the On
> > > Enter Property to allow the combo to be required each time the user
> > > enters it. This will stop it from droping data that has been added
> > > since the form was opened.
> > >
> > > On Error Resume Next
> > > [Forms]![FormName]![ComboName].Requery
> > > Exit Function[/color][/color]
>
> Sure it will. Heres a simple example you can use
>
> Dim xxx As String
> xxx = [FieldNameFromSw itchboard]
> DoCmd.OpenForm "FormToApplySea chTo"
> DoCmd.SelectObj ect acForm, "FormToApplySea rchTo"
> Dim rs As Object
> DoCmd.ShowAllRe cords
> Set rs = Forms![FormToApplySear chTo].Recordset.Clon e
> rs.FindFirst "[FieldFromFormTo ApplySearchTo] = '" & xxx & "'"
> If Not rs.EOF Then Forms![FromToApplySear chTo].Bookmark =
> rs.Bookmark
> DoCmd.SelectObj ect acForm, "Switchboar d", False
> DoCmd.Close
> Exit Sub
>
>
> Note: I am closing my search form (Switchboard) after making my
> selection. You don't have to. Just quote out the 'DoCmd.SelectOb ject
> acForm, "Switchboar d", False[/color]

and quote out the DoCmd.Close Too