Combo Box Doesn't Keep Selection/Search

[Guest]

I have a Select Distinct Look Up query to display the "Actor" names, but for
whatever reason when I select one of the records the combo box will not hold
the selected record. It's as if it were to reset itself. Also, once I have
made my selection what code do I need to use in order to open,
"ActorSearchForm," the form that will show me all the places my selection
appears. I hope this makes sense.
If it helps any, I am not using a PK because it would show me duplicate
entries of the same "Actor" in the combo box (which I do not want and the
reason for using Distinct). If there's a different way to do it, I would
highly appreciate it if I could be guided in the right direction.

Thank you very much and hope you can help me with this problem of mine.

Thank you,
JRG

 

[John W. Vinson]

I have a Select Distinct Look Up query to display the "Actor" names, but for
whatever reason when I select one of the records the combo box will not hold
the selected record. It's as if it were to reset itself. Also, once I have
made my selection what code do I need to use in order to open,
"ActorSearchForm," the form that will show me all the places my selection
appears. I hope this makes sense.
If it helps any, I am not using a PK because it would show me duplicate
entries of the same "Actor" in the combo box (which I do not want and the
reason for using Distinct). If there's a different way to do it, I would
highly appreciate it if I could be guided in the right direction.

Thank you very much and hope you can help me with this problem of mine.

Thank you,
JRG

Since you have not posted any information about the recordsource of your form,
the control source of the combo box, the row source of the combo box, or the
SQL of your query, I can't give you much help. Your statement about a PK in
particular is most perplexing, since a PK would PREVENT duplicates, not cause
them!

John W. Vinson [MVP]

 

[Guest]

Hi John,
The row source type is a query. The row source I'm using for the combo box
is as follows:
SELECT DISTINCT UseCaseTbl.Actor
FROM UseCaseTbl;

I know I should use UseCaseTbl.UseCaseID, but that will create duplicates
for me.

The code I'm using for the search button to execute the selection from the
combo box is as follows:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ActorSearch"
stLinkCriteria = "[Actor]=" & Me![Combo35]
DoCmd.OpenForm stDocName, , , stLinkCriteria

The form itself doesnt have a record source. I hope this helps you help me.
Just incase you may needed my relationship is as follows:
ReleaseTbl.ReleaseID, ReleaseTbl.Release (Indexed, no duplicates)
UseCaseTbl.UseCaseID, UseCaseTbl.Release, UseCaseTbl.UseCase (Indexed, No
Duplicates), UseCaseTbl.Actor.

Thank you,
JRG