By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,319 Members | 2,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,319 IT Pros & Developers. It's quick & easy.

Search record form

P: n/a
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.

Jun 19 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a

San wrote:
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.


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.

Jun 19 '06 #2

P: n/a
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:

Jun 20 '06 #3

P: n/a

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.ShowAllRecords
Set rs = Forms![FormName].Recordset.Clone
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.ShowAllRecords
Dim SrcSQL As String
SrcSQL = "SELECT TableName.* FROM TableName WHERE_
(((TableName.FieldName)=[Forms]![FormName]![ComboName]))"
DoCmd.ApplyFilter 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

Jun 20 '06 #4

P: n/a
San
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:
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.ShowAllRecords
Set rs = Forms![FormName].Recordset.Clone
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.ShowAllRecords
Dim SrcSQL As String
SrcSQL = "SELECT TableName.* FROM TableName WHERE_
(((TableName.FieldName)=[Forms]![FormName]![ComboName]))"
DoCmd.ApplyFilter 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


Jun 20 '06 #5

P: n/a

San wrote:
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:
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.ShowAllRecords
Set rs = Forms![FormName].Recordset.Clone
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.ShowAllRecords
Dim SrcSQL As String
SrcSQL = "SELECT TableName.* FROM TableName WHERE_
(((TableName.FieldName)=[Forms]![FormName]![ComboName]))"
DoCmd.ApplyFilter 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


Sure it will. Heres a simple example you can use

Dim xxx As String
xxx = [FieldNameFromSwitchboard]
DoCmd.OpenForm "FormToApplySeachTo"
DoCmd.SelectObject acForm, "FormToApplySearchTo"
Dim rs As Object
DoCmd.ShowAllRecords
Set rs = Forms![FormToApplySearchTo].Recordset.Clone
rs.FindFirst "[FieldFromFormToApplySearchTo] = '" & xxx & "'"
If Not rs.EOF Then Forms![FromToApplySearchTo].Bookmark =
rs.Bookmark
DoCmd.SelectObject acForm, "Switchboard", 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.SelectObject
acForm, "Switchboard", False

Jun 20 '06 #6

P: n/a

Cilla wrote:
San wrote:
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:
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.ShowAllRecords
Set rs = Forms![FormName].Recordset.Clone
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.ShowAllRecords
Dim SrcSQL As String
SrcSQL = "SELECT TableName.* FROM TableName WHERE_
(((TableName.FieldName)=[Forms]![FormName]![ComboName]))"
DoCmd.ApplyFilter 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


Sure it will. Heres a simple example you can use

Dim xxx As String
xxx = [FieldNameFromSwitchboard]
DoCmd.OpenForm "FormToApplySeachTo"
DoCmd.SelectObject acForm, "FormToApplySearchTo"
Dim rs As Object
DoCmd.ShowAllRecords
Set rs = Forms![FormToApplySearchTo].Recordset.Clone
rs.FindFirst "[FieldFromFormToApplySearchTo] = '" & xxx & "'"
If Not rs.EOF Then Forms![FromToApplySearchTo].Bookmark =
rs.Bookmark
DoCmd.SelectObject acForm, "Switchboard", 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.SelectObject
acForm, "Switchboard", False


and quote out the DoCmd.Close Too

Jun 20 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.