Forms bound to SQL Server views

[Marek Ropski]

If I have a form bound to a 'local table query' eg a table of contacts with
first and last names, plus a calculated field for the full name:

SELECT ConID, ConFirst, ConLast, ([ConFirst] & [ConLast]) AS ConFull FROM
tblContact

I place all 4 fields as textboxes on the form and after changing 'Smitt' to
'Smith' if I move my cursor to txtFirstName, I find that the full name
changes automatically to reflect the new surname although I have not saved
the record, nor requeried.

Big deal. That's what any Access beginner knows. However if I move the
data to SQL Server and replace the query with a SQL view I no longer get
this behaviour. So now after editing, the textbox shows First='Paul',
Last='Smith', Full='Paul Smitt' and that is obviously silly.

This is not a big surprise since I realize I have retrieved a record from
the server and it has not yet been saved. However, I am surprised to find I
am a bit stuck for a standard solution. I do not want to save the record,
but I would like the changes in one field to be reflected in the other.

What do people do in this pretty standard situation? Thanks for any advice
/ experience you have with this.

[Rick Brandt]

Marek Ropski wrote:

If I have a form bound to a 'local table query' eg a table of
contacts with first and last names, plus a calculated field for the
full name:
SELECT ConID, ConFirst, ConLast, ([ConFirst] & [ConLast]) AS ConFull
FROM tblContact

I place all 4 fields as textboxes on the form and after changing
'Smitt' to 'Smith' if I move my cursor to txtFirstName, I find that
the full name changes automatically to reflect the new surname
although I have not saved the record, nor requeried.

Big deal. That's what any Access beginner knows. However if I move
the data to SQL Server and replace the query with a SQL view I no
longer get this behaviour. So now after editing, the textbox shows
First='Paul', Last='Smith', Full='Paul Smitt' and that is obviously
silly.
This is not a big surprise since I realize I have retrieved a record
from the server and it has not yet been saved. However, I am
surprised to find I am a bit stuck for a standard solution. I do not
want to save the record, but I would like the changes in one field to
be reflected in the other.
What do people do in this pretty standard situation? Thanks for any
advice / experience you have with this.

If the concatenated expression was being executed on the form itself instead of
in the view then it would update. Just use a TextBox with a ControlSource of..

= [ConFirst] & " " & [ConLast]

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Marek Ropski]

"Rick Brandt" <ri*********@ho tmail.com> wrote in message
news:8E******** ********@newssv r12.news.prodig y.com...

Marek Ropski wrote:

If I have a form bound to a 'local table query' eg a table of
contacts with first and last names, plus a calculated field for the
full name:
SELECT ConID, ConFirst, ConLast, ([ConFirst] & [ConLast]) AS ConFull
FROM tblContact

I place all 4 fields as textboxes on the form and after changing
'Smitt' to 'Smith' if I move my cursor to txtFirstName, I find that
the full name changes automatically to reflect the new surname
although I have not saved the record, nor requeried.

Big deal. That's what any Access beginner knows. However if I move
the data to SQL Server and replace the query with a SQL view I no
longer get this behaviour. So now after editing, the textbox shows
First='Paul', Last='Smith', Full='Paul Smitt' and that is obviously
silly.
This is not a big surprise since I realize I have retrieved a record
from the server and it has not yet been saved. However, I am
surprised to find I am a bit stuck for a standard solution. I do not
want to save the record, but I would like the changes in one field to
be reflected in the other.
What do people do in this pretty standard situation? Thanks for any
advice / experience you have with this.

If the concatenated expression was being executed on the form itself
instead of in the view then it would update. Just use a TextBox with a
ControlSource of..

= [ConFirst] & " " & [ConLast]

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Hi Rick
Thank you for the suggestion. The only drawback with this is that I could
not then use it for a standard search - eg put my cursor in the textbox and
search for names beginning with 'Paul S' - and this is a key textbox which
is used for searching.
I may have to change the controlsource dynamically if a record gets edited,
but the all-Access behaviour is ideally what I would like to mimic.

[Rick Brandt]

Marek Ropski wrote:

