too few perameters expected 1

[Philip_collins]

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

[Phil Stanton]

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

[Len Robichaud]

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

[Bob Quintal]

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

[Philip_collins]

On 14 Aug, 23:16, "Phil Stanton" <[e-mail]:

What happens if the post code is blank?<[e-mail] 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

[Philip_collins]

On 14 Aug, 22:31, Bob Quintal <[e-mail]:

[e-mail] 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

[Philip_collins]

On 15 Aug, 00:04, "Len Robichaud" <[e-mail]>
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

[rquintal]

On Aug 15, 10:39 am, [e-mail] wrote:

On 14 Aug, 22:31, Bob Quintal <[e-mail]:

[e-mail] 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 & "'"

[Philip_collins]

On 14 Aug, 22:31, Bob Quintal <[e-mail]:

[e-mail] 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