Search Form

[rjw24]

I would like to create a search form which is identical to a form I have set
up already and gives the user the opportunity to search any of the fields
within that form. Essentially I want to be able to use the 'filter by form'
format but I am aware that code is unable to run in this format.

Thanks

 

[Ofer Cohen]

In the Form RecordSource you can refer to the text boxes and filter by them,
so only the related records will apear, and by adding another criteria return
all records if there was no value entered

Something like

Select * From TableName Where
(FieldName1 = Forms![FormName]![TextBoxName1] Or
Forms![FormName]![TextBoxName1] Is Null) And (FieldName2 =
Forms![FormName]![TextBoxName2] Or Forms![FormName]![TextBoxName2] Is Null)

On the After Update event refresh the form
Me.Requery

**************
If the Records displayed in a sub form, then
Me.[SubFormControlName].Requery

 

[rjw24]

Sorry, I should have said, I'm not particularly strong when it comes to
expressions etc. Am i trying something very complex here? I have a database
with many fields such as date, name, department and have set a subform named
updates with a one to many link between the id numbers. I would like this
form to be the search GUI. I want the form to be blank and when a search
button is clicked for it to return all records related to the entered data
ignoring those with no data entered.

Sorry to be a dummy haha

Thanks

In the Form RecordSource you can refer to the text boxes and filter by them,
so only the related records will apear, and by adding another criteria return
all records if there was no value entered

Something like

Select * From TableName Where
(FieldName1 = Forms![FormName]![TextBoxName1] Or
Forms![FormName]![TextBoxName1] Is Null) And (FieldName2 =
Forms![FormName]![TextBoxName2] Or Forms![FormName]![TextBoxName2] Is Null)

On the After Update event refresh the form
Me.Requery

**************
If the Records displayed in a sub form, then
Me.[SubFormControlName].Requery

--
Good Luck
BS"D

I would like to create a search form which is identical to a form I have set
up already and gives the user the opportunity to search any of the fields
within that form. Essentially I want to be able to use the 'filter by form'
format but I am aware that code is unable to run in this format.

Thanks

 

[Ofer Cohen]

Let start in stages, will start with one text box for the filter

Create a MainForm called - MainFilter
Create a text box in the main form, called - department
Create a Button the main form to active the filter

Create a sub form Called- SubFilter, and bound it to the table (record source)
select * From TableName
***********************
At that stage when the form is run all the records in the sub form will apear

***********************
Now add the filter to the SubForm using the record source
select * From TableName Where department = Forms![MainFilter]![department]
Or Forms![MainFilter]![department] Is Null

***********************
On the On Click event of the button write
Me.[SubFilter].Requery

Make sure that the subform control has the same name as the subform

***********************
Now,try it , anter a value in the text box and press the button.
If that will work well be able to move to the next text box

--
Good Luck
BS"D

Sorry, I should have said, I'm not particularly strong when it comes to
expressions etc. Am i trying something very complex here? I have a database
with many fields such as date, name, department and have set a subform named
updates with a one to many link between the id numbers. I would like this
form to be the search GUI. I want the form to be blank and when a search
button is clicked for it to return all records related to the entered data
ignoring those with no data entered.

Sorry to be a dummy haha

Thanks

In the Form RecordSource you can refer to the text boxes and filter by them,
so only the related records will apear, and by adding another criteria return
all records if there was no value entered

Something like

Select * From TableName Where
(FieldName1 = Forms![FormName]![TextBoxName1] Or
Forms![FormName]![TextBoxName1] Is Null) And (FieldName2 =
Forms![FormName]![TextBoxName2] Or Forms![FormName]![TextBoxName2] Is Null)

On the After Update event refresh the form
Me.Requery

**************
If the Records displayed in a sub form, then
Me.[SubFormControlName].Requery

--
Good Luck
BS"D

I would like to create a search form which is identical to a form I have set
up already and gives the user the opportunity to search any of the fields
within that form. Essentially I want to be able to use the 'filter by form'
format but I am aware that code is unable to run in this format.

Thanks

 

[rjw24]

I did everything as u said but the only way I could enter the code in the
record source for the subform was to create a query, When I search for a
department which I know is included in some records, all are filtered out.
Dont give up on me yet, im not too far away!

Let start in stages, will start with one text box for the filter

Create a MainForm called - MainFilter
Create a text box in the main form, called - department
Create a Button the main form to active the filter

Create a sub form Called- SubFilter, and bound it to the table (record source)
select * From TableName
***********************
At that stage when the form is run all the records in the sub form will apear