Hi Rick
Thank you for the suggestion. The only drawback with this is that I
could not then use it for a standard search - eg put my cursor in the
textbox and search for names beginning with 'Paul S' - and this is a
key textbox which is used for searching.
I may have to change the controlsource dynamically if a record gets
edited, but the all-Access behaviour is ideally what I would like to
mimic.

I would provide a method that doesn't rely on the built in "Find" tool. In
the first place "Find" is terribly inefficient againnst an ODBC linked
source as it basically forces a scan of the entire table being searched.
Second since you are applying the search to a calculated field in the view,
then it is even more inefficient. Searches and joins should always go
against raw table fields with indexes on them.

When you move to a Server back end you pretty much have to abandon the built
in search utilities. They are thrown in there for the desktop user running
a small (local) database. For a server back end you have to move to using
SQL solutons as much as possible and get the server to do the work.

Anyway...if you do Refresh against the form you should get the current field
to update.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Marek Ropski]

"Rick Brandt" <ri*********@ho tmail.com> wrote in message
news:DE******** ********@newssv r11.news.prodig y.com...

Marek Ropski wrote:

Hi Rick
Thank you for the suggestion. The only drawback with this is that I
could not then use it for a standard search - eg put my cursor in the
textbox and search for names beginning with 'Paul S' - and this is a
key textbox which is used for searching.
I may have to change the controlsource dynamically if a record gets
edited, but the all-Access behaviour is ideally what I would like to
mimic.

I would provide a method that doesn't rely on the built in "Find" tool.
In
the first place "Find" is terribly inefficient againnst an ODBC linked
source as it basically forces a scan of the entire table being searched.
Second since you are applying the search to a calculated field in the
view,
then it is even more inefficient. Searches and joins should always go
against raw table fields with indexes on them.

When you move to a Server back end you pretty much have to abandon the
built
in search utilities. They are thrown in there for the desktop user
running
a small (local) database. For a server back end you have to move to using
SQL solutons as much as possible and get the server to do the work.

Anyway...if you do Refresh against the form you should get the current
field
to update.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Hi Rick
Thanks for those suggestions which do, of course, make sense. In fact, I
simplified the situation to prevent potential readers from being bored rigid
with the actual details. I do have a pretty good idea of how slow the
built-in search functions are with linked tables as it is currently up and
working - but these are only used for 'secondary searching'. So normally to
find a record people use my custom search box which is fast, but I have told
the users that when their search returns a number of records (say about 100)
from the server they can also use these buttons and it isn't too bad on this
small subset. They can actually use my search form to return all 30,000
records and then search on, say, a phone number field and this might take 20
secs - but all my custom searching is almost instant. I could remove the
standard search functionality or write a lot more code to cater for fields
they'll almost never search on - but for the moment everybody seems happy.
Anyway, returning to the immediate problem - using Me.Refresh saves the
record so I can't quite use that, but thanks for the suggestion. Any other
ideas (Rick or anyone else)?

[Rick Brandt]

Marek Ropski wrote:

[snip] Anyway, returning to the immediate
problem - using Me.Refresh saves the record so I can't quite use
that, but thanks for the suggestion. Any other ideas (Rick or anyone
else)?

Well the record would have to be saved before "Find" would let the user move
to a different record so I don't see how that's a problem.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Marek Ropski]

"Rick Brandt" <ri*********@ho tmail.com> wrote in message
news:FF******** *********@newss vr12.news.prodi gy.com...

Marek Ropski wrote:

[snip] Anyway, returning to the immediate
problem - using Me.Refresh saves the record so I can't quite use
that, but thanks for the suggestion. Any other ideas (Rick or anyone
else)?

Well the record would have to be saved before "Find" would let the user
move
to a different record so I don't see how that's a problem.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Just that I need the full name to be correct after editing the first or last
name - and at this point I may not have finished editing yet. With a local
table the full name is updated but the record is not yet saved so I can
still undo all my changes.
I am considering at the back of my mind the possibility that I will have to
abandon hope of replicating this 'local table' behaviour and perhaps try
something quite different. But in the meantime if any idea occurs...
Thank you for your continued patience.