SQL Backend is way too slow

There was another thread on this but it went a different direction and
focused on only one item that ended up not being the problem and responses
stopped. So I'm re-posting with hopes that someone can help me find the
problem with this db.

Ac'07 in '03 format frontend, SQL backend. I developed the BE in Ac then
upsized to an existing SQL db on which a SQL developer said there were "a few
anomalies but I was able to fix them in SQL" (referring to some indexes and
relationships didn't upsize correctly).

Before the upsize, the frontend worked terrific linked to an Ac BE. After the
upsize, the main form for the app takes 30 secs to load and 10 secs to
navigate from record to record.

The SQL BE is located on the WAN but so was the Ac BE. We have more than
sufficient bandwidth.

I have followed the suggestions of others (either through previous threads or
direct posts) and:
1. changed all Control Sources to queries (rather than tables)
2. removed DLookup from several of the queries, replacing it with a right
join table criteria (without this change many of the queries took 5 minutes
to run, now they are less than 1 sec)
3. replaced several queries with stored procedures

When I run the database analyzer, two types of issues are found:
1. It "Recommends" Indexes for Foreign Keys of some of the tables. However, I
have verified through SQL Manager Studio that the indexes do exist on all the
foreigns keys.
2. It gives an "Idea" to "Use fewer controls" for several of the forms.

Although one of the forms has quite a few controls, it isn't any slower than
a couple forms that have a little amount and the analyzer recommends ("Idea")
trying to reduce the number of controls on all of them.

The main form has 7 subforms but performance did not improve by deleting all
subforms and leaving just 4 bound controls on the form.

I appreciate any suggestions to speed up the performance of this app.

Thanks!

--
---
TraciAnn

Message posted via http://www.accessmonster.com

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:986ded8faa407@uwe...



The SQL BE is located on the WAN but so was the Ac BE. We have more than
sufficient bandwidth.


Usually WANS don't have enough performance for a file share back end. If you
WAN did perform well, then moving to sql server for the back end will
perform even BETTER **if**you have good designs.

I am VERY Surprise that you're able to get adequate performance over a WAN
with
a file share back end.


direct posts) and:
1. changed all Control Sources to queries (rather than tables)

The above a general rule doesn't make any difference. In fact, often I find
changing the source of a combo box to a query from a table actually makes it
runs slower (by quite a bit in fact!!). So if you have a linked table or
view
on the SQL server side, then for the data source of the combo box don't use
any SQL at all, but just put in linked table name or link view
name. This runs BETTER then using a query in the front end built with
query builder. The reason why this works better as you reduce one layer of
complexitry here. With a query you load sql, process sql, link to sql server
and then data comes down. If you just place the name of the linked table or
(or linked view) in the combo box, you'll find it runs a lot more snappy.
This is not a huge deal one way or the other, but just simply out of the
blue to changing control sources to queries will not necessarily speed up
your application at all. And, often in this case, you find it runs slower
then just using the table or view name as the source without any sql at
all.

