Search funtion ....

[Sue Compelling]

Hi

Using ACCESS 2000

I have just completed a refresh of an existing DB that my users have been
happily using for 5 years, without too much complaint. The refresh was
mainly look and feel of the forms and reports.

Context:

I undertook the dev work in 2007 though saved the DB in 2000 (the version of
my users)
The DB only has some 6,000 records (tables DB is 11 MB, Forms, Queries,
Report DB is 1.5 MB)
The DB has max of 8 users at any one time

Problem:

When I undertake the Ctl F command in the test lab (ie my home) - it works
exactly as it should - quickly and accurately finds a record)

When my users try the same thing it can take up to a minute for a searched
for record to return (obviously unacceptable)

Where do I begin to resolve this issue? (ps they have compacted and
repaired the DB)

TIA

 

[Tom Wickerath]

Hi Sue,

How close does your test lab replicate the production copy? If done
properly, your application has been split into a FE (front-end) and BE
(back-end) databases, and each user has their own local copy of the FE
application loaded onto their hard drives. In other words, the FE is
unshared. Does your test lab at home include a network?

This article may provide some helpful ideas for you:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

Also, I think you would be better off to implement true Query by Form (QBF)
instead of relying on the built-in Find and Replace. A network is the weak
link in the chain (ie. slowest data transfer), so the golden rule is to
"request less data". In order to use the <Ctrl><F> functionality, I'm pretty
sure you would need to drag the entire recordset over the wire. You might
want to have a look at the downloads I offer for the January/February, 2008
meeting presentations at the Seattle Access web site:

http://www.seattleaccess.org/downloads.htm

Make sure to disable Name Autocorrect, set all subdatasheets to [None], and
establish a persistent connection. Experiment some with the JETShowPlan
registry setting, as discussed in my Multiuser apps. paper. Try to make sure
that you are not ending up with table scans. If you see table scans in the
resulting Showplan.out files, you should consider applying indexes as needed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Sue Compelling]

Hi Tim

Appreciate the response and indeed the links to QBF 'training tools'. Have
now saved this to my favs

In response to your questions;

a) Yes - the App is comprised of FE and BE DBs
b) No - my home environment doesn't replicate theirs (as I don't use a
network)
c) to add to the confusion - some users were using the old version and the
Ctl F function worked fine (ie fast)

A question - Rather than me deploying this myself - I did zip and email the
FE to the Manager and talked them through the download and linking (and
copying the master to their local drives etc). Could the zipping and
emailing be part of the problem?

TIA
--
Sue Compelling

Hi Sue,

How close does your test lab replicate the production copy? If done
properly, your application has been split into a FE (front-end) and BE
(back-end) databases, and each user has their own local copy of the FE
application loaded onto their hard drives. In other words, the FE is
unshared. Does your test lab at home include a network?

This article may provide some helpful ideas for you:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm

Also, I think you would be better off to implement true Query by Form (QBF)
instead of relying on the built-in Find and Replace. A network is the weak
link in the chain (ie. slowest data transfer), so the golden rule is to
"request less data". In order to use the <Ctrl><F> functionality, I'm pretty
sure you would need to drag the entire recordset over the wire. You might
want to have a look at the downloads I offer for the January/February, 2008
meeting presentations at the Seattle Access web site:

http://www.seattleaccess.org/downloads.htm

Make sure to disable Name Autocorrect, set all subdatasheets to [None], and
establish a persistent connection. Experiment some with the JETShowPlan
registry setting, as discussed in my Multiuser apps. paper. Try to make sure
that you are not ending up with table scans. If you see table scans in the
resulting Showplan.out files, you should consider applying indexes as needed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi

Using ACCESS 2000

I have just completed a refresh of an existing DB that my users have been
happily using for 5 years, without too much complaint. The refresh was
mainly look and feel of the forms and reports.

Context:

