Executing a SQL query on an event

[J. B.]

Hey,

I have a form in my database that I would like to do a SQL query on a table
to determine if the entered data will duplicate an already existing record.
Here is my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim bWarn As Boolean 'Flag to warn user
Dim strMsg As String 'Message to display to user
Dim strWhere As String 'Where clause for DLookup()
Dim varID As Variant ' Results of DLookup()

If Me.NewRecord Then 'Only for new records
'Warn if these fields are blank
If IsNull(Me.Artist) Then
bWarn = True
strMsg = strMsg & "Artist is blank" & vbCrLf
End If
End If
varID = 0
'if they're not blank, check if the combination exits.
If Not bWarn Then
varID = DLookup("Artist", "Artist", "Artist = '" & [Artist] & "'")
If Not IsNull(varID) Then
bWarn = True
strMsg = strMsg & "Possible duplicate of artist " & varID & "."
End If

'If we set a warning flag, ask the user what to do
If bWarn Then
strMsg = strMsg & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Warning") _
<> vbYes Then
Cancel = True
End If
End If
End If

I was wondering how would contruct the same query using SQL statement. The
WHERE statement would be dependent on the data that is entered in the Artist
field.

I would also like to know how to use the SQL statement to check on more than
one field in the record. I did it before but I forgot how I did it. My
WHERE statement was defined by the variable stLinkCriteria which was defined
as follows:
stLinkCriteria = "[CustID]=" & Me![CustID] & " AND [OpenClose] =" &
Me![OpenClose]

I was wondering if someone could provide some assistance on formulating the
SQL statement.

Thanks in advance,

J. B.

 

[Douglas J. Steele]

Something like:

Dim rsArtists As DAO.Recordset
Dim strSQL As String

If Not bWarn Then
strSQL = "SELECT Artist FROM Artist WHERE Artist = '" & [Artist] & "'"
Set rsArtists = CurrentDb.OpenRecordset(strSQL)
If rsArtists.EOF = False Then
bWarn = True
strMsg = strMsg & "Possible duplicate of artist " & varID & "."
End If
Set rsArtists = Nothing
End If

 

[J. B. Roman]

Yeah. That works.
How would you go about checking more than just the artist field. Say I
wanted to check the Artist field in addition to a field called HomeTown. I
want to make sure that both fields are the same. The example I am asking
for isn't for this specific database. I am designing another database that
has a table called tblContacts. I have a form called frmContactAdd where I
have people come and do some data entering. The reason I am writing this
snippet of code is to check for duplicate contacts within the tblContacts.
I would like to cross check the newly entered data on the form against all
the records in the Contacts Table: I would like to check the following
fields from the Contacts table -- the first name (FName), the last name
(LName), Zip (Zip), Street Address (Street), and their home phone #
(HomePhone).

If there is a duplicate then the message box would pop up informing the
person that there could be a possible duplicate record.

My question is how you would run the SQL query to check on the fields
simultaneously?

Thanks for you help. It goes much appreciated.

J. B.

Something like:

Dim rsArtists As DAO.Recordset
Dim strSQL As String

If Not bWarn Then
strSQL = "SELECT Artist FROM Artist WHERE Artist = '" & [Artist] &
"'"
Set rsArtists = CurrentDb.OpenRecordset(strSQL)
If rsArtists.EOF = False Then
bWarn = True
strMsg = strMsg & "Possible duplicate of artist " & varID & "."
End If
Set rsArtists = Nothing
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hey,

I have a form in my database that I would like to do a SQL query on a
table
to determine if the entered data will duplicate an already existing
record.
Here is my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim bWarn As Boolean 'Flag to warn user
Dim strMsg As String 'Message to display to user
Dim strWhere As String 'Where clause for DLookup()
Dim varID As Variant ' Results of DLookup()

If Me.NewRecord Then 'Only for new records
'Warn if these fields are blank
If IsNull(Me.Artist) Then
bWarn = True
strMsg = strMsg & "Artist is blank" & vbCrLf
End If
End If
varID = 0
'if they're not blank, check if the combination exits.
If Not bWarn Then
varID = DLookup("Artist", "Artist", "Artist = '" & [Artist] & "'")
If Not IsNull(varID) Then
bWarn = True
strMsg = strMsg & "Possible duplicate of artist " & varID & "."
End If

'If we set a warning flag, ask the user what to do
If bWarn Then
strMsg = strMsg & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Warning") _
<> vbYes Then
Cancel = True
End If
End If
End If

I was wondering how would contruct the same query using SQL statement.
The
WHERE statement would be dependent on the data that is entered in the
Artist
field.

