cboBox values

  • Thread starter Thread starter MBison80
  • Start date Start date
M

MBison80

I have a form based on one tbl and I have created a cboBox that wil
pull up a record on the afetr 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....
 
My interpretation of below is you want a unique list of Model numbers to
select from, then once selected, you want to navigate through the invoices
against that model number.

Firstly, is it possible to maintain a table of unique model numbers?, you
can base your cboBox on that.

Otherwise, you could revert to using a query that groups the data by model
number. See "Totals" in query design.

Try this in your cboBox ...

SELECT DISTINCT tblMainInv.model, Last(tblMainInv.invnumb) as LastInv
FROM tblMainInv
GROUP BY tblMainInv.model;

Hope this helps
Regards,
Kevin Rollo
Perth, Western Australia
 
Back
Top