Francis:
Try this:
Private Sub txtdoccontent_BeforeUpdate(Cancel As Integer)
Dim rst As ADODB.Recordset
Dim strSQL As String, strDocNameList As String, strMessage As String
strSQL = "SELECT docname FROM docname_Table" & _
" INNER JOIN doccontent_Table" & _
" ON docname_Table.docID = doccontent_Table.docID" & _
" WHERE doccontent = """ & Me. txtdoccontent & """" & _
" ORDER BY docname"
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText
With rst
Do While Not .EOF
strDocNameList = strDocNameList & ", " & .Fields("docname")
.MoveNext
Loop
End With
' remove leading comma and space
strDocNameList = Mid$( strDocNameList, 3)
strMessage = "There is already one or more docs with that " & _
"similar contents, and they are the following: " & strDocNameList & _
vbNewLine & vbNewLine & "Considering this click in your option."
If Msgbox(strMessage, vbOKCancel + vbInformation, "Warning") = vbCancel
Then
Cancel = True
Me.Undo
End If
Set rst = Nothing
End Sub
Ken Sheridan
Stafford, England
"Francis" wrote:
> Hello i have a subform textboxcontrol, that represents doccontent, and
> i want to warn the user in a msgbox when he inserts a doccontent, that
> exists already, and leave the option of keeping the new or the old
> value. Considering i want to inform the user in a msgbox in which
> docnames there are similar entries.
>
> Also docname is on a different table(docname_Table), then Form's and
> SubForm's ControlSource (doccontent_Table), which are related by docID
> (it's primary key of docname_Table, and foreign key of
> doccontent_Table), having came up with the following code, which is not
> working in the first dlookup line:
>
> Oh and as u can see i could just make an inner join between
> docname_table and doccontent_table (reducing thus the scripts)
>
> Private Sub txtdoccontent_BeforeUpdate(Cancel As Integer)
> Dim search_doccontent, search_docID, search_docname, msgdocs
>
> search_doccontent = DLookup("[doccontent]", "doccontent_Table",
> "[doccontent_Table].[doccontent] =" &
> Me![SubForm].[txtdoccontent].Value)
> search_docID = IIf(IsNull(search_doccontent), "",
> DLookup("[docID]", "doccontent_Table", "[doccontent]=" &
> search_doccontent ))
> search_docname = IIf(IsNull(search_doccontent ), "",
> DLookup("[docname]", "docname_Table", "[docID]=" & search_docID ))
> If Not IsNull(search_doccontent ) Then
> msgdocs = MsgBox("There is already one or more docs with that
> similar contents, and they are the following:" & Chr(10) & _
> "Considering this click in your option.", vbOKCancel)
> If msgdocs = vbCancel Then
> Me.txtdoccontent.Undo
> End If
> End If
> End Sub
>
>
|
Similar Threads
