Duplicate Entry Check - for Allan Browne

[Iona]

Hi Allan,

I'm using a nifty piece of code you put on here some time back to do a
duplicate entry check as below. I'm using to check for duplicate names.
However I am getting an error message on this line: Set rs =
db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" & sWhere & ");")

Contacts being the main table. I am using access 2003
The error message states that there are; Too few parameters. Expected 1
I have no idea how to resolve this. Any help would be appreciated. Many
thanks in advance
Iona


Dim sWhere As String
Dim bWarn As Boolean
Dim sMsg As String
Dim iLen As Integer
Dim db As Database
Dim rs As Recordset
Const SEP = "; "


'FistName field.
If IsNull(Me.First Name) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If


'LastName field.
If IsNull(Me.LastN ame) Then
bWarn = True
sMsg = "LastName is blank" & vbCrLf
Else
sWhere = sWhere & "(LastName = """ & Me.LastName & """) AND "
End If

If bWarn Then
sMsg = sMsg & vbCrLf & "Proceed anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2) <vbYes Then
Cancel = True
End If
End If


If Not Cancel Then
'Existing record is not a duplicate of itself.
If Not Me.NewRecord Then
sWhere = sWhere & "(ID <" & Me.ContactsID & ") AND "
End If

iLen = Len(sWhere) - 5 'Without trailing " AND ".
If iLen 0 Then
sWhere = Left$(sWhere, iLen)
sMsg = vbNullString
Set db = CurrentDb()
'Open a recordset of duplicates, and loop through them.
Set rs = db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" &
sWhere & ");")

With rs
If .RecordCount 0 Then
Do While Not .EOF
sMsg = sMsg & !ID & SEP
.MoveNext
Loop

'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &
"Continue anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If

End If
End With
End If
End If

Set rs = Nothing
Set db = Nothing


End Sub

[Allen Browne]

Re: Duplicate Entry Check - for Allan Browne

The request for a "parameter" means there is some name in the query
statement that Access is unable to resolve.

Is your table named:
Contacts

Does it have fields named:
ID
FirstName
LastName

If there are spaces or other odd characters in your field/table names add
square brackets around the names, e.g.:
SELECT [Contact ID] FROM [My Contact Table] WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1153876713 .891699.189020@ p79g2000cwp.goo glegroups.com.. .

Hi Allan,
>
I'm using a nifty piece of code you put on here some time back to do a
duplicate entry check as below. I'm using to check for duplicate names.
However I am getting an error message on this line: Set rs =
db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" & sWhere & ");")
>
Contacts being the main table. I am using access 2003
The error message states that there are; Too few parameters. Expected 1
I have no idea how to resolve this. Any help would be appreciated. Many
thanks in advance
Iona
>
>
Dim sWhere As String
Dim bWarn As Boolean
Dim sMsg As String
Dim iLen As Integer
Dim db As Database
Dim rs As Recordset
Const SEP = "; "
>
>
'FistName field.
If IsNull(Me.First Name) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
>
>
'LastName field.
If IsNull(Me.LastN ame) Then
bWarn = True
sMsg = "LastName is blank" & vbCrLf
Else
sWhere = sWhere & "(LastName = """ & Me.LastName & """) AND "
End If
>
If bWarn Then
sMsg = sMsg & vbCrLf & "Proceed anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2) <vbYes Then
Cancel = True
End If
End If
>
>
If Not Cancel Then
'Existing record is not a duplicate of itself.
If Not Me.NewRecord Then
sWhere = sWhere & "(ID <" & Me.ContactsID & ") AND "
End If
>
iLen = Len(sWhere) - 5 'Without trailing " AND ".
If iLen 0 Then
sWhere = Left$(sWhere, iLen)
sMsg = vbNullString
Set db = CurrentDb()
'Open a recordset of duplicates, and loop through them.
Set rs = db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" &
sWhere & ");")
>
With rs
If .RecordCount 0 Then
Do While Not .EOF
sMsg = sMsg & !ID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &
"Continue anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If
>
End If
End With
End If
End If
>
Set rs = Nothing
Set db = Nothing
End Sub

[Iona]

Re: Duplicate Entry Check - for Allan Browne

Thank you so much, a few [] solved the problem and another one
concurrently! When the msg box pops up thou, it doesn't have the full
contactid number :

Do While Not .EOF

sMsg = sMsg & !ContactsID & SEP
.MoveNext
Loop

'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &

The whole ID number is there for !ContactsID, however it doesn't seem
to be passed to sMsg = "Record:" & vbCrLf & Len(sMsg)

Again, thankyou so much for any help given.

kind regards
Iona


Allen Browne wrote:

The request for a "parameter" means there is some name in the query
statement that Access is unable to resolve.
>
Is your table named:
Contacts
>
Does it have fields named:
ID
FirstName
LastName
>
If there are spaces or other odd characters in your field/table names add
square brackets around the names, e.g.:
SELECT [Contact ID] FROM [My Contact Table] WHERE ...
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1153876713 .891699.189020@ p79g2000cwp.goo glegroups.com.. .

Hi Allan,

I'm using a nifty piece of code you put on here some time back to do a
duplicate entry check as below. I'm using to check for duplicate names.
However I am getting an error message on this line: Set rs =
db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" & sWhere & ");")

Contacts being the main table. I am using access 2003
The error message states that there are; Too few parameters. Expected 1
I have no idea how to resolve this. Any help would be appreciated. Many
thanks in advance
Iona


Dim sWhere As String
Dim bWarn As Boolean
Dim sMsg As String
Dim iLen As Integer
Dim db As Database
Dim rs As Recordset
Const SEP = "; "


'FistName field.
If IsNull(Me.First Name) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If


'LastName field.
If IsNull(Me.LastN ame) Then
bWarn = True
sMsg = "LastName is blank" & vbCrLf
Else
sWhere = sWhere & "(LastName = """ & Me.LastName & """) AND "
End If

If bWarn Then
sMsg = sMsg & vbCrLf & "Proceed anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2) <vbYes Then
Cancel = True
End If
End If


If Not Cancel Then
'Existing record is not a duplicate of itself.
If Not Me.NewRecord Then
sWhere = sWhere & "(ID <" & Me.ContactsID & ") AND "
End If

iLen = Len(sWhere) - 5 'Without trailing " AND ".
If iLen 0 Then
sWhere = Left$(sWhere, iLen)
sMsg = vbNullString
Set db = CurrentDb()
'Open a recordset of duplicates, and loop through them.
Set rs = db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" &
sWhere & ");")

With rs
If .RecordCount 0 Then
Do While Not .EOF
sMsg = sMsg & !ID & SEP
.MoveNext
Loop

'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &
"Continue anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If

End If
End With
End If
End If

Set rs = Nothing
Set db = Nothing
End Sub

[Allen Browne]

Re: Duplicate Entry Check - for Allan Browne

The code needs Left() in there:
sMsg = "Record:" & vbCrLf & Left(sMsg, Len(sMsg) - Len(SEP)) & vbCrLf &
"Continue anyway?"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1153889054 .792215.305140@ i3g2000cwc.goog legroups.com...

Thank you so much, a few [] solved the problem and another one
concurrently! When the msg box pops up thou, it doesn't have the full
contactid number :
>
Do While Not .EOF

sMsg = sMsg & !ContactsID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &

>
The whole ID number is there for !ContactsID, however it doesn't seem
to be passed to sMsg = "Record:" & vbCrLf & Len(sMsg)
>
Again, thankyou so much for any help given.
>
kind regards
Iona
>
>
Allen Browne wrote:

>The request for a "parameter" means there is some name in the query
>statement that Access is unable to resolve.
>>
>Is your table named:
> Contacts
>>
>Does it have fields named:
> ID
> FirstName
> LastName
>>
>If there are spaces or other odd characters in your field/table names add
>square brackets around the names, e.g.:
> SELECT [Contact ID] FROM [My Contact Table] WHERE ...

[Iona]

Re: Duplicate Entry Check - for Allan Browne

Hi, I'm really sorry to bother you with this, just can't figure it.

the duplicate entry check works great, however If I keep a duplicate
entry and then
go back and edit it, I then get the same error message as before

runtime error 3064
Too few parameters, expected 1.

Set rs = db.OpenRecordse t("SELECT [ContactsID] FROM [Contacts] WHERE ("
& sWhere & ");")

Set rs seems to be empty. sWhere as value (being the first & last Name
and the Contact ID).

any guidance would be deeply appreciate.
kind regards
iona


Iona wrote:

Thank you so much, a few [] solved the problem and another one
concurrently! When the msg box pops up thou, it doesn't have the full
contactid number :
>
Do While Not .EOF

sMsg = sMsg & !ContactsID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &

>
The whole ID number is there for !ContactsID, however it doesn't seem
to be passed to sMsg = "Record:" & vbCrLf & Len(sMsg)
>
Again, thankyou so much for any help given.
>
kind regards
Iona
>
>
Allen Browne wrote:

The request for a "parameter" means there is some name in the query
statement that Access is unable to resolve.

Is your table named:
Contacts

Does it have fields named:
ID
FirstName
LastName

If there are spaces or other odd characters in your field/table names add
square brackets around the names, e.g.:
SELECT [Contact ID] FROM [My Contact Table] WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1153876713 .891699.189020@ p79g2000cwp.goo glegroups.com.. .

Hi Allan,
>
I'm using a nifty piece of code you put on here some time back to do a
duplicate entry check as below. I'm using to check for duplicate names.
However I am getting an error message on this line: Set rs =
db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" & sWhere & ");")
>
Contacts being the main table. I am using access 2003
The error message states that there are; Too few parameters. Expected 1
I have no idea how to resolve this. Any help would be appreciated. Many
thanks in advance
Iona
>
>
Dim sWhere As String
Dim bWarn As Boolean
Dim sMsg As String
Dim iLen As Integer
Dim db As Database
Dim rs As Recordset
Const SEP = "; "
>
>
'FistName field.
If IsNull(Me.First Name) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
>
>
'LastName field.
If IsNull(Me.LastN ame) Then
bWarn = True
sMsg = "LastName is blank" & vbCrLf
Else
sWhere = sWhere & "(LastName = """ & Me.LastName & """) AND "
End If
>
If bWarn Then
sMsg = sMsg & vbCrLf & "Proceed anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2) <vbYes Then
Cancel = True
End If
End If
>
>
If Not Cancel Then
'Existing record is not a duplicate of itself.
If Not Me.NewRecord Then
sWhere = sWhere & "(ID <" & Me.ContactsID & ") AND "
End If
>
iLen = Len(sWhere) - 5 'Without trailing " AND ".
If iLen 0 Then
sWhere = Left$(sWhere, iLen)
sMsg = vbNullString
Set db = CurrentDb()
'Open a recordset of duplicates, and loop through them.
Set rs = db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" &
sWhere & ");")
>
With rs
If .RecordCount 0 Then
Do While Not .EOF
sMsg = sMsg & !ID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &
"Continue anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If
>
End If
End With
End If
End If
>
Set rs = Nothing
Set db = Nothing
End Sub

[Allen Browne]

Re: Duplicate Entry Check - for Allan Browne

To debug it, break the line down so you can see exactly the string that is
failing:
Dim strSql As String
strSql = "SELECT [ContactsID] FROM [Contacts] WHERE (" & sWhere & ");"
Debug.Print strSql
Set rs = db.OpenRecordse t(strSql)

Now when it fails, press Ctrl+G to open the Immediate window. You may be
able to see what's wrong with your string. If not, copy it to clipboard,
create a new query, switch it to SQL View, paste in the string, and see
what's the matter. Or, create a valid query in the graphical view using any
literals for the criteria, and then switch it to SQL View (View menu) to see
what you string should look like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1154060915 .149685.225300@ b28g2000cwb.goo glegroups.com.. .

Hi, I'm really sorry to bother you with this, just can't figure it.
>
the duplicate entry check works great, however If I keep a duplicate
entry and then
go back and edit it, I then get the same error message as before
>
runtime error 3064
Too few parameters, expected 1.
>
Set rs = db.OpenRecordse t("SELECT [ContactsID] FROM [Contacts] WHERE ("
& sWhere & ");")
>
Set rs seems to be empty. sWhere as value (being the first & last Name
and the Contact ID).
>
any guidance would be deeply appreciate.
kind regards
iona
>
>
Iona wrote:

>Thank you so much, a few [] solved the problem and another one
>concurrently ! When the msg box pops up thou, it doesn't have the full
>contactid number :
>>
>Do While Not .EOF

sMsg = sMsg & !ContactsID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &

>>
>The whole ID number is there for !ContactsID, however it doesn't seem
>to be passed to sMsg = "Record:" & vbCrLf & Len(sMsg)
>>
>Again, thankyou so much for any help given.
>>
>kind regards
>Iona
>>
>>
>Allen Browne wrote:

The request for a "parameter" means there is some name in the query
statement that Access is unable to resolve.
>
Is your table named:
Contacts
>
Does it have fields named:
ID
FirstName
LastName
>
If there are spaces or other odd characters in your field/table names
add
square brackets around the names, e.g.:
SELECT [Contact ID] FROM [My Contact Table] WHERE ...
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1153876713 .891699.189020@ p79g2000cwp.goo glegroups.com.. .
Hi Allan,
>
I'm using a nifty piece of code you put on here some time back to do
a
duplicate entry check as below. I'm using to check for duplicate
names.
However I am getting an error message on this line: Set rs =
db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" & sWhere & ");")
>
Contacts being the main table. I am using access 2003
The error message states that there are; Too few parameters. Expected
1
I have no idea how to resolve this. Any help would be appreciated.
Many
thanks in advance
Iona
>
>
Dim sWhere As String
Dim bWarn As Boolean
Dim sMsg As String
Dim iLen As Integer
Dim db As Database
Dim rs As Recordset
Const SEP = "; "
>
>
'FistName field.
If IsNull(Me.First Name) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
>
>
'LastName field.
If IsNull(Me.LastN ame) Then
bWarn = True
sMsg = "LastName is blank" & vbCrLf
Else
sWhere = sWhere & "(LastName = """ & Me.LastName & """) AND "
End If
>
If bWarn Then
sMsg = sMsg & vbCrLf & "Proceed anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2) <vbYes Then
Cancel = True
End If
End If
>
>
If Not Cancel Then
'Existing record is not a duplicate of itself.
If Not Me.NewRecord Then
sWhere = sWhere & "(ID <" & Me.ContactsID & ") AND "
End If
>
iLen = Len(sWhere) - 5 'Without trailing " AND ".
If iLen 0 Then
sWhere = Left$(sWhere, iLen)
sMsg = vbNullString
Set db = CurrentDb()
'Open a recordset of duplicates, and loop through them.
Set rs = db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" &
sWhere & ");")
>
With rs
If .RecordCount 0 Then
Do While Not .EOF
sMsg = sMsg & !ID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &
"Continue anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If
>
End If
End With
End If
End If
>
Set rs = Nothing
Set db = Nothing
End Sub

>

[Iona]

Re: Duplicate Entry Check - for Allan Browne

Thankyou again Allen. I did as you said and found the correct SQL
string it now works perfectly. Thanks again for your generosity and
patience.

kind regards
iona


Allen Browne wrote:

To debug it, break the line down so you can see exactly the string that is
failing:
Dim strSql As String
strSql = "SELECT [ContactsID] FROM [Contacts] WHERE (" & sWhere & ");"
Debug.Print strSql
Set rs = db.OpenRecordse t(strSql)
>
Now when it fails, press Ctrl+G to open the Immediate window. You may be
able to see what's wrong with your string. If not, copy it to clipboard,
create a new query, switch it to SQL View, paste in the string, and see
what's the matter. Or, create a valid query in the graphical view using any
literals for the criteria, and then switch it to SQL View (View menu) to see
what you string should look like.
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1154060915 .149685.225300@ b28g2000cwb.goo glegroups.com.. .

Hi, I'm really sorry to bother you with this, just can't figure it.

the duplicate entry check works great, however If I keep a duplicate
entry and then
go back and edit it, I then get the same error message as before

runtime error 3064
Too few parameters, expected 1.

Set rs = db.OpenRecordse t("SELECT [ContactsID] FROM [Contacts] WHERE ("
& sWhere & ");")

Set rs seems to be empty. sWhere as value (being the first & last Name
and the Contact ID).

any guidance would be deeply appreciate.
kind regards
iona


Iona wrote:

Thank you so much, a few [] solved the problem and another one
concurrently! When the msg box pops up thou, it doesn't have the full
contactid number :
>
Do While Not .EOF
sMsg = sMsg & !ContactsID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &
>
The whole ID number is there for !ContactsID, however it doesn't seem
to be passed to sMsg = "Record:" & vbCrLf & Len(sMsg)
>
Again, thankyou so much for any help given.
>
kind regards
Iona
>
>
Allen Browne wrote:
The request for a "parameter" means there is some name in the query
statement that Access is unable to resolve.

Is your table named:
Contacts

Does it have fields named:
ID
FirstName
LastName

If there are spaces or other odd characters in your field/table names
add
square brackets around the names, e.g.:
SELECT [Contact ID] FROM [My Contact Table] WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1153876713 .891699.189020@ p79g2000cwp.goo glegroups.com.. .
Hi Allan,
>
I'm using a nifty piece of code you put on here some time back to do
a
duplicate entry check as below. I'm using to check for duplicate
names.
However I am getting an error message on this line: Set rs =
db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" & sWhere & ");")
>
Contacts being the main table. I am using access 2003
The error message states that there are; Too few parameters. Expected
1
I have no idea how to resolve this. Any help would be appreciated.
Many
thanks in advance
Iona
>
>
Dim sWhere As String
Dim bWarn As Boolean
Dim sMsg As String
Dim iLen As Integer
Dim db As Database
Dim rs As Recordset
Const SEP = "; "
>
>
'FistName field.
If IsNull(Me.First Name) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
>
>
'LastName field.
If IsNull(Me.LastN ame) Then
bWarn = True
sMsg = "LastName is blank" & vbCrLf
Else
sWhere = sWhere & "(LastName = """ & Me.LastName & """) AND "
End If
>
If bWarn Then
sMsg = sMsg & vbCrLf & "Proceed anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2) <vbYes Then
Cancel = True
End If
End If
>
>
If Not Cancel Then
'Existing record is not a duplicate of itself.
If Not Me.NewRecord Then
sWhere = sWhere & "(ID <" & Me.ContactsID & ") AND "
End If
>
iLen = Len(sWhere) - 5 'Without trailing " AND ".
If iLen 0 Then
sWhere = Left$(sWhere, iLen)
sMsg = vbNullString
Set db = CurrentDb()
'Open a recordset of duplicates, and loop through them.
Set rs = db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" &
sWhere & ");")
>
With rs
If .RecordCount 0 Then
Do While Not .EOF
sMsg = sMsg & !ID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &
"Continue anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If
>
End If
End With
End If
End If
>
Set rs = Nothing
Set db = Nothing
End Sub

[Iona]

Re: Duplicate Entry Check - for Allan Browne

ahem... well kinda need to test that patience again. Try as I might,
and I have tried everything you have suggested, I cannot get past
another %$#@ error message (same one - expecting 1 parameter) for this
bit of code; trying to select the same date as today within the last
five years. Now this does work in a normal query. Its works perfectly.
In the code it doesn't. I have tried every combination of square
brackets, !, dots and no brackets. Please help. Am tearing hair out
with frustration (and no small amount of embarrasment that I have to
ask you gain)

code Is:

strSql = "SELECT Contacts.Annive rsary FROM Contacts WHERE (Anniversary
<= Date()) And (Anniversary >= DateAdd(yyyy,-5,Date()) And
Day(Anniversary ) = Day( Date()) And Month(Anniversa ry) =
Month(Date())); "
Set db = CurrentDb
'MsgBox strSql, vbOKCancel
Set rs = db.OpenRecordse t(strSql, dbOpenSnapshot, dbForwardOnly)
If rs.RecordCount 0 Then
DoCmd.OpenForm etc etc

Sql is
SELECT Contacts.Annive rsary
FROM Contacts
WHERE (Anniversary <= Date()) And (Anniversary >=
DateAdd("yyyy",-5,Date()) And Day(Anniversary ) = Day( Date()) And
Month(Anniversa ry) = Month(Date()));

The only diff I can find is the " " around the yyyy (but the code
didn't like it). Is the sql statement too much? but the help said that
you can use about 40 expressions as long as they are joined by an AND
or OR.

Set rs shows (rs = nothing)
the msgbox just shows the sql string.

Pls anyhelp (which so far as been spot on) would be so appreciated.

kind regards again
Iona


Iona wrote:

Thankyou again Allen. I did as you said and found the correct SQL
string it now works perfectly. Thanks again for your generosity and
patience.
>
kind regards
iona
>
>
Allen Browne wrote:

To debug it, break the line down so you can see exactly the string that is
failing:
Dim strSql As String
strSql = "SELECT [ContactsID] FROM [Contacts] WHERE (" & sWhere & ");"
Debug.Print strSql
Set rs = db.OpenRecordse t(strSql)

Now when it fails, press Ctrl+G to open the Immediate window. You may be
able to see what's wrong with your string. If not, copy it to clipboard,
create a new query, switch it to SQL View, paste in the string, and see
what's the matter. Or, create a valid query in the graphical view using any
literals for the criteria, and then switch it to SQL View (View menu) to see
what you string should look like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1154060915 .149685.225300@ b28g2000cwb.goo glegroups.com.. .

Hi, I'm really sorry to bother you with this, just can't figure it.
>
the duplicate entry check works great, however If I keep a duplicate
entry and then
go back and edit it, I then get the same error message as before
>
runtime error 3064
Too few parameters, expected 1.
>
Set rs = db.OpenRecordse t("SELECT [ContactsID] FROM [Contacts] WHERE ("
& sWhere & ");")
>
Set rs seems to be empty. sWhere as value (being the first & last Name
and the Contact ID).
>
any guidance would be deeply appreciate.
kind regards
iona
>
>
Iona wrote:
>Thank you so much, a few [] solved the problem and another one
>concurrently ! When the msg box pops up thou, it doesn't have the full
>contactid number :
>>
>Do While Not .EOF
sMsg = sMsg & !ContactsID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &
>>
>The whole ID number is there for !ContactsID, however it doesn't seem
>to be passed to sMsg = "Record:" & vbCrLf & Len(sMsg)
>>
>Again, thankyou so much for any help given.
>>
>kind regards
>Iona
>>
>>
>Allen Browne wrote:
The request for a "parameter" means there is some name in the query
statement that Access is unable to resolve.
>
Is your table named:
Contacts
>
Does it have fields named:
ID
FirstName
LastName
>
If there are spaces or other odd characters in your field/table names
add
square brackets around the names, e.g.:
SELECT [Contact ID] FROM [My Contact Table] WHERE ...
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1153876713 .891699.189020@ p79g2000cwp.goo glegroups.com.. .
Hi Allan,
>
I'm using a nifty piece of code you put on here some time back to do
a
duplicate entry check as below. I'm using to check for duplicate
names.
However I am getting an error message on this line: Set rs =
db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" & sWhere & ");")
>
Contacts being the main table. I am using access 2003
The error message states that there are; Too few parameters. Expected
1
I have no idea how to resolve this. Any help would be appreciated.
Many
thanks in advance
Iona
>
>
Dim sWhere As String
Dim bWarn As Boolean
Dim sMsg As String
Dim iLen As Integer
Dim db As Database
Dim rs As Recordset
Const SEP = "; "
>
>
'FistName field.
If IsNull(Me.First Name) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
>
>
'LastName field.
If IsNull(Me.LastN ame) Then
bWarn = True
sMsg = "LastName is blank" & vbCrLf
Else
sWhere = sWhere & "(LastName = """ & Me.LastName & """) AND "
End If
>
If bWarn Then
sMsg = sMsg & vbCrLf & "Proceed anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2) <vbYes Then
Cancel = True
End If
End If
>
>
If Not Cancel Then
'Existing record is not a duplicate of itself.
If Not Me.NewRecord Then
sWhere = sWhere & "(ID <" & Me.ContactsID & ") AND "
End If
>
iLen = Len(sWhere) - 5 'Without trailing " AND ".
If iLen 0 Then
sWhere = Left$(sWhere, iLen)
sMsg = vbNullString
Set db = CurrentDb()
'Open a recordset of duplicates, and loop through them.
Set rs = db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" &
sWhere & ");")
>
With rs
If .RecordCount 0 Then
Do While Not .EOF
sMsg = sMsg & !ID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf &
"Continue anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If
>
End If
End With
End If
End If
>
Set rs = Nothing
Set db = Nothing
End Sub
>

[Allen Browne]

Re: Duplicate Entry Check - for Allan Browne

Looks like a problem with the quotes around the yyyy.

Try:
strSql = "SELECT Contacts.Annive rsary FROM Contacts WHERE (Anniversary
<= Date()) And (Anniversary >= DateAdd(""yyyy" ",-5,Date()) And
Day(Anniversary ) = Day( Date()) And Month(Anniversa ry) =
Month(Date())); "

Explanation:
Quotation marks within quotes
at:

http://allenbrowne.com/casu-17.html

BTW, if Access can't resolve a name as a table, field, or whatever, it
thinks it must be a parameter. In this case, it could not find any field
named yyyy.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1154314928 .385974.215070@ m73g2000cwd.goo glegroups.com.. .

ahem... well kinda need to test that patience again. Try as I might,
and I have tried everything you have suggested, I cannot get past
another %$#@ error message (same one - expecting 1 parameter) for this
bit of code; trying to select the same date as today within the last
five years. Now this does work in a normal query. Its works perfectly.
In the code it doesn't. I have tried every combination of square
brackets, !, dots and no brackets. Please help. Am tearing hair out
with frustration (and no small amount of embarrasment that I have to
ask you gain)
>
code Is:
>
strSql = "SELECT Contacts.Annive rsary FROM Contacts WHERE (Anniversary
<= Date()) And (Anniversary >= DateAdd(yyyy,-5,Date()) And
Day(Anniversary ) = Day( Date()) And Month(Anniversa ry) =
Month(Date())); "
Set db = CurrentDb
'MsgBox strSql, vbOKCancel
Set rs = db.OpenRecordse t(strSql, dbOpenSnapshot, dbForwardOnly)
If rs.RecordCount 0 Then
DoCmd.OpenForm etc etc
>
Sql is
SELECT Contacts.Annive rsary
FROM Contacts
WHERE (Anniversary <= Date()) And (Anniversary >=
DateAdd("yyyy",-5,Date()) And Day(Anniversary ) = Day( Date()) And
Month(Anniversa ry) = Month(Date()));
>
The only diff I can find is the " " around the yyyy (but the code
didn't like it). Is the sql statement too much? but the help said that
you can use about 40 expressions as long as they are joined by an AND
or OR.
>
Set rs shows (rs = nothing)
the msgbox just shows the sql string.
>
Pls anyhelp (which so far as been spot on) would be so appreciated.
>
kind regards again
Iona
>
>
Iona wrote:

>Thankyou again Allen. I did as you said and found the correct SQL
>string it now works perfectly. Thanks again for your generosity and
>patience.
>>
>kind regards
>iona
>>
>>
>Allen Browne wrote:

To debug it, break the line down so you can see exactly the string that
is
failing:
Dim strSql As String
strSql = "SELECT [ContactsID] FROM [Contacts] WHERE (" & sWhere &
");"
Debug.Print strSql
Set rs = db.OpenRecordse t(strSql)
>
Now when it fails, press Ctrl+G to open the Immediate window. You may
be
able to see what's wrong with your string. If not, copy it to
clipboard,
create a new query, switch it to SQL View, paste in the string, and see
what's the matter. Or, create a valid query in the graphical view using
any
literals for the criteria, and then switch it to SQL View (View menu)
to see
what you string should look like.
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1154060915 .149685.225300@ b28g2000cwb.goo glegroups.com.. .
Hi, I'm really sorry to bother you with this, just can't figure it.
>
the duplicate entry check works great, however If I keep a duplicate
entry and then
go back and edit it, I then get the same error message as before
>
runtime error 3064
Too few parameters, expected 1.
>
Set rs = db.OpenRecordse t("SELECT [ContactsID] FROM [Contacts] WHERE
("
& sWhere & ");")
>
Set rs seems to be empty. sWhere as value (being the first & last
Name
and the Contact ID).
>
any guidance would be deeply appreciate.
kind regards
iona
>
>
Iona wrote:
>Thank you so much, a few [] solved the problem and another one
>concurrently ! When the msg box pops up thou, it doesn't have the
>full
>contactid number :
>>
>Do While Not .EOF
sMsg = sMsg & !ContactsID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf
&
>>
>The whole ID number is there for !ContactsID, however it doesn't
>seem
>to be passed to sMsg = "Record:" & vbCrLf & Len(sMsg)
>>
>Again, thankyou so much for any help given.
>>
>kind regards
>Iona
>>
>>
>Allen Browne wrote:
The request for a "parameter" means there is some name in the
query
statement that Access is unable to resolve.
>
Is your table named:
Contacts
>
Does it have fields named:
ID
FirstName
LastName
>
If there are spaces or other odd characters in your field/table
names
add
square brackets around the names, e.g.:
SELECT [Contact ID] FROM [My Contact Table] WHERE ...
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"Iona" <hmcgregor@inte rnode.on.netwro te in message
news:1153876713 .891699.189020@ p79g2000cwp.goo glegroups.com.. .
Hi Allan,
>
I'm using a nifty piece of code you put on here some time back
to do
a
duplicate entry check as below. I'm using to check for duplicate
names.
However I am getting an error message on this line: Set rs =
db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" & sWhere &
");")
>
Contacts being the main table. I am using access 2003
The error message states that there are; Too few parameters.
Expected
1
I have no idea how to resolve this. Any help would be
appreciated.
Many
thanks in advance
Iona
>
>
Dim sWhere As String
Dim bWarn As Boolean
Dim sMsg As String
Dim iLen As Integer
Dim db As Database
Dim rs As Recordset
Const SEP = "; "
>
>
'FistName field.
If IsNull(Me.First Name) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
>
>
'LastName field.
If IsNull(Me.LastN ame) Then
bWarn = True
sMsg = "LastName is blank" & vbCrLf
Else
sWhere = sWhere & "(LastName = """ & Me.LastName & """) AND "
End If
>
If bWarn Then
sMsg = sMsg & vbCrLf & "Proceed anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2) <vbYes Then
Cancel = True
End If
End If
>
>
If Not Cancel Then
'Existing record is not a duplicate of itself.
If Not Me.NewRecord Then
sWhere = sWhere & "(ID <" & Me.ContactsID & ") AND "
End If
>
iLen = Len(sWhere) - 5 'Without trailing " AND ".
If iLen 0 Then
sWhere = Left$(sWhere, iLen)
sMsg = vbNullString
Set db = CurrentDb()
'Open a recordset of duplicates, and loop through them.
Set rs = db.OpenRecordse t("SELECT ID FROM Contacts WHERE (" &
sWhere & ");")
>
With rs
If .RecordCount 0 Then
Do While Not .EOF
sMsg = sMsg & !ID & SEP
.MoveNext
Loop
>
'Ask the user if these are duplicates.
sMsg = "Record:" & vbCrLf & Len(sMsg) - Len(SEP) & vbCrLf
&
"Continue anyway?"
If MsgBox(sMsg, vbYesNo + vbDefaultButton 2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If
>
End If
End With
End If
End If
>
Set rs = Nothing
Set db = Nothing
End Sub
>

>