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

too few perameters expected 1

P: n/a
Hi folks!
I have pasted some code from anothere Db that works. the only thing
thats changed is the field names and the query. I then get the above??
I am completely stumped the references are the same and one works on
the same computer and the other doesn't. the following is the code
that does'nt.
Tia
Phil

Private Sub Postcode_AfterUpdate()
On Error GoTo Postcode_AfterUpdate_Err
Dim db As Database, rstAddress As Recordset

Set db = CurrentDb()
Set rstAddress = db.OpenRecordset("Select * From qryaddresses
Where [post Code] = " & " '" & Me![Postcode] & "'", dbOpenDynaset)
Street = rstAddress![Street]
PAO = rstAddress![PAO]
SAO = rstAddress![SAO]
Town = rstAddress![Town]
Me![PAO].SetFocus
rstAddress.Close
Set db = Nothing
Postcode_AfterUpdate_Exit:
Exit Sub

Postcode_AfterUpdate_Err:
Select Case Err.Number
Case Else
MsgBox Err.Description
Resume Postcode_AfterUpdate_Exit
Resume
End Select

End Sub

Aug 14 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
What happens if the post code is blank?
<Ph************@lineone.netwrote in message
news:11*********************@w3g2000hsg.googlegrou ps.com...
Hi folks!
I have pasted some code from anothere Db that works. the only thing
thats changed is the field names and the query. I then get the above??
I am completely stumped the references are the same and one works on
the same computer and the other doesn't. the following is the code
that does'nt.
Tia
Phil

Private Sub Postcode_AfterUpdate()
On Error GoTo Postcode_AfterUpdate_Err
Dim db As Database, rstAddress As Recordset

Set db = CurrentDb()
Set rstAddress = db.OpenRecordset("Select * From qryaddresses
Where [post Code] = " & " '" & Me![Postcode] & "'", dbOpenDynaset)
Street = rstAddress![Street]
PAO = rstAddress![PAO]
SAO = rstAddress![SAO]
Town = rstAddress![Town]
Me![PAO].SetFocus
rstAddress.Close
Set db = Nothing
Postcode_AfterUpdate_Exit:
Exit Sub

Postcode_AfterUpdate_Err:
Select Case Err.Number
Case Else
MsgBox Err.Description
Resume Postcode_AfterUpdate_Exit
Resume
End Select

End Sub

Aug 14 '07 #2

P: n/a
Try this. Your Where statement quotes were incorrect. I moved them outside
of the Open command for readability.

Private Sub Postcode_AfterUpdate()
On Error GoTo Postcode_AfterUpdate_Err
Dim db As Database, rstAddress As Recordset
Dim strSQL as String

strSQL = "Select * From qryaddresses " _
&"Where [Post Code] = '" & Me!PostCode & "'"

Set db = CurrentDb()
Set rstAddress = db.OpenRecordset strSQL, dbOpenDynaset
Street = rstAddress![Street]
PAO = rstAddress![PAO]
SAO = rstAddress![SAO]
Town = rstAddress![Town]
Me![PAO].SetFocus
rstAddress.Close
Set db = Nothing
Postcode_AfterUpdate_Exit:
Exit Sub

Postcode_AfterUpdate_Err:
Select Case Err.Number
Case Else
MsgBox Err.Description
Resume Postcode_AfterUpdate_Exit
Resume
End Select

End Sub

Len Robichaud
Aug 14 '07 #3

P: n/a
Ph************@lineone.net wrote in
news:11*********************@w3g2000hsg.googlegrou ps.com:
Hi folks!
I have pasted some code from anothere Db that works. the only
thing thats changed is the field names and the query. I then
get the above?? I am completely stumped the references are the
same and one works on the same computer and the other doesn't.
the following is the code that does'nt.
Tia
Phil
That error often indicates that Jet has failed to digest the
SQL. Check for typos in field names, missing spaces, etc.

A handy way to debug is to first build the SQL into a variable,
so you can debug.print it.

Dim stSQL as string
stSQL = "SELECT * FROM qryAddresses WHERE "
stSQL = stSQL & Where [post Code] = " & " '"
stSQL = stSQL & Me!Postcode & "'"
debug.print stSQL
Set rstAddress = db.OpenRecordset(stSQL,dbOpenDynaset)

HTH,
Q
Private Sub Postcode_AfterUpdate()
On Error GoTo Postcode_AfterUpdate_Err
Dim db As Database, rstAddress As Recordset

Set db = CurrentDb()
Set rstAddress = db.OpenRecordset("Select * From
qryaddresses
Where [post Code] = " & " '" & Me![Postcode] & "'",
dbOpenDynaset)
Street = rstAddress![Street]
PAO = rstAddress![PAO]
SAO = rstAddress![SAO]
Town = rstAddress![Town]
Me![PAO].SetFocus
rstAddress.Close
Set db = Nothing
Postcode_AfterUpdate_Exit:
Exit Sub

Postcode_AfterUpdate_Err:
Select Case Err.Number
Case Else
MsgBox Err.Description
Resume Postcode_AfterUpdate_Exit
Resume
End Select

End Sub



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 15 '07 #4

P: n/a
On 14 Aug, 23:16, "Phil Stanton" <p...@stantonfamily.co.ukwrote:
What happens if the post code is blank?<Philip_coll...@lineone.netwrote in message

news:11*********************@w3g2000hsg.googlegrou ps.com...
Hi folks!
I have pasted some code from anothere Db that works. the only thing
thats changed is the field names and the query. I then get the above??
I am completely stumped the references are the same and one works on
the same computer and the other doesn't. the following is the code
that does'nt.
Tia
Phil
Private Sub Postcode_AfterUpdate()
On Error GoTo Postcode_AfterUpdate_Err
Dim db As Database, rstAddress As Recordset
Set db = CurrentDb()
Set rstAddress = db.OpenRecordset("Select * From qryaddresses
Where [post Code] = " & " '" & Me![Postcode] & "'", dbOpenDynaset)
Street = rstAddress![Street]
PAO = rstAddress![PAO]
SAO = rstAddress![SAO]
Town = rstAddress![Town]
Me![PAO].SetFocus
rstAddress.Close
Set db = Nothing
Postcode_AfterUpdate_Exit:
Exit Sub
Postcode_AfterUpdate_Err:
Select Case Err.Number
Case Else
MsgBox Err.Description
Resume Postcode_AfterUpdate_Exit
Resume
End Select
End Sub- Hide quoted text -

- Show quoted text -
Hi Thank you for responding

Private Sub Postcode_LostFocus()
If IsNull(Me.Postcode) Then
Me.PAO.SetFocus
End If
End Sub
Thanks again Phil

Aug 15 '07 #5

P: n/a
On 14 Aug, 22:31, Bob Quintal <rquin...@sPAmpatico.cawrote:
Philip_coll...@lineone.net wrote innews:11*********************@w3g2000hsg.googlegr oups.com:
Hi folks!
I have pasted some code from anothere Db that works. the only
thing thats changed is the field names and the query. I then
get the above?? I am completely stumped the references are the
same and one works on the same computer and the other doesn't.
the following is the code that does'nt.
Tia
Phil

That error often indicates that Jet has failed to digest the
SQL. Check for typos in field names, missing spaces, etc.

A handy way to debug is to first build the SQL into a variable,
so you can debug.print it.

Dim stSQL as string
stSQL = "SELECT * FROM qryAddresses WHERE "
stSQL = stSQL & Where [post Code] = " & " '"
stSQL = stSQL & Me!Postcode & "'"
debug.print stSQL
Set rstAddress = db.OpenRecordset(stSQL,dbOpenDynaset)

HTH,
Q


Private Sub Postcode_AfterUpdate()
On Error GoTo Postcode_AfterUpdate_Err
Dim db As Database, rstAddress As Recordset
Set db = CurrentDb()
Set rstAddress = db.OpenRecordset("Select * From
qryaddresses
Where [post Code] = " & " '" & Me![Postcode] & "'",
dbOpenDynaset)
Street = rstAddress![Street]
PAO = rstAddress![PAO]
SAO = rstAddress![SAO]
Town = rstAddress![Town]
Me![PAO].SetFocus
rstAddress.Close
Set db = Nothing
Postcode_AfterUpdate_Exit:
Exit Sub
Postcode_AfterUpdate_Err:
Select Case Err.Number
Case Else
MsgBox Err.Description
Resume Postcode_AfterUpdate_Exit
Resume
End Select
End Sub

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -

- Show quoted text -
Hi Bob many thanks for your help. I'm getting a syntax error on line:-

stSQL = stSQL & Where [post Code] = " & " '"

Many Thanks
Phil

Aug 15 '07 #6

P: n/a
On 15 Aug, 00:04, "Len Robichaud" <len.robich...@rqwproserv.com>
wrote:
Try this. Your Where statement quotes were incorrect. I moved them outside
of the Open command for readability.

Private Sub Postcode_AfterUpdate()
On Error GoTo Postcode_AfterUpdate_Err
Dim db As Database, rstAddress As Recordset
Dim strSQL as String

strSQL = "Select * From qryaddresses " _
&"Where [Post Code] = '" & Me!PostCode & "'"

Set db = CurrentDb()
Set rstAddress = db.OpenRecordset strSQL, dbOpenDynaset
Street = rstAddress![Street]
PAO = rstAddress![PAO]
SAO = rstAddress![SAO]
Town = rstAddress![Town]
Me![PAO].SetFocus
rstAddress.Close
Set db = Nothing

Postcode_AfterUpdate_Exit:
Exit Sub

Postcode_AfterUpdate_Err:
Select Case Err.Number
Case Else
MsgBox Err.Description
Resume Postcode_AfterUpdate_Exit
Resume
End Select

End Sub

Len Robichaud
thank you for your help. it is much appreciated.
The answer was to knock the dynaset out:-

Set rstAddress = db.OpenRecordset(strSQL)

Why it works in one database and not the other I don't know.
Thanks again for your help.
Phil

Aug 15 '07 #7

P: n/a
On Aug 15, 10:39 am, Philip_coll...@lineone.net wrote:
On 14 Aug, 22:31, Bob Quintal <rquin...@sPAmpatico.cawrote:


Philip_coll...@lineone.net wrote innews:11*********************@w3g2000hsg.googlegr oups.com:
Hi folks!
I have pasted some code from anothere Db that works. the only
thing thats changed is the field names and the query. I then
get the above?? I am completely stumped the references are the
same and one works on the same computer and the other doesn't.
the following is the code that does'nt.
Tia
Phil
That error often indicates that Jet has failed to digest the
SQL. Check for typos in field names, missing spaces, etc.
A handy way to debug is to first build the SQL into a variable,
so you can debug.print it.
Dim stSQL as string
stSQL = "SELECT * FROM qryAddresses WHERE "
stSQL = stSQL & Where [post Code] = " & " '"
stSQL = stSQL & Me!Postcode & "'"
debug.print stSQL
Set rstAddress = db.OpenRecordset(stSQL,dbOpenDynaset)
HTH,
Q
Private Sub Postcode_AfterUpdate()
On Error GoTo Postcode_AfterUpdate_Err
Dim db As Database, rstAddress As Recordset
Set db = CurrentDb()
Set rstAddress = db.OpenRecordset("Select * From
qryaddresses
Where [post Code] = " & " '" & Me![Postcode] & "'",
dbOpenDynaset)
Street = rstAddress![Street]
PAO = rstAddress![PAO]
SAO = rstAddress![SAO]
Town = rstAddress![Town]
Me![PAO].SetFocus
rstAddress.Close
Set db = Nothing
Postcode_AfterUpdate_Exit:
Exit Sub
Postcode_AfterUpdate_Err:
Select Case Err.Number
Case Else
MsgBox Err.Description
Resume Postcode_AfterUpdate_Exit
Resume
End Select
End Sub
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account fromhttp://www.teranews.com-Hide quoted text -
- Show quoted text -

Hi Bob many thanks for your help. I'm getting a syntax error on line:-

stSQL = stSQL & Where [post Code] = " & " '"

Many Thanks
Phil- Hide quoted text -

- Show quoted text -
I forgot to add a quotation mark when I pasted your code.
Cleaned up version is
Dim stSQL as string
stSQL = "SELECT * FROM qryAddresses WHERE "
stSQL = stSQL & "[post Code] = '"
stSQL = stSQL & Me!Postcode & "'"

Aug 15 '07 #8

P: n/a
On 14 Aug, 22:31, Bob Quintal <rquin...@sPAmpatico.cawrote:
Philip_coll...@lineone.net wrote innews:11*********************@w3g2000hsg.googlegr oups.com:
Hi folks!
I have pasted some code from anothere Db that works. the only
thing thats changed is the field names and the query. I then
get the above?? I am completely stumped the references are the
same and one works on the same computer and the other doesn't.
the following is the code that does'nt.
Tia
Phil

That error often indicates that Jet has failed to digest the
SQL. Check for typos in field names, missing spaces, etc.

A handy way to debug is to first build the SQL into a variable,
so you can debug.print it.

Dim stSQL as string
stSQL = "SELECT * FROM qryAddresses WHERE "
stSQL = stSQL & Where [post Code] = " & " '"
stSQL = stSQL & Me!Postcode & "'"
debug.print stSQL
Set rstAddress = db.OpenRecordset(stSQL,dbOpenDynaset)

HTH,
Q


Private Sub Postcode_AfterUpdate()
On Error GoTo Postcode_AfterUpdate_Err
Dim db As Database, rstAddress As Recordset
Set db = CurrentDb()
Set rstAddress = db.OpenRecordset("Select * From
qryaddresses
Where [post Code] = " & " '" & Me![Postcode] & "'",
dbOpenDynaset)
Street = rstAddress![Street]
PAO = rstAddress![PAO]
SAO = rstAddress![SAO]
Town = rstAddress![Town]
Me![PAO].SetFocus
rstAddress.Close
Set db = Nothing
Postcode_AfterUpdate_Exit:
Exit Sub
Postcode_AfterUpdate_Err:
Select Case Err.Number
Case Else
MsgBox Err.Description
Resume Postcode_AfterUpdate_Exit
Resume
End Select
End Sub

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -

- Show quoted text -
Thank you for you help and your time.
Regards
Phil

Aug 17 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.