HELP PLEASE: Display search results in the same tab below the crit

[sam]

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance

 

[Maurice]

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.

 

[sam]

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub

 

[Maurice]

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.

 

[sam]

Hi Maurice,

I placed the search criterias (Text boxes) and the search button in form
header.

But now how do I change the "recordsource" property to "qGeneric"?

Should I change "recordsource" property of detail or footer section to
'qGeneric"?

I tried looking up the "properties" of footer and detail sections but I
couldnt locate the "recordsource" option there.

Am I missing something here?

Thanks in advance

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.
--
Maurice Ausum


:

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance

 

[Maurice]

Hi Sam,

Open your form in designview. Then goto the menubar choose view - properties
(or right click on the titlebar of your form and choose properties there).
You'll see the properties window appear for that form. Under the tab "all"
the first option is [Record Source]. That's where you choose the query
"qGeneric". But remember the query has to be there and that's why i said that
when the code has run you don't need to delete the qdf because otherwise the
query doesn't exist. So what you are changing this way is the recordsource of
the form. You can't set a recordsource specifically for a footer or a
detailsection, this is form based.


--
Maurice Ausum

Hi Maurice,

I placed the search criterias (Text boxes) and the search button in form
header.

But now how do I change the "recordsource" property to "qGeneric"?

Should I change "recordsource" property of detail or footer section to
'qGeneric"?

I tried looking up the "properties" of footer and detail sections but I
couldnt locate the "recordsource" option there.

Am I missing something here?

Thanks in advance

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub


:

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.
--
Maurice Ausum


:

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance

 

[sam]

Hey Maurice,

Thanks a lot for your help.

I changed the record source for the form to "qGeneric" and removed the
"delete" statement and added the "me.requery" statement.

But then I get an error on this line: "Set qdf =
dbs.CreateQueryDef("qGeneric")"

The error says: "Run time error: 3021, Object qGeneric already exixts"

I think its because we are not deleting the qGeneric, And we cannot delete
because the record source of the form is set to this query.

Is there a way to fix this?

Thanks in advance

Hi Sam,

Open your form in designview. Then goto the menubar choose view - properties
(or right click on the titlebar of your form and choose properties there).
You'll see the properties window appear for that form. Under the tab "all"
the first option is [Record Source]. That's where you choose the query
"qGeneric". But remember the query has to be there and that's why i said that
when the code has run you don't need to delete the qdf because otherwise the
query doesn't exist. So what you are changing this way is the recordsource of
the form. You can't set a recordsource specifically for a footer or a
detailsection, this is form based.


--
Maurice Ausum

Hi Maurice,

I placed the search criterias (Text boxes) and the search button in form
header.

But now how do I change the "recordsource" property to "qGeneric"?

Should I change "recordsource" property of detail or footer section to
'qGeneric"?

I tried looking up the "properties" of footer and detail sections but I
couldnt locate the "recordsource" option there.

Am I missing something here?

Thanks in advance

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum


:

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub


:

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.
--
Maurice Ausum


:

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance

 

[Maurice]

sure, because the qGeneric already exists you don't have to create it anymore
you just have to fill it.
You can remove the line: Set qdf = dbs.CreateQueryDef("qGeneric")

so your code looks like this:

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

hth
--
Maurice Ausum

Hey Maurice,

Thanks a lot for your help.

I changed the record source for the form to "qGeneric" and removed the
"delete" statement and added the "me.requery" statement.

But then I get an error on this line: "Set qdf =
dbs.CreateQueryDef("qGeneric")"

The error says: "Run time error: 3021, Object qGeneric already exixts"

I think its because we are not deleting the qGeneric, And we cannot delete
because the record source of the form is set to this query.

Is there a way to fix this?

Thanks in advance

Hi Sam,

Open your form in designview. Then goto the menubar choose view - properties
(or right click on the titlebar of your form and choose properties there).
You'll see the properties window appear for that form. Under the tab "all"
the first option is [Record Source]. That's where you choose the query
"qGeneric". But remember the query has to be there and that's why i said that
when the code has run you don't need to delete the qdf because otherwise the
query doesn't exist. So what you are changing this way is the recordsource of
the form. You can't set a recordsource specifically for a footer or a
detailsection, this is form based.


--
Maurice Ausum

Hi Maurice,

I placed the search criterias (Text boxes) and the search button in form
header.

