Synchro Combo Box Issue

[gledav]

Hi There.

I'm trying to put a little app together that allows me to synchronise a combo box with a list box. I want to make a selection from the combo box and have only data related to it appear in the list box. I can get it to work when the primary and foreign keys are both numbers in a test application but the actual database this app will be used in has text data types for the keys and it does not seem to work with this data type. This is an historic artifact of our database and can't be changed. So, in the code below, [ProgrammeID] might be 'AB001'. Is there anything I can do to modify the code to make it work with text data types? Any help would be appeciated.

Thanks


Private Sub cboProgrammes_AfterUpdate()

Dim strStudentSource As String

strStudentSource = "SELECT [tblStudents1].[StudentID]," & _
" [tblStudents1].[ProgrammeID]," & _
" [tblStudents1].[StudentName] " & _
"FROM tblStudents1 " & _
"WHERE [ProgrammeID] = " & Me.cboProgrammes.Value

Me.lboStudents.RowSource = strStudentSource
Me.lboStudents.Requery

End Sub

 

[Galaxiom]

Srting must be in quotes

"WHERE [ProgrammeID] = [COLOR=blue][B]'[/B][/COLOR]" & Me.cboProgrammes.Value & "[COLOR=blue][B]'[/B][/COLOR]"

 

[gledav]

Thanks Galaxiom.

 

[Galaxiom]

Actually I have made a mistake here. The single quotes can only be used in Access (Control Source, queries etc) but will comment out the rest of the line in VBA.
In VBA nested quotes are done by doubling up:

"WHERE [ProgrammeID] = """ & Me.cboProgrammes.Value & """"

Sorry about that.

 

[dcb]

Actually I have made a mistake here. The single quotes can only be used in Access (Control Source, queries etc) but will comment out the rest of the line in VBA.
In VBA nested quotes are done by doubling up:

"WHERE [ProgrammeID] = """ & Me.cboProgrammes.Value & """"

Sorry about that.

I hate nested quotes therefore I often put in

Public Const str1C = """"

as a declaration - means that in all my code I can just reference this at any point and not have to think

"WHERE [ProgrammeID] = " & str1C & Me.cboProgrammes.Value & str1C

Don't ask about the name I use - only a very tired mind thats had enough of trying to work out """""""""""" can tell you!

Give it a try - it saves me alot of time debugging