P
Pressedrat N Warthog
I apparently can't get the syntax correct for DLookup and I am
wondering if anyone can help.
I have a database that stores documents in one table. I want to use a
form to add new documents but I want to check first to be sure a
document is not added if it already exists in the database.
From the online help I see I can use the DLookup method to do that
with an event procedure at the Before Update event.
Here's what I have:
Documents <== the table with the document information
Documents.DocumentTitle <== title
Documents.DocumentVersion <== 1.0, 2.0, etc
Documents.idDocumentType <== 1, 2, 3, etc.
The idDocumentType field is a lookup to another table where we have
the following:
1 = Project Plan
2 = Design Document
3 = Test Plan
etc.
LogNewDocument <== the form to enter new documents
On the form I have controls that are bound to the above fields in the
Documents table. When the form comes up, you are supposed to enter the
Title, the Version, and select the Document Type (from a combo box).
Then you hit a Done button to enter the record. It's at that point
that I have written a procedure and have associated it to the Before
Update event. This procedure is supposed to check to see if the
document already exists and give an error message if it does or allow
the record to be added if it does not exist.
Here's the procedure.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Not IsNull(DLookup("[LogNewDocument]", "Documents", _
"[DocumentTitle] = '" & Me!DocumentTitle & _
"' And [DocumentVersion] = " & Me!DocumentVersion & _
" And [idDocumentType] = " & Me!idDocumentType))) Then
MsgBox "Document has already been entered in the database."
Cancel = True
Me!DocumentTitle.Undo
Me!DocumentVersion.Undo
Me!DocumentSizePages.Undo
Me!idDocumentType.Undo
Else
'Since the document is not already in the database
'it's safe to add it.
'Set the variable for Document Review Status.
'All info for the new document is provided
'Set the document to have a status of NEW
Forms!LogNewDocument.DocumentReviewStatus = DocReviewStatusNew
Dim DocReviewStatusNew As Long
DocReviewStatusNew = 1 'This is a NEW document
End If
End Sub
When I add a new document I get the following message:
Run-time error '2001':
You canceled the previous operation.
END DEBUG
When I hit Debug it puts me at the last line in the IF clause:
If (Not IsNull(DLookup("[LogNewDocument]", "Documents", _
"[DocumentTitle] = '" & Me!DocumentTitle & _
"' And [DocumentVersion] = " & Me!DocumentVersion & _
=> " And [idDocumentType] = " & Me!idDocumentType))) Then
I'm sure I have the syntax wrong but I cannot figure it out. I am
pretty lost about where to place the brackets and the quote marks, so
that's probably it. I have tried reading the online help several times
but I still can't figure it out.
Anyone can help?
wondering if anyone can help.
I have a database that stores documents in one table. I want to use a
form to add new documents but I want to check first to be sure a
document is not added if it already exists in the database.
From the online help I see I can use the DLookup method to do that
with an event procedure at the Before Update event.
Here's what I have:
Documents <== the table with the document information
Documents.DocumentTitle <== title
Documents.DocumentVersion <== 1.0, 2.0, etc
Documents.idDocumentType <== 1, 2, 3, etc.
The idDocumentType field is a lookup to another table where we have
the following:
1 = Project Plan
2 = Design Document
3 = Test Plan
etc.
LogNewDocument <== the form to enter new documents
On the form I have controls that are bound to the above fields in the
Documents table. When the form comes up, you are supposed to enter the
Title, the Version, and select the Document Type (from a combo box).
Then you hit a Done button to enter the record. It's at that point
that I have written a procedure and have associated it to the Before
Update event. This procedure is supposed to check to see if the
document already exists and give an error message if it does or allow
the record to be added if it does not exist.
Here's the procedure.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Not IsNull(DLookup("[LogNewDocument]", "Documents", _
"[DocumentTitle] = '" & Me!DocumentTitle & _
"' And [DocumentVersion] = " & Me!DocumentVersion & _
" And [idDocumentType] = " & Me!idDocumentType))) Then
MsgBox "Document has already been entered in the database."
Cancel = True
Me!DocumentTitle.Undo
Me!DocumentVersion.Undo
Me!DocumentSizePages.Undo
Me!idDocumentType.Undo
Else
'Since the document is not already in the database
'it's safe to add it.
'Set the variable for Document Review Status.
'All info for the new document is provided
'Set the document to have a status of NEW
Forms!LogNewDocument.DocumentReviewStatus = DocReviewStatusNew
Dim DocReviewStatusNew As Long
DocReviewStatusNew = 1 'This is a NEW document
End If
End Sub
When I add a new document I get the following message:
Run-time error '2001':
You canceled the previous operation.
END DEBUG
When I hit Debug it puts me at the last line in the IF clause:
If (Not IsNull(DLookup("[LogNewDocument]", "Documents", _
"[DocumentTitle] = '" & Me!DocumentTitle & _
"' And [DocumentVersion] = " & Me!DocumentVersion & _
=> " And [idDocumentType] = " & Me!idDocumentType))) Then
I'm sure I have the syntax wrong but I cannot figure it out. I am
pretty lost about where to place the brackets and the quote marks, so
that's probably it. I have tried reading the online help several times
but I still can't figure it out.
Anyone can help?