Coding a Search Button

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hi I have some tables and in each one I have diffrent
things for example one maybe DVD's annother maybe CD's....

I have certain forms which display them... I would like
these forms to be able to search through the table that
the form is based on so it would be searching through the
DVD table if the form was based on it... its just a simple
thing so that the user can search the records listed
rather than scroll through them all...

Is there a way I could do this???

Then could you tell me how I display my results in a
subform please... I would like it so that if the user
types in something like ra* and them can get things like
Rambo1, Rambo2, Rambo3,Ransom etc... so I would like to
excorporate the Wildcard (*).

Many Thanks

James
 
James said:
Hi I have some tables and in each one I have diffrent
things for example one maybe DVD's annother maybe CD's....

I have certain forms which display them... I would like
these forms to be able to search through the table that
the form is based on so it would be searching through the
DVD table if the form was based on it... its just a simple
thing so that the user can search the records listed
rather than scroll through them all...

Is there a way I could do this???

Then could you tell me how I display my results in a
subform please... I would like it so that if the user
types in something like ra* and them can get things like
Rambo1, Rambo2, Rambo3,Ransom etc... so I would like to
excorporate the Wildcard (*).

Many Thanks

James
Click to expand...


James,

There's already a built-in search and filter capability in access forms. If
you right-click on any field, and enter , for example, "ra*" into the title
field, this will bring up all titles that start with ra.

Depending on the technical ability of your users , this may or may not be
sufficient. In many cases we have to build customized search forms. If you
scroll down , you'll see my response to "searching Tables"

Jack
 
Hi there treebeard...

I cannot find that "Searching Tables" post...

PLease could you either advise me on what I need to do? or
could you be so kind as you copy your reply into this
thread please?

How do I use a command button to utilise that filter thing
you were on about but by using the text box on my form?

Would this open in a sub form or the current window? as I
would like it to open a form and display the results so
that they can see there results. then if they want to
scroll through the list then they can just close the open
form stating there results of there search...

Can this be done?

Many Thanks

James


Many Thanks

James
 
James,

Here it is. It opens a form with the results of the search. Let us know if
you need any more help.

Jack


Create a form and add text boxes to the form. Put as many as you have
fields that you want the user to search on.(i.e. Date, VTitle, &
WindowQuantity).

Place a button on the form called searchButton.

In the OnClick event put this:

Dim strCriteria As String
Const strLogicalOperator = "AND"

strCriteria = " "

' use this formatting for Dates
If IsDate(Me.Date) Then ' a better IsNull
strCriteria = "[Date] = #" & Format$(Me.Date, "mm/dd/yyyy") & "#"
End If

' use this formatting for strings
If Len(Trim(Me.VTitle& vbNullString)) > 0 Then ' a better IsNull
strCriteria = strCriteria & " AND [VTitle] = '" & Trim(Me.VTitle) & "'"
End If

' use this formatting for Numbers
If IsNumeric(Me.windowquanty) Then ' a better IsNull
strCriteria = strCriteria & " AND [windowquanty] = " & Me.windowquanty
End If

'etc
'etc
' do this for as many fields you have on your form
'
'
' check to see if the user entered anything

If Not Len(Trim(strCriteria)) > 0 Then
MsgBox "Please enter in a search criteria."
Exit Sub
End If

' strip off the prefix in case the user had nothing for Date
strCriteria = RemovePrefix(strLogicalOperator, strCriteria)

' check for matching records
If DCount("*", "[YourTableName]", strCriteria) = 0 then
MsgBox "There are no records which match your criteria. Try Again.
Exit Sub
End If

DoCmd.Close ' close this form

' open the form with the query results
DoCmd.OpenForm "YourFormName", , , strCriteria

...........................................................................
here's the code for the "RemovePrefix" function:

Public Function RemovePrefix(strPrefix As String, strEntireString As String)
As String
Dim strReturnedString As String, intStringLength As Integer, intPrefixLength
As Integer
' strips off the prefix from the string , if present
intPrefixLength = Len(strPrefix)
strReturnedString = Trim(strEntireString)
intStringLength = Len(strReturnedString)
If intPrefixLength < intStringLength Then
If Left(strReturnedString, intPrefixLength) = strPrefix Then
strReturnedString = Right(strReturnedString, intStringLength -
intPrefixLength)
strReturnedString = Trim(strReturnedString)
End If
End If

