DLookup issue

[seth1685]

Ok, so this piece of code is supposed to take an array:

"4514, 4234, 2345" (RS!PROG)

split it:

4514 (txtPCODE)

lookup the description that goes with that Module Number from tblPROG:

4514 (txtPCODE) - BA Geography (txtPDESC)

then iterate through the array to populate lstPROGS:

BA Geography
BSC Maths
MA Physics

But what I am getting is:

BA Geography
BA Geography
BA Geography

Any ideas?



I used an almost identical piece of code but without the DLOOKUP to populate another list with module codes and that worked fine.

 'Check there are programmes assigned to the selected EE
If Not IsNull(Rs!PROG) Then
 
'If there are, take the string of programmes, split them where they are divided by ","
a = Split(Rs!PROG, ",")
 
For i = LBound(a) To UBound(a)
 
'Print each Programme Description in full
lstPROGS.AddItem DLookup("txtPDESC", "tblPROG", a(i))
 
Next
 
'List "None" if none are assigned
Else
 
lstPROGS.AddItem ("None")
 
'End of populating the programme list
End If

Thanks!

[pbaldy]

You don't have the proper syntax for the criteria, which would be "FieldName = Value"; you simply have "Value". See if this helps:

DLookup Usage Samples

[seth1685]

Ok, so now i have

lstPROGS.AddItem DLookup("txtPDESC", "tblPROG", "txtPCODE = 'a(i)'") at line 10

but I'm getting error, invalid use of null

[pbaldy]

Does this work?

lstPROGS.AddItem DLookup("txtPDESC", "tblPROG", "txtPCODE = '" & a(i) & "'")

[seth1685]

Fantastic! Had to add in trim, but working perfectly now!

Thanks so much.

[pbaldy]

Happy to help.