***********************
Now add the filter to the SubForm using the record source
select * From TableName Where department = Forms![MainFilter]![department]
Or Forms![MainFilter]![department] Is Null

***********************
On the On Click event of the button write
Me.[SubFilter].Requery

Make sure that the subform control has the same name as the subform

***********************
Now,try it , anter a value in the text box and press the button.
If that will work well be able to move to the next text box

--
Good Luck
BS"D

Sorry, I should have said, I'm not particularly strong when it comes to
expressions etc. Am i trying something very complex here? I have a database
with many fields such as date, name, department and have set a subform named
updates with a one to many link between the id numbers. I would like this
form to be the search GUI. I want the form to be blank and when a search
button is clicked for it to return all records related to the entered data
ignoring those with no data entered.

Sorry to be a dummy haha

Thanks

In the Form RecordSource you can refer to the text boxes and filter by them,
so only the related records will apear, and by adding another criteria return
all records if there was no value entered

Something like

Select * From TableName Where
(FieldName1 = Forms![FormName]![TextBoxName1] Or
Forms![FormName]![TextBoxName1] Is Null) And (FieldName2 =
Forms![FormName]![TextBoxName2] Or Forms![FormName]![TextBoxName2] Is Null)

On the After Update event refresh the form
Me.Requery

**************
If the Records displayed in a sub form, then
Me.[SubFormControlName].Requery

--
Good Luck
BS"D


:

I would like to create a search form which is identical to a form I have set
up already and gives the user the opportunity to search any of the fields
within that form. Essentially I want to be able to use the 'filter by form'
format but I am aware that code is unable to run in this format.

Thanks

 

[rjw24]

The table is called 'tblEvents' and contains the following fields:

Event Number, Auto
Entry Date, Date/Time
Entry Time, Date/Time
Allocated Person, Text(Look-up)
Review Date, Date/Time
Business Area, Text(Lookup)
Date of Occurance, Date/Time
Department, Text (Lookup)
Status, Text (Lookup)
SBU, Text (Lookup)

Plus a few other fields which are irrlevant

email: [email protected]

Thanks

Post the structure of the table

TableName, fields names and type.
'
Tomorow I'll send you an MDB sample with a form,

Please include your EMail Address

UserName <@> hotmail <.> com

--
Good Luck
BS"D

I did everything as u said but the only way I could enter the code in the
record source for the subform was to create a query, When I search for a
department which I know is included in some records, all are filtered out.
Dont give up on me yet, im not too far away!

Let start in stages, will start with one text box for the filter

Create a MainForm called - MainFilter
Create a text box in the main form, called - department
Create a Button the main form to active the filter

Create a sub form Called- SubFilter, and bound it to the table (record source)
select * From TableName
***********************
At that stage when the form is run all the records in the sub form will apear

***********************
Now add the filter to the SubForm using the record source
select * From TableName Where department = Forms![MainFilter]![department]
Or Forms![MainFilter]![department] Is Null

***********************
On the On Click event of the button write
Me.[SubFilter].Requery

Make sure that the subform control has the same name as the subform

***********************
Now,try it , anter a value in the text box and press the button.
If that will work well be able to move to the next text box

--
Good Luck
BS"D


:

Sorry, I should have said, I'm not particularly strong when it comes to
expressions etc. Am i trying something very complex here? I have a database
with many fields such as date, name, department and have set a subform named
updates with a one to many link between the id numbers. I would like this
form to be the search GUI. I want the form to be blank and when a search
button is clicked for it to return all records related to the entered data
ignoring those with no data entered.

Sorry to be a dummy haha

Thanks

:

In the Form RecordSource you can refer to the text boxes and filter by them,
so only the related records will apear, and by adding another criteria return
all records if there was no value entered

Something like

Select * From TableName Where
(FieldName1 = Forms![FormName]![TextBoxName1] Or
Forms![FormName]![TextBoxName1] Is Null) And (FieldName2 =
Forms![FormName]![TextBoxName2] Or Forms![FormName]![TextBoxName2] Is Null)

On the After Update event refresh the form
Me.Requery

**************
If the Records displayed in a sub form, then
Me.[SubFormControlName].Requery

--
Good Luck
BS"D


:

I would like to create a search form which is identical to a form I have set
up already and gives the user the opportunity to search any of the fields
within that form. Essentially I want to be able to use the 'filter by form'
format but I am aware that code is unable to run in this format.

Thanks

 

[rjw24]

done it! used allen search form creator, excellent