check similar values before update

[Francis]

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

 

[Guest]

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]

Hi Ken, your example was nice, but i have the following error when i
try to implement the code:
Compile Error: User-defined type not defined

In the step: Dim rst As ADODB.Recordset

Why is this happening? I'm using Microsoft Access 2003 SP2 in Visual
Basic 6.3

Francisco
Évora, Portugal

 

[Guest]

Francisco:

It sounds like you don't have a reference to the ADO object library. This
is possibly due to the application having originally been created in an
earlier version of Access. Select References from the Tools menu on the VBA
menu bar and scroll down to the Microsoft ActiveX Data Objects Library (I'm
not sure what the version is for Access 2003, but try the latest one). Check
it and close the dialogue. The code should compile OK then.

Ken Sheridan
Stafford, England

 

[Francis]

found a way to sort this in another fashion.

cheers