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

  • Thread starter Thread starter Bob Quintal
  • Start date Start date
B

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.
Click to expand...
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.
 
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.
 
Ceebaby said:
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 & "'")
Click to expand...


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)
 
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





Marshall said:
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
Click to expand...
[quoted text clipped - 6 lines]
Set rs = CurrentDb.OpenRecordset("SELECT * FROM QryWardLook" & "WHERE
LUAddress = '" & Me!LUAddress & "'")
Click to expand...

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)
Click to expand...

--
Ceebaby

Trying to be great at Access

Message posted 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?
Click to expand...
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





Marshall said:
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
Click to expand...
[quoted text clipped - 6 lines]
Set rs = CurrentDb.OpenRecordset("SELECT * FROM
QryWardLook" & "WHERE
LUAddress = '" & Me!LUAddress & "'")
Click to expand...

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)
Click to expand...
Click to expand...
 
Back
Top