Combo Box Search Function - Sort Ascending

  • Thread starter Thread starter weircolin
  • Start date Start date
W

weircolin

Hi There

I have a form where I am using a combo box to search for entries on a
table. It works fine apart from a few things.

1. If I was doing a search for the name "Smith" it would list the
first 8 (I have set it to list 8) people with the name Smith, but if I
select the third "Smith" on the list, it automaticaly shows the first
entry under Smith.

2. The above is no longer doing that as for some reason the names
aren't sorting in ascending order anymore.

Does anyone have a solution to these?

Thanks

Colin
 
What are these properties of the combo box:
Row Source
Control Source
Column Count
Bound Column
 
Hi

Row Source - SELECT [Newsletter Database].* FROM [Newsletter Database];
Control Source - Blank
Column Count - 2
Bound Column - 1
 
Don't select "*" in a Row Source. Select your field names and specify and
ORDER BY like:

SELECT FieldA, FieldB
FROM [Newsletter Database]
ORDER BY FieldA;

--
Duane Hookom
MS Access MVP

Hi

Row Source - SELECT [Newsletter Database].* FROM [Newsletter Database];
Control Source - Blank
Column Count - 2
Bound Column - 1
Click to expand...
 
Hi

Thanks, that has solved that problem. It is still at the moment only
displaying the first entry of that name. When I click on the "Next
Record" button it doesn't take it to the next person with the name of
Smith.

Hope this makes sense, not sure how else to explain it.

Thanks

Colin
Duane said:
Don't select "*" in a Row Source. Select your field names and specify and
ORDER BY like:

SELECT FieldA, FieldB
FROM [Newsletter Database]
ORDER BY FieldA;

--
Duane Hookom
MS Access MVP

Hi

Row Source - SELECT [Newsletter Database].* FROM [Newsletter Database];
Control Source - Blank
Column Count - 2
Bound Column - 1

Duane said:
What are these properties of the combo box:
Row Source
Control Source
Column Count
Bound Column

--
Duane Hookom
MS Access MVP

Hi There

I have a form where I am using a combo box to search for entries on a
table. It works fine apart from a few things.

1. If I was doing a search for the name "Smith" it would list the
first 8 (I have set it to list 8) people with the name Smith, but if I
select the third "Smith" on the list, it automaticaly shows the first
entry under Smith.

2. The above is no longer doing that as for some reason the names
aren't sorting in ascending order anymore.

Does anyone have a solution to these?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
We don't know anything about the record source of your form or how it
depends on your combo box or the new properties of your combo box.

--
Duane Hookom
MS Access MVP

Hi

Thanks, that has solved that problem. It is still at the moment only
displaying the first entry of that name. When I click on the "Next
Record" button it doesn't take it to the next person with the name of
Smith.

Hope this makes sense, not sure how else to explain it.

Thanks

Colin
Duane said:
Don't select "*" in a Row Source. Select your field names and specify and
ORDER BY like:

SELECT FieldA, FieldB
FROM [Newsletter Database]
ORDER BY FieldA;

--
Duane Hookom
MS Access MVP

Hi

Row Source - SELECT [Newsletter Database].* FROM [Newsletter Database];
Control Source - Blank
Column Count - 2
Bound Column - 1

Duane Hookom wrote:
What are these properties of the combo box:
Row Source
Control Source
Column Count
Bound Column

--
Duane Hookom
MS Access MVP

Hi There

I have a form where I am using a combo box to search for entries on
a
table. It works fine apart from a few things.

1. If I was doing a search for the name "Smith" it would list the
first 8 (I have set it to list 8) people with the name Smith, but if
I
select the third "Smith" on the list, it automaticaly shows the
first
entry under Smith.

2. The above is no longer doing that as for some reason the names
aren't sorting in ascending order anymore.

Does anyone have a solution to these?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
Ah, ok

Do you know if there's anyway I could could link a "Find Next" button
to the combo box I am using for the surname search?

This is the code I am using for the search:

Private Sub cboMoveTo_AfterUpdate()


If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Surname] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Thanks


Colin
Duane said:
We don't know anything about the record source of your form or how it
depends on your combo box or the new properties of your combo box.

--
Duane Hookom
MS Access MVP

Hi

Thanks, that has solved that problem. It is still at the moment only
displaying the first entry of that name. When I click on the "Next
Record" button it doesn't take it to the next person with the name of
Smith.

Hope this makes sense, not sure how else to explain it.

Thanks

Colin
Duane said:
Don't select "*" in a Row Source. Select your field names and specify and
ORDER BY like:

SELECT FieldA, FieldB
FROM [Newsletter Database]
ORDER BY FieldA;

