Speed up search

D

dbl

Hi I have the following code that looks up data in a table and enters it
into the correct fields, the table is in the front end because there are
over one million records so it not a network issue. It takes for ever to
find the data 20-30 seconds.

I have been told that when searching large amounts of data you can code it
in such away that it will do a quick search, is this correct? and how do you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
 
D

Douglas J Steele

See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the table. If it
isn't, is it at least in an index?
 
D

dbl

Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is it
because I deleted this part of the code?

Bob

Douglas J Steele said:
See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the table. If it
isn't, is it at least in an index?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dbl said:
Hi I have the following code that looks up data in a table and enters it
into the correct fields, the table is in the front end because there are
over one million records so it not a network issue. It takes for ever to
find the data 20-30 seconds.

I have been told that when searching large amounts of data you can code
it
in such away that it will do a quick search, is this correct? and how do you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
Click to expand...
Click to expand...
 
D

dbl

Douglas what does this part of the code do

If rsCurr.BOF = False And rsCurr.EOF = False Then

I don't understand the rsCurr.BOF = False And rsCurr.EOF =False

what is the BOF and EOF want do they mean and how do they fit into it please
(sorry if I don't ask I will never learn )

Thanks Bob

dbl said:
Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is it
because I deleted this part of the code?

Bob

Douglas J Steele said:
See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the table. If it
isn't, is it at least in an index?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dbl said:
Hi I have the following code that looks up data in a table and enters it
into the correct fields, the table is in the front end because there are
over one million records so it not a network issue. It takes for ever
to
find the data 20-30 seconds.

I have been told that when searching large amounts of data you can code
it
in such away that it will do a quick search, is this correct? and how do you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

That's just to ensure that the recordset you've opened has something in it.

BOF returns a value that indicates whether the current record position is
before the first record in a Recordset object, EOF returns a value that
indicates whether the current record position is after the last record in a
Recordset object.

If both of them are false when you open the recordset, you know that there
is at least one record in it. (You could probably get away with just If
rsCurr.EOF = False, but there's no harm in being thorough)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dbl said:
Douglas what does this part of the code do

If rsCurr.BOF = False And rsCurr.EOF = False Then

I don't understand the rsCurr.BOF = False And rsCurr.EOF =False

what is the BOF and EOF want do they mean and how do they fit into it please
(sorry if I don't ask I will never learn )

Thanks Bob

dbl said:
Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is it
because I deleted this part of the code?

Bob

Douglas J Steele said:
See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the table. If it
isn't, is it at least in an index?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi I have the following code that looks up data in a table and enters it
into the correct fields, the table is in the front end because there are
over one million records so it not a network issue. It takes for ever
to
find the data 20-30 seconds.

I have been told that when searching large amounts of data you can code
it
in such away that it will do a quick search, is this correct? and how do
you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
B

Bob

Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is it
because I deleted this part of the code?

Thanks for your help Bob

Douglas J Steele said:
That's just to ensure that the recordset you've opened has something in
it.

BOF returns a value that indicates whether the current record position is
before the first record in a Recordset object, EOF returns a value that
indicates whether the current record position is after the last record in
a
Recordset object.

If both of them are false when you open the recordset, you know that there
is at least one record in it. (You could probably get away with just If
rsCurr.EOF = False, but there's no harm in being thorough)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dbl said:
Douglas what does this part of the code do

If rsCurr.BOF = False And rsCurr.EOF = False Then

I don't understand the rsCurr.BOF = False And rsCurr.EOF =False

what is the BOF and EOF want do they mean and how do they fit into it please
(sorry if I don't ask I will never learn )

Thanks Bob

dbl said:
Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is it
because I deleted this part of the code?

Bob

See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the table. If
it
isn't, is it at least in an index?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi I have the following code that looks up data in a table and enters it
into the correct fields, the table is in the front end because there are
over one million records so it not a network issue. It takes for
ever
to
find the data 20-30 seconds.

I have been told that when searching large amounts of data you can code
it
in such away that it will do a quick search, is this correct? and how do
you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

I just noticed the typo in what I'd pasted before.

What code did you end up with (that isn't working)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bob said:
Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is it
because I deleted this part of the code?

Thanks for your help Bob

Douglas J Steele said:
That's just to ensure that the recordset you've opened has something in
it.

BOF returns a value that indicates whether the current record position is
before the first record in a Recordset object, EOF returns a value that
indicates whether the current record position is after the last record in
a
Recordset object.

If both of them are false when you open the recordset, you know that there
is at least one record in it. (You could probably get away with just If
rsCurr.EOF = False, but there's no harm in being thorough)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dbl said:
Douglas what does this part of the code do

If rsCurr.BOF = False And rsCurr.EOF = False Then

I don't understand the rsCurr.BOF = False And rsCurr.EOF =False

what is the BOF and EOF want do they mean and how do they fit into it please
(sorry if I don't ask I will never learn )

Thanks Bob

Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up
Click to expand...
with
an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses
Click to expand...
is
it
because I deleted this part of the code?

Bob

See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the table. If
it
isn't, is it at least in an index?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi I have the following code that looks up data in a table and
Click to expand...
enters
it
into the correct fields, the table is in the front end because
Click to expand...
there
are
over one million records so it not a network issue. It takes for
ever
to
find the data 20-30 seconds.

I have been told that when searching large amounts of data you can code
it
in such away that it will do a quick search, is this correct? and
Click to expand...
how
do
you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
D

dbl

It never finds the address it just goes to Me.AddressOP without inputting
the address into the relevant fields. No errors are produced. So I take it
the code runs but doesn't find any data?


Douglas J Steele said:
I just noticed the typo in what I'd pasted before.

What code did you end up with (that isn't working)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bob said:
Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is it
because I deleted this part of the code?

Thanks for your help Bob

Douglas J Steele said:
That's just to ensure that the recordset you've opened has something in
it.

BOF returns a value that indicates whether the current record position is
before the first record in a Recordset object, EOF returns a value that
indicates whether the current record position is after the last record in
a
Recordset object.

If both of them are false when you open the recordset, you know that there
is at least one record in it. (You could probably get away with just If
rsCurr.EOF = False, but there's no harm in being thorough)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas what does this part of the code do

If rsCurr.BOF = False And rsCurr.EOF = False Then

I don't understand the rsCurr.BOF = False And rsCurr.EOF =False

what is the BOF and EOF want do they mean and how do they fit into it
please
(sorry if I don't ask I will never learn )

Thanks Bob

Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with
an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is
it
because I deleted this part of the code?

Bob

See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the table.
If
it
isn't, is it at least in an index?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi I have the following code that looks up data in a table and enters
it
into the correct fields, the table is in the front end because there
are
over one million records so it not a network issue. It takes for
ever
to
find the data 20-30 seconds.

I have been told that when searching large amounts of data you can
code
it
in such away that it will do a quick search, is this correct? and how
do
you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]",
"tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

You didn't show me the actual code you're using, as I'd asked. However, try
changing

If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
End If

to

If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
Else
MsgBox "No data found for " & Me.PostcodeOP
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dbl said:
It never finds the address it just goes to Me.AddressOP without inputting
the address into the relevant fields. No errors are produced. So I take it
the code runs but doesn't find any data?


Douglas J Steele said:
I just noticed the typo in what I'd pasted before.

What code did you end up with (that isn't working)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bob said:
Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up
Click to expand...
with
an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is it
because I deleted this part of the code?

Thanks for your help Bob

That's just to ensure that the recordset you've opened has something in
it.

BOF returns a value that indicates whether the current record
Click to expand...
position
is
before the first record in a Recordset object, EOF returns a value that
indicates whether the current record position is after the last
Click to expand...
record
in
a
Recordset object.

If both of them are false when you open the recordset, you know that there
is at least one record in it. (You could probably get away with just If
rsCurr.EOF = False, but there's no harm in being thorough)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas what does this part of the code do

If rsCurr.BOF = False And rsCurr.EOF = False Then

I don't understand the rsCurr.BOF = False And rsCurr.EOF =False

what is the BOF and EOF want do they mean and how do they fit into it
please
(sorry if I don't ask I will never learn )

Thanks Bob

Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with
an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any
Click to expand...
addresses
is
it
because I deleted this part of the code?

Bob

See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the table.
If
it
isn't, is it at least in an index?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi I have the following code that looks up data in a table and enters
it
into the correct fields, the table is in the front end because there
are
over one million records so it not a network issue. It takes for
ever
to
find the data 20-30 seconds.

I have been told that when searching large amounts of data you can
code
it
in such away that it will do a quick search, is this correct?
Click to expand...
and
how
do
you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]",
"tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
D

dbl

This is the code now being used it brings up error 91 as indicated on the
line below

Private Sub PostalCode_AfterUpdate()
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

If rsCurr.BOF = False And rsCurr.EOF = False Then (this line error 91
object variable or with block variable not set)
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
Else
MsgBox "No data found for " & Me.PostcodeOP
End If
Set rsCurr = Nothing

End Sub


Thanks Bob


Douglas J Steele said:
You didn't show me the actual code you're using, as I'd asked. However,
try
changing

If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
End If

to

If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
Else
MsgBox "No data found for " & Me.PostcodeOP
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dbl said:
It never finds the address it just goes to Me.AddressOP without
inputting
the address into the relevant fields. No errors are produced. So I take it
the code runs but doesn't find any data?


Douglas J Steele said:
I just noticed the typo in what I'd pasted before.

What code did you end up with (that isn't working)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with
an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is it
because I deleted this part of the code?

Thanks for your help Bob

message
That's just to ensure that the recordset you've opened has something in
it.

BOF returns a value that indicates whether the current record position
is
before the first record in a Recordset object, EOF returns a value that
indicates whether the current record position is after the last record
in
a
Recordset object.

If both of them are false when you open the recordset, you know that
there
is at least one record in it. (You could probably get away with just If
rsCurr.EOF = False, but there's no harm in being thorough)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas what does this part of the code do

If rsCurr.BOF = False And rsCurr.EOF = False Then

I don't understand the rsCurr.BOF = False And rsCurr.EOF =False

what is the BOF and EOF want do they mean and how do they fit into it
please
(sorry if I don't ask I will never learn )

Thanks Bob

Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came
up
with
an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses
is
it
because I deleted this part of the code?

Bob

message
See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the
table.
If
it
isn't, is it at least in an index?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi I have the following code that looks up data in a table and
enters
it
into the correct fields, the table is in the front end because
there
are
over one million records so it not a network issue. It takes for
ever
to
find the data 20-30 seconds.

I have been told that when searching large amounts of data you can
code
it
in such away that it will do a quick search, is this correct? and
how
do
you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]",
"tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]",
"tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

You're missing the line

Set rsCurr = CurrentDb().OpenRecordset(strSQL)

before the If statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dbl said:
This is the code now being used it brings up error 91 as indicated on the
line below

Private Sub PostalCode_AfterUpdate()
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

If rsCurr.BOF = False And rsCurr.EOF = False Then (this line error 91
object variable or with block variable not set)
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
Else
MsgBox "No data found for " & Me.PostcodeOP
End If
Set rsCurr = Nothing

End Sub


Thanks Bob


Douglas J Steele said:
You didn't show me the actual code you're using, as I'd asked. However,
try
changing

If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
End If

to

If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
Else
MsgBox "No data found for " & Me.PostcodeOP
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dbl said:
It never finds the address it just goes to Me.AddressOP without
inputting
the address into the relevant fields. No errors are produced. So I
Click to expand...
take
it
the code runs but doesn't find any data?


I just noticed the typo in what I'd pasted before.

What code did you end up with (that isn't working)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up with
an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses
Click to expand...
is
it
because I deleted this part of the code?

Thanks for your help Bob

message
That's just to ensure that the recordset you've opened has
Click to expand...
something
in
it.

BOF returns a value that indicates whether the current record position
is
before the first record in a Recordset object, EOF returns a value that
indicates whether the current record position is after the last record
in
a
Recordset object.

If both of them are false when you open the recordset, you know that
there
is at least one record in it. (You could probably get away with
Click to expand...
just
If
rsCurr.EOF = False, but there's no harm in being thorough)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas what does this part of the code do

If rsCurr.BOF = False And rsCurr.EOF = False Then

I don't understand the rsCurr.BOF = False And rsCurr.EOF =False

what is the BOF and EOF want do they mean and how do they fit
Click to expand...
into
it
please
(sorry if I don't ask I will never learn )

Thanks Bob

Hi Douglas

txtPCPostCode is indexed but isn't the primary key because
Click to expand...
there
are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came
up
with
an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses
is
it
because I deleted this part of the code?

Bob

message
See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], "
Click to expand...
&
_
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the
table.
If
it
isn't, is it at least in an index?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi I have the following code that looks up data in a table and
enters
it
into the correct fields, the table is in the front end because
there
are
over one million records so it not a network issue. It takes for
ever
to
find the data 20-30 seconds.

I have been told that when searching large amounts of data
Click to expand...
you
can
code
it
in such away that it will do a quick search, is this correct? and
how
do
you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]",
"tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]",
"tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
D

dbl

Sorted it is now almost instant.

Thanks again for all your help its very much appreciated.

Bob
Douglas J Steele said:
You're missing the line

Set rsCurr = CurrentDb().OpenRecordset(strSQL)

before the If statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dbl said:
This is the code now being used it brings up error 91 as indicated on the
line below

Private Sub PostalCode_AfterUpdate()
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation], " & _
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

If rsCurr.BOF = False And rsCurr.EOF = False Then (this line error 91
object variable or with block variable not set)
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
Else
MsgBox "No data found for " & Me.PostcodeOP
End If
Set rsCurr = Nothing

End Sub


Thanks Bob


Douglas J Steele said:
You didn't show me the actual code you're using, as I'd asked. However,
try
changing

If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
End If

to

If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown]
Me.CountyOP = rsCurr![txtPCCounty]
Else
MsgBox "No data found for " & Me.PostcodeOP
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


It never finds the address it just goes to Me.AddressOP without
inputting
the address into the relevant fields. No errors are produced. So I take
it
the code runs but doesn't find any data?


message
I just noticed the typo in what I'd pasted before.

What code did you end up with (that isn't working)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there
are
duplicate postcodes (you can have the same postcode with a slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it came up
with
an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any addresses is
it
because I deleted this part of the code?

Thanks for your help Bob

message
That's just to ensure that the recordset you've opened has something
in
it.

BOF returns a value that indicates whether the current record
position
is
before the first record in a Recordset object, EOF returns a
value
that
indicates whether the current record position is after the last
record
in
a
Recordset object.

If both of them are false when you open the recordset, you know that
there
is at least one record in it. (You could probably get away with just
If
rsCurr.EOF = False, but there's no harm in being thorough)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas what does this part of the code do

If rsCurr.BOF = False And rsCurr.EOF = False Then

I don't understand the rsCurr.BOF = False And rsCurr.EOF =False

what is the BOF and EOF want do they mean and how do they fit into
it
please
(sorry if I don't ask I will never learn )

Thanks Bob

Hi Douglas

txtPCPostCode is indexed but isn't the primary key because there
are
duplicate postcodes (you can have the same postcode with a
slightly
different address)

I deleted rsCurr![txtPCCounty] from the 11 line because it
came
up
with
an
error expected end of statement

Unfortunately it runs with no errors but doesn't fine any
addresses
is
it
because I deleted this part of the code?

Bob

in
message
See whether this is any faster:

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [txtPCStreetName], [txtPCStreetLocation],
" &
_
"[txtPCTown], [txtPCCounty] " & _
"FROM tblPostCode " & _
"WHERE [txtPCPostCode]='" & Me.PostcodeOP & "'"

Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.BOF = False And rsCurr.EOF = False Then
Me.AddressOP = rsCurr![txtPCStreetName]
Me.AddressOP1 = rsCurr![txtPCStreetLocation]
Me.AddressOP2 = rsCurr![txtPCTown] rsCurr![txtPCCounty]
Me.CountyOP = rsCurr![txtPCCounty]
End If
Set rsCurr = Nothing


I'm assuming that txtPCPostCode is the primary key for the
table.
If
it
isn't, is it at least in an index?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi I have the following code that looks up data in a table and
enters
it
into the correct fields, the table is in the front end because
there
are
over one million records so it not a network issue. It
takes
for
ever
to
find the data 20-30 seconds.

I have been told that when searching large amounts of data you
can
code
it
in such away that it will do a quick search, is this
correct?
and
how
do
you
go about it?

Dim ExistingPCode As Variant
Dim ExistingSN As Variant
Dim ExistingSL As Variant
Dim ExistingTO As Variant
Dim ExistingCO As Variant

ExistingPCode = DLookup("[txtPCPostCode]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSN = DLookup("[txtPCStreetName]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingSL = DLookup("[txtPCStreetLocation]",
"tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingTO = DLookup("[txtPCTown]", "tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")
ExistingCO = DLookup("[txtPCCounty]",
"tblPostCode",
"[txtPCPostCode]='" & Me.PostcodeOP & "'")


If Not IsNull(ExistingPCode) Then
Me.AddressOP = ExistingSN
Me.AddressOP1 = ExistingSL
Me.AddressOP2 = ExistingTO
Me.CountyOP = ExistingCO
End If

Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I search 2 tables 10
Update unbound TextBoxes on Form; Need to Read AND Write 1
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
Recordset.update 3
Data look up 1
Subform Search Function 2
Slow data entry form 1
3rd grade code returns extra zero instead of decimal or hides deci 2

Top