But now how do I change the "recordsource" property to "qGeneric"?

Should I change "recordsource" property of detail or footer section to
'qGeneric"?

I tried looking up the "properties" of footer and detail sections but I
couldnt locate the "recordsource" option there.

Am I missing something here?

Thanks in advance



:

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum


:

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub


:

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.
--
Maurice Ausum


:

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance

 

[sam]

Hey Maurice, Thanks a lot.. I am pretty close now to what I want.

I did what all you said. and i tihnk I am pretty close to what I want, But
Should I approach this with a split form? or a blank form and then have the
search criterias and search button in the header section? and expect to see
the results in the detail section?

sure, because the qGeneric already exists you don't have to create it anymore
you just have to fill it.
You can remove the line: Set qdf = dbs.CreateQueryDef("qGeneric")

so your code looks like this:

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

hth
--
Maurice Ausum

Hey Maurice,

Thanks a lot for your help.

I changed the record source for the form to "qGeneric" and removed the
"delete" statement and added the "me.requery" statement.

But then I get an error on this line: "Set qdf =
dbs.CreateQueryDef("qGeneric")"

The error says: "Run time error: 3021, Object qGeneric already exixts"

I think its because we are not deleting the qGeneric, And we cannot delete
because the record source of the form is set to this query.

Is there a way to fix this?

Thanks in advance

Hi Sam,

Open your form in designview. Then goto the menubar choose view - properties
(or right click on the titlebar of your form and choose properties there).
You'll see the properties window appear for that form. Under the tab "all"
the first option is [Record Source]. That's where you choose the query
"qGeneric". But remember the query has to be there and that's why i said that
when the code has run you don't need to delete the qdf because otherwise the
query doesn't exist. So what you are changing this way is the recordsource of
the form. You can't set a recordsource specifically for a footer or a
detailsection, this is form based.


--
Maurice Ausum


:

Hi Maurice,

I placed the search criterias (Text boxes) and the search button in form
header.

But now how do I change the "recordsource" property to "qGeneric"?

Should I change "recordsource" property of detail or footer section to
'qGeneric"?

I tried looking up the "properties" of footer and detail sections but I
couldnt locate the "recordsource" option there.

Am I missing something here?

Thanks in advance



:

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum


:

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub


:

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.
--
Maurice Ausum


:

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance

 

[Maurice]

Sam,

You should go for a blank form with the search options in the header. Set
the form to continuous if you want to see multiple results in one set. If you
choose the default you have to use the navigationbuttons to see the next
records.

When in desgin set the recordsource of the form to qGeneric. Now add the
fields from that query to your form in the detailsection.
You can add the fields from the field dialog (button 'show fields' in the
toolbar)

make sure you set the code to the afterupdate of the designated button to
perform the search action. Don not use a split form for what you are trying
to achive it will not work as expected.

hth
--
Maurice Ausum

Hey Maurice, Thanks a lot.. I am pretty close now to what I want.

I did what all you said. and i tihnk I am pretty close to what I want, But
Should I approach this with a split form? or a blank form and then have the
search criterias and search button in the header section? and expect to see
the results in the detail section?

sure, because the qGeneric already exists you don't have to create it anymore
you just have to fill it.
You can remove the line: Set qdf = dbs.CreateQueryDef("qGeneric")

so your code looks like this:

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

hth
--
Maurice Ausum

Hey Maurice,

Thanks a lot for your help.

I changed the record source for the form to "qGeneric" and removed the
"delete" statement and added the "me.requery" statement.

But then I get an error on this line: "Set qdf =
dbs.CreateQueryDef("qGeneric")"

The error says: "Run time error: 3021, Object qGeneric already exixts"

I think its because we are not deleting the qGeneric, And we cannot delete
because the record source of the form is set to this query.

Is there a way to fix this?

Thanks in advance


:

Hi Sam,

Open your form in designview. Then goto the menubar choose view - properties
(or right click on the titlebar of your form and choose properties there).
You'll see the properties window appear for that form. Under the tab "all"
the first option is [Record Source]. That's where you choose the query
"qGeneric". But remember the query has to be there and that's why i said that
when the code has run you don't need to delete the qdf because otherwise the
query doesn't exist. So what you are changing this way is the recordsource of
the form. You can't set a recordsource specifically for a footer or a
detailsection, this is form based.


--
Maurice Ausum


