1

I am trying to use DLookup for a ValidationRule for a TextBox on a form in Access.

As per an example found on MSDN I used

dlookup("ABKUERZUNG", "tblABKUERZUNG", "ABKUERZUNG=[Forms]![frmMutBetriebspunkt]![BP_ABKUERZUNG]") is null

Which does not work: The expression you entered contains invalid syntax.

Now, if someone knew what and where exactly this invalid syntax is, I'd be grateful.

Thanks Rene

edit: corrected wrong ' to " (which was not the cause of the problem)

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
  • The example you pasted works. I copied and pasted all the object names. Do you have a validation Text? – JeffO Nov 03 '09 at 16:48

3 Answers3

2

EDITED

For Code:

If IsNull(dlookup( _
"ABKUERZUNG", "tblABKUERZUNG", _
"ABKUERZUNG=[Forms]![frmMutBetriebspunkt]![BP_ABKUERZUNG]")) Then

For a Validation Rule:

dlookup("ABKUERZUNG", "tblABKUERZUNG", "ABKUERZUNG=[Forms]![frmMutBetriebspunkt]![BP_ABKUERZUNG]")=Null
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • I am trying to enter the expression into the ValidationRule, and trying to put the expression within an IF .. THEN .. TRUE END IF still brings the same error message. – René Nyffenegger Nov 03 '09 at 14:08
  • Also, if I use the numeric primary key of the table instead of ABKUERZUNG, it doesn't change anything. – René Nyffenegger Nov 03 '09 at 14:08
  • Apologies - in that format delimters are not required. I note that you have a single quote at trhe start of the Where statement, it should be a double quote ("). Where do you have this code? In a module? – Fionnuala Nov 03 '09 at 14:19
  • The ' is a mistake when I sort-of manually copied the text to stackoverflow. In the original code, I have a ". The code *is* the ValidationRule for a textBox on the form frmMutBetriebspunkt. – René Nyffenegger Nov 03 '09 at 14:26
2

If you use a function in code, the parameter separator is the comma(,), but in the GUI-editor it is the semicolon (;). This could be your syntax error.

So try:

=Dlookup("field";"table";"where")
dwo
  • 3,576
  • 2
  • 22
  • 39
  • This is it! Thank you very much. For the record, I used a =DLookUp("ABKUERZUNG";"tblBETRIEBSPUNKT";"ABKUERZUNG=[Forms]![frmMutBetriebspunkt]![BP_ABKUERZUNG]") Is Null in order to make sure that ABKUERZUNG doesn't already exist in the table. – René Nyffenegger Nov 03 '09 at 19:09
  • Er, what? Where is ";" a valid separater for function parameters? I cannot get it to be accepted anywhere in A2003. Is this, perhaps, a localization issue? – David-W-Fenton Nov 04 '09 at 01:44
  • If you use a function in a calculated form-field or report-field, you have to use the ; like in Excel. But I can tell that only for the german version of Office. – dwo Nov 04 '09 at 09:51
0

You may want isolate the string you are creating for your filter, so you could check in the immediate window if the value is what you expect.

dim sFilter as String

sFilter = "ABKUERZUNG='" & [Forms]![frmMutBetriebspunkt]![BP_ABKUERZUNG] & "'"

dlookup("ABKUERZUNG", "tblABKUERZUNG", sFilter) is null
JeffO
  • 7,957
  • 3
  • 44
  • 53
  • This is not true. Try it in the immediate window. – Fionnuala Nov 03 '09 at 14:47
  • So use to doing it that way when coding, I didn't realize it is not required. Made changes. – JeffO Nov 03 '09 at 16:43
  • Yes, but you're depending on the Access expression service to resolve the reference correctly in the guts of the DLookup() code, over which you have no control. I hardly ever use DLookup(), but if I did, I'd resolve the control reference before passing anything to it. – David-W-Fenton Nov 04 '09 at 01:40