RemovePrefix = strReturnedString
End Function
 
Ok many Thanks...

Which ones would I need? Also is the below code using the
inbuild function? When the user clicks on Search Iwould
like it to put the results in a form which pops up...

then when they close the results form it asks you if you
wish to search again... if you do respond yes then it goes
back to the search form with the cursor in the search
field with teh previous serce removed... I only have one
field per search form you see... where by if the user
selects no then it just returns to the form.

Is this possible if so how would I achieve this?

In your post before you mentioned about Removeing
Prefixes, formatting strings what is this all about? Which
bits would I need from the below post? and does the below
post use the (*) wildcard?

Many Thanks

James
-----Original Message-----
James,

Here it is. It opens a form with the results of the search. Let us know if
you need any more help.

Jack


Create a form and add text boxes to the form. Put as many as you have
fields that you want the user to search on.(i.e. Date, VTitle, &
WindowQuantity).

Place a button on the form called searchButton.

In the OnClick event put this:

Dim strCriteria As String
Const strLogicalOperator = "AND"

strCriteria = " "

' use this formatting for Dates
If IsDate(Me.Date) Then ' a better IsNull
strCriteria = "[Date] = #" &
Click to expand...
Format$(Me.Date, "mm/dd/yyyy") & "#"
End If

' use this formatting for strings
If Len(Trim(Me.VTitle& vbNullString)) > 0 Then ' a better IsNull
strCriteria = strCriteria & " AND [VTitle] = '" & Trim (Me.VTitle) & "'"
End If

' use this formatting for Numbers
If IsNumeric(Me.windowquanty) Then ' a better IsNull
strCriteria = strCriteria & " AND [windowquanty] = " & Me.windowquanty
End If

'etc
'etc
' do this for as many fields you have on your form
'
'
' check to see if the user entered anything

If Not Len(Trim(strCriteria)) > 0 Then
MsgBox "Please enter in a search criteria."
Exit Sub
End If

' strip off the prefix in case the user had nothing for Date
strCriteria = RemovePrefix(strLogicalOperator, strCriteria)

' check for matching records
If DCount("*", "[YourTableName]", strCriteria) = 0 then
MsgBox "There are no records which match your criteria. Try Again.
Exit Sub
End If

DoCmd.Close ' close this form

' open the form with the query results
DoCmd.OpenForm "YourFormName", , , strCriteria

.......................................................... ..................
here's the code for the "RemovePrefix" function:

Public Function RemovePrefix(strPrefix As String, strEntireString As String)
As String
Dim strReturnedString As String, intStringLength As Integer, intPrefixLength
As Integer
' strips off the prefix from the string , if present
intPrefixLength = Len(strPrefix)
strReturnedString = Trim(strEntireString)
intStringLength = Len(strReturnedString)
If intPrefixLength < intStringLength Then
If Left(strReturnedString, intPrefixLength) = strPrefix Then
strReturnedString = Right(strReturnedString, intStringLength -
intPrefixLength)
strReturnedString = Trim(strReturnedString)
End If
End If

RemovePrefix = strReturnedString
End Function




Hi there treebeard...

I cannot find that "Searching Tables" post...

PLease could you either advise me on what I need to do? or
could you be so kind as you copy your reply into this
thread please?

How do I use a command button to utilise that filter thing
you were on about but by using the text box on my form?

Would this open in a sub form or the current window? as I
would like it to open a form and display the results so
that they can see there results. then if they want to
scroll through the list then they can just close the open
form stating there results of there search...

Can this be done?

Many Thanks

James


Many Thanks

James
Click to expand...


.
Click to expand...
 
James said:
Ok many Thanks...

Which ones would I need? Also is the below code using the
inbuild function? When the user clicks on Search Iwould
like it to put the results in a form which pops up...
Click to expand...

James ,

I never heard of the imBuild function. You have to create the form yourself
(Results Form), the one that will display the results. I usually create
these in the "Continuous Forms" view for displaying results like this. The
data source of the form should be the table you are searching.

then when they close the results form it asks you if you
wish to search again... if you do respond yes then it goes
back to the search form with the cursor in the search
field with teh previous serce removed...
Click to expand...