:

Hi Maurice,

I placed the search criterias (Text boxes) and the search button in form
header.

But now how do I change the "recordsource" property to "qGeneric"?

Should I change "recordsource" property of detail or footer section to
'qGeneric"?

I tried looking up the "properties" of footer and detail sections but I
couldnt locate the "recordsource" option there.

Am I missing something here?

Thanks in advance



:

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum


:

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub


:

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.
--
Maurice Ausum


:

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance

 

[tom_willpa]

high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD
hairstraightener supplier from www.willpa.com

Are you a Retail businessman who bother by the purchase price? China
Cheapest TOP wholesale website can help you

we are specialize in replica sport goods manufacturing in china, we can
offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are
the best brand replica goods whih are look the same as the original goods.
excellent quality and steady supply for them. we have been marketed in Europe
and American for 3 year. all the goods we offer are AAA quality. our soccer
jersey are Thailand style. If any goods you buy from my company have problem,
we will refund or resend them again. Most of ourProducts have no minimum
order requirements,soyou can shop retail goods at wholesale prices. if you
can buy more than 300usd. We offer free shipping. The more you buy the more
discount for you.

National soccer jerseys: http://www.willpa.com
Club soccer jerseys: http://www.willpa.com
NBA Jerseys: http://www.willpa.com
T-shirt and shirt: http://www.willpa.com
Tracksuit: http://www.willpa.com
Hoody & Jackets: http://www.willpa.com
UGG boots: http://www.willpa.com
Hair style: http://www.willpa.com
shopping Index: http://www.willpa.com

EMS shipping. 7days arrive, paypal accept

want more information pls contact us or check our website: www.willpa.com

 

[sam]

Hey maurice,

I did everything you mentioned, But even after selecting "Continous form"
the results are not displayed in table format in the detail section, but a
"form format" where one record is displayed at a time in the detail section,
and we have to hit the arrow at the bottom to navigate through the results.

One more issue I am having is with refreshing the results. Once I fill in
the criteria and click search, I have to reopen the form to see the updated
results. I think the "Me.requery" is not working properly. Again this results
are in a form view, like one record at a time(even after selecting continous)

Also, What do you mean by "make sure you set the code to the afterupdate of
the designated button to perform the search action."

I am almost there, Hope you can help me finish this.

Thanks a lot again for all your help.

Sam,

You should go for a blank form with the search options in the header. Set
the form to continuous if you want to see multiple results in one set. If you
choose the default you have to use the navigationbuttons to see the next
records.

When in desgin set the recordsource of the form to qGeneric. Now add the
fields from that query to your form in the detailsection.
You can add the fields from the field dialog (button 'show fields' in the
toolbar)

make sure you set the code to the afterupdate of the designated button to
perform the search action. Don not use a split form for what you are trying
to achive it will not work as expected.

hth
--
Maurice Ausum

Hey Maurice, Thanks a lot.. I am pretty close now to what I want.

I did what all you said. and i tihnk I am pretty close to what I want, But
Should I approach this with a split form? or a blank form and then have the
search criterias and search button in the header section? and expect to see
the results in the detail section?

sure, because the qGeneric already exists you don't have to create it anymore
you just have to fill it.
You can remove the line: Set qdf = dbs.CreateQueryDef("qGeneric")

so your code looks like this:

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

hth
--
Maurice Ausum


:

Hey Maurice,

Thanks a lot for your help.

I changed the record source for the form to "qGeneric" and removed the
"delete" statement and added the "me.requery" statement.

But then I get an error on this line: "Set qdf =
dbs.CreateQueryDef("qGeneric")"

The error says: "Run time error: 3021, Object qGeneric already exixts"

I think its because we are not deleting the qGeneric, And we cannot delete
because the record source of the form is set to this query.

Is there a way to fix this?

Thanks in advance


:

Hi Sam,

Open your form in designview. Then goto the menubar choose view - properties
(or right click on the titlebar of your form and choose properties there).
You'll see the properties window appear for that form. Under the tab "all"
the first option is [Record Source]. That's where you choose the query
"qGeneric". But remember the query has to be there and that's why i said that
when the code has run you don't need to delete the qdf because otherwise the
query doesn't exist. So what you are changing this way is the recordsource of
the form. You can't set a recordsource specifically for a footer or a
detailsection, this is form based.


--
Maurice Ausum


