Combo Box Lists

[gryffyn]

First off - I am not very technical when it comes to this (well, or any)program, so explain it as you would to a child <vbg>>

Currently I have three tables with accompanying forms for 2 of them - they are tbl_breeds, tbl_clubs and tbl_rescues

They have a field in common - "breed" (no quotes)

I am using a combo box in the forms (for the club and rescue tables) for the "breed" value. How do I set it up so that the combo box (record source would be "tbl_breeds") is automatically populated with those form entries which are not already included in the breeds table?

I am currently using Access 2003.

I had an old database someone had set up for me that did something similar but I cannot duplicate it. There were several references to "SELECT DISTINCTROW"

Hope this makes sense and someone can help me out!

Thanks in advance,

RLynn

[MMcCarthy]

First off - I am not very technical when it comes to this (well, or any)program, so explain it as you would to a child <vbg>>

Currently I have three tables with accompanying forms for 2 of them - they are tbl_breeds, tbl_clubs and tbl_rescues

They have a field in common - "breed" (no quotes)

I am using a combo box in the forms (for the club and rescue tables) for the "breed" value. How do I set it up so that the combo box (record source would be "tbl_breeds") is automatically populated with those form entries which are not already included in the breeds table?

I am currently using Access 2003.

I had an old database someone had set up for me that did something similar but I cannot duplicate it. There were several references to "SELECT DISTINCTROW"

Hope this makes sense and someone can help me out!

Thanks in advance,

RLynn

Try this ...

SELECT Q.Breed
FROM  (SELECT Breed FROM tbl_clubs UNION
SELECT Breed tbl_Rescues GROUP BY Breed) As Q
RIGHT JOIN tbl_breeds As B
ON Q.Breed = B.Breed
WHERE B.Breed Is Null;
 

[gryffyn]

Thanks - where/exactly how do I enter the code??

[MMcCarthy]

Thanks - where/exactly how do I enter the code??

In the properties of the combobox put this in the Row Source property

[gryffyn]

I added that code to the Row Source property of the Clubs form (which is for the tbl_clubs)

The Combo box is not not populated with anything (completely blank when I click on the arrow instead of a list corresponding to the breed field in tbl_breeds), and does not add new entries to the tbl_breeds

:(

[MMcCarthy]

I added that code to the Row Source property of the Clubs form (which is for the tbl_clubs)

The Combo box is not not populated with anything (completely blank when I click on the arrow instead of a list corresponding to the breed field in tbl_breeds), and does not add new entries to the tbl_breeds

:(

Not the Record Source of the Form but the Row Source of the combobox.

[gryffyn]

Not the Record Source of the Form but the Row Source of the combobox.

Yes - that's where I put it. The Row Source of the combo box (for the breed field) on the Clubs form.

[MMcCarthy]

Yes - that's where I put it. The Row Source of the combo box (for the breed field) on the Clubs form.

Check the column widths property. What is that set to?

[gryffyn]

column widths for that is 1"

[MMcCarthy]

column widths for that is 1"

Sorry just rethought the logic. You might have to change RIGHT JOIN to LEFT JOIN as follows:

SELECT Q.Breed FROM (SELECT Breed FROM tbl_clubs UNION SELECT Breed tbl_Rescues GROUP BY Breed) As Q LEFT JOIN tbl_breeds As B ON Q.Breed = B.Breed WHERE B.Breed Is Null;
 

[gryffyn]

Thanks for the help.

I managed to find the following at databasedev.co.uk - and so far it's working!:

Private Sub cboBookCategory_NotInList(NewData As String, Response As Integer)
 
    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String
 
    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub
 
    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
 
    i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
    If i = vbYes Then
        strSQL = "Insert Into tblBookCategories ([strBookCategory]) " & _
                 "values ('" & NewData & "');"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub
 

Ouch - I think I hurt something trying to get this done!

[NeoPa]

If I understand this correctly, it will add entries that the operator types in rather than list those missing from the Breeds table that are found in either of the other two tables. This may be what you require, but if so this seems different from what you were asking for in your original post.
The first response, other than the use of RIGHT JOIN instead of LEFT JOIN, was a perfect answer to your question as far as I could understand it. If this is still what you actually want then I suggest you try Mary's latest SQL as it should do the job for you.
If you find that the code better fits your need then that's a different matter of course. I'm glad you found a solution anyway - whichever you prefer in the end.