Searching for records

  • Thread starter Thread starter John
  • Start date Start date
J

John

What is the procedure for setting up search capabilities
on a form? I have set up a command button but cannot get
the code right on the "click"
Can you search through Pages?
 
John, I think you want to place an unbound text box on the form (in Form
Header section perhaps?), along with a command button. User enters
something, and then you find the records that have that in a particular
field (such as surname).

If that's the idea, and their could be several possible matches, the best
idea might be to filter the form so it shows only the matches.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.txtFindSurname & """) AND "
End If

If Not IsNull(Me.txtFindFirstName) Then
strWhere = strWhere & "([FirstName] = """ & _
Me.txtFindFirstName & """) AND "
End If

'etc for other search boxes.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub
 
Allen,
I don't mean to get in the middle of you'll
conversation. I have been looking for code like this for
over a month and its works great. I would like to modify
it a little I am trying to using this code with a combo
box. I placed the code in the AfterUpdate and I get is a
pop up asking for me to enter the project name. How can
I alter it code so that the choice from the combo box is
what it will use.
Thank you very much
Raj
-----Original Message-----
John, I think you want to place an unbound text box on the form (in Form
Header section perhaps?), along with a command button. User enters
something, and then you find the records that have that in a particular
field (such as surname).

If that's the idea, and their could be several possible matches, the best
idea might be to filter the form so it shows only the matches.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.txtFindSurname & """) AND "
End If

If Not IsNull(Me.txtFindFirstName) Then
strWhere = strWhere & "([FirstName] = """ & _
Me.txtFindFirstName & """) AND "
End If

'etc for other search boxes.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John said:
What is the procedure for setting up search capabilities
on a form? I have set up a command button but cannot get
the code right on the "click"
Can you search through Pages?
Click to expand...


.
Click to expand...
 
Replace txtFindSurname with the name of your combo. Put square brackets
around the name it it contains a space.

If the Bound Column of the combo is a Number field, omit the extra quotes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Raj said:
Allen,
I don't mean to get in the middle of you'll
conversation. I have been looking for code like this for
over a month and its works great. I would like to modify
it a little I am trying to using this code with a combo
box. I placed the code in the AfterUpdate and I get is a
pop up asking for me to enter the project name. How can
I alter it code so that the choice from the combo box is
what it will use.
Thank you very much
Raj
-----Original Message-----
John, I think you want to place an unbound text box on the form (in Form
Header section perhaps?), along with a command button. User enters
something, and then you find the records that have that in a particular
field (such as surname).

If that's the idea, and their could be several possible matches, the best
idea might be to filter the form so it shows only the matches.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.txtFindSurname & """) AND "
End If

If Not IsNull(Me.txtFindFirstName) Then
strWhere = strWhere & "([FirstName] = """ & _
Me.txtFindFirstName & """) AND "
End If

'etc for other search boxes.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub


John said:
What is the procedure for setting up search capabilities
on a form? I have set up a command button but cannot get
the code right on the "click"
Can you search through Pages?
Click to expand...
Click to expand...
Click to expand...
 
Allen,
Work perfect!!
Thank you!!
-----Original Message-----
Replace txtFindSurname with the name of your combo. Put square brackets
around the name it it contains a space.

If the Bound Column of the combo is a Number field, omit the extra quotes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I don't mean to get in the middle of you'll
conversation. I have been looking for code like this for
over a month and its works great. I would like to modify
it a little I am trying to using this code with a combo
box. I placed the code in the AfterUpdate and I get is a
pop up asking for me to enter the project name. How can
I alter it code so that the choice from the combo box is
what it will use.
Thank you very much
Raj
-----Original Message-----
John, I think you want to place an unbound text box on the form (in Form
Header section perhaps?), along with a command button. User enters
something, and then you find the records that have
Click to expand...
that
in a particular
field (such as surname).

If that's the idea, and their could be several
Click to expand...
possible
matches, the best
idea might be to filter the form so it shows only the matches.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.txtFindSurname & """) AND "
End If