:

Hi Maurice,

I placed the search criterias (Text boxes) and the search button in form
header.

But now how do I change the "recordsource" property to "qGeneric"?

Should I change "recordsource" property of detail or footer section to
'qGeneric"?

I tried looking up the "properties" of footer and detail sections but I
couldnt locate the "recordsource" option there.

Am I missing something here?

Thanks in advance



:

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum


:

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub


:

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.
--
Maurice Ausum


:

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance

 

[Maurice]

Hi Sam,

Looks like your are almost there. I was thinking that it would be more clear
if i would show you a sample for this setup so you can follow that.

http://www.themausman.blogspot.com

i've added a sample db for your so you can test what you read.
Let me know if this is what you were looking for. Remember i've build this
in Acc 2003 but it will work in 2007 as well

hth
--
Maurice Ausum

Hey maurice,

I did everything you mentioned, But even after selecting "Continous form"
the results are not displayed in table format in the detail section, but a
"form format" where one record is displayed at a time in the detail section,
and we have to hit the arrow at the bottom to navigate through the results.

One more issue I am having is with refreshing the results. Once I fill in
the criteria and click search, I have to reopen the form to see the updated
results. I think the "Me.requery" is not working properly. Again this results
are in a form view, like one record at a time(even after selecting continous)

Also, What do you mean by "make sure you set the code to the afterupdate of
the designated button to perform the search action."

I am almost there, Hope you can help me finish this.

Thanks a lot again for all your help.

Sam,

You should go for a blank form with the search options in the header. Set
the form to continuous if you want to see multiple results in one set. If you
choose the default you have to use the navigationbuttons to see the next
records.

When in desgin set the recordsource of the form to qGeneric. Now add the
fields from that query to your form in the detailsection.
You can add the fields from the field dialog (button 'show fields' in the
toolbar)

make sure you set the code to the afterupdate of the designated button to
perform the search action. Don not use a split form for what you are trying
to achive it will not work as expected.

hth
--
Maurice Ausum

Hey Maurice, Thanks a lot.. I am pretty close now to what I want.

I did what all you said. and i tihnk I am pretty close to what I want, But
Should I approach this with a split form? or a blank form and then have the
search criterias and search button in the header section? and expect to see
the results in the detail section?



:

sure, because the qGeneric already exists you don't have to create it anymore
you just have to fill it.
You can remove the line: Set qdf = dbs.CreateQueryDef("qGeneric")

so your code looks like this:

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

hth
--
Maurice Ausum


:

Hey Maurice,

Thanks a lot for your help.

I changed the record source for the form to "qGeneric" and removed the
"delete" statement and added the "me.requery" statement.

But then I get an error on this line: "Set qdf =
dbs.CreateQueryDef("qGeneric")"

The error says: "Run time error: 3021, Object qGeneric already exixts"

I think its because we are not deleting the qGeneric, And we cannot delete
because the record source of the form is set to this query.

Is there a way to fix this?

Thanks in advance


:

Hi Sam,

Open your form in designview. Then goto the menubar choose view - properties
(or right click on the titlebar of your form and choose properties there).
You'll see the properties window appear for that form. Under the tab "all"
the first option is [Record Source]. That's where you choose the query
"qGeneric". But remember the query has to be there and that's why i said that
when the code has run you don't need to delete the qdf because otherwise the
query doesn't exist. So what you are changing this way is the recordsource of
the form. You can't set a recordsource specifically for a footer or a
detailsection, this is form based.


--
Maurice Ausum


:

Hi Maurice,

I placed the search criterias (Text boxes) and the search button in form
header.

But now how do I change the "recordsource" property to "qGeneric"?

Should I change "recordsource" property of detail or footer section to
'qGeneric"?

I tried looking up the "properties" of footer and detail sections but I
couldnt locate the "recordsource" option there.

Am I missing something here?

Thanks in advance



:

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum


:

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub


:

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.
--
Maurice Ausum


:

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance

 

[sam]

Thanks a lot for all youe help Maurice.

It worked out perfectly fine

Thanks again

Hi Sam,

Looks like your are almost there. I was thinking that it would be more clear
if i would show you a sample for this setup so you can follow that.

http://www.themausman.blogspot.com

i've added a sample db for your so you can test what you read.
Let me know if this is what you were looking for. Remember i've build this
in Acc 2003 but it will work in 2007 as well