2. removed DLookup from several of the queries, replacing it with a right
join table criteria (without this change many of the queries took 5
minutes

Excellent! We been complaining about people using Dlookups here for years.
In fact you can use dlookup() to get values from sql server, and you can
even
place/use a dlookup() as the source for a contorl on a form.

However, using dlookups() in a query is as slow as a turtle. It will kill
your performance. In fact dlookup() is slow EVEN when you do this in
ms-access without sql server. So, it just a bad choice. However, when you
not running a multi user system, often the performance is adequate, so
people
use it (so at the end of the day, it's not like this is something evil thing
here, it's just a performance hit that you can often take when you're
building
a desktop application that is not networked).


What people don't often realize is that MS access is very forgiving for poor
designs. Often we do a lot of things that experienced developers would NEVER
attempt to do it all. For this reason MS access often gets a bad reputation
as being a poor performing product. In fact it's not the product, it the
way we often do things. To be fair to MS access makes it very easy to do
things the wrong way. In other development platforms often developers don't
get in trouble because they don't have that kind of flexibility that we have
an access to do things the wrong way. Advanced software developers call this
concept social engineering thus they desing their software in such a way
that
users can not do stupid things. So, we might take away the clutch in a car
and give everyone automatic transmissions - presto no more burnt out
clutches anymore!


3. replaced several queries with stored procedures


Again replacing a query with a store procedure won't necessarily help.
However using views with aggruate functions, groping by, or
actually doing summary type totals in which many records are involved in
the totals, but the amount of data (records) returned back is very small,
then
significant savings can be had by using a view or stored procedure. I
useally prefer views, because write t-sql code on sql in a procedure lanuge
in in what amounts to a simple sql select makes no sense at all. use views
fot his. We don't write vba + sql code for a simple select, we biult a
query in the quqery biulder. The same goes for sql server, most cases
views are a better choice.

If you have some processing routines that take advantage of parameters and
need to update data, then by all means do use procedural code on SQL server
side as this can result in big gains in performance. (data is updated on
the server...and never even travles down the wire to your local machine).


When I run the database analyzer, two types of issues are found:
1. It "Recommends" Indexes for Foreign Keys of some of the tables.
However, I
have verified through SQL Manager Studio that the indexes do exist on all
the
foreigns keys.

I don't think the database analyzers has ever given anybody anywhere on the
planet earth a useful piece of advice in the last fifteen years. If you been
using the database analyzer for years and years and it's been giving you
great advice, then all the power to you. Maybe you can tell me some stores
and experiences you've had with using the analyzer over the lastmany years.
I can't think of when it's helped me, and furthermore it really does not
apply to sql server anyway.


The main form has 7 subforms but performance did not improve by deleting
all
subforms and leaving just 4 bound controls on the form.


Right, but I bet removing all the sub forms will dramatically increase the
performance. Again, loading firstname, and then loading first name +
lastname into two contorls might talk half the time, but that time is 1/2 of
a 10,000 of a second. So, you save 1/5000th of a second. That double the
speed, but that not yoru bottle nect anyway. So, again the advice and theory
sounds really good to remove a few controls, but in practice that's not the
bottleneck is it? So, sure you save time by remvign contorls. So, 1 contorl
is 10 times faster then 10 conrorls, but the whole process in total is only
1/100th of a second, so you not going to really save anything here.


I appreciate any suggestions to speed up the performance of this app.

The solution here is simply to reduce your bandwidth requirements.` I once
asked to 80 year old grandmother if it makes sense for an instant teller
machine to download everyone's account into the instant teller machine, and
THEN ask the person what account they want to work on. Why download huge
amounts of records into a form, and then ask the person what record to
work on? If a 80 year old grandmother can tell me this common sense, then
perhaps we should get rid of more developers and start hiring old
grandmothers to do our designs!! ;-)

It makes far more sense to ask the person what customer account number, or
whatever to work on, and then load up the form to the one record. Simply
throwing up a form bound to a large table is a formula for disaster and poor
performance.

As you've seen, even without SQL server, MS access can perform quite well,
with SQL server you can get even more performance. However the trick here
again is not some technology, but simply having patience and taking the time
to build designs in which you limit the amount of information that's
transferred over the wire into your forms.

In the case of having 3-5 sub forms I would place some of those
sub forms behind tabs. Then, you can have those sub forms load
ONLY when you actually click on that tab. Furthrmoe, those sub
forms should then not take long to load since they are loading
ONLY reocrds related to the main form.

I have a series of screen shots and I talk about reducing as bandwidth by
building screens that prompt the user for what they want in the following
little article of mine:

http://www.members.shaw.ca/AlbertKal...rch/index.html

Here is few more tips:

As mentioned watch your combo boxes, a combo box is good for maybe about
100 records, after that you're simply being rude to your users and forcing
them to scroll through hundreds of records is just torture. So you are not
only torturing your users, but also dragging hundreds of records across the
network. If the combo boxes is for some kind of customer name selection,
then place a button on the form, launch another form that allows them to
search for the customer. They then select the customer, and then return back
to the form. A typical form can maybe handle one or two combo boxes
linked to sql server. After that, your form load times will start to go
increase too much.

another combo box solution is if the list of options is
static, then place the table list of options into the front end of the
application, not on the sql server side. This approach is not
always possible for information that changes and is updated all the time,
but it is possible for quite a few number of lists that are static in
nature.

Also as I mentioned don't use a query for the source of a combo box, use
the direct name of the view or linked SQL server table. So, no sql at
all in the combo source, JUST the name of the table view. You will find
they load far more snappy when you do this.

As mentioned, as a general rule, you don't want to allow any type of
navigation in your main forms. That means you can remove the navigation
options on the bottom of the screen and reduce further clutter. By the
way this advice applies even when you're not doing SQL server. I mean if you
ask a person to load one record into a form, even without SQL server, only
the one record gets loaded into the form (access will only pull the one
record down the network write -- how can that be slow?) This approach
means your form will load as fast when the table has ten records, or 500,000
records.

As mentioned, for sub forms, place them behind tabs on your form, and don't
load the sub form at all until the person clicks on the tab.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Albert,

First of all...THANK YOU! This is excellent information. You took a lot of
valuable time to provide very helpful advice to a novice and it is much
appreciated.

I am VERY Surprise that you're able to get adequate performance over a WAN
with a file share back end.

I understand the skepticism. My statement of "adequate performance" is based
on the performance of other applications (including mine) that function quite
well over the WAN.

on the SQL server side, then for the data source of the combo box don't use
any SQL at all, but just put in linked table name or link view name.

This is obscure to me. To my (novice) knowledge It is the SQL statement that
provides the visible columns to the User allowing them to select "John Smith"
to insert an otherwise ambiguous ID "123456". How can I choose just a couple
user friendly columns from a table and insert the Foreign Key of their
selected row using just a table name?

significant savings can be had by using a view or stored procedure. I
useally prefer views... The same goes for sql server, most cases
views are a better choice.

I agree and I'm in the process of identifying the sprocs to be converted to
views. I am at a disadvantage though: due to my unfamiliarity with SQL Server
Manager Studio and SQL in general, I have to submit my requests to a part-
time (only available in the evenings) developer who builds them, then I test
them the next day...very slow process. Can you recommend a primer/link/book
that could get me rolling on building the basic objects in SQL?


The main form has 7 subforms but performance did not improve by deleting
all
subforms and leaving just 4 bound controls on the form.

Right, but I bet removing all the sub forms will dramatically increase the
performance.

Actually, as stated above, deleting (removing) all 7 subforms from the main
form did not improve (increase) performance. On the other hand, I agree with
your logic and the main emphasis of your reply: DO NOT LOAD UNNECESSARY
RECORDS. (I have more questions on this below).

throwing up a form bound to a large table is a formula for disaster and poor
performance.

I agree...but it is the only method I currently know. Hopefully, you are
about to change that

In the case of having 3-5 sub forms I would place some of those
sub forms behind tabs.

I forgot to mention in my original post that all subforms are on tabs. Only 1
of the 7 subforms load when the mainform loads.

I have a series of screen shots and I talk about reducing as bandwidth by
building screens that prompt the user for what they want in the following
little article of mine:

http://www.members.shaw.ca/AlbertKal...rch/index.html

This is where I need your help. I understand the concept but I need to break
down the concept in steps. Your very well written article is written to a
more experienced audience than me. I believe in order to implement your
concept I will need to create a form with objects who's values will be passed
as arguments to a query and then return the results of the query back to the
form; allowing a second argument (drilldown) to be passed, returning the
record that matches the final criteria to a separate form (main form).

I can build the UI but I have never created code to pass arguments. My coding
has been limited, more or less, to changing control properties and "do
commands".

Assuming my understanding is correct AND If you are willing to help, my
specific need would be:
frmContact (Main form that displays all the information and subforms)
- Opens when the mdb is opened
- Current Control Source is a local query with intentions of creating a view
- Currently uses a combo box in the form header to select from a queried list
of names
- User desires to search for records primarily on UserName (PK text) but
would occassionally search on Last/First names.

Next Steps?

Albert, Thank you again for all the time you have already put into explaining
your concepts to me. If that is all you have the time to do, I understand.

Sincerely,

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/1

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:987a7b6daf040@uwe...
on the SQL server side, then for the data source of the combo box don't
use
any SQL at all, but just put in linked table name or link view name.

This is obscure to me. To my (novice) knowledge It is the SQL statement
that
provides the visible columns to the User allowing them to select "John
Smith"
to insert an otherwise ambiguous ID "123456". How can I choose just a
couple
user friendly columns from a table and insert the Foreign Key of their
selected row using just a table name?

If you read closely, I was saying to use a table or a view. I mean looking
at my applications, I have a significant amount of combo boxes that are
driven from tables that provide a list of options for the combo box. for
example:

tblAddSource:

ID AdSource
1 Radio
2 Newspaper
3 Coupons
4 Past Customer
5 Posters
6 Mailings
7 Tourguide
8 Promotion
9 Groups
10 Internet
11 Referred
12 Sex Show
13 On-Line Booking
14 Drivers

So the above is often how many combo boxes are driven. You have a table with
two columns. The combo box will store the "ID", but display the 2nd column.

Here is another table:

tblHowPaid
Code PayType
A Amex
C Cash
CH Cheque
D Debit
DR Direct Deposit
M Master Card
P Promo
PV PV
R Refund
S Sponsor
V Visa


Again the table has two columns, and again the combo box stores the 1st
column, but displays the second text description column. In my experience a
good number of combo boxes are thus based on a table with two columns. So in
the above case Obviously then you can base the combo box on a direct table
without any SQL in combo boxes as the data source and you have your two
columns.

How can I choose just a couple
user friendly columns from a table and insert the Foreign Key of their
selected row using just a table name?

For the case that a combo boxes is to be driven on from a table with many
columns, again often your are storing the id, but want to display a text
column. In this case then you use a view in place of the table. A view
on sql server is just like a saved select query in ms-access. I mean it's
kind of obvious that if your table has many columns, and you only need to
display two columns, then you have to use my suggestion to base the combo
box on a view (I didn't think this quite needed to be pointed out).

So what I'm saying is based the combo box directly on the table. Obviously
if the table has many columns and you only want the data source to have two
columns, then in these cases you'll have to use a view. So, either way, the
source of the combo box is going to be JUST a table name, or JUST a view
name....NO sql for that rowsource. Again an obvious conclusion here is that
if you're using the table name, then you'll not be able to sort the data in
the combo box. On the other hand if it's just for list 5 choices then
sorting the combo box is not required and likely not a big deal. And it if
sorting is needed, then use a view with a sort option.

So, all I'm saying is that if you can base the combo box directly on
a table or view (we are talking about a linked table or view here),
then you find the combo box performs better then placing a query or
sql in the combo box data rowsource. You can also write code to load
up a combo box to save even more time, but that becomes too much
coding effort in My humble opinion.

So, I do the table/view trick first. If the combo boxes are still slowing
down form load time, then
one can resort to doing some coding here. Again I assume it's it doesn't
need to be pointed out that you want to test if in fact it is the combo
boxes that are slowing down the forms load time. You don't want to run off
and start writing code or creating a bunch of views when you haven't
determined that the slow form load time is due to combo boxes. I find a form
can withstand usually about 2-3 normal combo boxes without any work at all.
If you have 4-5 or several combo's that are fairly large, then using the
table/view trick will often speed things up to an acceptable level. And,
last resort is using coding to load up those combo boxes. So the trick here
is to do the minimal amount of work, and only the work you need to get
things up to an acceptable level (because people time and developer time is
very expensive these days). So I think it's a good idea to test check if the
combos is are in fact slowing down the forms load time, if they're not then
don't do anything at all to them.

to
views. I am at a disadvantage though: due to my unfamiliarity with SQL
Server
Manager Studio and SQL in general, I have to submit my requests to a part-
time (only available in the evenings) developer who builds them, then I
test
them the next day...very slow process. Can you recommend a
primer/link/book
that could get me rolling on building the basic objects in SQL?

The query builder is so very similar to ms-access. I found
in just a hour or even less time you can build quires in sql server. It
really the same as doing it in ms-access. Creating store procedures is a
different issue, but creating views is simply the same as creating a query
in ms-access. So a view is really just a saved query like in ms-access, but
it is saved on the sql server side of things. It just that ms access people
don't generally call saved queries "views", but conceptually they are the
same thing.

There is a few links I used:

Convert Microsoft Access (JET SQL) to SQL Server (T-SQL) Cheatsheet
http://weblogs.sqlteam.com/jeffs/arc...heatsheet.aspx

and
http://www.teratrax.com/sql_guide/da...ata_types.html


http://support.microsoft.com/default...19&Product=acc

ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321

Some of the above links may no work due to be being fairly old..

throwing up a form bound to a large table is a formula for disaster and
poor
performance.

I agree...but it is the only method I currently know. Hopefully, you are
about to change that



I forgot to mention in my original post that all subforms are on tabs.
Only 1
of the 7 subforms load when the mainform loads.


Excellent!...then you can use code like:

Private Sub tab1_Change()

Select Case tab1

Case 1

If Me.frmItineraryView.SourceObject = "" Then
Me.frmItineraryView.SourceObject = "frmItineraryView"

Do watch our for the link master/child settings. Often you need to
set them
also. So, typical code looks like:


Me.frmOldRes.SourceObject = "frmActiveResA"
Me.frmOldRes.LinkChildFields = "ContactID"
Me.frmOldRes.LinkMasterFields = "ContactID"

me.fromOldRes.Form.RecordSource = "qryTours"



I can build the UI but I have never created code to pass arguments. My
coding
has been limited, more or less, to changing control properties and "do
commands".

Simply build search forms and "ask" the user for what account number or
persons name you want to work on. In the after update event of the text box,
simply go:

me.RecocrdSource = "select * from tblCustomers " & _
"where LastName like '" & me.SeachText & "*'"

So, it really is only one line of code to stuff the sql into a search form.
The above uses the "*" as a wild card, so if I type in

Smi

It will also match smith etc...

You can get a search form up in less time then it takes to write this post.
Very little code is needed.

I also have an example search form in my super easy word merge he

http://www.members.shaw.ca/AlbertKal.../msaccess.html

I think a search form is so easy to build, I'm not really sure if it's
helpful to look at the above example. However, it should give you some
ideas...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Albert, Thank you again for your help! I find all your information very
helpful.

Jumping right to the main task:

I forgot to mention in my original post that all subforms are on tabs.
Only 1
of the 7 subforms load when the mainform loads.

Excellent!...then you can use code like:
Private Sub tab1_Change()
Select Case tab1
Case 1
If Me.frmItineraryView.SourceObject = "" Then
Me.frmItineraryView.SourceObject = "frmItineraryView"

In Ac'07 "Tabs" are called "Pages" and Pages do not have Change events. The
only events they have a Click, Dbl Click, MouseDwn, MouseUp, and MouseMove.
Am I looking at the wrong thing?

Also, I believe you lead me into the code assuming I could take it from there.
I'm pretty ignorant on this and need a little more information beyond the
assumptions (sorry). I have seen "Case" used in VBA before but haven't been
able to figure out how/why it is used other than to list multible values.
Therefore, I have no idea how to complete the Sub above.

Simply build search forms and "ask" the user for what account number or
persons name you want to work on. In the after update event of the text box,
simply go:

me.RecocrdSource = "select * from tblCustomers " & _
"where LastName like '" & me.SeachText & "*'"

I took the above literally and did exactly as you specified (using my object
names) and it works beautifully. I believe though, in order for it to work
with my main form, it either needs to be in the header of the form (hence the
"Me" in ME.RecordSource) or I need to pass the argument some how to the main
form (which I don't know how to do).

In the event I put SearchText in the Form Header what do I initially set the
Record Source of the main form to in order to prevent all the unbound
controls? Or do I initially open it Filtered or in a different view?

Thanks!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/1

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:987cc689dbe18@uwe...


In Ac'07 "Tabs" are called "Pages" and Pages do not have Change events.
The
only events they have a Click, Dbl Click, MouseDwn, MouseUp, and
MouseMove.
Am I looking at the wrong thing?

Yes, you looking at the wrong thing. The name of the tab control and how it
works has not change for 2007.

Nothing changed in 2007 over the last 10+ years and previous versions in
terms of the tab control.

You can use the "new" tabbed interface in 2007, but then you not using
sub-forms anymore. That new interface simple means that each new form
appears as a tab, but we talking about the tab control from the toolbox
being placed on a form in design mode.

So, we are talking about a tab control ON A FORM!

We are taking about loading ONE FORM with a tab control. And behind each tab
is a sub-form. The code as posed is correct, and the tab control on a
form has the "change" event that fires when you click on a tab. Thus, the
example code as posted is correct and is actually code I using in production
applications. So, yes..it works just fine.

In the event I put SearchText in the Form Header what do I initially set
the
Record Source of the main form to in order to prevent all the unbound
controls? Or do I initially open it Filtered or in a different view?


It is likely best to have it set to nothing so no records are loaded.

As mentioned there is a sample search form in the word merge download of
mine. You can use that as a starting point for your needs.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Albert,

You are right, the Search function is VERY EASY. And is working well with it
entered in the main form header. With one exception, when the form is loaded
all text boxes are filled yellow and Have "Name?" in each one, how can I get
rid of this?

Also, I am having quite a bit of trouble with the VBA on "Select Case". As
mentioned, I have never used Case syntax. The example you pointed me to
("Super Easy Word Merge") doesn't have an example of using Select Case. Your
help is appreciated.

Here is what I have in the Change Event of my tab control (TabUserInfo):
============================
Private Sub TabUserInfo_Change()
On Error GoTo Err_TabUserInfo_Change

Select Case TabUserInfo

Case 1
If Me.fsubCommunication.SourceObject = "" Then
Me.fsubCommunication.SourceObject = "fsubCommunication"
Me.fsubCommunication.LinkChildFields = "UserName"
Me.fsubCommunication.LinkMasterFields = "UserName"
Me.fsubCommunication.Form.RecordSource = "qryFormfsubCommunication"

Case 2
If Me.fsubUserTraining.SourceObject = "" Then
Me.fsubUserTraining.SourceObject = "fsubUserTraining"
Me.fsubUserTraining.LinkChildFields = "UserName"
Me.fsubUserTraining.LinkMasterFields = "UserName"
Me.fsubUserTraining.Form.RecordSource = "qryFormfsubUserTraining"

Case 3
If Me.fsubRequestLocation.SourceObject = "" Then
Me.fsubRequestLocation.SourceObject = "fsubRequestLocation"
Me.fsubRequestLocation.LinkChildFields = "UserName"
Me.fsubRequestLocation.LinkMasterFields = "UserName"
Me.fsubRequestLocation.Form.RecordSource =
"qryFormfsubRequestLocation"

Case 4
If Me.fsubTravelProfile.SourceObject = "" Then
Me.fsubTravelProfile.SourceObject = "fsubTravelProfile"
Me.fsubTravelProfile.LinkChildFields = "UserName"
Me.fsubTravelProfile.LinkMasterFields = "UserName"
Me.fsubTravelProfile.Form.RecordSource = "dbo_TravelProfile"

Case 5
If Me.fsubEmergencyContact.SourceObject = "" Then
Me.fsubEmergencyContact.SourceObject = "fsubEmergencyContact"
Me.fsubEmergencyContact.LinkChildFields = "UserName"
Me.fsubEmergencyContact.LinkMasterFields = "UserName"
Me.fsubEmergencyContact.Form.RecordSource = "dbo_EmergencyContact"

End Select

Exit_TabUserInfo_Change:
Exit Sub

Err_TabUserInfo_Change:
MsgBox Err.Description
Resume Exit_TabUserInfo_Change

End Sub
============================
As you can see, I entered the information for each tab under the next
sequential "Case". But the subforms do not load and I get an error saying
"Case without Select Case".

Again, thanks for your help Albert!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/1

Hi TraciAnn -

As you can see, I entered the information for each tab under the next
sequential "Case". But the subforms do not load and I get an error
saying
"Case without Select Case".

You're getting there ... you have:

=======
Select Case TabUserInfo

Case 1
If Me.fsubCommunication.SourceObject = "" Then
Me.fsubCommunication.SourceObject = "fsubCommunication"
Me.fsubCommunication.LinkChildFields = "UserName"
Me.fsubCommunication.LinkMasterFields = "UserName"
Me.fsubCommunication.Form.RecordSource =
"qryFormfsubCommunication"


Case 2
If Me.fsubUserTraining.SourceObject = "" Then
(etc)

========

change it to:

Select Case TabUserInfo


Case 1
If Me.fsubCommunication.SourceObject = "" Then
Me.fsubCommunication.SourceObject = "fsubCommunication"
Me.fsubCommunication.LinkChildFields = "UserName"
Me.fsubCommunication.LinkMasterFields = "UserName"
Me.fsubCommunication.Form.RecordSource =
"qryFormfsubCommunication"
End If


Case 2
If Me.fsubUserTraining.SourceObject = "" Then
(code)
End If


(etc.)


Basically I just skimmed through the thread and when I saw the missing
End If I quit looking .... if that doesn't get you on your way post
back.


--
Clif



PS: Are you getting Alberts samples figured out? I've noticed he
approaches problem solving with Access somewhat differently than I (and
many others) do .... and I find that I like what I've taken the time to
study and understand!

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:98aa8e9654ec0@uwe...
Albert,

You are right, the Search function is VERY EASY. And is working well
with it
entered in the main form header. With one exception, when the form is
loaded
all text boxes are filled yellow and Have "Name?" in each one, how can
I get
rid of this?

Also, I am having quite a bit of trouble with the VBA on "Select
Case". As
mentioned, I have never used Case syntax. The example you pointed me
to
("Super Easy Word Merge") doesn't have an example of using Select
Case. Your
help is appreciated.

Here is what I have in the Change Event of my tab control
(TabUserInfo):
============================
Private Sub TabUserInfo_Change()
On Error GoTo Err_TabUserInfo_Change

Select Case TabUserInfo

Case 1
If Me.fsubCommunication.SourceObject = "" Then
Me.fsubCommunication.SourceObject = "fsubCommunication"
Me.fsubCommunication.LinkChildFields = "UserName"
Me.fsubCommunication.LinkMasterFields = "UserName"
Me.fsubCommunication.Form.RecordSource =
"qryFormfsubCommunication"

Case 2
If Me.fsubUserTraining.SourceObject = "" Then
Me.fsubUserTraining.SourceObject = "fsubUserTraining"
Me.fsubUserTraining.LinkChildFields = "UserName"
Me.fsubUserTraining.LinkMasterFields = "UserName"
Me.fsubUserTraining.Form.RecordSource =
"qryFormfsubUserTraining"

Case 3
If Me.fsubRequestLocation.SourceObject = "" Then
Me.fsubRequestLocation.SourceObject = "fsubRequestLocation"
Me.fsubRequestLocation.LinkChildFields = "UserName"
Me.fsubRequestLocation.LinkMasterFields = "UserName"
Me.fsubRequestLocation.Form.RecordSource =
"qryFormfsubRequestLocation"

Case 4
If Me.fsubTravelProfile.SourceObject = "" Then
Me.fsubTravelProfile.SourceObject = "fsubTravelProfile"
Me.fsubTravelProfile.LinkChildFields = "UserName"
Me.fsubTravelProfile.LinkMasterFields = "UserName"
Me.fsubTravelProfile.Form.RecordSource = "dbo_TravelProfile"

Case 5
If Me.fsubEmergencyContact.SourceObject = "" Then
Me.fsubEmergencyContact.SourceObject = "fsubEmergencyContact"
Me.fsubEmergencyContact.LinkChildFields = "UserName"
Me.fsubEmergencyContact.LinkMasterFields = "UserName"
Me.fsubEmergencyContact.Form.RecordSource =
"dbo_EmergencyContact"

End Select

Exit_TabUserInfo_Change:
Exit Sub

Err_TabUserInfo_Change:
MsgBox Err.Description
Resume Exit_TabUserInfo_Change

End Sub
============================
As you can see, I entered the information for each tab under the next
sequential "Case". But the subforms do not load and I get an error
saying
"Case without Select Case".

Again, thanks for your help Albert!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/1




--
Clif

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:98aa8e9654ec0@uwe...
Albert,

You are right, the Search function is VERY EASY. And is working well with
it
entered in the main form header. With one exception, when the form is
loaded
all text boxes are filled yellow and Have "Name?" in each one, how can I
get
rid of this?

There is several ways to accomplish this.

On the forms load event, you can go:

Me.Detail.Visible = False

The above will simply turn off any control and visibility of anything in the
form's detail part. I am assuming that your little search text boxes are in
the heading part of the form (otherwise the above won't work). You then in
the after update part of the search text boxes go:

Me.Detail.Visible = true


Another way is to simply set the form's data source to a query that returns
no records.

eg:

me.RecordSource = "select * from tblCustomers where CustomerID = 0"

(there is no customer with id = 0)


Here is what I have in the Change Event of my tab control (TabUserInfo):

I think cliff kindly pointed out the problem....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Clif and Albert,

Thank you so much!!!

It is up and running and sooooo much faster

You guys are wonderful!

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200907/1