If Not IsNull(Me.txtFindFirstName) Then
strWhere = strWhere & "([FirstName] = """ & _
Me.txtFindFirstName & """) AND "
End If

'etc for other search boxes.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub


What is the procedure for setting up search capabilities
on a form? I have set up a command button but
Click to expand...
cannot
get
the code right on the "click"
Can you search through Pages?
Click to expand...
Click to expand...


.
Click to expand...
 
Allen,
I have another question for you. My database is used to
track circuits that have been research and it is sorted
by the project name. When new circuits are uploaded to
the database they do not have and project associated to
them. I have added New Circuits and All circuits to my
combo box now how can i modify the code to make it sort
by all null record and all records?
I REALLY appreciate all you help
Raj
-----Original Message-----
Allen,
Work perfect!!
Thank you!!
-----Original Message-----
Replace txtFindSurname with the name of your combo.
Click to expand...
Put
square brackets
around the name it it contains a space.

If the Bound Column of the combo is a Number field,
Click to expand...
omit
the extra quotes.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I don't mean to get in the middle of you'll
conversation. I have been looking for code like this for
over a month and its works great. I would like to modify
it a little I am trying to using this code with a combo
box. I placed the code in the AfterUpdate and I get is a
pop up asking for me to enter the project name. How can
I alter it code so that the choice from the combo box is
what it will use.
Thank you very much
Raj

-----Original Message-----
John, I think you want to place an unbound text box on
the form (in Form
Header section perhaps?), along with a command button.
User enters
something, and then you find the records that have that
in a particular
field (such as surname).

If that's the idea, and their could be several possible
matches, the best
idea might be to filter the form so it shows only the
matches.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.txtFindSurname & """) AND "
End If

If Not IsNull(Me.txtFindFirstName) Then
strWhere = strWhere & "([FirstName] = """ & _
Me.txtFindFirstName & """) AND "
End If

'etc for other search boxes.

lngLen = Len(strWhere) - 5 'Without trailing "
AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub


What is the procedure for setting up search
capabilities
on a form? I have set up a command button but cannot
get
the code right on the "click"
Can you search through Pages?
Click to expand...


.
Click to expand...
.
Click to expand...
 
You are trying to use the combo to sort the form? If so, you will be setting
the form's OrderBy property instead instead of its Filter. Null values
should sort to the top. If you want to sort them to the bottom of the form:
Me.OrderBy = "IsNull([MyField]) DESC, [MyField]"
Me.OrderBy = True

If you have added items to the combo's RowSource so it contains "{Blank
Records}", the curly braces will probably sort to the top. You will still
have to test for that case in your code, and then treat the combo as Null,
i.e.:
If Not (IsNull(Me.MyCombo) Or Me.MyCombo = "{Blank Records}" Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Raj said:
I have another question for you. My database is used to
track circuits that have been research and it is sorted
by the project name. When new circuits are uploaded to
the database they do not have and project associated to
them. I have added New Circuits and All circuits to my
combo box now how can i modify the code to make it sort
by all null record and all records?
I REALLY appreciate all you help
Raj
-----Original Message-----
Allen,
Work perfect!!
Thank you!!
-----Original Message-----
Replace txtFindSurname with the name of your combo.
Click to expand...
Put
square brackets
around the name it it contains a space.

If the Bound Column of the combo is a Number field,
Click to expand...
omit
the extra quotes.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I don't mean to get in the middle of you'll
conversation. I have been looking for code like this for
over a month and its works great. I would like to modify
it a little I am trying to using this code with a combo
box. I placed the code in the AfterUpdate and I get is a
pop up asking for me to enter the project name. How can
I alter it code so that the choice from the combo box is
what it will use.
Thank you very much
Raj

-----Original Message-----
John, I think you want to place an unbound text box on
the form (in Form
Header section perhaps?), along with a command button.
User enters
something, and then you find the records that have that
in a particular
field (such as surname).

If that's the idea, and their could be several possible
matches, the best
idea might be to filter the form so it shows only the
matches.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.txtFindSurname & """) AND "
End If

