Combo box distinct values

[scheeps]

I've got a form with a combo box in the header and a subform in the detail section.

The combo box selection criteria (Maingroup Type Description) reads from the same table the subform is dependant on and basically just need to display the distinct Maingroup Type Descriptions from the source table.

1) Is it possible to only have a description (no code/key) in a combo box and filter on the subform based on the description selected?

2) Can I just add a distinct to my combo's query? Or a group by?

In both cases my combo box return a blank drop down.

I've got my Link Master/Child Fields set as Maingroup_Type_Desc.

Is there a trick to this? What am I doing wrong?

 

[vbaInet]

1) Is it possible to only have a description (no code/key) in a combo box and filter on the subform based on the description selected?

The no key/code part defeats the purpose of a database. Naturally you use a key to lookup the corresponding value from another table. You should include the key and filter on that key.

2) Can I just add a distinct to my combo's query? Or a group by?

Put the DISTINCT clause in the SQL statement of the combo box's Row Source property. Only Show the key and Description fields:

http://www.techonthenet.com/sql/distinct.php

 

[scheeps]

The no key/code part defeats the purpose of a database. Naturally you use a key to lookup the corresponding value from another table. You should include the key and filter on that key.

Put the DISTINCT clause in the SQL statement of the combo box's Row Source property. Only Show the key and Description fields:

Thanks vbaInet, I hear what you are saying about no key/code and the purpose of it in a database.
But in my case I would only like to use the Maingroup Type Description as a filter for the subform - nothing is going to be written back to the database at this stage.

I know, it is bizarre, but if I add a distinct to my Row Source, the combo just return a blank drop down.

 

[vbaInet]

It's nothing to do with it updating a field or not. To get the best match you use the ID of the table because it is the ID that makes the record unique. There's the benefit of indexes as well. There's never a legitimate reason where you would not use the ID especially when dealing with subforms. I suspect you're needing to do this because your subform doesn't contain the ID. If this is the case then there's a problem with your table structure.

Let's see the SQL statement of your query. Is the combo box bound?

 

[scheeps]

Here is the SQL:

SELECT DISTINCT Maingroup_Type_Code, Maingroup_Type_Desc FROM ODS.Energy_Type

And ODS.Energy_Type table looks as follow:

Energy_Type_ID,Energy_Type_Name,Maingroup_Type_Code,Maingroup_Type_Desc
1,Other Bituminous,CL,Coal
2,Coking,CL,Coal
3,Sub-Bituminous,CL,Coal
4,Crude Oil,OL,Oil
5,Kapuni Condensate,OL,Oil

So the Combo needs to display Coal and Oil. If I select Coal, records 1-3 need to display and if I select Oil, records 4-5 need to be displayed in the subform.

I've changed the Link Master and Child fields to Maingroup_Type_Code. I got the combo box to display the Coal and Oil but get a Type Mismatch on:

rs.Find "[Maingroup_Type_Code] = " & Str(Nz(Me![cboMainGroup], 0))

There is probably a simple answer to this...just can't seem to find it.

 

[vbaInet]

Show the full code and I will amend it to suit.

 

[scheeps]

Thanks vbaInet, I appreciate it.

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

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

 

[vbaInet]

There you go:

Private Sub cboMainGroup_AfterUpdate()
' Find the record that matches the control.
    Dim rs As DAO.Recordset
    
    Set rs = Me.RecordsetClone
    
    rs.FindFirst "[Maingroup_Type_Code] = '" & Nz(Me![cboMainGroup], "0") & "'"
    
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    rs.Close
    Set rs = Nothing
End Sub

 

[scheeps]

Thanks vbaInet, but I get a "Type mismatch" error on:

Set rs = Me.RecordsetClone

Why would this be?

 

[scheeps]

I saw I did not have the Microsoft DAO 3.6 Object Library selected.

If I want to do so, I get a "Error in loading DLL" error.

I can't seem to win here

 

[scheeps]

vbaInet, maybe I should mention that this is a ADP project. Will that make any difference?

Because what I've done now is creating a clean Access db with the necessary tables and forms in (unlinked tables, no ADP) and it works!

Any idea if ADP will interfere with the logic/functionality of normal Access?

 

[vbaInet]

I don't work with ADPs so I can't advise there I'm afraid. But I would imagine it won't interfer, however, things are different in ADPs.

 

[scheeps]

Got it!

Private Sub cboMainGroup_AfterUpdate()
' Find the record that matches the control.
    Dim rs As Object
    
    Set rs = Me.RecordsetClone
    
    rs.Find "[Maingroup_Type_Code] = '" & Nz(Me![cboMainGroup], "0") & "'"
    
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    rs.Close
    Set rs = Nothing
End Sub

I just needed to dim rs as Object instead of DAO.Recordset.

For some reason DAO does not work 100% with ADP and seeing that ADO does not have a RecordsetClone method the best way is to specify rs as an Object - probably not the best way of doing it, but it works.

 

[vbaInet]

That should be fine - letting the compiler decide.