Put several buttons on the footer of your results form. Each button will
provide a different action such as "New Search", "Close".

I only have one
field per search form you see... where by if the user
selects no then it just returns to the form.

Is this possible if so how would I achieve this?

In your post before you mentioned about Removeing
Prefixes, formatting strings what is this all about?
Click to expand...

It only comes into play when you give the user several fields to enter
search criteria
Which
bits would I need from the below post? and does the below
post use the (*) wildcard?
Click to expand...

No it doesn't include wildcards. Let's see if we can get it to work without
wildcards, then I'll show you how to use them.

again:
Step 1: Create a form for searching
Step 2: Put a text Box on the form and name it the name of the field you are
providing the user to search with.
Step 3 : create a new form for displaying the results. Select the "Tabular
Layout" or "Continuous Mode" . Make the record source to be the table you
are searching on.

After you've done that , let me know what the names of your forms and fields
and buttons are.

Good Luck,

Jack
Many Thanks

James
-----Original Message-----
James,

Here it is. It opens a form with the results of the search. Let us know if
you need any more help.

Jack


Create a form and add text boxes to the form. Put as many as you have
fields that you want the user to search on.(i.e. Date, VTitle, &
WindowQuantity).

Place a button on the form called searchButton.

In the OnClick event put this:

Dim strCriteria As String
Const strLogicalOperator = "AND"

strCriteria = " "

' use this formatting for Dates
If IsDate(Me.Date) Then ' a better IsNull
strCriteria = "[Date] = #" &
Click to expand...
Format$(Me.Date, "mm/dd/yyyy") & "#"
End If

' use this formatting for strings
If Len(Trim(Me.VTitle& vbNullString)) > 0 Then ' a better IsNull
strCriteria = strCriteria & " AND [VTitle] = '" & Trim (Me.VTitle) & "'"
End If

' use this formatting for Numbers
If IsNumeric(Me.windowquanty) Then ' a better IsNull
strCriteria = strCriteria & " AND [windowquanty] = " & Me.windowquanty
End If

'etc
'etc
' do this for as many fields you have on your form
'
'
' check to see if the user entered anything

If Not Len(Trim(strCriteria)) > 0 Then
MsgBox "Please enter in a search criteria."
Exit Sub
End If

' strip off the prefix in case the user had nothing for Date
strCriteria = RemovePrefix(strLogicalOperator, strCriteria)

' check for matching records
If DCount("*", "[YourTableName]", strCriteria) = 0 then
MsgBox "There are no records which match your criteria. Try Again.
Exit Sub
End If

DoCmd.Close ' close this form

' open the form with the query results
DoCmd.OpenForm "YourFormName", , , strCriteria

.......................................................... .................
here's the code for the "RemovePrefix" function:

Public Function RemovePrefix(strPrefix As String, strEntireString As String)
As String
Dim strReturnedString As String, intStringLength As Integer, intPrefixLength
As Integer
' strips off the prefix from the string , if present
intPrefixLength = Len(strPrefix)
strReturnedString = Trim(strEntireString)
intStringLength = Len(strReturnedString)
If intPrefixLength < intStringLength Then
If Left(strReturnedString, intPrefixLength) = strPrefix Then
strReturnedString = Right(strReturnedString, intStringLength -
intPrefixLength)
strReturnedString = Trim(strReturnedString)
End If
End If

RemovePrefix = strReturnedString
End Function




Hi there treebeard...

I cannot find that "Searching Tables" post...

PLease could you either advise me on what I need to do? or
could you be so kind as you copy your reply into this
thread please?

How do I use a command button to utilise that filter thing
you were on about but by using the text box on my form?

Would this open in a sub form or the current window? as I
would like it to open a form and display the results so
that they can see there results. then if they want to
scroll through the list then they can just close the open
form stating there results of there search...

Can this be done?

Many Thanks

James


Many Thanks

James
-----Original Message-----

message
Hi I have some tables and in each one I have diffrent
things for example one maybe DVD's annother maybe
CD's....

I have certain forms which display them... I would like
these forms to be able to search through the table that
the form is based on so it would be searching through
the
DVD table if the form was based on it... its just a
simple
thing so that the user can search the records listed
rather than scroll through them all...

Is there a way I could do this???