If Not IsNull(Me.txtFindFirstName) Then
strWhere = strWhere & "([FirstName] = """ & _
Me.txtFindFirstName & """) AND "
End If

'etc for other search boxes.

lngLen = Len(strWhere) - 5 'Without trailing "
AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub


What is the procedure for setting up search
capabilities
on a form? I have set up a command button but cannot
get
the code right on the "click"
Can you search through Pages?


.
Click to expand...
.
Click to expand...
Click to expand...
 
Allen,
I again would like to thank you for all you assistance,
but I have just one more problem that I need a help
with.
The database opens to the first record in the table and
when I select my option in the [projectname] it sort
correctly but the first record stays and the
[projectname] changes. How can I get the first record to
be the first of the sort data?
-----Original Message-----
You are trying to use the combo to sort the form? If so, you will be setting
the form's OrderBy property instead instead of its Filter. Null values
should sort to the top. If you want to sort them to the bottom of the form:
Me.OrderBy = "IsNull([MyField]) DESC, [MyField]"
Me.OrderBy = True

If you have added items to the combo's RowSource so it contains "{Blank
Records}", the curly braces will probably sort to the top. You will still
have to test for that case in your code, and then treat the combo as Null,
i.e.:
If Not (IsNull(Me.MyCombo) Or Me.MyCombo = "{Blank Records}" Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have another question for you. My database is used to
track circuits that have been research and it is sorted
by the project name. When new circuits are uploaded to
the database they do not have and project associated to
them. I have added New Circuits and All circuits to my
combo box now how can i modify the code to make it sort
by all null record and all records?
I REALLY appreciate all you help
Raj
-----Original Message-----
Allen,
Work perfect!!
Thank you!!
-----Original Message-----
Replace txtFindSurname with the name of your combo. Put
square brackets
around the name it it contains a space.

If the Bound Column of the combo is a Number field, omit
the extra quotes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
Allen,
I don't mean to get in the middle of you'll
conversation. I have been looking for code like this
for
over a month and its works great. I would like to
modify
it a little I am trying to using this code with a combo
box. I placed the code in the AfterUpdate and I get
is a
pop up asking for me to enter the project name. How
can
I alter it code so that the choice from the combo box
is
what it will use.
Thank you very much
Raj

-----Original Message-----
John, I think you want to place an unbound text
Click to expand...
box
on
the form (in Form
Header section perhaps?), along with a command button.
User enters
something, and then you find the records that have
that
in a particular
field (such as surname).

If that's the idea, and their could be several
possible
matches, the best
idea might be to filter the form so it shows only the
matches.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.txtFindSurname & """) AND "
End If

If Not IsNull(Me.txtFindFirstName) Then
strWhere = strWhere & "([FirstName] = """ & _
Me.txtFindFirstName & """) AND "
End If

'etc for other search boxes.

lngLen = Len(strWhere) - 5 'Without trailing "
AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub


What is the procedure for setting up search
capabilities
on a form? I have set up a command button but
cannot
get
the code right on the "click"
Can you search through Pages?


.

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


.
Click to expand...
 
Not sure what is happening here.

Either you are altering the value in a control so it is different than it
was before, or else some controls are unbound so they do not change when you
move record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
j said:
Allen,
I again would like to thank you for all you assistance,
but I have just one more problem that I need a help
with.
The database opens to the first record in the table and
when I select my option in the [projectname] it sort
correctly but the first record stays and the
[projectname] changes. How can I get the first record to
be the first of the sort data?
-----Original Message-----
You are trying to use the combo to sort the form? If so, you will be setting
the form's OrderBy property instead instead of its Filter. Null values
should sort to the top. If you want to sort them to the bottom of the form:
Me.OrderBy = "IsNull([MyField]) DESC, [MyField]"
Me.OrderBy = True

If you have added items to the combo's RowSource so it contains "{Blank
Records}", the curly braces will probably sort to the top. You will still
have to test for that case in your code, and then treat the combo as Null,
i.e.:
If Not (IsNull(Me.MyCombo) Or Me.MyCombo = "{Blank Records}" Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have another question for you. My database is used to
track circuits that have been research and it is sorted
by the project name. When new circuits are uploaded to
the database they do not have and project associated to
them. I have added New Circuits and All circuits to my
combo box now how can i modify the code to make it sort
by all null record and all records?
I REALLY appreciate all you help
Raj

-----Original Message-----
Allen,
Work perfect!!
Thank you!!
-----Original Message-----
Replace txtFindSurname with the name of your combo.
Put
square brackets
around the name it it contains a space.

If the Bound Column of the combo is a Number field,
omit
the extra quotes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
Allen,
I don't mean to get in the middle of you'll
conversation. I have been looking for code like this
for
over a month and its works great. I would like to
modify
it a little I am trying to using this code with a
combo
box. I placed the code in the AfterUpdate and I get
is a
pop up asking for me to enter the project name. How
can
I alter it code so that the choice from the combo box
is
what it will use.
Thank you very much
Raj

-----Original Message-----
John, I think you want to place an unbound text box
on
the form (in Form
Header section perhaps?), along with a command
button.
User enters
something, and then you find the records that have
that
in a particular
field (such as surname).

If that's the idea, and their could be several
possible
matches, the best
idea might be to filter the form so it shows only the
matches.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.txtFindSurname & """) AND "
End If

