asked on

Dlookup in VBA Update Query

This works on an access form.

=Nz(DLookUp("[Rebate]","Rebates",("[foo]=[foo] and ([DMS]) Between [MSH] And [MSL] And ([DDoses]) Between [VH] And [VL]")),0)

But in a complicated scenario I need it to operate to update a table after a value is changed on another part of the form.
I've tried for days and I can't get it to work.

Rebate, DMS and DDoses are numbers while foo is text

Here, I'm trying to just break it down into components to isolate anything.

Dim db As Database
Set db = CurrentDb

Dim xfoo As String
Dim xDMS As Long
Dim XDDoses As Long
Dim varx As Long

xfoo = Forms!PST.Form![PST1 Subform]![foo]
xDMS = Forms!PST.Form![PST1 Subform]![DMS]
XDDoses = Forms!PST.Form![PST1 Subform]![DDoses]

varx = DLookup("[Rebate]", "[Rebates]", "[foo]= " & xfoo)

DoCmd.RunSQL "Update PST1 Set PST1.Cohort = '" & varx & "'"

PatHartman

I don't like what you are doing. It seems to be overly complicated but what is the question?

SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Neil Udovich

ASKER

I figure it has something to do with enclosures - but I'm still struggling with this statement.

varx = Nz(DLookUp("[Rebate]","Rebates",("[foo]=[foo] and ([DMS]) Between [MSH] And [MSL] And ([DDoses]) Between [VH] And [VL]")),0)

Pat: I don't like what I am doing either. I have an interactive form. When someone changes a value in DDoses it is to looks up a value from a table which the form then uses to do additional calculations. This all works fine. However, because the form footer does real time sums - I need to also pass those values to the table.

Neil Udovich

ASKER

I got it - it was in my declarations

DIM'ed all as Strings and it worked as you instructed.