DISTINCT ROW help needed

[magmike]

I have a code in the OnCurrent of a form that inserts the ID of the
company for that record into a table. The purpose of that table
(LastVisitedRecord) is a history of sorts. On that form, I have a
combo box that uses the following sql as row source -

SELECT DISTINCTROW ProspectTable.Company
FROM ProspectTable INNER JOIN LastVisitedRecord ON ProspectTable.ID =
LastVisitedRecord.lvCompanyID
ORDER BY LastVisitedRecord.lvID DESC;

The problem is, if i visit company Acme, Inc. a second time, it still
shows the first instance (at the bottom of the list) and I want it to
show the last instance as the distinct row. How can I do this?

magmike

 

[Jerry Whittle]

Try this:

SELECT DISTINCT ProspectTable.Company

 

[magmike]

Try this:

SELECT DISTINCT ProspectTable.Company
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.








- Show quoted text -

Did you mean ONLY that line, or just to change what I currently have,
ONLY deleting ROW off the end of DISTINCT?

When I do the latter, and then click on the arrow attached to the
combo box, I get the following error:

ORDER BY clause (LastRecordVisited.lvID) conflicts
with DISTINCT

It may also be important to know that I have the following code in the
AfterUpdate event.

Private Sub Combo392_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo392], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Finally, I notice that my pasting left out some of the SQL statement,
which should show as follows:

SELECT DISTINCTROW ProspectTable.Company, ProspectTable.ID
FROM ProspectTable INNER JOIN LastVisitedRecord ON ProspectTable.ID =
LastVisitedRecord.lvCompanyID
ORDER BY LastVisitedRecord.lvID DESC;


Does that change things?

 

[Jerry Whittle]

The missing bit of the SQL statement makes a little difference; however, I
don't think it's the main problem. You may need to requery or refresh the
data in the form or combo box as part of the code.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Try this:

SELECT DISTINCT ProspectTable.Company
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.








- Show quoted text -

Did you mean ONLY that line, or just to change what I currently have,
ONLY deleting ROW off the end of DISTINCT?

When I do the latter, and then click on the arrow attached to the
combo box, I get the following error:

ORDER BY clause (LastRecordVisited.lvID) conflicts
with DISTINCT

It may also be important to know that I have the following code in the
AfterUpdate event.

Private Sub Combo392_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo392], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Finally, I notice that my pasting left out some of the SQL statement,
which should show as follows:

SELECT DISTINCTROW ProspectTable.Company, ProspectTable.ID
FROM ProspectTable INNER JOIN LastVisitedRecord ON ProspectTable.ID =
LastVisitedRecord.lvCompanyID
ORDER BY LastVisitedRecord.lvID DESC;


Does that change things?