loop dlookup

[Derkins]

hey guys I know there is a way to loop this but i cant get it

town1.Value = DLookup("City", "tblpatients", "CaseNumber = '" & CN1.Value & "'")
town2.Value = DLookup("City", "tblpatients", "CaseNumber = '" & CN2.Value & "'")
town3.Value = DLookup("City", "tblpatients", "CaseNumber = '" & CN3.Value & "'")
.
.
.
town20.Value = DLookup("City", "tblpatients", "CaseNumber = '" & CN20.Value & "'")

I keep getting an invalid use of null, if anyone could help that would be great.

Thanks

 

[dkinley]

You are probably getting the error because you are trying to assign a null value. If DLookup finds nothing, it returns a null. You can get around this by using the Nz(DLookup(...),0) so if there is a null from DLookup, it will assign a value of 0.

For your loop issue, just guessing here and probably will require some tweaking because I am not sure of your circumstances ... but something along like ...

Dim i As Integer
For i = 1 to 20
   town(i).Value = Nz(DLookup("City", "tblpatients", "CaseNumber = '" & CN(i).Value & "'"),0)
Next i

For the usage of variable in this fashion, I can't recall if it's parenthesis or not like it would be with a different command, so I could be talking outta my rear on this one.

-dK

 

[Derkins]

Can anyone confirm this syntax? still having problems with the code, Getting a sub not defined error on CN.

Dim i As Integer

For i = 1 To 20

town(i).Value = Nz(DLookup("City", "tblpatients", "CaseNumber = '" & [B]CN[/B](i).Value & "'"), " ")

Next i

 

[WayneRyan]

Derkins,

DK's syntax is perfect!

The problem revolves around "What is CN"?

If it is a form control (and you have CN1 ... CN20), then you need to
reference like:

Me.Controls("CN" & CStr(i))

within your DLookUp.

I'm guessing that it is not a VBA variable, because of the .Value

You don't really need the .Value anyway, that's the default.

hth,
Wayne

 

[Banana]

Exactly what is "CN"? Did you name your controls "CN1", "CN2", "CN3"..."CN20"? If so then the syntax should be:

Me.Controls("CN" & Cstr(i))

Also, without knowing a bit about your circumstances, I'm not sure if this is the best solution, but at least want you to know that an alternative way of doing this is to use IN clause:

SELECT City FROM tblPatients WHERE CaseNumber IN (value1, value2, value3...value20)

This gives you a nice recordset object which can be queried, searched among other things. HTH.

 

[Derkins]

Awesome that worked perfectly, Thanks a bunch guys.