I undertook the dev work in 2007 though saved the DB in 2000 (the version of
my users)
The DB only has some 6,000 records (tables DB is 11 MB, Forms, Queries,
Report DB is 1.5 MB)
The DB has max of 8 users at any one time

Problem:

When I undertake the Ctl F command in the test lab (ie my home) - it works
exactly as it should - quickly and accurately finds a record)

When my users try the same thing it can take up to a minute for a searched
for record to return (obviously unacceptable)

Where do I begin to resolve this issue? (ps they have compacted and
repaired the DB)

TIA

 

[Tom Wickerath]

Hi Sue,

b) No - my home environment doesn't replicate theirs (as I don't use a
network)

In that case, any speed comparison tests of what you get at home versus what
your clients get in their office is an apples and oranges comparison.

c) to add to the confusion - some users were using the old version and the
Ctl F function worked fine (ie fast)

What changed between your old version and your newer version? Make sure that
they start with a compacted database (both FE & BE), as compacting will cause
all query plans to be reset. Access will determine the best query plan the
next time each query is run, immediately after a compact and repair. In this
way, you will have the most efficient query plan for the number of records in
each table.

Could the zipping and emailing be part of the problem?

I can't imagine how the manager could have done something wrong
here....unless they set everyone up to share a common FE file on the file
server. Is it possible for you to visit this location in person?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Sue Compelling]

Hi Tom

Take your point on issue a)
And yes - I think I will drive in to deploy.

The difference between the old FE and the new FE was almost solely around
look and feel of the forms and reports, though of note is that I undertook
the dev work in 2007 though saved the DB in 2000 (the version of my users).

Would this conribute to any problems?

 

[Tom Wickerath]

Hi Sue,

It's always best to develop using the lowest common denominator version of
Access. In that way, you don't inadvertently use functionality that is not
supported in an earlier version.

You might want to try talking the manager (or do it yourself, when you do go
on-site) into decompiling the VBA code in your project, compacting, and then
re-compiling.

I forgot to ask earlier, but do you have relationships established between
the tables in the BE database, with enforced referential integrity (RI)?

As long as you will be on site soon, I highly recommend that you become
familiar with Access MVP Tony Toews free AutoFE Updater utility. This will
make it breezy-easy for you to deploy updated FE's in the future.

http://www.granite.ab.ca/access/autofe.htm
http://www.granite.ab.ca/access/autofe/gettingstarted.htm

Do spend some time using JETShowPlan, using your home test lab. With this
registry flag set, use each command button on a switchboard that a user might
click on. Do some searches using your <Ctrl><F> method. Then open the
resulting Showplan.out file using your favorite text editor, and search for
the word "scan". Don't become too concerned with table scans against small
tables, as scanning may be the most efficient method in that case. However,
if the table has more than, say, 100 records or so, I'd start to get
concerned about seeing table scans. Also, keep in mind that table scans
against lookup tables, for example the rowsource for a combo box, are
perfectly fine. After all, the combo box likely displays all records in the
lookup table.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Sue Compelling]

Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.

I changed 2 fields on the form (which were populating using queries) into
lookups. On my DB it seemed to present a more transparent look for the user
though has obviously stuffed up the search.

Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.

That said Tom - while I have your attention - is it possible for you to fix
this field for me below? (happy if you want me to repost).

In most instances the 'coordinator' details that get returned will not
always have every type of contact number displayed, so in those instances I
don't want the H:, W: or M: to be shown - I think I'm supposed to use the Nz
function?


Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")

TIA

 

[Tom Wickerath]

Hi Sue,

I had called it a night after my last reply--it was after midnight for me,
so I just read your reply shown below.

Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.

Maybe, but maybe not....

I changed 2 fields on the form (which were populating using queries) into
lookups.

I think you are saying that you changed two text boxes to lookup combo
boxes. Is this correct? If so, how many records are in the row sources for
each combo box? Are you pulling only the required data for the combo box?
I've often times seen people use rowsources for combo boxes that look
something like this:

SELECT * FROM [TableName] ORDER BY [FieldName]

when all they *really* needed was one or two fields from the table involved.
However, by using the SELECT * syntax, they brought ALL fields in each record
over the wire.

Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.

I'm pretty much self taught as well. What parts of my replies have seemed
scary to you?

I think I'm supposed to use the Nz function?

The Nz function can be used to convert a null (unknown) to something else,
such as 0 (zero), or a zero length string (ZLS) --> "". A null plus anything
else is still a null, so I think you can use the idea of null concatentation
here, if the fields are truly null (and not ZLS). Try something like this (I
didn't attempt to test it):

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] + (" H: "
& Format([homephone],"(00) 000-0000")) + (" W: " &
Format([workphone],"(00) 000-0000" & ", " & [workextension]) + (" M: " &
Format([mobilephone],"(000) 000-0000"))


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.

I changed 2 fields on the form (which were populating using queries) into
lookups. On my DB it seemed to present a more transparent look for the user
though has obviously stuffed up the search.

Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.

That said Tom - while I have your attention - is it possible for you to fix
this field for me below? (happy if you want me to repost).

In most instances the 'coordinator' details that get returned will not
always have every type of contact number displayed, so in those instances I
don't want the H:, W: or M: to be shown - I think I'm supposed to use the Nz
function?


Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")

TIA

 

[Tom Wickerath]

Let me try that again:

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType]
& (" H: " + Format([homephone],"(00) 000-0000"))
& (" W: " + Format([workphone],"(00) 000-0000" + ", " + [workextension]))
& (" M: " + Format([mobilephone],"(000) 000-0000"))

You might also try something like this, to get the phone numbers to print on
different lines:

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType]
& (Chr(13) + Chr(10) + " H: " + Format([homephone],"(00) 000-0000"))
& (Chr(13) + Chr(10) + " W: " + Format([workphone],"(00) 000-0000" + ", "
+ [workextension]))
& (Chr(13) + Chr(10) + " M: " + Format([mobilephone],"(000) 000-0000"))


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html

 

[Sue Compelling]

Hi Tom

Unfortunately this presents a blank field where nothing populates - but it's
fixed now from Marshall using another post:

( .... & Format([homephone]," \H\: (@@) @@@-@@@@") & Format([workphone],"
\W\: (@@) @@@-@@@@") & (" X" +
[workextension]) & Format([mobilephone]," \M\: (@@@) @@@-@@@@")

Appreciate your response regardless. And midnight - bedtime!!!! surely the
night has just begun.

Will respond to your questions using your other reply.

 

[Sue Compelling]

Hi Tom

Responses to your questions ...

Q1 - "I think you are saying that you changed two text boxes to lookup combo
boxes. Is this correct? If so, how many records are in the row sources for
each combo box?"

A1 - Even worse - I changed 2 list boxes into text boxes with DLOOKUPS. I
have reverted back to the list box which sources it's data from a query
(which will only have 1 or 2 records). The row source: SELECT [Coordinator
TAG - Locations].[Region], [Coordinator TAG - Locations].[Location] FROM
[Coordinator TAG - Locations]; This worked well (and fast in the previous
version and I'm hoping it's the answer to my slowness"

Q2 - "I forgot to ask earlier, but do you have relationships established
between
the tables in the BE database, with enforced referential integrity (RI)?"

A2 - Definitely

Q3 - "I'm pretty much self taught as well. What parts of my replies have
seemed
scary to you?"

A3 - This bit .... I don't even know what a registry flag thing is ... "Do
spend some time using JETShowPlan, using your home test lab. With this
registry flag set, use each command button on a switchboard that a user might
click on. Do some searches using your <Ctrl><F> method. Then open the
resulting Showplan.out file using your favorite text editor, and search for
the word "scan". Don't become too concerned with table scans against small
tables, as scanning may be the most efficient method in that case. However,
if the table has more than, say, 100 records or so, I'd start to get
concerned about seeing table scans. Also, keep in mind that table scans
against lookup tables, for example the rowsource for a combo box, are
perfectly fine. After all, the combo box likely displays all records in the
lookup table"

Cheers ...
--
Sue Compelling

Hi Sue,

I had called it a night after my last reply--it was after midnight for me,
so I just read your reply shown below.

Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.

Maybe, but maybe not....

I changed 2 fields on the form (which were populating using queries) into
lookups.

I think you are saying that you changed two text boxes to lookup combo
boxes. Is this correct? If so, how many records are in the row sources for
each combo box? Are you pulling only the required data for the combo box?
I've often times seen people use rowsources for combo boxes that look
something like this:

SELECT * FROM [TableName] ORDER BY [FieldName]

when all they *really* needed was one or two fields from the table involved.
However, by using the SELECT * syntax, they brought ALL fields in each record
over the wire.

Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.

I'm pretty much self taught as well. What parts of my replies have seemed
scary to you?

I think I'm supposed to use the Nz function?

The Nz function can be used to convert a null (unknown) to something else,
such as 0 (zero), or a zero length string (ZLS) --> "". A null plus anything
else is still a null, so I think you can use the idea of null concatentation
here, if the fields are truly null (and not ZLS). Try something like this (I
didn't attempt to test it):

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] + (" H: "
& Format([homephone],"(00) 000-0000")) + (" W: " &
Format([workphone],"(00) 000-0000" & ", " & [workextension]) + (" M: " &
Format([mobilephone],"(000) 000-0000"))


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.

I changed 2 fields on the form (which were populating using queries) into
lookups. On my DB it seemed to present a more transparent look for the user
though has obviously stuffed up the search.

Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.

That said Tom - while I have your attention - is it possible for you to fix
this field for me below? (happy if you want me to repost).

In most instances the 'coordinator' details that get returned will not
always have every type of contact number displayed, so in those instances I
don't want the H:, W: or M: to be shown - I think I'm supposed to use the Nz
function?


Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")

TIA

 

[Tom Wickerath]

Q1, Continued:

SELECT [Coordinator TAG - Locations].[Region]....

My advice is to avoid the use of special characters, such as spaces and
hyphens, in anything that you assign a name to in Access, including the names
of fields, tables, queries, forms, reports, macros, modules, and controls on
forms and reports. This Microsoft KB (Knowledge Base) article discusses this
in further detail:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Also, here is a link to a comprehensive listing of reserved words to avoid
using. This list is maintained by Access MVP Allen Browne. He also has a free
utility available that you can download, which can be used to scan your
database for the use of special characters and reserved words in field and
table names, along with several other potential issues:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

This worked well (and fast in the previous version and I'm hoping it's the
answer to my slowness

I would think that the use of your DLookups would only affect form load
time, and if the [Coordinator TAG - Locations] table only has two records,
the effect should be negligable.

A3 - This bit .... I don't even know what a registry flag thing is ...

In my Multiuser Applications article, where I mention using JETShowPlan, I
include this link, where you can read more about using this technique:
http://articles.techrepublic.com.com/5100-10878_11-5064388.html

I also include a .zip file that you can download, which has sample output
files that you can open with NotePad or WordPad and search for the word
"scan" (without the quotes). I even included two .reg files to make the
process really easy. All you need to do to set the registry flag
appropriately is to double-click the .reg file. It's not scary. Honest.
<smile>


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Tom

Responses to your questions ...

Q1 - "I think you are saying that you changed two text boxes to lookup combo
boxes. Is this correct? If so, how many records are in the row sources for
each combo box?"

A1 - Even worse - I changed 2 list boxes into text boxes with DLOOKUPS. I
have reverted back to the list box which sources it's data from a query
(which will only have 1 or 2 records). The row source: SELECT [Coordinator
TAG - Locations].[Region], [Coordinator TAG - Locations].[Location] FROM
[Coordinator TAG - Locations]; This worked well (and fast in the previous
version and I'm hoping it's the answer to my slowness"

Q2 - "I forgot to ask earlier, but do you have relationships established
between the tables in the BE database, with enforced referential integrity (RI)?"

A2 - Definitely

Q3 - "I'm pretty much self taught as well. What parts of my replies have
seemed scary to you?"

A3 - This bit .... I don't even know what a registry flag thing is ... "Do
spend some time using JETShowPlan, using your home test lab. With this
registry flag set, use each command button on a switchboard that a user might
click on. Do some searches using your <Ctrl><F> method. Then open the
resulting Showplan.out file using your favorite text editor, and search for
the word "scan". Don't become too concerned with table scans against small
tables, as scanning may be the most efficient method in that case. However,
if the table has more than, say, 100 records or so, I'd start to get
concerned about seeing table scans. Also, keep in mind that table scans
against lookup tables, for example the rowsource for a combo box, are
perfectly fine. After all, the combo box likely displays all records in the
lookup table"

Cheers ...
--
Sue Compelling

Hi Sue,

I had called it a night after my last reply--it was after midnight for me,
so I just read your reply shown below.

Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.

Maybe, but maybe not....

I changed 2 fields on the form (which were populating using queries) into
lookups.

I think you are saying that you changed two text boxes to lookup combo
boxes. Is this correct? If so, how many records are in the row sources for
each combo box? Are you pulling only the required data for the combo box?
I've often times seen people use rowsources for combo boxes that look
something like this:

SELECT * FROM [TableName] ORDER BY [FieldName]

when all they *really* needed was one or two fields from the table involved.
However, by using the SELECT * syntax, they brought ALL fields in each record
over the wire.

Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.

I'm pretty much self taught as well. What parts of my replies have seemed
scary to you?

I think I'm supposed to use the Nz function?

The Nz function can be used to convert a null (unknown) to something else,
such as 0 (zero), or a zero length string (ZLS) --> "". A null plus anything
else is still a null, so I think you can use the idea of null concatentation
here, if the fields are truly null (and not ZLS). Try something like this (I
didn't attempt to test it):

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] + (" H: "
& Format([homephone],"(00) 000-0000")) + (" W: " &
Format([workphone],"(00) 000-0000" & ", " & [workextension]) + (" M: " &
Format([mobilephone],"(000) 000-0000"))


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.

I changed 2 fields on the form (which were populating using queries) into
lookups. On my DB it seemed to present a more transparent look for the user
though has obviously stuffed up the search.

Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.

That said Tom - while I have your attention - is it possible for you to fix
this field for me below? (happy if you want me to repost).

In most instances the 'coordinator' details that get returned will not
always have every type of contact number displayed, so in those instances I
don't want the H:, W: or M: to be shown - I think I'm supposed to use the Nz
function?


Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")

TIA

 

[Sue Compelling]

Yeah Tom - on the gaps and hyphens etc in tbs,forms, queries etc.

I created this DB when I was a real, real novice (before normalisation and
naming conventions were part of my vocab).

I haven't had any problems to date - is it just a matter of time before
something goes wrong with these and would you suggest going through and doing
a big clean?


--
Sue Compelling

Q1, Continued:

SELECT [Coordinator TAG - Locations].[Region]....

My advice is to avoid the use of special characters, such as spaces and
hyphens, in anything that you assign a name to in Access, including the names
of fields, tables, queries, forms, reports, macros, modules, and controls on
forms and reports. This Microsoft KB (Knowledge Base) article discusses this
in further detail:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Also, here is a link to a comprehensive listing of reserved words to avoid
using. This list is maintained by Access MVP Allen Browne. He also has a free
utility available that you can download, which can be used to scan your
database for the use of special characters and reserved words in field and
table names, along with several other potential issues:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

This worked well (and fast in the previous version and I'm hoping it's the
answer to my slowness

I would think that the use of your DLookups would only affect form load
time, and if the [Coordinator TAG - Locations] table only has two records,
the effect should be negligable.

A3 - This bit .... I don't even know what a registry flag thing is ...

In my Multiuser Applications article, where I mention using JETShowPlan, I
include this link, where you can read more about using this technique:
http://articles.techrepublic.com.com/5100-10878_11-5064388.html

I also include a .zip file that you can download, which has sample output
files that you can open with NotePad or WordPad and search for the word
"scan" (without the quotes). I even included two .reg files to make the
process really easy. All you need to do to set the registry flag
appropriately is to double-click the .reg file. It's not scary. Honest.
<smile>


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Tom

Responses to your questions ...

Q1 - "I think you are saying that you changed two text boxes to lookup combo
boxes. Is this correct? If so, how many records are in the row sources for
each combo box?"

A1 - Even worse - I changed 2 list boxes into text boxes with DLOOKUPS. I
have reverted back to the list box which sources it's data from a query
(which will only have 1 or 2 records). The row source: SELECT [Coordinator
TAG - Locations].[Region], [Coordinator TAG - Locations].[Location] FROM
[Coordinator TAG - Locations]; This worked well (and fast in the previous
version and I'm hoping it's the answer to my slowness"

Q2 - "I forgot to ask earlier, but do you have relationships established
between the tables in the BE database, with enforced referential integrity (RI)?"

A2 - Definitely

Q3 - "I'm pretty much self taught as well. What parts of my replies have
seemed scary to you?"

A3 - This bit .... I don't even know what a registry flag thing is ... "Do
spend some time using JETShowPlan, using your home test lab. With this
registry flag set, use each command button on a switchboard that a user might
click on. Do some searches using your <Ctrl><F> method. Then open the
resulting Showplan.out file using your favorite text editor, and search for
the word "scan". Don't become too concerned with table scans against small
tables, as scanning may be the most efficient method in that case. However,
if the table has more than, say, 100 records or so, I'd start to get
concerned about seeing table scans. Also, keep in mind that table scans
against lookup tables, for example the rowsource for a combo box, are
perfectly fine. After all, the combo box likely displays all records in the
lookup table"

Cheers ...
--
Sue Compelling

Hi Sue,

I had called it a night after my last reply--it was after midnight for me,
so I just read your reply shown below.

Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.

Maybe, but maybe not....


I changed 2 fields on the form (which were populating using queries) into
lookups.

I think you are saying that you changed two text boxes to lookup combo
boxes. Is this correct? If so, how many records are in the row sources for
each combo box? Are you pulling only the required data for the combo box?
I've often times seen people use rowsources for combo boxes that look
something like this:

SELECT * FROM [TableName] ORDER BY [FieldName]

when all they *really* needed was one or two fields from the table involved.
However, by using the SELECT * syntax, they brought ALL fields in each record
over the wire.


Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.

I'm pretty much self taught as well. What parts of my replies have seemed
scary to you?


I think I'm supposed to use the Nz function?

The Nz function can be used to convert a null (unknown) to something else,
such as 0 (zero), or a zero length string (ZLS) --> "". A null plus anything
else is still a null, so I think you can use the idea of null concatentation
here, if the fields are truly null (and not ZLS). Try something like this (I
didn't attempt to test it):

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] + (" H: "
& Format([homephone],"(00) 000-0000")) + (" W: " &
Format([workphone],"(00) 000-0000" & ", " & [workextension]) + (" M: " &
Format([mobilephone],"(000) 000-0000"))


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Aaaaaaaaaaaaaaarrrrrrrrggggg - I think you've just solved my problem.

I changed 2 fields on the form (which were populating using queries) into
lookups. On my DB it seemed to present a more transparent look for the user
though has obviously stuffed up the search.

Also - I'm a self taught relative newbie unfortunately - so some of your
explanations below have scared the bejeebuz out of me.

That said Tom - while I have your attention - is it possible for you to fix
this field for me below? (happy if you want me to repost).

In most instances the 'coordinator' details that get returned will not
always have every type of contact number displayed, so in those instances I
don't want the H:, W: or M: to be shown - I think I'm supposed to use the Nz
function?


Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")

TIA

 

[Tom Wickerath]

Hi Sue,

If you haven't had any problems to date, then you may want to leave well
enough alone. It just depends on the complexity of your database. On the
other hand, if you see opportunities to improve on the level of
normalization, then you might as well tackle the naming issues at the same
time.

If you ever need to upsize the BE database to SQL Server, you will need to
eliminate the spaces and special characters in the field and/or table names.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Sue Compelling]

Thanks for your guiding hand Tom - and I have my fingers crossed that when my
user deploys the new version tomorrow that indeed my search issues are
resolved.

Cheers

 

[James A. Fortune]

I'm pretty much self taught as well. What parts of my replies have seemed
scary to you?

I'd say the parts where you might figure out where that ham actor is
lying are the scariest . Such gall! I take that back. I suppose
that "relative newbie" is not really a lie after all given his posts.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

I'd say the parts where you might figure out where that ham actor is
lying are the scariest . Such gall! I take that back. I suppose
that "relative newbie" is not really a lie after all given his posts.

James A. Fortune
(e-mail address removed)

To clarify, I am at least 99% sure that "Sue" is the same person who
uses the alias "PC Datasheet," even if he comes up with a silly outraged
act. I believe that he has been posting in Access NG's under various
aliases for at least 10 years. And he's still a relative newbie!

In CDMA, Lyle wrote (To PC Datasheet):

"And your Aliases? They're probably real people who work in your office,
right? Just like Kyle is really my evil twin? And so you have to share
your profits with


Abbey, Abbie, Abby, Abigail, Ada, Adah, Adaline, Adam, Addie, Adela,
Adelaida, Adelaide, Adele, Adelia, Adelina, Adeline, Adell, Adella,
Adelle, Adena, Adina, Adria, Adrian, Adriana, Adriane, Adrianna,
Adrianne, Adrien, Adriene, Adrienne, Afton, Agatha, Agnes, Agnus,
Agripina, Agueda, Agustina, Ai, Aida, Aide, Aiko, Aileen, Ailene, Aimee,
Aisha, Aja, Akiko, Akilah, Alaina, Alaine, Alana, Alane, Alanna, Alayna,
Alba, Albert, Alberta, Albertha, Albertina, Albertine, Albina, Alda,
Alease, Alecia, Aleen, Aleida, Aleisha, Alejandra, Alejandrina, Alena,
Alene, Alesha, Aleshia, Alesia, Alessandra, Aleta, Aletha, Alethea,
Alethia, Alex, Alexa, Alexander, Alexandra, Alexandria, Alexia, Alexis,
Alfreda, Alfredia, Ali, Alia, Alica, Alice, Alicia, Alida, Alina, Aline,
Alisa, Alise, Alisha, Alishia, Alisia, Alison, Alissa, Alita, Alix,
Aliza, Alla, Alleen, Allegra, Allen, Allena, Allene, Allie, Alline,
Allison, Allyn, Allyson, Alma, Almeda, Almeta, Alona, Alpha, Alta,
Altagracia, Altha, Althea, Alva, Alvera, Alverta, Alvina, Alyce, Alycia,
Alysa, Alyse, Alysha, Alysia, Alyson, Alyssa, Amada, Amal, Amalia,
Amanda, Amber, Amberly, Amee, Amelia, America, Ami, Amie, Amiee, Amina,
Amira, Ammie, Amparo, Amy, An, Ana, Anabel, Analisa, Anamaria,
Anastacia, Anastasia, Andera, Andra, Andre, Andrea, Andree, Andrew,
Andria, Anette, Angel, Angela, Angele, Angelena, Angeles, Angelia,
Angelic, Angelica, Angelika, Angelina, Angeline, Angelique, Angelita,
Angella, Angelo, Angelyn, Angie, Angila, Angla, Angle, Anglea, Anh,
Anika, Anisa, Anisha, Anissa, Anita, Anitra, Anja, Anjanette, Anjelica,
Ann, Anna, Annabel, Annabell, Annabelle, Annalee, Annalisa, Annamae,
Annamaria, Annamarie, Anne, Anneliese, Annelle, Annemarie, Annett,
Annetta, Annette, Annice, Annie, Annika, Annis, Annita, Annmarie,
Anthony, Antionette, Antoinette, Antonetta, Antonette, Antonia,
Antonietta, Antonina, Antonio, Anya, Apolonia, April, Apryl, Ara,
Araceli, Aracelis, Aracely, Arcelia, Ardath, Ardelia, Ardell, Ardella,
Ardelle, Ardis, Ardith, Aretha, Argelia, Argentina, Ariana, Ariane,
Arianna, Arianne, Arica, Arie, Ariel, Arielle, Arla, Arlean, Arleen,
Arlena, Arlene, Arletha, Arletta, Arlette, Arlinda, Arline, Arlyne,
Armanda, Armandina, Armida, Arminda, Arnetta, Arnette, Arnita, Arthur,
Artie, Arvilla, Asha, Ashanti, Ashely, Ashlea, Ashlee, Ashleigh, Ashley,
Ashli, Ashlie, Ashly, Ashlyn, Ashton, Asia, Asley,
Assunta, Astrid, Asuncion, Athena, Aubrey, Audie, Audra, Audrea, Audrey,
Audria, Audrie, Audry, Augusta, Augustina, Augustine, Aundrea, Aura,
...."

"PC" hasn't finished using all the names in the list yet . Yet for
all this I think that it's still profitable for everyone if his
questions get answered.

James A. Fortune
(e-mail address removed)

 

[Sue Compelling]

James ... I'm appalled and dissappointed in your comments. I am a genuine
user of these forums and do not and have never used any alias. Unfortunately
once written these comments are here forever though if there is a way you
could retract them I'd appreciate it.

I also can't understand why anyone would want to waste their time putting in
fictitious questions under the myriad of aliases you've outlined.

With regard to profits - this work is something I generally undertake for no
to low fees (DB design is not my day job), is invariably for not-for-profit
orgs of low employee #

Sue Compelling

 

[Tom Wickerath]

James,

I'm at least 99.999% sure that you are sadly mistaken in your
misidentification. Why do I say this? Take, for instance, this post made by
Sue Compelling:

http://groups.google.com/group/microsoft.public.access.forms/msg/cec3a1249a53c9c9?dmode=source

which shows the following header information:

Thread-Topic: Inserting Date/Time stamps
X-WBNR-Posting-Host: 222.155.104.54
From: =?Utf-8?B?U3VlIENvbXBlbGxpbmc=?=
<[email protected]>
Subject: Re: Inserting Date/Time stamps
Date: Tue, 17 Oct 2006 14:32:01 -0700

Looking up the IP address, 222.155.104.54, in Melissa Data, we see that this
person posted from New Zealand:

http://www.melissadata.com/Lookups/iplocation.asp?ipaddress=222.155.104.54

This message was also posted from New Zealand
http://groups.google.com/group/microsoft.public.access/msg/0022416fa8135c2f?dmode=source

http://www.melissadata.com/Lookups/iplocation.asp?ipaddress=222.153.14.1

I think you owe Sue a very public apology!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

To clarify, I am at least 99% sure that "Sue" is the same person who
uses the alias "PC Datasheet,"....

<snip>

 

[Sue Compelling]

Thanks so much Tom - from a bewildered and slight battered non-alias Sue