There seems to be something wrong with the query for getting all the contacts for a specific client. If one client has many contact details, then you would remove the DISTINCT from the query for contact details.
There is also an error in the query, the ClientID is queried twice, and you can't select the client contact from the combo box, since it is bound to the column 1, which is ClientID previously.
I change it to something like this, and use the Click event instead.
Code:
With Me.cmbClientsContact
.RowSource = _
"SELECT [tblContact].ContactID, [tblContact].ContactName, [tblContact].ContactSurName FROM tblClients" & _
" RIGHT JOIN tblContact ON tblClients.ClientID=[tblContact].ClientID" & _
" WHERE [tblContact].ClientID=" & Me.cmbClients & " ORDER BY [tblContact].ContactName;"
.Requery
.Value = .ItemData(0)
.Visible = True
End With
I think you need to clean up your test data as well, and perhaps create some kind of relationship usnig the Database Tools -> Relationships to enforce data integrity. Fix your database design on paper first, before you create in Access. It will save you alot of programming time and trouble debugging later on.