By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Duplicate Entry Check - for Allan Browne

P: n/a
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.OpenRecordset("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.FirstName) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
'LastName field.
If IsNull(Me.LastName) 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 + vbDefaultButton2) <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.OpenRecordset("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 + vbDefaultButton2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If

End If
End With
End If
End If

Set rs = Nothing
Set db = Nothing
End Sub

Jul 26 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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" <hm*******@internode.on.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.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.OpenRecordset("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.FirstName) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
'LastName field.
If IsNull(Me.LastName) 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 + vbDefaultButton2) <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.OpenRecordset("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 + vbDefaultButton2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If

End If
End With
End If
End If

Set rs = Nothing
Set db = Nothing
End Sub

Jul 26 '06 #2

P: n/a
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" <hm*******@internode.on.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.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.OpenRecordset("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.FirstName) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
'LastName field.
If IsNull(Me.LastName) 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 + vbDefaultButton2) <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.OpenRecordset("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 + vbDefaultButton2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If

End If
End With
End If
End If

Set rs = Nothing
Set db = Nothing
End Sub
Jul 26 '06 #3

P: n/a
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" <hm*******@internode.on.netwrote in message
news:11**********************@i3g2000cwc.googlegro ups.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 ...

Jul 26 '06 #4

P: n/a
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.OpenRecordset("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" <hm*******@internode.on.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.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.OpenRecordset("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.FirstName) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
>
>
'LastName field.
If IsNull(Me.LastName) 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 + vbDefaultButton2) <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.OpenRecordset("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 + vbDefaultButton2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If
>
End If
End With
End If
End If
>
Set rs = Nothing
Set db = Nothing
End Sub
Jul 28 '06 #5

P: n/a
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.OpenRecordset(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" <hm*******@internode.on.netwrote in message
news:11**********************@b28g2000cwb.googlegr oups.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.OpenRecordset("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" <hm*******@internode.on.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.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.OpenRecordset("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.FirstName) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
'LastName field.
If IsNull(Me.LastName) 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 + vbDefaultButton2) <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.OpenRecordset("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 + vbDefaultButton2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If

End If
End With
End If
End If

Set rs = Nothing
Set db = Nothing
End Sub

Jul 28 '06 #6

P: n/a
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.OpenRecordset(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" <hm*******@internode.on.netwrote in message
news:11**********************@b28g2000cwb.googlegr oups.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.OpenRecordset("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" <hm*******@internode.on.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.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.OpenRecordset("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.FirstName) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
>
>
'LastName field.
If IsNull(Me.LastName) 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 + vbDefaultButton2) <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.OpenRecordset("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 + vbDefaultButton2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If
>
End If
End With
End If
End If
>
Set rs = Nothing
Set db = Nothing
End Sub
Jul 30 '06 #7

P: n/a
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.Anniversary FROM Contacts WHERE (Anniversary
<= Date()) And (Anniversary >= DateAdd(yyyy,-5,Date()) And
Day(Anniversary) = Day( Date()) And Month(Anniversary) =
Month(Date()));"
Set db = CurrentDb
'MsgBox strSql, vbOKCancel
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot, dbForwardOnly)
If rs.RecordCount 0 Then
DoCmd.OpenForm etc etc

Sql is
SELECT Contacts.Anniversary
FROM Contacts
WHERE (Anniversary <= Date()) And (Anniversary >=
DateAdd("yyyy",-5,Date()) And Day(Anniversary) = Day( Date()) And
Month(Anniversary) = 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.OpenRecordset(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" <hm*******@internode.on.netwrote in message
news:11**********************@b28g2000cwb.googlegr oups.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.OpenRecordset("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" <hm*******@internode.on.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.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.OpenRecordset("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.FirstName) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
>
>
'LastName field.
If IsNull(Me.LastName) 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 + vbDefaultButton2) <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.OpenRecordset("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 + vbDefaultButton2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If
>
End If
End With
End If
End If
>
Set rs = Nothing
Set db = Nothing
End Sub
>
Jul 31 '06 #8

P: n/a
Looks like a problem with the quotes around the yyyy.

Try:
strSql = "SELECT Contacts.Anniversary FROM Contacts WHERE (Anniversary
<= Date()) And (Anniversary >= DateAdd(""yyyy"",-5,Date()) And
Day(Anniversary) = Day( Date()) And Month(Anniversary) =
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" <hm*******@internode.on.netwrote in message
news:11**********************@m73g2000cwd.googlegr oups.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.Anniversary FROM Contacts WHERE (Anniversary
<= Date()) And (Anniversary >= DateAdd(yyyy,-5,Date()) And
Day(Anniversary) = Day( Date()) And Month(Anniversary) =
Month(Date()));"
Set db = CurrentDb
'MsgBox strSql, vbOKCancel
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot, dbForwardOnly)
If rs.RecordCount 0 Then
DoCmd.OpenForm etc etc

Sql is
SELECT Contacts.Anniversary
FROM Contacts
WHERE (Anniversary <= Date()) And (Anniversary >=
DateAdd("yyyy",-5,Date()) And Day(Anniversary) = Day( Date()) And
Month(Anniversary) = 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.OpenRecordset(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" <hm*******@internode.on.netwrote in message
news:11**********************@b28g2000cwb.googlegr oups.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.OpenRecordset("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" <hm*******@internode.on.netwrote in message
news:11**********************@p79g2000cwp.googlegr oups.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.OpenRecordset("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.FirstName) Then
bWarn = True
sMsg = "FirstName is blank" & vbCrLf
Else
sWhere = sWhere & "(FirstName = """ & Me.FirstName & """) AND "
End If
'LastName field.
If IsNull(Me.LastName) 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 + vbDefaultButton2) <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.OpenRecordset("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 + vbDefaultButton2, "Possible
Duplicate") <vbYes Then
Cancel = True
End If

End If
End With
End If
End If

Set rs = Nothing
Set db = Nothing
End Sub

Jul 31 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.