If Not IsNull(Me.txtFindFirstName) Then
strWhere = strWhere & "([FirstName] = """ & _
Me.txtFindFirstName & """) AND "
End If

'etc for other search boxes.

lngLen = Len(strWhere) - 5 'Without trailing "
AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub


What is the procedure for setting up search
capabilities
on a form? I have set up a command button but
cannot
get
the code right on the "click"
Can you search through Pages?


.

.
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks again for all the help..
-----Original Message-----
Not sure what is happening here.

Either you are altering the value in a control so it is different than it
was before, or else some controls are unbound so they do not change when you
move record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Allen,
I again would like to thank you for all you assistance,
but I have just one more problem that I need a help
with.
The database opens to the first record in the table and
when I select my option in the [projectname] it sort
correctly but the first record stays and the
[projectname] changes. How can I get the first record to
be the first of the sort data?
-----Original Message-----
You are trying to use the combo to sort the form? If
Click to expand...
so,
you will be setting
the form's OrderBy property instead instead of its Filter. Null values
should sort to the top. If you want to sort them to
Click to expand...
the
bottom of the form:
Me.OrderBy = "IsNull([MyField]) DESC, [MyField]"
Me.OrderBy = True

If you have added items to the combo's RowSource so it contains "{Blank
Records}", the curly braces will probably sort to the top. You will still
have to test for that case in your code, and then
Click to expand...
treat
the combo as Null,
i.e.:
If Not (IsNull(Me.MyCombo) Or Me.MyCombo = "{Blank Records}" Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


I have another question for you. My database is
Click to expand...
used
to
track circuits that have been research and it is sorted
by the project name. When new circuits are uploaded to
the database they do not have and project associated to
them. I have added New Circuits and All circuits to my
combo box now how can i modify the code to make it sort
by all null record and all records?
I REALLY appreciate all you help
Raj

-----Original Message-----
Allen,
Work perfect!!
Thank you!!
-----Original Message-----
Replace txtFindSurname with the name of your combo.
Put
square brackets
around the name it it contains a space.

If the Bound Column of the combo is a Number field,
omit
the extra quotes.
Click to expand...
dot
org.
message
Allen,
I don't mean to get in the middle of you'll
conversation. I have been looking for code like this
for
over a month and its works great. I would like to
modify
it a little I am trying to using this code with a
combo
box. I placed the code in the AfterUpdate and I get
is a
pop up asking for me to enter the project name. How
can
I alter it code so that the choice from the
Click to expand...
combo
box
is
what it will use.
Thank you very much
Raj

-----Original Message-----
John, I think you want to place an unbound text box
on
the form (in Form
Header section perhaps?), along with a command
button.
User enters
something, and then you find the records that have
that
in a particular
field (such as surname).

If that's the idea, and their could be several
possible
matches, the best
idea might be to filter the form so it shows
Click to expand...
only
the
matches.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname]
Click to expand...
= """ &
_
Me.txtFindSurname & """) AND "
End If

If Not IsNull(Me.txtFindFirstName) Then
strWhere = strWhere & "([FirstName]
Click to expand...
= """
& _
Me.txtFindFirstName & """) AND "
End If

'etc for other search boxes.

lngLen = Len(strWhere) - 5 'Without trailing "
AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub


What is the procedure for setting up search
capabilities
on a form? I have set up a command button but
cannot
get
the code right on the "click"
Can you search through Pages?


.

.



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


.
Click to expand...
 
Back
Top