Then could you tell me how I display my results in a
subform please... I would like it so that if the user
types in something like ra* and them can get things like
Rambo1, Rambo2, Rambo3,Ransom etc... so I would like to
excorporate the Wildcard (*).

Many Thanks

James


James,

There's already a built-in search and filter capability
in access forms. If
you right-click on any field, and enter , for
example, "ra*" into the title
field, this will bring up all titles that start with ra.

Depending on the technical ability of your users , this
may or may not be
sufficient. In many cases we have to build customized
search forms. If you
scroll down , you'll see my response to "searching Tables"

Jack


.
Click to expand...


.
Click to expand...
Click to expand...
 
Ok seems like a good idea to me take it a step at a time...

Right as for the inbuilt function someone told me that you
right click on the data and them filter it or something...

Anyway...

I have created a Search form called (frmSearchAllRecords)
at the moment its based on tblCD which has only 1 field CD
Title... because I am going to need either a search form
to search all tables or a general search form which is
relevant to each form but opens one results form which
displayes the results of any search made....

I have also created that other tabular form based on
tblCDs... thats called frmResultsCD (Just incase I have to
create the same form for each table...

Where do we go from here?

Many Thanks

James

-----Original Message-----

Ok many Thanks...

Which ones would I need? Also is the below code using the
inbuild function? When the user clicks on Search Iwould
like it to put the results in a form which pops up...
Click to expand...

James ,

I never heard of the imBuild function. You have to create the form yourself
(Results Form), the one that will display the results. I usually create
these in the "Continuous Forms" view for displaying results like this. The
data source of the form should be the table you are searching.
then when they close the results form it asks you if you
wish to search again... if you do respond yes then it goes
back to the search form with the cursor in the search
field with teh previous serce removed...
Click to expand...

Put several buttons on the footer of your results form. Each button will
provide a different action such as "New Search", "Close".

I only have one
field per search form you see... where by if the user
selects no then it just returns to the form.

Is this possible if so how would I achieve this?

In your post before you mentioned about Removeing
Prefixes, formatting strings what is this all about?
Click to expand...

It only comes into play when you give the user several fields to enter
search criteria
Which
bits would I need from the below post? and does the below
post use the (*) wildcard?
Click to expand...

No it doesn't include wildcards. Let's see if we can get it to work without
wildcards, then I'll show you how to use them.

again:
Step 1: Create a form for searching
Step 2: Put a text Box on the form and name it the name of the field you are
providing the user to search with.
Step 3 : create a new form for displaying the results. Select the "Tabular
Layout" or "Continuous Mode" . Make the record source to be the table you
are searching on.

After you've done that , let me know what the names of your forms and fields
and buttons are.

Good Luck,

Jack
Many Thanks

James
-----Original Message-----
James,

Here it is. It opens a form with the results of the search. Let us know if
you need any more help.

Jack


Create a form and add text boxes to the form. Put as many as you have
fields that you want the user to search on.(i.e. Date, VTitle, &
WindowQuantity).

Place a button on the form called searchButton.

In the OnClick event put this:

Dim strCriteria As String
Const strLogicalOperator = "AND"

strCriteria = " "

' use this formatting for Dates
If IsDate(Me.Date) Then ' a better IsNull
strCriteria = "[Date] = #" &
Click to expand...
Format$(Me.Date, "mm/dd/yyyy") & "#"
End If

' use this formatting for strings
If Len(Trim(Me.VTitle& vbNullString)) > 0 Then ' a
Click to expand...
better
IsNull
strCriteria = strCriteria & " AND [VTitle] = '" & Trim (Me.VTitle) & "'"
End If

' use this formatting for Numbers
If IsNumeric(Me.windowquanty) Then ' a better IsNull
strCriteria = strCriteria & " AND [windowquanty] = " & Me.windowquanty
End If

'etc
'etc
' do this for as many fields you have on your form
'
'
' check to see if the user entered anything

If Not Len(Trim(strCriteria)) > 0 Then
MsgBox "Please enter in a search criteria."
Exit Sub
End If

' strip off the prefix in case the user had nothing for Date
strCriteria = RemovePrefix(strLogicalOperator, strCriteria)

' check for matching records
If DCount("*", "[YourTableName]", strCriteria) = 0 then
MsgBox "There are no records which match your criteria. Try Again.
Exit Sub
End If

DoCmd.Close ' close this form

' open the form with the query results
DoCmd.OpenForm "YourFormName", , , strCriteria
Click to expand...
..........................................................
.................
here's the code for the "RemovePrefix" function:

Public Function RemovePrefix(strPrefix As String, strEntireString As String)
As String
Dim strReturnedString As String, intStringLength As Integer, intPrefixLength
As Integer
' strips off the prefix from the string , if present
intPrefixLength = Len(strPrefix)
strReturnedString = Trim(strEntireString)
intStringLength = Len(strReturnedString)
If intPrefixLength < intStringLength Then
If Left(strReturnedString, intPrefixLength) = strPrefix Then
strReturnedString = Right
Click to expand...
(strReturnedString,
intStringLength -
intPrefixLength)
strReturnedString = Trim(strReturnedString)
End If
End If

RemovePrefix = strReturnedString
End Function




Hi there treebeard...

I cannot find that "Searching Tables" post...

PLease could you either advise me on what I need to
Click to expand...
do?
or
could you be so kind as you copy your reply into this
thread please?

How do I use a command button to utilise that filter thing
you were on about but by using the text box on my form?

Would this open in a sub form or the current window?
Click to expand...
as
I
would like it to open a form and display the results so
that they can see there results. then if they want to
scroll through the list then they can just close the open
form stating there results of there search...

Can this be done?

Many Thanks

James


Many Thanks

James
-----Original Message-----

message
Hi I have some tables and in each one I have diffrent
things for example one maybe DVD's annother maybe
CD's....

I have certain forms which display them... I would like
these forms to be able to search through the table that
the form is based on so it would be searching through
the
DVD table if the form was based on it... its just a
simple
thing so that the user can search the records listed
rather than scroll through them all...

Is there a way I could do this???

Then could you tell me how I display my results in a
subform please... I would like it so that if the user
types in something like ra* and them can get
Click to expand...
things
like
Rambo1, Rambo2, Rambo3,Ransom etc... so I would
Click to expand...
like
to
excorporate the Wildcard (*).

Many Thanks

James


James,

There's already a built-in search and filter capability
in access forms. If
you right-click on any field, and enter , for
example, "ra*" into the title
field, this will bring up all titles that start with ra.

Depending on the technical ability of your users , this
may or may not be
sufficient. In many cases we have to build customized
search forms. If you
scroll down , you'll see my response to "searching Tables"

Jack


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
James said:
Ok seems like a good idea to me take it a step at a time...

Right as for the inbuilt function someone told me that you
right click on the data and them filter it or something...
Click to expand...

Oh, I see. No it doesn't use the built-in function. You can't use that
function if you want to use a command button.

Anyway...

I have created a Search form called (frmSearchAllRecords)
at the moment its based on tblCD which has only 1 field CD
Title... because I am going to need either a search form
to search all tables or a general search form which is
relevant to each form but opens one results form which
displayes the results of any search made....

I have also created that other tabular form based on
tblCDs... thats called frmResultsCD (Just incase I have to
create the same form for each table...

Where do we go from here?

Many Thanks

James
Click to expand...


Put frmSearchAllRecords in design mode. Place a button on the form and name
it "SearchButton" . In the "On Click" Event of the button(select Code
Builder) put the following code:
*********************************
Private Sub SearchButton_Click()
Dim strCriteria As String

strCriteria = "[CDTitle] = '" & Me.CDTitle & "'"
DoCmd.OpenForm "frmResultsCD", , , strCriteria

End Sub
*********************************


Then view the form in "Form View" and type a CD Title you want to search for
and click the button.

Make sure you have one unbound text box on the form called "CDTitle ".

Jack
 
Ok thanks for that can I do the same for each of my little
search parts or should I make one big search form which
would be easiest?

Not to nit pick or anything (because you have helped me
alot with this) It picks up 56 records but the thing is I
have copied the search criteria from the table thinking it
wil only pull up one result... Is it possible that this
could be done? if not dont worry it still works I will
just tidy up my search form a little...

So whick would you suggest the easiest search form to do?
and also I will be taking this database with me over
weekend so I will continue then if you will be around...

Many Thanks for all your efforts

James
-----Original Message-----

Ok seems like a good idea to me take it a step at a time...

Right as for the inbuilt function someone told me that you
right click on the data and them filter it or
Click to expand...
something...

Oh, I see. No it doesn't use the built-in function. You can't use that
function if you want to use a command button.

Anyway...

I have created a Search form called (frmSearchAllRecords)
at the moment its based on tblCD which has only 1 field CD
Title... because I am going to need either a search form
to search all tables or a general search form which is
relevant to each form but opens one results form which
displayes the results of any search made....

I have also created that other tabular form based on
tblCDs... thats called frmResultsCD (Just incase I have to
create the same form for each table...

Where do we go from here?

Many Thanks

James
Click to expand...


Put frmSearchAllRecords in design mode. Place a button on the form and name
it "SearchButton" . In the "On Click" Event of the button (select Code
Builder) put the following code:
*********************************
Private Sub SearchButton_Click()
Dim strCriteria As String

strCriteria = "[CDTitle] = '" & Me.CDTitle & "'"
DoCmd.OpenForm "frmResultsCD", , , strCriteria

End Sub
*********************************


Then view the form in "Form View" and type a CD Title you want to search for
and click the button.

Make sure you have one unbound text box on the form called "CDTitle ".

Jack













.
Click to expand...
 
James said:
Ok thanks for that can I do the same for each of my little
search parts or should I make one big search form which
would be easiest?

Not to nit pick or anything (because you have helped me
alot with this) It picks up 56 records but the thing is I
have copied the search criteria from the table thinking it
wil only pull up one result... Is it possible that this
could be done? if not dont worry it still works I will
just tidy up my search form a little...
Click to expand...

I don't understand why it is showing 56 records; it will show as many
records as the search criteria specifies.

Do you want to show one record at a time? If so set your frmResultsCD
default view to be "Single Form"

So whick would you suggest the easiest search form to do?
and also I will be taking this database with me over
weekend so I will continue then if you will be around...
Click to expand...

One search form with multiple buttons for searching different tables would
be the easiest.

John


message
Ok seems like a good idea to me take it a step at a time...

Right as for the inbuilt function someone told me that you
right click on the data and them filter it or
Click to expand...
something...

Oh, I see. No it doesn't use the built-in function. You can't use that
function if you want to use a command button.

Anyway...

I have created a Search form called (frmSearchAllRecords)
at the moment its based on tblCD which has only 1 field CD
Title... because I am going to need either a search form
to search all tables or a general search form which is
relevant to each form but opens one results form which
displayes the results of any search made....

I have also created that other tabular form based on
tblCDs... thats called frmResultsCD (Just incase I have to
create the same form for each table...

Where do we go from here?

Many Thanks

James
Click to expand...


Put frmSearchAllRecords in design mode. Place a button on the form and name
it "SearchButton" . In the "On Click" Event of the button (select Code
Builder) put the following code:
*********************************
Private Sub SearchButton_Click()
Dim strCriteria As String

strCriteria = "[CDTitle] = '" & Me.CDTitle & "'"
DoCmd.OpenForm "frmResultsCD", , , strCriteria

End Sub
*********************************


Then view the form in "Form View" and type a CD Title you want to search for
and click the button.

Make sure you have one unbound text box on the form called "CDTitle ".

Jack













.
Click to expand...
Click to expand...
 
Ok cool I have sorted out that little problem I didnt
change the code at the back sorry my fault...

So to do this search form all I would need to do is to use
the code you have given me in like a case statment? for
each table? or would I need the original form based on a
query which has all the tables in? If I need the query
then how do I go about doing that??

Then when all this is done you will have to help me with
the Wildcards...

Many Thanks for all this...

James
-----Original Message-----

Ok thanks for that can I do the same for each of my little
search parts or should I make one big search form which
would be easiest?

Not to nit pick or anything (because you have helped me
alot with this) It picks up 56 records but the thing is I
have copied the search criteria from the table thinking it
wil only pull up one result... Is it possible that this
could be done? if not dont worry it still works I will
just tidy up my search form a little...
Click to expand...

I don't understand why it is showing 56 records; it will show as many
records as the search criteria specifies.

Do you want to show one record at a time? If so set your frmResultsCD
default view to be "Single Form"

So whick would you suggest the easiest search form to do?
and also I will be taking this database with me over
weekend so I will continue then if you will be around...
Click to expand...

One search form with multiple buttons for searching different tables would
be the easiest.

John


message
Ok seems like a good idea to me take it a step at a time...

Right as for the inbuilt function someone told me
Click to expand...
that
you
right click on the data and them filter it or something...

Oh, I see. No it doesn't use the built-in function.
Click to expand...
You
can't use that
function if you want to use a command button.


Anyway...

I have created a Search form called (frmSearchAllRecords)
at the moment its based on tblCD which has only 1
Click to expand...
field
CD
Title... because I am going to need either a search form
to search all tables or a general search form which is
relevant to each form but opens one results form which
displayes the results of any search made....

I have also created that other tabular form based on
tblCDs... thats called frmResultsCD (Just incase I
Click to expand...
have
to
create the same form for each table...

Where do we go from here?

Many Thanks

James


Put frmSearchAllRecords in design mode. Place a button
Click to expand...
on
the form and name
it "SearchButton" . In the "On Click" Event of the
Click to expand...
button
(select Code
Builder) put the following code:
*********************************
Private Sub SearchButton_Click()
Dim strCriteria As String

strCriteria = "[CDTitle] = '" & Me.CDTitle & "'"
DoCmd.OpenForm "frmResultsCD", , , strCriteria

End Sub
*********************************


Then view the form in "Form View" and type a CD Title
Click to expand...
you
want to search for
and click the button.

Make sure you have one unbound text box on the form called "CDTitle ".

Jack













.
Click to expand...
Click to expand...


.
Click to expand...
 
James,

I'll be away until Thursday. Perhaps you should submit a new post with a new
topic and explain what you have done so far and what you need to do.

Don't cross-post (one post in multiple newsgroups) because sometimes that
upsets people and they won't answer you.

Jack

james said:
Ok cool I have sorted out that little problem I didnt
change the code at the back sorry my fault...

So to do this search form all I would need to do is to use
the code you have given me in like a case statment? for
each table? or would I need the original form based on a
query which has all the tables in? If I need the query
then how do I go about doing that??

Then when all this is done you will have to help me with
the Wildcards...

Many Thanks for all this...

James
-----Original Message-----

Ok thanks for that can I do the same for each of my little
search parts or should I make one big search form which
would be easiest?

Not to nit pick or anything (because you have helped me
alot with this) It picks up 56 records but the thing is I
have copied the search criteria from the table thinking it
wil only pull up one result... Is it possible that this
could be done? if not dont worry it still works I will
just tidy up my search form a little...
Click to expand...

I don't understand why it is showing 56 records; it will show as many
records as the search criteria specifies.

Do you want to show one record at a time? If so set your frmResultsCD
default view to be "Single Form"

So whick would you suggest the easiest search form to do?
and also I will be taking this database with me over
weekend so I will continue then if you will be around...
Click to expand...

One search form with multiple buttons for searching different tables would
be the easiest.

John


message
Ok seems like a good idea to me take it a step at a
time...

Right as for the inbuilt function someone told me that
you
right click on the data and them filter it or
something...

Oh, I see. No it doesn't use the built-in function. You
can't use that
function if you want to use a command button.


Anyway...

I have created a Search form called
(frmSearchAllRecords)
at the moment its based on tblCD which has only 1 field
CD
Title... because I am going to need either a search form
to search all tables or a general search form which is
relevant to each form but opens one results form which
displayes the results of any search made....

I have also created that other tabular form based on
tblCDs... thats called frmResultsCD (Just incase I have
to
create the same form for each table...

Where do we go from here?

Many Thanks

James


Put frmSearchAllRecords in design mode. Place a button on
the form and name
it "SearchButton" . In the "On Click" Event of the button
(select Code
Builder) put the following code:
*********************************
Private Sub SearchButton_Click()
Dim strCriteria As String

strCriteria = "[CDTitle] = '" & Me.CDTitle
& "'"
DoCmd.OpenForm "frmResultsCD", , , strCriteria

End Sub
*********************************


Then view the form in "Form View" and type a CD Title you
want to search for
and click the button.

Make sure you have one unbound text box on the form
called "CDTitle ".

Jack













.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top