[b]DLookup function not working in ValidationRule property[/b] 1

[greggranger]

I currently have a form which allows the user to input a date parameter in an unbound text box; I’ll refer to this unbound text box as unbDate.

I am attempting to use the DLookup function in the ValidationRule property of the unbDate object to determine whether the date value entered into the unbDate object exists in Table1’s Period Date column. Below is the syntax I’m using in the ValidationRule property.

(DLookUp("[Period Date]", "Table1","[Period Date] = [Forms]![frmAppendRecords]![unbPerEndingDate] ")) Is Null

Basically, if the date entered into the unbDate object exists in Table1, I’d like the ValidationText to appear and the subsequent activity halted. If the date entered into the unbDate object does not exist, the user may proceed.

When I open the form, enter a date, and tab out of the unbDate object, the ValidationRule does not execute and the following error appears:

There is a " in the form control's ValidationRule Property.

I am using this same logic in an .mdb database and it works fine. I have searched previous tek-tips posts and have found nothing! Any help is greatly appreciated since I’m at a loss on how to attack this.

Thank You!

 

[cmmrfrds]

I take it that this is an ADP which does not have a dblookup function.

In the beforeupdate event of the text box, format and execute a query against the table.

The beforeupdate() event.
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset

sql1 = "Select perioddate form table1 where " & _
"perioddate = '" & [Forms]![frmAppendRecords]![unbPerEndingDate] & "')) Is Null"
Set cn = CurrentProject.Connection
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

If not (rs.EOF and rs.BOF) then
msgbox "you found a record"
else
msgbox "no record found?
End if


rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

 

[greggranger]

First off, thanks for replying to my post and for the code. This is an .adp database; I apologize for not specifying that earlier.

Your idea seems like a great workaround, but I'm having an error with a portion of the code...

I placed the code in the beforeupdate event of the text box, but am getting the following error:

Run-time error'-2147217900 (80040e14)'
Line 1: Incorrect syntax near ')'.

Once I open the Visual Basic Editor, the following line is highlighted in yellow.

rs.Open sql1, cn, adOpenStatic, adLockOptimistic

I'm really quite new to VB, so it's difficult for me to resolve the error...any ideas as to what I’ve screwed up?

Also, if the unbPerEndingDate value is located in the table (i.e. msgbox "you found a record") I'd like the user to not proceed. Conversely, if the date is not found in the table, the user should be allowed to continue past the unbPerEndingDate object. How can I accomplish this with VB?

Entire Code I'm using in the beforeupdate event of the text box:

Private Sub unbPerEndingDate_BeforeUpdate(Cancel As Integer)

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset

sql1 = "Select [Period Ending Date] from [Loss Run Table] where " & _
"[Period Ending Date] = '" & [Forms]![frmAppendLossRunRecords_SelectPED]![unbPerEndingDate] & "')) Is Null"
Set cn = CurrentProject.Connection
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

If Not (rs.EOF And rs.BOF) Then
MsgBox "you found a record"
Else
MsgBox "no record found?"
End If


rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub


Again, I greatley appreciate any assistance. Thank You!

 

[cmmrfrds]

There is something wrong with the way the sql string is formatted. Check by putting a debug.print in the code.

sql1 = "Select [Period Ending Date] from [Loss Run Table] where " & _
"[Period Ending Date] = '" & [Forms]![frmAppendLossRunRecords_SelectPED]![unbPerEndingDate] & "'))"
Debug.Print sql1
This will copy the string to the immediate window, which you can open by pressing Control G

Not sure why this is in the sql "Is Null"

Please paste in the sql that appears in the debug window.

 

[greggranger]

The Debug.Print sql1 worked excellent....it seemed that the two open parenthesis in the SQL query were causing the problem. Star for you, my friend!

One final question...if a value is located in the table (i.e. MsgBox "you found a record" executes), how would I inabilitate the user from exiting the textbox?

Basically, if the value entered into the text box does not exists in the table, the user can proceed out of the text box, if not, they shouldn't proceed. How can I inabilitate the user form procedding with VB?

THANK YOU!

 

[greggranger]

I figured it out...!

Thanks again for your help