DLookup function in ADP form Validation Rule

[Charax]

Running A2003, SQL Server 2000, WinXP.

After upsizing an MDB to ADP, I discovered that the DLookup function is not
supported. [It is not widely publicized. I finally found it in Microsoft
Office Access Help, "Examples of expressions that look up values in other
tables", which states: "Use the DLookup function to look up a corresponding
value in another table when writing Visual Basic for Applications (VBA)
code, macros, and expressions. Note that you can't use this function in a
data access page or an Access project (.adp)."]

Is it correct that DLookup cannot be used on ADP forms? If so, what
techniques are available to replace the DLookup functionality in the
Validation Rule property on a form control?

Cheers,

Charax

 

[Sylvain Lafontaine]

In my tests, the DLookup seems to work well in many place with ADP, even
with validation rules.

I'm not sure about the exact meaning of the Office Access Help citation or
of the source of your problem; however I would suspect a permission problem
on the table(s) that you are trying to access with the DLookup function.

You should describe your problem with more details.

 

[Charax]

In my tests, the DLookup seems to work well in many place with ADP, even
with validation rules.

I'm not sure about the exact meaning of the Office Access Help citation or
of the source of your problem; however I would suspect a permission
problem on the table(s) that you are trying to access with the DLookup
function.

You should describe your problem with more details.

Thanks, Sylvain.

I have an SQL table Coins with a nvarchar(255) indexed field PictureFile
which permits nulls, and prohibits zero-length entries with constraint
([PictureFile] <> '').

In the MDB version of the front end, the following ValidationRule on a form
control prevents duplicates from being entered, works well:
DLookUp("[PictureFile]","[Coins]","[PictureFile]=Forms!frmCoins![PictureFile]")
Is Null

In the ADP version, the same syntax gives this error:
There is a(n) " in the form control's ValidationRule property

I tried the following single quote syntax but get the same error when trying
to enter a duplicate in the control on the form view:
DLookUp('[PictureFile]','Coins','[PictureFile]=' &
[Forms]![frmCoins]![PictureFile]) Is Null

And then I used semi-colons but could not enter the rule because ADP
protested with a "syntax error".

Can you see the problem?

Many thanks,

Charax

 

[Sylvain Lafontaine]

The code must be:

DLookUp("[PictureFile]","[Coins]","[PictureFile]=" &
Forms!frmCoins![PictureFile]) Is Null

The following should also work:

DLookUp("[PictureFile]","[Coins]","[PictureFile]=" & [PictureFile]) Is Null

Finally, you should remove the current record from the search:

DLookUp("[PictureFile]","[Coins]","[PictureFile]=" & [PictureFile] & " and
CoinId <> " & [CoinId]) Is Null

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

In my tests, the DLookup seems to work well in many place with ADP, even
with validation rules.

I'm not sure about the exact meaning of the Office Access Help citation
or of the source of your problem; however I would suspect a permission
problem on the table(s) that you are trying to access with the DLookup
function.

You should describe your problem with more details.

Thanks, Sylvain.

I have an SQL table Coins with a nvarchar(255) indexed field PictureFile
which permits nulls, and prohibits zero-length entries with constraint
([PictureFile] <> '').

In the MDB version of the front end, the following ValidationRule on a
form control prevents duplicates from being entered, works well:
DLookUp("[PictureFile]","[Coins]","[PictureFile]=Forms!frmCoins![PictureFile]")
Is Null

In the ADP version, the same syntax gives this error:
There is a(n) " in the form control's ValidationRule property

I tried the following single quote syntax but get the same error when
trying to enter a duplicate in the control on the form view:
DLookUp('[PictureFile]','Coins','[PictureFile]=' &
[Forms]![frmCoins]![PictureFile]) Is Null

And then I used semi-colons but could not enter the rule because ADP
protested with a "syntax error".

Can you see the problem?

Many thanks,

Charax

 

[Charax]

Sylvain,

Sorry to report that each of the rules you suggested result in the same
error:
There is a(n) " in the form control's ValidationRule property

Troubleshooting steps taken:
I created a simple TestTable which has no indexes and no constraints on the
PictureFile field.
I created a simple form frmTest based on TestTable and named the control
txtPictureFile to avoid any confusion.
Rewrote the validation rules you suggested as follows:
DLookUp("[PictureFile]","[TestTable]","[PictureFile]=" &
[Forms]![frmTest]![txtPictureFile]) Is Null

DLookUp("[PictureFile]","[TestTable]","[PictureFile]=" & [txtPictureFile])
Is Null

DLookUp("[PictureFile]","[TestTable]","[PictureFile]=" & [txtPictureFile] &
" and CoinId <> " & [CoinId]) Is Null

The syntax is what Access Project saved in the ValidationRule property. But
they all produce the error:
There is a(n) " in the form control's ValidationRule property

If you can shed any light on why these won't work in an ADP would be much
appreciated.

Charax

_______________________

 

[Sylvain Lafontaine]

If PictureFile is a string property instead of an integer, it should be
enclosed inside single or double quotes. If you are using double quotes, I
think that you must use two consecutive double quotes like in VBA.

Also, in my second exemple, if you rename the control from PictureFile to
txtPictureFile and use txtPictureFile in the validation, I'm not sure if it
will still work. By [txtPictureFile], I was making a direct reference to
the underlying field in the recordset and not to the control
[Forms]![frmTest]![txtPictureFile].

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Sylvain,

Sorry to report that each of the rules you suggested result in the same
error:
There is a(n) " in the form control's ValidationRule property

Troubleshooting steps taken:
I created a simple TestTable which has no indexes and no constraints on
the
PictureFile field.
I created a simple form frmTest based on TestTable and named the control
txtPictureFile to avoid any confusion.
Rewrote the validation rules you suggested as follows:
DLookUp("[PictureFile]","[TestTable]","[PictureFile]=" &
[Forms]![frmTest]![txtPictureFile]) Is Null

DLookUp("[PictureFile]","[TestTable]","[PictureFile]=" & [txtPictureFile])
Is Null

DLookUp("[PictureFile]","[TestTable]","[PictureFile]=" & [txtPictureFile]
& " and CoinId <> " & [CoinId]) Is Null

The syntax is what Access Project saved in the ValidationRule property.
But they all produce the error:
There is a(n) " in the form control's ValidationRule property

If you can shed any light on why these won't work in an ADP would be much
appreciated.

Charax

_______________________

The code must be:

DLookUp("[PictureFile]","[Coins]","[PictureFile]=" &
Forms!frmCoins![PictureFile]) Is Null

The following should also work:

DLookUp("[PictureFile]","[Coins]","[PictureFile]=" & [PictureFile]) Is
Null

Finally, you should remove the current record from the search:

DLookUp("[PictureFile]","[Coins]","[PictureFile]=" & [PictureFile] & "
and
CoinId <> " & [CoinId]) Is Null

 

[aaron.kempf]

uh can't you just put this same logic into a beforeupdate event on the
form?

 

[Sylvain Lafontaine]

Yes, of course.

 

[Mark Shultz Jr]

My understanding was the DLookup functions should NOT be used in ADP's and
that we should use an ado recordset that would only return one record. It's
a lot more code, but said to be more reliable, AND you can use a stored
procedure or UDF to populate the recordset, which could result in a
performance enhancement with a large recordset. I guess some people have had
unpredictable results using the Domain Aggregate functions supplied with
Access.