I would also like to know how to use the SQL statement to check on more
than
one field in the record. I did it before but I forgot how I did it. My
WHERE statement was defined by the variable stLinkCriteria which was
defined
as follows:
stLinkCriteria = "[CustID]=" & Me![CustID] & " AND [OpenClose] =" &
Me![OpenClose]

I was wondering if someone could provide some assistance on formulating
the
SQL statement.

Thanks in advance,

J. B.

 

[Douglas J. Steele]

Assuming you're going to have only some of the fields filled in, and that
you want to only have to enter part of the name or address, you can
dynamically build the WHERE clause:

Dim rsArtists As DAO.Recordset
Dim strSQL As String
Dim strWhere As String

If Not bWarn Then
strWhere = ""
If Len(Me.txtFName & "") > 0 Then
strWhere = strWhere & "FName Like '" & Me.txtFName & "*' AND "
End If
If Len(Me.txtLName & "") > 0 Then
strWhere = strWhere & "LName Like '" & Me.txtLName & "*' AND "
End If
If Len(Me.txtZip & "") > 0 Then
strWhere = strWhere & "Zip = '" & Me.txtZip & "' AND "
End If
If Len(Me.txtStreet & "") > 0 Then
strWhere = strWhere & "Street Like '" & Me.txtStreet & "' AND "
End If

etc.

If Len(strWhere) > 0 Then
' Remove last AND from phrase:
strWhere = Left$(strWhere, Len(strWhere) - 5)
strSQL = "SELECT Artist FROM Artist WHERE " & strWhere
Else
strSQL = "SELECT Artist FROM Artist"
End If
Set rsArtists = CurrentDb.OpenRecordset(strSQL)
If rsArtists.EOF = False Then
bWarn = True
strMsg = strMsg & "Possible duplicate of artist " & varID & "."
End If
Set rsArtists = Nothing
End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yeah. That works.
How would you go about checking more than just the artist field. Say I
wanted to check the Artist field in addition to a field called HomeTown.
I want to make sure that both fields are the same. The example I am
asking for isn't for this specific database. I am designing another
database that has a table called tblContacts. I have a form called
frmContactAdd where I have people come and do some data entering. The
reason I am writing this snippet of code is to check for duplicate
contacts within the tblContacts. I would like to cross check the newly
entered data on the form against all the records in the Contacts Table: I
would like to check the following fields from the Contacts table -- the
first name (FName), the last name (LName), Zip (Zip), Street Address
(Street), and their home phone # (HomePhone).

If there is a duplicate then the message box would pop up informing the
person that there could be a possible duplicate record.

My question is how you would run the SQL query to check on the fields
simultaneously?

Thanks for you help. It goes much appreciated.

J. B.

Something like:

Dim rsArtists As DAO.Recordset
Dim strSQL As String

If Not bWarn Then
strSQL = "SELECT Artist FROM Artist WHERE Artist = '" & [Artist] &
"'"
Set rsArtists = CurrentDb.OpenRecordset(strSQL)
If rsArtists.EOF = False Then
bWarn = True
strMsg = strMsg & "Possible duplicate of artist " & varID & "."
End If
Set rsArtists = Nothing
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hey,

I have a form in my database that I would like to do a SQL query on a
table
to determine if the entered data will duplicate an already existing
record.
Here is my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim bWarn As Boolean 'Flag to warn user
Dim strMsg As String 'Message to display to user
Dim strWhere As String 'Where clause for DLookup()
Dim varID As Variant ' Results of DLookup()

If Me.NewRecord Then 'Only for new records
'Warn if these fields are blank
If IsNull(Me.Artist) Then
bWarn = True
strMsg = strMsg & "Artist is blank" & vbCrLf
End If
End If
varID = 0
'if they're not blank, check if the combination exits.
If Not bWarn Then
varID = DLookup("Artist", "Artist", "Artist = '" & [Artist] & "'")
If Not IsNull(varID) Then
bWarn = True
strMsg = strMsg & "Possible duplicate of artist " & varID & "."
End If

'If we set a warning flag, ask the user what to do
If bWarn Then
strMsg = strMsg & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Warning")
_
<> vbYes Then
Cancel = True
End If
End If
End If

I was wondering how would contruct the same query using SQL statement.
The
WHERE statement would be dependent on the data that is entered in the
Artist
field.

I would also like to know how to use the SQL statement to check on more
than
one field in the record. I did it before but I forgot how I did it. My
WHERE statement was defined by the variable stLinkCriteria which was
defined
as follows:
stLinkCriteria = "[CustID]=" & Me![CustID] & " AND [OpenClose] =" &
Me![OpenClose]

I was wondering if someone could provide some assistance on formulating
the
SQL statement.

Thanks in advance,

J. B.