hth
--
Maurice Ausum

Hey maurice,

I did everything you mentioned, But even after selecting "Continous form"
the results are not displayed in table format in the detail section, but a
"form format" where one record is displayed at a time in the detail section,
and we have to hit the arrow at the bottom to navigate through the results.

One more issue I am having is with refreshing the results. Once I fill in
the criteria and click search, I have to reopen the form to see the updated
results. I think the "Me.requery" is not working properly. Again this results
are in a form view, like one record at a time(even after selecting continous)

Also, What do you mean by "make sure you set the code to the afterupdate of
the designated button to perform the search action."

I am almost there, Hope you can help me finish this.

Thanks a lot again for all your help.

Sam,

You should go for a blank form with the search options in the header. Set
the form to continuous if you want to see multiple results in one set. If you
choose the default you have to use the navigationbuttons to see the next
records.

When in desgin set the recordsource of the form to qGeneric. Now add the
fields from that query to your form in the detailsection.
You can add the fields from the field dialog (button 'show fields' in the
toolbar)

make sure you set the code to the afterupdate of the designated button to
perform the search action. Don not use a split form for what you are trying
to achive it will not work as expected.

hth
--
Maurice Ausum


:

Hey Maurice, Thanks a lot.. I am pretty close now to what I want.

I did what all you said. and i tihnk I am pretty close to what I want, But
Should I approach this with a split form? or a blank form and then have the
search criterias and search button in the header section? and expect to see
the results in the detail section?



:

sure, because the qGeneric already exists you don't have to create it anymore
you just have to fill it.
You can remove the line: Set qdf = dbs.CreateQueryDef("qGeneric")

so your code looks like this:

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

hth
--
Maurice Ausum


:

Hey Maurice,

Thanks a lot for your help.

I changed the record source for the form to "qGeneric" and removed the
"delete" statement and added the "me.requery" statement.

But then I get an error on this line: "Set qdf =
dbs.CreateQueryDef("qGeneric")"

The error says: "Run time error: 3021, Object qGeneric already exixts"

I think its because we are not deleting the qGeneric, And we cannot delete
because the record source of the form is set to this query.

Is there a way to fix this?

Thanks in advance


:

Hi Sam,

Open your form in designview. Then goto the menubar choose view - properties
(or right click on the titlebar of your form and choose properties there).
You'll see the properties window appear for that form. Under the tab "all"
the first option is [Record Source]. That's where you choose the query
"qGeneric". But remember the query has to be there and that's why i said that
when the code has run you don't need to delete the qdf because otherwise the
query doesn't exist. So what you are changing this way is the recordsource of
the form. You can't set a recordsource specifically for a footer or a
detailsection, this is form based.


--
Maurice Ausum


:

Hi Maurice,

I placed the search criterias (Text boxes) and the search button in form
header.

But now how do I change the "recordsource" property to "qGeneric"?

Should I change "recordsource" property of detail or footer section to
'qGeneric"?

I tried looking up the "properties" of footer and detail sections but I
couldnt locate the "recordsource" option there.

Am I missing something here?

Thanks in advance



:

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum


:

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub


:

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.
--
Maurice Ausum


:

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance

 

[Maurice]

My pleasure
--
Maurice Ausum

Thanks a lot for all youe help Maurice.

It worked out perfectly fine

Thanks again

Hi Sam,

Looks like your are almost there. I was thinking that it would be more clear
if i would show you a sample for this setup so you can follow that.

http://www.themausman.blogspot.com

i've added a sample db for your so you can test what you read.
Let me know if this is what you were looking for. Remember i've build this
in Acc 2003 but it will work in 2007 as well

hth
--
Maurice Ausum

Hey maurice,

I did everything you mentioned, But even after selecting "Continous form"
the results are not displayed in table format in the detail section, but a
"form format" where one record is displayed at a time in the detail section,
and we have to hit the arrow at the bottom to navigate through the results.

One more issue I am having is with refreshing the results. Once I fill in
the criteria and click search, I have to reopen the form to see the updated
results. I think the "Me.requery" is not working properly. Again this results
are in a form view, like one record at a time(even after selecting continous)

Also, What do you mean by "make sure you set the code to the afterupdate of
the designated button to perform the search action."

I am almost there, Hope you can help me finish this.

Thanks a lot again for all your help.



:

Sam,