--
Duane Hookom
MS Access MVP

Hi

Row Source - SELECT [Newsletter Database].* FROM [Newsletter Database];
Control Source - Blank
Column Count - 2
Bound Column - 1

Duane Hookom wrote:
What are these properties of the combo box:
Row Source
Control Source
Column Count
Bound Column

--
Duane Hookom
MS Access MVP

Hi There

I have a form where I am using a combo box to search for entries on
a
table. It works fine apart from a few things.

1. If I was doing a search for the name "Smith" it would list the
first 8 (I have set it to list 8) people with the name Smith, but if
I
select the third "Smith" on the list, it automaticaly shows the
first
entry under Smith.

2. The above is no longer doing that as for some reason the names
aren't sorting in ascending order anymore.

Does anyone have a solution to these?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
Consider using the results of the combo box to filter the form:
Me.Filter = "Surname= """ & Me.cboMoveTo & """"
Me.FilterOn = True


--
Duane Hookom
MS Access MVP


Ah, ok

Do you know if there's anyway I could could link a "Find Next" button
to the combo box I am using for the surname search?

This is the code I am using for the search:

Private Sub cboMoveTo_AfterUpdate()


If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Surname] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Thanks


Colin
Duane said:
We don't know anything about the record source of your form or how it
depends on your combo box or the new properties of your combo box.

--
Duane Hookom
MS Access MVP

Hi

Thanks, that has solved that problem. It is still at the moment only
displaying the first entry of that name. When I click on the "Next
Record" button it doesn't take it to the next person with the name of
Smith.

Hope this makes sense, not sure how else to explain it.

Thanks

Colin
Duane Hookom wrote:
Don't select "*" in a Row Source. Select your field names and specify
and
ORDER BY like:

SELECT FieldA, FieldB
FROM [Newsletter Database]
ORDER BY FieldA;

--
Duane Hookom
MS Access MVP

Hi

Row Source - SELECT [Newsletter Database].* FROM [Newsletter
Database];
Control Source - Blank
Column Count - 2
Bound Column - 1

Duane Hookom wrote:
What are these properties of the combo box:
Row Source
Control Source
Column Count
Bound Column

--
Duane Hookom
MS Access MVP

Hi There

I have a form where I am using a combo box to search for entries
on
a
table. It works fine apart from a few things.

1. If I was doing a search for the name "Smith" it would list
the
first 8 (I have set it to list 8) people with the name Smith, but
if
I
select the third "Smith" on the list, it automaticaly shows the
first
entry under Smith.

2. The above is no longer doing that as for some reason the names
aren't sorting in ascending order anymore.

Does anyone have a solution to these?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
Excuse my ignorance, would I put that in the "AfterUpdate" of a find
next button?
Duane said:
Consider using the results of the combo box to filter the form:
Me.Filter = "Surname= """ & Me.cboMoveTo & """"
Me.FilterOn = True


--
Duane Hookom
MS Access MVP


Ah, ok

Do you know if there's anyway I could could link a "Find Next" button
to the combo box I am using for the surname search?

This is the code I am using for the search:

Private Sub cboMoveTo_AfterUpdate()


If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Surname] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Thanks


Colin
Duane said:
We don't know anything about the record source of your form or how it
depends on your combo box or the new properties of your combo box.

--
Duane Hookom
MS Access MVP

Hi

Thanks, that has solved that problem. It is still at the moment only
displaying the first entry of that name. When I click on the "Next
Record" button it doesn't take it to the next person with the name of
Smith.

Hope this makes sense, not sure how else to explain it.

Thanks

Colin
Duane Hookom wrote:
Don't select "*" in a Row Source. Select your field names and specify
and
ORDER BY like:

SELECT FieldA, FieldB
FROM [Newsletter Database]
ORDER BY FieldA;

--
Duane Hookom
MS Access MVP

Hi

Row Source - SELECT [Newsletter Database].* FROM [Newsletter
Database];
Control Source - Blank
Column Count - 2
Bound Column - 1

Duane Hookom wrote:
What are these properties of the combo box:
Row Source
Control Source
Column Count
Bound Column

--
Duane Hookom
MS Access MVP

Hi There

I have a form where I am using a combo box to search for entries
on
a
table. It works fine apart from a few things.

1. If I was doing a search for the name "Smith" it would list
the
first 8 (I have set it to list 8) people with the name Smith, but
if
I
select the third "Smith" on the list, it automaticaly shows the
first
entry under Smith.

2. The above is no longer doing that as for some reason the names
aren't sorting in ascending order anymore.

Does anyone have a solution to these?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
The code I suggested could replace the code in the after update event of
cboMoveTo.

--
Duane Hookom
MS Access MVP

Excuse my ignorance, would I put that in the "AfterUpdate" of a find
next button?
Duane said:
Consider using the results of the combo box to filter the form:
Me.Filter = "Surname= """ & Me.cboMoveTo & """"
Me.FilterOn = True


--
Duane Hookom
MS Access MVP


Ah, ok

Do you know if there's anyway I could could link a "Find Next" button
to the combo box I am using for the surname search?

This is the code I am using for the search:

Private Sub cboMoveTo_AfterUpdate()


If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Surname] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Thanks


Colin
Duane Hookom wrote:
We don't know anything about the record source of your form or how it
depends on your combo box or the new properties of your combo box.

--
Duane Hookom
MS Access MVP

Hi

Thanks, that has solved that problem. It is still at the moment
only
displaying the first entry of that name. When I click on the "Next
Record" button it doesn't take it to the next person with the name
of
Smith.

Hope this makes sense, not sure how else to explain it.

Thanks

Colin
Duane Hookom wrote:
Don't select "*" in a Row Source. Select your field names and
specify
and
ORDER BY like:

SELECT FieldA, FieldB
FROM [Newsletter Database]
ORDER BY FieldA;

--
Duane Hookom
MS Access MVP

Hi

Row Source - SELECT [Newsletter Database].* FROM [Newsletter
Database];
Control Source - Blank
Column Count - 2
Bound Column - 1

Duane Hookom wrote:
What are these properties of the combo box:
Row Source
Control Source
Column Count
Bound Column

--
Duane Hookom
MS Access MVP

Hi There

I have a form where I am using a combo box to search for
entries
on
a
table. It works fine apart from a few things.

1. If I was doing a search for the name "Smith" it would list
the
first 8 (I have set it to list 8) people with the name Smith,
but
if
I
select the third "Smith" on the list, it automaticaly shows
the
first
entry under Smith.

2. The above is no longer doing that as for some reason the
names
aren't sorting in ascending order anymore.

Does anyone have a solution to these?

Thanks

Colin
Click to expand...
Click to expand...
Click to expand...
 
Duane

Worked great, thank you very much - really appreciate it!

It still only goes to the first person when selecting a name from a
list but at least I can navigate through them now.

Thanks again

Colin
Duane said:
The code I suggested could replace the code in the after update event of
cboMoveTo.

--
Duane Hookom
MS Access MVP

Excuse my ignorance, would I put that in the "AfterUpdate" of a find
next button?
Duane said:
Consider using the results of the combo box to filter the form:
Me.Filter = "Surname= """ & Me.cboMoveTo & """"
Me.FilterOn = True


--
Duane Hookom
MS Access MVP


Ah, ok

Do you know if there's anyway I could could link a "Find Next" button
to the combo box I am using for the surname search?

This is the code I am using for the search:

Private Sub cboMoveTo_AfterUpdate()


If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[Surname] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

Thanks


Colin
Duane Hookom wrote:
We don't know anything about the record source of your form or how it
depends on your combo box or the new properties of your combo box.

--
Duane Hookom
MS Access MVP

Hi

Thanks, that has solved that problem. It is still at the moment
only
displaying the first entry of that name. When I click on the "Next
Record" button it doesn't take it to the next person with the name
of
Smith.

Hope this makes sense, not sure how else to explain it.

Thanks

Colin
Duane Hookom wrote:
Don't select "*" in a Row Source. Select your field names and
specify
and
ORDER BY like:

SELECT FieldA, FieldB
FROM [Newsletter Database]
ORDER BY FieldA;

--
Duane Hookom
MS Access MVP

Hi

Row Source - SELECT [Newsletter Database].* FROM [Newsletter
Database];
Control Source - Blank
Column Count - 2
Bound Column - 1

Duane Hookom wrote:
What are these properties of the combo box:
Row Source
Control Source
Column Count
Bound Column

--
Duane Hookom
MS Access MVP

Hi There

I have a form where I am using a combo box to search for
entries
on
a
table. It works fine apart from a few things.

1. If I was doing a search for the name "Smith" it would list
the
first 8 (I have set it to list 8) people with the name Smith,
but
if
I
select the third "Smith" on the list, it automaticaly shows
the
first
entry under Smith.

2. The above is no longer doing that as for some reason the
names
aren't sorting in ascending order anymore.

Does anyone have a solution to these?

Thanks

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Windows 10 How to sort an index created in Word 2013 0
Excel Comparing two columns 1
Code for "OK" in Dialog Box to lead to selected records in Form Vi 1
Combo Box Debug error 4
Retain combo box order 1
combo box 1
Find Next 2
Sort by ascending on event 1

Back
Top