SQL Query on a combo box

[Mirach]

Hi everybody, I have a problem with a SQL statement in a combo box.

I have the following query, called qryParticipants, originating from four different tables, linked in a relationship:

ID_Course; Course Title; ID_Edition; ID_User; Surname; Name

In my combo box I want to display only people related to a determinate Course Edition (ID_Edition), so my SQL in the "Row Source" should sound like this:

SELECT DISTINCT qryParticipants.ID_User, [Surname] & " " & [Name] FROM qryParticipants WHERE qryParticipants.ID_Edition=" & ID_Edition & "

where the ID_Edition variable in the WHERE clause is taken inside the current form. Instead, I don't obtain anything and, if I change the WHERE clause in this way:

WHERE qryParticipants.ID_Edition= ID_Edition

I obtain all ID_User listed in the query.

What's wrong?

 

[CBrighton]

The first one is more correct than the second, ID_Edition= ID_Edition will always be true.

However, you should remove the trailing & " from the code.

So, going by your SQL there is a control called "ID_Edition" on the same form as the combobox. Also the data type of the ID_Edition field in the table is int / long / some other number type.

Is this correct?

 

[Mirach]

Yes, is correct. On the form I have a control called ID_Edition, and is an integer (Auto Number), used as index key in the originating table.

 

[CBrighton]

Try this (Replace "FORMNAME" with the name of your form):

SELECT DISTINCT qryParticipants.ID_User, qryParticipants.Surname & " " & qryParticipants.Name AS UserName FROM qryParticipants WHERE qryParticipants.ID_Edition= " & forms!FORMNAME!ID_Edition

 

[Mirach]

I'm sorry, but it's not working

I've tried to insert & " at the end of the code too, along with every combinations of brackets and " but the WHERE clause is not processed yet.

I'm very confused about it...

 

[Mirach]

Finally, I found a (complicated ) solution to the problem:

First, I created a new blank query, called qryParticipantsBkp, and in the SQL view I wrote this:

SELECT * FROM qryParticipants

then, in the VBA code inside the parent form, when launching my new form I wrote this:

Dim dbs as DAO.Databse
Dim strSQL As String
 
Set dbs = CurrentDb()
 
strSQL = "SELECT * FROM qryParticipants WHERE qryParticipants.ID_Edition = " & IDEdition
 
dbs.QueryDefs("qryParticipantsBkp").SQL = strSQL

where IDEdition is a variable in my parent form. Finally, in the combo box, I put this code:

SELECT DISTINCT qryParticipantsBkp.ID_User, qryParticipantsBkp.Surname & " " & qryParticipantsBkp.Name AS UserName FROM qryParticipantsBkp;