Combo Box Duplicates

[ArmySGT via AccessMonster.com]

I have a combo box on a form designed to be used to search for records within
the database.

I am having difficulties with it, it is not showing duplicates for some
reason.

Combo box search is ID Last Name First Name and is unbound.

Form contains the above in seperate boxes along with other information.

Also, does anyone know how to sync the records so that when the records
change within the form the data also changes within the combo box and visa
versa.

Also is there a way to put it in alphabetical order?

Also is there a way to format the box so that the two colloumns show as one?

IE instead of

Superman John
Superman, John

Thanks!!!!!!

 

[Klatuu]

You can combine the name fields for your combo like this:
SELECT ID, LastName & ", " & FirstName as FullName FROM SomeTable ORDER BY
LastName, FirstName;

As to not showing duplicates, I don't understand what you are saying, but if
there is the word DISTINCT in your combo's row source query, that will cause
it.

As to syncing the display of the combo with the records, there are two
places where you need to put code. First, in the form's Current event to
make the combo show the record in the form:

Me.MyCombo = Me.[ID]

Now to choose a name from the combo and make that the current record, you
use the combo's After Update event:

With Me.RecordsetClone
.FindFirst "[ID] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

 

[Arvin Meyer [MVP]]

1. The SQL statement in the combo box may be saying:

"Select Distinct ..."

Remove the word "Distinct"

2. A Search combo syncs the form, but if you're wanting to sync the combo,
you'll need something like this in the form's Current event:

Private Sub Form_Current()
Me.cboSearch = Me.txtID
End Sub

3. Add an Order By clause to sort it, like:

Order By [Last Name];

4. To use a fullname, use a SQL statement like:

Select ID, [Last Name] & (", " + [First Name) From tblMyTable Order By
[Last Name];

 

[ArmySGT via AccessMonster.com]

It is this thread Klatuu

You can combine the name fields for your combo like this:
SELECT ID, LastName & ", " & FirstName as FullName FROM SomeTable ORDER BY
LastName, FirstName;

As to not showing duplicates, I don't understand what you are saying, but if
there is the word DISTINCT in your combo's row source query, that will cause
it.

As to syncing the display of the combo with the records, there are two
places where you need to put code. First, in the form's Current event to
make the combo show the record in the form:

Me.MyCombo = Me.[ID]

Now to choose a name from the combo and make that the current record, you
use the combo's After Update event:

With Me.RecordsetClone
.FindFirst "[ID] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

I have a combo box on a form designed to be used to search for records within
the database.

[quoted text clipped - 20 lines]

Thanks!!!!!!

 

[ArmySGT via AccessMonster.com]

Thanks for all the help,

Where does these extra bits of code go in at in relation to the exsisting
code? Sorry I know nothing about code....

Thanks again to everyone!!!!

1. The SQL statement in the combo box may be saying:

"Select Distinct ..."

Remove the word "Distinct"

2. A Search combo syncs the form, but if you're wanting to sync the combo,
you'll need something like this in the form's Current event:

Private Sub Form_Current()
Me.cboSearch = Me.txtID
End Sub

3. Add an Order By clause to sort it, like:

Order By [Last Name];

4. To use a fullname, use a SQL statement like:

Select ID, [Last Name] & (", " + [First Name) From tblMyTable Order By
[Last Name];

I have a combo box on a form designed to be used to search for records
within

[quoted text clipped - 22 lines]

Thanks!!!!!!

 

[Arvin Meyer [MVP]]

If the problem is the Distinct keyword in the rowsource property of the
combo.

The code goes in an [Event Procedure] in the form's Current event.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks for all the help,

Where does these extra bits of code go in at in relation to the exsisting
code? Sorry I know nothing about code....

Thanks again to everyone!!!!

1. The SQL statement in the combo box may be saying:

"Select Distinct ..."

Remove the word "Distinct"

2. A Search combo syncs the form, but if you're wanting to sync the combo,
you'll need something like this in the form's Current event:

Private Sub Form_Current()
Me.cboSearch = Me.txtID
End Sub

3. Add an Order By clause to sort it, like:

Order By [Last Name];

4. To use a fullname, use a SQL statement like:

Select ID, [Last Name] & (", " + [First Name) From tblMyTable Order By
[Last Name];

I have a combo box on a form designed to be used to search for records
within

[quoted text clipped - 22 lines]

Thanks!!!!!!