You should go for a blank form with the search options in the header. Set
the form to continuous if you want to see multiple results in one set. If you
choose the default you have to use the navigationbuttons to see the next
records.

When in desgin set the recordsource of the form to qGeneric. Now add the
fields from that query to your form in the detailsection.
You can add the fields from the field dialog (button 'show fields' in the
toolbar)

make sure you set the code to the afterupdate of the designated button to
perform the search action. Don not use a split form for what you are trying
to achive it will not work as expected.

hth
--
Maurice Ausum


:

Hey Maurice, Thanks a lot.. I am pretty close now to what I want.

I did what all you said. and i tihnk I am pretty close to what I want, But
Should I approach this with a split form? or a blank form and then have the
search criterias and search button in the header section? and expect to see
the results in the detail section?



:

sure, because the qGeneric already exists you don't have to create it anymore
you just have to fill it.
You can remove the line: Set qdf = dbs.CreateQueryDef("qGeneric")

so your code looks like this:

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

hth
--
Maurice Ausum


:

Hey Maurice,

Thanks a lot for your help.

I changed the record source for the form to "qGeneric" and removed the
"delete" statement and added the "me.requery" statement.

But then I get an error on this line: "Set qdf =
dbs.CreateQueryDef("qGeneric")"

The error says: "Run time error: 3021, Object qGeneric already exixts"

I think its because we are not deleting the qGeneric, And we cannot delete
because the record source of the form is set to this query.

Is there a way to fix this?

Thanks in advance


:

Hi Sam,

Open your form in designview. Then goto the menubar choose view - properties
(or right click on the titlebar of your form and choose properties there).
You'll see the properties window appear for that form. Under the tab "all"
the first option is [Record Source]. That's where you choose the query
"qGeneric". But remember the query has to be there and that's why i said that
when the code has run you don't need to delete the qdf because otherwise the
query doesn't exist. So what you are changing this way is the recordsource of
the form. You can't set a recordsource specifically for a footer or a
detailsection, this is form based.


--
Maurice Ausum


:

Hi Maurice,

I placed the search criterias (Text boxes) and the search button in form
header.

But now how do I change the "recordsource" property to "qGeneric"?

Should I change "recordsource" property of detail or footer section to
'qGeneric"?

I tried looking up the "properties" of footer and detail sections but I
couldnt locate the "recordsource" option there.

Am I missing something here?

Thanks in advance



:

Sam,

Place the searchbutton in the formheader (if there's no header set a header
yourself).
Set the recordsource of your form to your query "qGeneric".
Drag all of the fields from that query to your form.

Now in the code you just have to make a minor change

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object
Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value

dbs.QueryDefs("qGeneric").SQL = StrSql
me.requery

Set qdf = Nothing
Set dbs = Nothing

End Sub

You don't have to delete the query because it will be overwritten everytime.

hth
--
Maurice Ausum


:

Hi Maurice,

Here is the search code:

Private Sub Search_Click()

Dim qdf As Object, StrSql As String, dbs As Object

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("qGeneric")

StrSql = "PROCEDURE CategoryList; " _
& "SELECT [Stu_ID],[HeightIn],[WeightLB],[Delta]" _
& " FROM Student_Table" _
& " WHERE " _
& “Student_ID = “ & Me.StudId.Value _
& " AND " _
& “Height BETWEEN “ & Me.Height1.Value & “ AND “ &
Me.Height2.Value _
& " AND " _
& “Weight BETWEEN “ & Me.Weight1.Value & “ AND “ &
Me.Weight2.Value _
& “Delta BETWEEN “ & Me.Delta1.Value & “ AND “ &
Me.Delta2.Value


dbs.QueryDefs("qGeneric").SQL = StrSql


DoCmd.OpenQuery "qGeneric"

dbs.QueryDefs.Delete "qGeneric"

Set qdf = Nothing
Set dbs = Nothing

End Sub


:

First you need to tell us how you added the search functionality. Why does it
go to a seperate tab? You could place the searchbutton in a form header and
present the results in the detailsection of the form. But then again we need
to know the current situation.
Paste your search code here so we can take a look at it.
--
Maurice Ausum


:

Hi All,

I have designed a form with search button to search from the database. how
can I make the output display below the search button?

Right now the results are displayed in a new tab, But I want to display the
results in the same window below the search criteria.

How can I get this to work?

Thanks in advance