cboBox values
I have a form based on one tbl and I have created a cboBox that wil
pull up a record on the after update event of the cboBox.
It works fine but i'd like it to display unique values since there ar
duplicate values in this field.
I tried unique records/unique values but this does not work since th
qry uses two fields and the key field has unique values.
Here is the code for the after update event.

Private Sub cboModel_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.findfirst "[invnumb] = " & Str(Nz(Me![cboModel], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

And the SQL for the row source is as follows.

SELECT DISTINCT tblMainInv.invnumb, tblMainInv.model
FROM tblMainInv
ORDER BY tblMainInv.model;

Can anyone offer any advice. I realise that once this display just th
one model the results
will display the first item, with the rest being accessed by the na
buttons.

Thanks in advance,
Craig....

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Hi,


Use, for query:



SELECT invnumb , MAX(model) As maxOfModel
FROM tblMainInv
GROUP BY invnumb
ORDER BY maxOfModel


Sure, using MAX means you won't see the other model associated to a
invnumb... but I assume you are already aware of that.



Hoping it may help,
Vanderghast, Access MVP




"MBison80" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> cboBox values
> I have a form based on one tbl and I have created a cboBox that will
> pull up a record on the after update event of the cboBox.
> It works fine but i'd like it to display unique values since there are
> duplicate values in this field.
> I tried unique records/unique values but this does not work since the
> qry uses two fields and the key field has unique values.
> Here is the code for the after update event.
>
> Private Sub cboModel_AfterUpdate()
>
> ' Find the record that matches the control.
> Dim rs As Object
>
> Set rs = Me.Recordset.Clone
> rs.findfirst "[invnumb] = " & Str(Nz(Me![cboModel], 0))
> If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>
> End Sub
>
> And the SQL for the row source is as follows.
>
> SELECT DISTINCT tblMainInv.invnumb, tblMainInv.model
> FROM tblMainInv
> ORDER BY tblMainInv.model;
>
> Can anyone offer any advice. I realise that once this display just the
> one model the results
> will display the first item, with the rest being accessed by the nav
> buttons.
>
> Thanks in advance,
> Craig.....
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>