error msgs creating a recordset in VBA for a form combo box

[Bob Quintal]

Good Evening



I am basically trying to fill 3 fields postcode, ward and UPRN on
a form on the afterupdate of a combo box which a users selects to
create an address. I was using dlookup but this was so slow
taking 30 secs to find and return the values looking up a table
with 120k entries.

I found this method by creating a recordset but I have a problem
with the following code. I keep getting a syntax in from clause
error.


Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM
QryWardLook" & "WHERE
LUAddress = '" & Me!LUAddress & "'")


If Not rs.BOF And Not rs.EOF Then
Me.WardName = rs![WardName]
Me.[Post Code] = rs![PostCode]
Me.UPRN = rs![UPRN]
End If
rs.CLOSE
Set rs = Nothing

Any thoughts anyone. Thanks in advance for any help.

First thing I noticed is the lack of spaces between the words
QryWardLook" & "WHERE
Try QryWardLook " & " WHERE
I doesn't hurt to have two, but none won't work for sure.

Hopefully, your LUaddress field is indexed. The recordset will be
almost as slow as dlookup unless it is.

 

[Ceebaby via AccessMonster.com]

Good Evening



I am basically trying to fill 3 fields postcode, ward and UPRN on a form on
the afterupdate of a combo box which a users selects to create an address. I
was using dlookup but this was so slow taking 30 secs to find and return the
values looking up a table with 120k entries.

I found this method by creating a recordset but I have a problem with the
following code. I keep getting a syntax in from clause error.


Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM QryWardLook" & "WHERE
LUAddress = '" & Me!LUAddress & "'")


If Not rs.BOF And Not rs.EOF Then
Me.WardName = rs![WardName]
Me.[Post Code] = rs![PostCode]
Me.UPRN = rs![UPRN]
End If
rs.CLOSE
Set rs = Nothing

Any thoughts anyone. Thanks in advance for any help.

 

[Marshall Barton]

I am basically trying to fill 3 fields postcode, ward and UPRN on a form on
the afterupdate of a combo box which a users selects to create an address. I
was using dlookup but this was so slow taking 30 secs to find and return the
values looking up a table with 120k entries.

I found this method by creating a recordset but I have a problem with the
following code. I keep getting a syntax in from clause error.

Set rs = CurrentDb.OpenRecordset("SELECT * FROM QryWardLook" & "WHERE
LUAddress = '" & Me!LUAddress & "'")

You are missing a space between Look and WHERE. It might be
easier to keep track of such thing if you code it more like
this:

Dim SQL As String
SQL = "SELECT * FROM QryWardLook " _
& "WHERE LUAddress = '" & Me!LUAddress & "'"
'MsgBox SQL ' uncomment when debugging.
Set rs = CurrentDb.OpenRecordset(SQL)

 

[Ceebaby via AccessMonster.com]

Hi Bob Hi Marshall
Thanks for your response its much appreciated.
Tried both methods and they work. Thanks for your time on this.

Just one other quick question on this. I did not think I could index a
calculated field in a query. I have had to make LUAddress a calculated field
in the qrywardlook as it comprises 5 different address fields concantenated
together.

Am I wrong?

Cheers
Ceebaby
London

I am basically trying to fill 3 fields postcode, ward and UPRN on a form on
the afterupdate of a combo box which a users selects to create an address. I

[quoted text clipped - 6 lines]

Set rs = CurrentDb.OpenRecordset("SELECT * FROM QryWardLook" & "WHERE
LUAddress = '" & Me!LUAddress & "'")

You are missing a space between Look and WHERE. It might be
easier to keep track of such thing if you code it more like
this:

Dim SQL As String
SQL = "SELECT * FROM QryWardLook " _
& "WHERE LUAddress = '" & Me!LUAddress & "'"
'MsgBox SQL ' uncomment when debugging.
Set rs = CurrentDb.OpenRecordset(SQL)

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com

 

[Bob Quintal]

Hi Bob Hi Marshall
Thanks for your response its much appreciated.
Tried both methods and they work. Thanks for your time on this.

Just one other quick question on this. I did not think I could
index a calculated field in a query. I have had to make LUAddress
a calculated field in the qrywardlook as it comprises 5 different
address fields concantenated together.

Am I wrong?

A calculated field in a query can't be indexed, to my knowledge. I'd
love to learn differently. I imagine your recordset solution will be
slow, too. You may benefit from indexing the leftmost string in the
concatenation.

Is there a still reason to concatenate? Since you are using the
recordset instead of the dlookup, you could create a query with the
relevant criteria separate.

Q

Cheers
Ceebaby
London

I am basically trying to fill 3 fields postcode, ward and UPRN on
a form on the afterupdate of a combo box which a users selects to
create an address. I

[quoted text clipped - 6 lines]

Set rs = CurrentDb.OpenRecordset("SELECT * FROM
QryWardLook" & "WHERE
LUAddress = '" & Me!LUAddress & "'")

You are missing a space between Look and WHERE. It might be
easier to keep track of such thing if you code it more like
this:

Dim SQL As String
SQL = "SELECT * FROM QryWardLook " _
& "WHERE LUAddress = '" & Me!LUAddress & "'"
'MsgBox SQL ' uncomment when debugging.
Set rs = CurrentDb.OpenRecordset(SQL)