update queries and forms

[Mary Pode]

Hi

I am trying to build a "front end" to an application and am having some
difficulty. Here's my problem:

I would basically like to be able to do via a form what an update query does:

I work in insurance and I have a table that has all the information about
the insured accounts in it and each account has it's own unique identifier.
I have a second table with all the policy information in it. A third table
has all the information about the locations insured for each account, and
each location also has a unique identifier.

I would like to have a form that displays all the basic information about
the insured account with some additional boxes that I can fill out to update
information in the underlying location table.

eg, say I have an account that has locations all over the US. I would like
to be able to find that account and then fill out additional boxes that would
update the deductible values at all CA locations to 5%. So in an update
query the criteria would be CA and the update to field would be 0.05.

I have tried so many things and just can't get this seemingly basic idea to
work as part of a form.

Any help would be greatly appreciated.

 

[Ken Snell [MVP]]

What you seek to do can be done within a form, using unbound controls on the
form and using programming behind the form that, when you click a button,
will update the data as you wish.

This is not a simple thing to set up, and talking you through it will take a
bit of iterations, so get ready for a few exchanges here! ;-)

First, let's start with what you've already set up. I assume that this is a
form that is bound to a query or table that is returning the values that
you're displaying. Are you using subforms for the children data? Or are you
using separate forms that you open from this one main form? It will be
easier for your use if you use subforms.

 

[Mary Pode]

Thanks very much for your quick response.

At the moment what I have set up is a form that just shows the main details
of the account - account number, name of insured etc. This comes from a
query picking out certain information from an underlying "Accounts" table.
I don't really need to be able to see all the other information such as the
locations that are part of the account because there could be tens of
thousands of them! I take it this is what you were thinking would be on the
subform?

So what I would like are two boxes somewhere on the form - one that I would
fill out with the deductible amount to be updated eg 0.05 and the other with
the criteria eg CA for California locations only. And then a button that you
press to essentially do the same as the run button in a regular update query.

I hope this is what you wanted to know - thanks again for helping me with
this!

 

[Ken Snell [MVP]]

It appears, then, that what you want to do is simpler than I'd been
thinking.

What you want to do is to create an update query that will read the values
to be used in the query from your form.

Note: Remember that, once run, an update query cannot be undone. So you'll
need to be sure that the user means to do the update before you run it, or
else your data will be changed.

First, let's have you create a select query (a normal query) that will
select the records that you want to update. We will turn this query into an
update query after you post it -- it's easier to do it this way.

Also, what is the name of the form that you'll be using to enter the values
that will be used by the query? And what are the names of the textboxes into
which you'll type those values?

--

Ken Snell
<MS ACCESS MVP>

 

[Mary Pode]

" What you want to do is to create an update query that will read the values

to be used in the query from your form.

"

yes - this is exactly what I want to do.

I can set up a select query easily to select the locations that I want to
update - I take it this query is kept in the original query format and isn't
changed to a form view or anything?

The name of the form that I'll be using is called "FormUpdateQuoteValues"

The names of the textboxes are "FieldUpdateTo" which is where I enter the
value that I want the values to be updated to and "FieldWhere" which is where
I enter the criteria eg CA.

Thanks again

 

[Ken Snell [MVP]]

Post the SQL statement of the select query that you've created. We need to
modify it into an update query so that we can run it from your form.

 

[Mary Pode]

Hi

Is this what you wanted? This is the SQL view of my select query for one
account showing all the locations in CA for that account whose deductible I'd
like to update.


SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY,
dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM,
dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME,
dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT
FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON
dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN
dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID = dbo_eqdet_QTE05_01.LOCID)
INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US
REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US
REGIONS].STATECODE)
WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND
((dbo_loc_QTE05_01.COUNTRY)="US") AND ((dbo_loc_QTE05_01.STATECODE)="CA"));

Thanks

 

[Ken Snell [MVP]]

OK - now we need you to identify the following fields from this query:

-- the field (and table that it is in) that holds the deductible value
that is to be updated;
-- the primary key field of the table that holds the deductible field;
-- which fields correspond to the values that you're selecting on your
form so that you are "identifying" the appropriate person's records that are
to be updated.

--

Ken Snell
<MS ACCESS MVP>

Hi

Is this what you wanted? This is the SQL view of my select query for one
account showing all the locations in CA for that account whose deductible
I'd
like to update.


SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY,
dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM,
dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME,
dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT
FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON
dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN
dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID = dbo_eqdet_QTE05_01.LOCID)
INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US
REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US
REGIONS].STATECODE)
WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND
((dbo_loc_QTE05_01.COUNTRY)="US") AND
((dbo_loc_QTE05_01.STATECODE)="CA"));

Thanks

Post the SQL statement of the select query that you've created. We need
to
modify it into an update query so that we can run it from your form.

 

[Mary Pode]

Hi

The field to be updated is called SITEDEDAMT
The table it's in is called dbo_eqdet_QTE_05_01
The primary key of the table with the deductible is EQDETID
The values that corrospond to what I'm selecting on my form will be a
primary key identifier - called ACCGRPID

Thanks

OK - now we need you to identify the following fields from this query:

-- the field (and table that it is in) that holds the deductible value
that is to be updated;
-- the primary key field of the table that holds the deductible field;
-- which fields correspond to the values that you're selecting on your
form so that you are "identifying" the appropriate person's records that are
to be updated.

--

Ken Snell
<MS ACCESS MVP>

Hi

Is this what you wanted? This is the SQL view of my select query for one
account showing all the locations in CA for that account whose deductible
I'd
like to update.


SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY,
dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM,
dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME,
dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT
FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON
dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN
dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID = dbo_eqdet_QTE05_01.LOCID)
INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US
REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US
REGIONS].STATECODE)
WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND
((dbo_loc_QTE05_01.COUNTRY)="US") AND
((dbo_loc_QTE05_01.STATECODE)="CA"));

Thanks

Post the SQL statement of the select query that you've created. We need
to
modify it into an update query so that we can run it from your form.

--

Ken Snell
<MS ACCESS MVP>

" What you want to do is to create an update query that will read the
values
to be used in the query from your form.
"

yes - this is exactly what I want to do.

I can set up a select query easily to select the locations that I want
to
update - I take it this query is kept in the original query format and
isn't
changed to a form view or anything?

The name of the form that I'll be using is called
"FormUpdateQuoteValues"

The names of the textboxes are "FieldUpdateTo" which is where I enter
the
value that I want the values to be updated to and "FieldWhere" which is
where
I enter the criteria eg CA.

Thanks again

:

It appears, then, that what you want to do is simpler than I'd been
thinking.

What you want to do is to create an update query that will read the
values
to be used in the query from your form.

Note: Remember that, once run, an update query cannot be undone. So
you'll
need to be sure that the user means to do the update before you run
it,
or
else your data will be changed.

First, let's have you create a select query (a normal query) that will
select the records that you want to update. We will turn this query
into
an
update query after you post it -- it's easier to do it this way.

Also, what is the name of the form that you'll be using to enter the
values
that will be used by the query? And what are the names of the
textboxes
into
which you'll type those values?

--

Ken Snell
<MS ACCESS MVP>



Thanks very much for your quick response.

At the moment what I have set up is a form that just shows the main
details
of the account - account number, name of insured etc. This comes
from
a
query picking out certain information from an underlying "Accounts"
table.
I don't really need to be able to see all the other information such
as
the
locations that are part of the account because there could be tens
of
thousands of them! I take it this is what you were thinking would
be
on
the
subform?

So what I would like are two boxes somewhere on the form - one that
I
would
fill out with the deductible amount to be updated eg 0.05 and the
other
with
the criteria eg CA for California locations only. And then a button
that
you
press to essentially do the same as the run button in a regular
update
query.

I hope this is what you wanted to know - thanks again for helping me
with
this!



:

What you seek to do can be done within a form, using unbound
controls
on
the
form and using programming behind the form that, when you click a
button,
will update the data as you wish.

This is not a simple thing to set up, and talking you through it
will
take a
bit of iterations, so get ready for a few exchanges here! ;-)

First, let's start with what you've already set up. I assume that
this
is
a
form that is bound to a query or table that is returning the values
that
you're displaying. Are you using subforms for the children data? Or
are
you
using separate forms that you open from this one main form? It will
be
easier for your use if you use subforms.
--

Ken Snell
<MS ACCESS MVP>





Hi

I am trying to build a "front end" to an application and am
having
some
difficulty. Here's my problem:

I would basically like to be able to do via a form what an update
query
does:

I work in insurance and I have a table that has all the
information
about
the insured accounts in it and each account has it's own unique
identifier.
I have a second table with all the policy information in it. A
third
table
has all the information about the locations insured for each
account,
and
each location also has a unique identifier.

I would like to have a form that displays all the basic
information
about
the insured account with some additional boxes that I can fill
out
to
update
information in the underlying location table.

eg, say I have an account that has locations all over the US. I
would
like
to be able to find that account and then fill out additional
boxes
that
would
update the deductible values at all CA locations to 5%. So in an
update
query the criteria would be CA and the update to field would be
0.05.

I have tried so many things and just can't get this seemingly
basic
idea
to
work as part of a form.

Any help would be greatly appreciated.

 

[Ken Snell [MVP]]

ACCGRPID is or is not the same value as EQDETID? In other words, does
ACCGRPID have the value that we would use to find the matching EQDETID? If
not, what is the relationship between ACCGRPID and EQDETID?

What we're trying to do now is to identify the children record(s) that need
to be updated can be identified from what you select on your form.

--

Ken Snell
<MS ACCESS MVP>

Hi

The field to be updated is called SITEDEDAMT
The table it's in is called dbo_eqdet_QTE_05_01
The primary key of the table with the deductible is EQDETID
The values that corrospond to what I'm selecting on my form will be a
primary key identifier - called ACCGRPID

Thanks

OK - now we need you to identify the following fields from this query:

-- the field (and table that it is in) that holds the deductible
value
that is to be updated;
-- the primary key field of the table that holds the deductible
field;
-- which fields correspond to the values that you're selecting on
your
form so that you are "identifying" the appropriate person's records that
are
to be updated.

--

Ken Snell
<MS ACCESS MVP>

Hi

Is this what you wanted? This is the SQL view of my select query for
one
account showing all the locations in CA for that account whose
deductible
I'd
like to update.


SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY,
dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM,
dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME,
dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT
FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON
dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN
dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID =
dbo_eqdet_QTE05_01.LOCID)
INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US
REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US
REGIONS].STATECODE)
WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND
((dbo_loc_QTE05_01.COUNTRY)="US") AND
((dbo_loc_QTE05_01.STATECODE)="CA"));

Thanks

:

Post the SQL statement of the select query that you've created. We
need
to
modify it into an update query so that we can run it from your form.

--

Ken Snell
<MS ACCESS MVP>

" What you want to do is to create an update query that will read the
values
to be used in the query from your form.
"

yes - this is exactly what I want to do.

I can set up a select query easily to select the locations that I
want
to
update - I take it this query is kept in the original query format
and
isn't
changed to a form view or anything?

The name of the form that I'll be using is called
"FormUpdateQuoteValues"

The names of the textboxes are "FieldUpdateTo" which is where I
enter
the
value that I want the values to be updated to and "FieldWhere" which
is
where
I enter the criteria eg CA.

Thanks again

:

It appears, then, that what you want to do is simpler than I'd been
thinking.

What you want to do is to create an update query that will read the
values
to be used in the query from your form.

Note: Remember that, once run, an update query cannot be undone.
So
you'll
need to be sure that the user means to do the update before you run
it,
or
else your data will be changed.

First, let's have you create a select query (a normal query) that
will
select the records that you want to update. We will turn this query
into
an
update query after you post it -- it's easier to do it this way.

Also, what is the name of the form that you'll be using to enter
the
values
that will be used by the query? And what are the names of the
textboxes
into
which you'll type those values?

--

Ken Snell
<MS ACCESS MVP>



Thanks very much for your quick response.

At the moment what I have set up is a form that just shows the
main
details
of the account - account number, name of insured etc. This comes
from
a
query picking out certain information from an underlying
"Accounts"
table.
I don't really need to be able to see all the other information
such
as
the
locations that are part of the account because there could be
tens
of
thousands of them! I take it this is what you were thinking
would
be
on
the
subform?

So what I would like are two boxes somewhere on the form - one
that
I
would
fill out with the deductible amount to be updated eg 0.05 and the
other
with
the criteria eg CA for California locations only. And then a
button
that
you
press to essentially do the same as the run button in a regular
update
query.

I hope this is what you wanted to know - thanks again for helping
me
with
this!



:

What you seek to do can be done within a form, using unbound
controls
on
the
form and using programming behind the form that, when you click
a
button,
will update the data as you wish.

This is not a simple thing to set up, and talking you through it
will
take a
bit of iterations, so get ready for a few exchanges here! ;-)

First, let's start with what you've already set up. I assume
that
this
is
a
form that is bound to a query or table that is returning the
values
that
you're displaying. Are you using subforms for the children data?
Or
are
you
using separate forms that you open from this one main form? It
will
be
easier for your use if you use subforms.
--

Ken Snell
<MS ACCESS MVP>





message
Hi

I am trying to build a "front end" to an application and am
having
some
difficulty. Here's my problem:

I would basically like to be able to do via a form what an
update
query
does:

I work in insurance and I have a table that has all the
information
about
the insured accounts in it and each account has it's own
unique
identifier.
I have a second table with all the policy information in it.
A
third
table
has all the information about the locations insured for each
account,
and
each location also has a unique identifier.

I would like to have a form that displays all the basic
information
about
the insured account with some additional boxes that I can fill
out
to
update
information in the underlying location table.

eg, say I have an account that has locations all over the US.
I
would
like
to be able to find that account and then fill out additional
boxes
that
would
update the deductible values at all CA locations to 5%. So in
an
update
query the criteria would be CA and the update to field would
be
0.05.

I have tried so many things and just can't get this seemingly
basic
idea
to
work as part of a form.

Any help would be greatly appreciated.

 

[Mary Pode]

ACCGRPID is a unique identifier for each seperate account. Each account can
have many locations (tens of thousands sometimes). Each location has a value
EQSITEDEDAMT, which is the value to be updated for certain locations eg only
the ones in CA. This value is in a table with other information about the
locations which is not needed here. The primary key of that table is called
EQDETID.

Hope this clarifies!

ACCGRPID is or is not the same value as EQDETID? In other words, does
ACCGRPID have the value that we would use to find the matching EQDETID? If
not, what is the relationship between ACCGRPID and EQDETID?

What we're trying to do now is to identify the children record(s) that need
to be updated can be identified from what you select on your form.

--

Ken Snell
<MS ACCESS MVP>

Hi

The field to be updated is called SITEDEDAMT
The table it's in is called dbo_eqdet_QTE_05_01
The primary key of the table with the deductible is EQDETID
The values that corrospond to what I'm selecting on my form will be a
primary key identifier - called ACCGRPID

Thanks

OK - now we need you to identify the following fields from this query:

-- the field (and table that it is in) that holds the deductible
value
that is to be updated;
-- the primary key field of the table that holds the deductible
field;
-- which fields correspond to the values that you're selecting on
your
form so that you are "identifying" the appropriate person's records that
are
to be updated.

--

Ken Snell
<MS ACCESS MVP>


Hi

Is this what you wanted? This is the SQL view of my select query for
one
account showing all the locations in CA for that account whose
deductible
I'd
like to update.


SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY,
dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM,
dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME,
dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT
FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON
dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN
dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID =
dbo_eqdet_QTE05_01.LOCID)
INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US
REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US
REGIONS].STATECODE)
WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND
((dbo_loc_QTE05_01.COUNTRY)="US") AND
((dbo_loc_QTE05_01.STATECODE)="CA"));

Thanks

:

Post the SQL statement of the select query that you've created. We
need
to
modify it into an update query so that we can run it from your form.

--

Ken Snell
<MS ACCESS MVP>

" What you want to do is to create an update query that will read the
values
to be used in the query from your form.
"

yes - this is exactly what I want to do.

I can set up a select query easily to select the locations that I
want
to
update - I take it this query is kept in the original query format
and
isn't
changed to a form view or anything?

The name of the form that I'll be using is called
"FormUpdateQuoteValues"

The names of the textboxes are "FieldUpdateTo" which is where I
enter
the
value that I want the values to be updated to and "FieldWhere" which
is
where
I enter the criteria eg CA.

Thanks again

:

It appears, then, that what you want to do is simpler than I'd been
thinking.

What you want to do is to create an update query that will read the
values
to be used in the query from your form.

Note: Remember that, once run, an update query cannot be undone.
So
you'll
need to be sure that the user means to do the update before you run
it,
or
else your data will be changed.

First, let's have you create a select query (a normal query) that
will
select the records that you want to update. We will turn this query
into
an
update query after you post it -- it's easier to do it this way.

Also, what is the name of the form that you'll be using to enter
the
values
that will be used by the query? And what are the names of the
textboxes
into
which you'll type those values?

--

Ken Snell
<MS ACCESS MVP>



Thanks very much for your quick response.

At the moment what I have set up is a form that just shows the
main
details
of the account - account number, name of insured etc. This comes
from
a
query picking out certain information from an underlying
"Accounts"
table.
I don't really need to be able to see all the other information
such
as
the
locations that are part of the account because there could be
tens
of
thousands of them! I take it this is what you were thinking
would
be
on
the
subform?

So what I would like are two boxes somewhere on the form - one
that
I
would
fill out with the deductible amount to be updated eg 0.05 and the
other
with
the criteria eg CA for California locations only. And then a
button
that
you
press to essentially do the same as the run button in a regular
update
query.

I hope this is what you wanted to know - thanks again for helping
me
with
this!



:

What you seek to do can be done within a form, using unbound
controls
on
the
form and using programming behind the form that, when you click
a
button,
will update the data as you wish.

This is not a simple thing to set up, and talking you through it
will
take a
bit of iterations, so get ready for a few exchanges here! ;-)

First, let's start with what you've already set up. I assume
that
this
is
a
form that is bound to a query or table that is returning the
values
that
you're displaying. Are you using subforms for the children data?
Or
are
you
using separate forms that you open from this one main form? It
will
be
easier for your use if you use subforms.
--

Ken Snell
<MS ACCESS MVP>





message
Hi

I am trying to build a "front end" to an application and am
having
some
difficulty. Here's my problem:

I would basically like to be able to do via a form what an
update
query
does:

I work in insurance and I have a table that has all the
information
about
the insured accounts in it and each account has it's own
unique
identifier.
I have a second table with all the policy information in it.
A
third
table
has all the information about the locations insured for each
account,
and
each location also has a unique identifier.

I would like to have a form that displays all the basic
information
about
the insured account with some additional boxes that I can fill
out
to
update
information in the underlying location table.

eg, say I have an account that has locations all over the US.
I
would
like
to be able to find that account and then fill out additional
boxes
that
would
update the deductible values at all CA locations to 5%. So in
an
update
query the criteria would be CA and the update to field would
be
0.05.

I have tried so many things and just can't get this seemingly
basic
idea
to
work as part of a form.

Any help would be greatly appreciated.

 

[Ken Snell [MVP]]

Let me recap....

Each account is uniquely identified by ACCGRPID.

Each account has many locations. Each location is uniquely identified by
EQDETID. These records are in table dbo_eqdet_QTE_05_01.

You desire to update the value for the EQSITEDEDAMT field (you also called
this the SITEDEDAMT field?), which is in the dbo_eqdet_QTE_05_01 table where
each record is uniquely identified by the EQDETID value.

So, assuming that I have correctly stated the information, let me give you a
generic update query to show you how it's constructed:

UPDATE dbo_eqdet_QTE_05_01
SET EQSITEDEDAMT = "New Site DED Amt"
WHERE ACCGRPID = "The account number";

So, for example, suppose that you want to set the EQSITEDEDAMT value to 10
for all locations for account number 11223:

UPDATE dbo_eqdet_QTE_05_01
SET EQSITEDEDAMT = 10
WHERE ACCGRPID = 11223;

Now, let me take your Select query that you posted, and let's see if we can
turn it into an appropriate update query, using the values that you provided
for the various criteria and my example of 10 for SITEDEDAMT:

UPDATE dbo_accgrp_QTE05_01
SET dbo_eqdet_QTE05_01.SITEDEDAMT = 10
WHERE dbo_accgrp_QTE05_01.ACCGRPID=208 AND
dbo_loc_QTE05_01.COUNTRY="US" AND
dbo_loc_QTE05_01.STATECODE="CA";

--

Ken Snell
<MS ACCESS MVP>

ACCGRPID is a unique identifier for each seperate account. Each account
can
have many locations (tens of thousands sometimes). Each location has a
value
EQSITEDEDAMT, which is the value to be updated for certain locations eg
only
the ones in CA. This value is in a table with other information about the
locations which is not needed here. The primary key of that table is
called
EQDETID.

Hope this clarifies!

ACCGRPID is or is not the same value as EQDETID? In other words, does
ACCGRPID have the value that we would use to find the matching EQDETID?
If
not, what is the relationship between ACCGRPID and EQDETID?

What we're trying to do now is to identify the children record(s) that
need
to be updated can be identified from what you select on your form.

--

Ken Snell
<MS ACCESS MVP>

Hi

The field to be updated is called SITEDEDAMT
The table it's in is called dbo_eqdet_QTE_05_01
The primary key of the table with the deductible is EQDETID
The values that corrospond to what I'm selecting on my form will be a
primary key identifier - called ACCGRPID

Thanks


:

OK - now we need you to identify the following fields from this query:

-- the field (and table that it is in) that holds the deductible
value
that is to be updated;
-- the primary key field of the table that holds the deductible
field;
-- which fields correspond to the values that you're selecting on
your
form so that you are "identifying" the appropriate person's records
that
are
to be updated.

--

Ken Snell
<MS ACCESS MVP>


Hi

Is this what you wanted? This is the SQL view of my select query
for
one
account showing all the locations in CA for that account whose
deductible
I'd
like to update.


SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY,
dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM,
dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME,
dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT
FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON
dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN
dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID =
dbo_eqdet_QTE05_01.LOCID)
INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US
REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US
REGIONS].STATECODE)
WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND
((dbo_loc_QTE05_01.COUNTRY)="US") AND
((dbo_loc_QTE05_01.STATECODE)="CA"));

Thanks

:

Post the SQL statement of the select query that you've created. We
need
to
modify it into an update query so that we can run it from your
form.

--

Ken Snell
<MS ACCESS MVP>

" What you want to do is to create an update query that will read
the
values
to be used in the query from your form.
"

yes - this is exactly what I want to do.

I can set up a select query easily to select the locations that I
want
to
update - I take it this query is kept in the original query
format
and
isn't
changed to a form view or anything?

The name of the form that I'll be using is called
"FormUpdateQuoteValues"

The names of the textboxes are "FieldUpdateTo" which is where I
enter
the
value that I want the values to be updated to and "FieldWhere"
which
is
where
I enter the criteria eg CA.

Thanks again

:

It appears, then, that what you want to do is simpler than I'd
been
thinking.

What you want to do is to create an update query that will read
the
values
to be used in the query from your form.

Note: Remember that, once run, an update query cannot be
undone.
So
you'll
need to be sure that the user means to do the update before you
run
it,
or
else your data will be changed.

First, let's have you create a select query (a normal query)
that
will
select the records that you want to update. We will turn this
query
into
an
update query after you post it -- it's easier to do it this way.

Also, what is the name of the form that you'll be using to enter
the
values
that will be used by the query? And what are the names of the
textboxes
into
which you'll type those values?

--

Ken Snell
<MS ACCESS MVP>



message
Thanks very much for your quick response.

At the moment what I have set up is a form that just shows the
main
details
of the account - account number, name of insured etc. This
comes
from
a
query picking out certain information from an underlying
"Accounts"
table.
I don't really need to be able to see all the other
information
such
as
the
locations that are part of the account because there could be
tens
of
thousands of them! I take it this is what you were thinking
would
be
on
the
subform?

So what I would like are two boxes somewhere on the form - one
that
I
would
fill out with the deductible amount to be updated eg 0.05 and
the
other
with
the criteria eg CA for California locations only. And then a
button
that
you
press to essentially do the same as the run button in a
regular
update
query.

I hope this is what you wanted to know - thanks again for
helping
me
with
this!



:

What you seek to do can be done within a form, using unbound
controls
on
the
form and using programming behind the form that, when you
click
a
button,
will update the data as you wish.

This is not a simple thing to set up, and talking you through
it
will
take a
bit of iterations, so get ready for a few exchanges here! ;-)

First, let's start with what you've already set up. I assume
that
this
is
a
form that is bound to a query or table that is returning the
values
that
you're displaying. Are you using subforms for the children
data?
Or
are
you
using separate forms that you open from this one main form?
It
will
be
easier for your use if you use subforms.
--

Ken Snell
<MS ACCESS MVP>





message
Hi

I am trying to build a "front end" to an application and am
having
some
difficulty. Here's my problem:

I would basically like to be able to do via a form what an
update
query
does:

I work in insurance and I have a table that has all the
information
about
the insured accounts in it and each account has it's own
unique
identifier.
I have a second table with all the policy information in
it.
A
third
table
has all the information about the locations insured for
each
account,
and
each location also has a unique identifier.

I would like to have a form that displays all the basic
information
about
the insured account with some additional boxes that I can
fill
out
to
update
information in the underlying location table.

eg, say I have an account that has locations all over the
US.
I
would
like
to be able to find that account and then fill out
additional
boxes
that
would
update the deductible values at all CA locations to 5%. So
in
an
update
query the criteria would be CA and the update to field
would
be
0.05.

I have tried so many things and just can't get this
seemingly
basic
idea
to
work as part of a form.

Any help would be greatly appreciated.

 

[Mary Pode]

You're almost right with the assumptions - sorry, it's probably me not being
clear.

Everything you've said is correct except that each location is uniquely
identified by a primary key field called LOCID.

Each locations details such as address are kept in a seperate table (where
the primary key is LOCID) from where the financial details for each location
are kept. The field SITEDEDAMT is kept in the table where the financial
details are kept (dbo_eqdet_QTE_05_01). The primary key for this table is
called EQDETID. LOCID appears here as a foreign key.

Having said all this, your final example appears to do exactly what I want
it to.

Let me recap....

Each account is uniquely identified by ACCGRPID.

Each account has many locations. Each location is uniquely identified by
EQDETID. These records are in table dbo_eqdet_QTE_05_01.

You desire to update the value for the EQSITEDEDAMT field (you also called
this the SITEDEDAMT field?), which is in the dbo_eqdet_QTE_05_01 table where
each record is uniquely identified by the EQDETID value.

So, assuming that I have correctly stated the information, let me give you a
generic update query to show you how it's constructed:

UPDATE dbo_eqdet_QTE_05_01
SET EQSITEDEDAMT = "New Site DED Amt"
WHERE ACCGRPID = "The account number";

So, for example, suppose that you want to set the EQSITEDEDAMT value to 10
for all locations for account number 11223:

UPDATE dbo_eqdet_QTE_05_01
SET EQSITEDEDAMT = 10
WHERE ACCGRPID = 11223;

Now, let me take your Select query that you posted, and let's see if we can
turn it into an appropriate update query, using the values that you provided
for the various criteria and my example of 10 for SITEDEDAMT:

UPDATE dbo_accgrp_QTE05_01
SET dbo_eqdet_QTE05_01.SITEDEDAMT = 10
WHERE dbo_accgrp_QTE05_01.ACCGRPID=208 AND
dbo_loc_QTE05_01.COUNTRY="US" AND
dbo_loc_QTE05_01.STATECODE="CA";

--

Ken Snell
<MS ACCESS MVP>

ACCGRPID is a unique identifier for each seperate account. Each account
can
have many locations (tens of thousands sometimes). Each location has a
value
EQSITEDEDAMT, which is the value to be updated for certain locations eg
only
the ones in CA. This value is in a table with other information about the
locations which is not needed here. The primary key of that table is
called
EQDETID.

Hope this clarifies!

ACCGRPID is or is not the same value as EQDETID? In other words, does
ACCGRPID have the value that we would use to find the matching EQDETID?
If
not, what is the relationship between ACCGRPID and EQDETID?

What we're trying to do now is to identify the children record(s) that
need
to be updated can be identified from what you select on your form.

--

Ken Snell
<MS ACCESS MVP>


Hi

The field to be updated is called SITEDEDAMT
The table it's in is called dbo_eqdet_QTE_05_01
The primary key of the table with the deductible is EQDETID
The values that corrospond to what I'm selecting on my form will be a
primary key identifier - called ACCGRPID

Thanks


:

OK - now we need you to identify the following fields from this query:

-- the field (and table that it is in) that holds the deductible
value
that is to be updated;
-- the primary key field of the table that holds the deductible
field;
-- which fields correspond to the values that you're selecting on
your
form so that you are "identifying" the appropriate person's records
that
are
to be updated.

--

Ken Snell
<MS ACCESS MVP>


Hi

Is this what you wanted? This is the SQL view of my select query
for
one
account showing all the locations in CA for that account whose
deductible
I'd
like to update.


SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY,
dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM,
dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME,
dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT
FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON
dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN
dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID =
dbo_eqdet_QTE05_01.LOCID)
INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US
REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US
REGIONS].STATECODE)
WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND
((dbo_loc_QTE05_01.COUNTRY)="US") AND
((dbo_loc_QTE05_01.STATECODE)="CA"));

Thanks

:

Post the SQL statement of the select query that you've created. We
need
to
modify it into an update query so that we can run it from your
form.

--

Ken Snell
<MS ACCESS MVP>

" What you want to do is to create an update query that will read
the
values
to be used in the query from your form.
"

yes - this is exactly what I want to do.

I can set up a select query easily to select the locations that I
want
to
update - I take it this query is kept in the original query
format
and
isn't
changed to a form view or anything?

The name of the form that I'll be using is called
"FormUpdateQuoteValues"

The names of the textboxes are "FieldUpdateTo" which is where I
enter
the
value that I want the values to be updated to and "FieldWhere"
which
is
where
I enter the criteria eg CA.

Thanks again

:

It appears, then, that what you want to do is simpler than I'd
been
thinking.

What you want to do is to create an update query that will read
the
values
to be used in the query from your form.

Note: Remember that, once run, an update query cannot be
undone.
So
you'll
need to be sure that the user means to do the update before you
run
it,
or
else your data will be changed.

First, let's have you create a select query (a normal query)
that
will
select the records that you want to update. We will turn this
query
into
an
update query after you post it -- it's easier to do it this way.

Also, what is the name of the form that you'll be using to enter
the
values
that will be used by the query? And what are the names of the
textboxes
into
which you'll type those values?

--

Ken Snell
<MS ACCESS MVP>



message
Thanks very much for your quick response.

At the moment what I have set up is a form that just shows the
main
details
of the account - account number, name of insured etc. This
comes
from
a
query picking out certain information from an underlying
"Accounts"
table.
I don't really need to be able to see all the other
information
such
as
the
locations that are part of the account because there could be
tens
of
thousands of them! I take it this is what you were thinking
would
be
on
the
subform?

So what I would like are two boxes somewhere on the form - one
that
I
would
fill out with the deductible amount to be updated eg 0.05 and
the
other
with
the criteria eg CA for California locations only. And then a
button
that
you
press to essentially do the same as the run button in a
regular
update
query.

I hope this is what you wanted to know - thanks again for
helping
me
with
this!



:

What you seek to do can be done within a form, using unbound
controls
on
the
form and using programming behind the form that, when you
click
a
button,
will update the data as you wish.

This is not a simple thing to set up, and talking you through
it
will
take a
bit of iterations, so get ready for a few exchanges here! ;-)

First, let's start with what you've already set up. I assume
that
this
is
a
form that is bound to a query or table that is returning the
values
that
you're displaying. Are you using subforms for the children
data?
Or
are
you
using separate forms that you open from this one main form?
It
will
be
easier for your use if you use subforms.
--

Ken Snell
<MS ACCESS MVP>





message
Hi

I am trying to build a "front end" to an application and am
having
some
difficulty. Here's my problem:

I would basically like to be able to do via a form what an
update
query
does:

I work in insurance and I have a table that has all the
information
about
the insured accounts in it and each account has it's own
unique
identifier.
I have a second table with all the policy information in
it.
A
third
table
has all the information about the locations insured for
each
account,
and
each location also has a unique identifier.

I would like to have a form that displays all the basic
information
about
the insured account with some additional boxes that I can
fill
out
to
update
information in the underlying location table.

eg, say I have an account that has locations all over the
US.
I
would
like
to be able to find that account and then fill out
additional
boxes
that
would
update the deductible values at all CA locations to 5%. So
in
an
update
query the criteria would be CA and the update to field
would
be
0.05.

I have tried so many things and just can't get this
seemingly
basic
idea
to
work as part of a form.

Any help would be greatly appreciated.

 

[Ken Snell [MVP]]

OK - so now the next step will be to build the form that you'll use for
entering/selecting the parameters and then running the update query,
correct?

Which parameters will you want to enter/select on the form? I assume account
number, SITEDEDAMT, and perhaps others? Which of these do you want to select
from a combo box? which do you want to allow the user to enter as a typed-in
value?

Set up the form accordingly...put textboxes and/or combo boxes on it. Give
them names that are meaningful...e.g., txtSiteDEDAmt; cboAccountNumber, etc.

Put a command button on the form that will be used to run the update query.

After you have this set up, post back and describe what you have, including
the names of the form and the controls (textboxes, comboboxes, and command
button).
--

Ken Snell
<MS ACCESS MVP>

You're almost right with the assumptions - sorry, it's probably me not
being
clear.

Everything you've said is correct except that each location is uniquely
identified by a primary key field called LOCID.

Each locations details such as address are kept in a seperate table (where
the primary key is LOCID) from where the financial details for each
location
are kept. The field SITEDEDAMT is kept in the table where the financial
details are kept (dbo_eqdet_QTE_05_01). The primary key for this table is
called EQDETID. LOCID appears here as a foreign key.

Having said all this, your final example appears to do exactly what I want
it to.

Let me recap....

Each account is uniquely identified by ACCGRPID.

Each account has many locations. Each location is uniquely identified by
EQDETID. These records are in table dbo_eqdet_QTE_05_01.

You desire to update the value for the EQSITEDEDAMT field (you also
called
this the SITEDEDAMT field?), which is in the dbo_eqdet_QTE_05_01 table
where
each record is uniquely identified by the EQDETID value.

So, assuming that I have correctly stated the information, let me give
you a
generic update query to show you how it's constructed:

UPDATE dbo_eqdet_QTE_05_01
SET EQSITEDEDAMT = "New Site DED Amt"
WHERE ACCGRPID = "The account number";

So, for example, suppose that you want to set the EQSITEDEDAMT value to
10
for all locations for account number 11223:

UPDATE dbo_eqdet_QTE_05_01
SET EQSITEDEDAMT = 10
WHERE ACCGRPID = 11223;

Now, let me take your Select query that you posted, and let's see if we
can
turn it into an appropriate update query, using the values that you
provided
for the various criteria and my example of 10 for SITEDEDAMT:

UPDATE dbo_accgrp_QTE05_01
SET dbo_eqdet_QTE05_01.SITEDEDAMT = 10
WHERE dbo_accgrp_QTE05_01.ACCGRPID=208 AND
dbo_loc_QTE05_01.COUNTRY="US" AND
dbo_loc_QTE05_01.STATECODE="CA";

--

Ken Snell
<MS ACCESS MVP>

ACCGRPID is a unique identifier for each seperate account. Each
account
can
have many locations (tens of thousands sometimes). Each location has a
value
EQSITEDEDAMT, which is the value to be updated for certain locations eg
only
the ones in CA. This value is in a table with other information about
the
locations which is not needed here. The primary key of that table is
called
EQDETID.

Hope this clarifies!

:

ACCGRPID is or is not the same value as EQDETID? In other words, does
ACCGRPID have the value that we would use to find the matching
EQDETID?
If
not, what is the relationship between ACCGRPID and EQDETID?

What we're trying to do now is to identify the children record(s) that
need
to be updated can be identified from what you select on your form.

--

Ken Snell
<MS ACCESS MVP>


Hi

The field to be updated is called SITEDEDAMT
The table it's in is called dbo_eqdet_QTE_05_01
The primary key of the table with the deductible is EQDETID
The values that corrospond to what I'm selecting on my form will be
a
primary key identifier - called ACCGRPID

Thanks


:

OK - now we need you to identify the following fields from this
query:

-- the field (and table that it is in) that holds the
deductible
value
that is to be updated;
-- the primary key field of the table that holds the deductible
field;
-- which fields correspond to the values that you're selecting
on
your
form so that you are "identifying" the appropriate person's records
that
are
to be updated.

--

Ken Snell
<MS ACCESS MVP>


Hi

Is this what you wanted? This is the SQL view of my select query
for
one
account showing all the locations in CA for that account whose
deductible
I'd
like to update.


SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY,
dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM,
dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME,
dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT
FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON
dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER
JOIN
dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID =
dbo_eqdet_QTE05_01.LOCID)
INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US
REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US
REGIONS].STATECODE)
WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND
((dbo_loc_QTE05_01.COUNTRY)="US") AND
((dbo_loc_QTE05_01.STATECODE)="CA"));

Thanks

:

Post the SQL statement of the select query that you've created.
We
need
to
modify it into an update query so that we can run it from your
form.

--

Ken Snell
<MS ACCESS MVP>

message
" What you want to do is to create an update query that will
read
the
values
to be used in the query from your form.
"

yes - this is exactly what I want to do.

I can set up a select query easily to select the locations
that I
want
to
update - I take it this query is kept in the original query
format
and
isn't
changed to a form view or anything?

The name of the form that I'll be using is called
"FormUpdateQuoteValues"

The names of the textboxes are "FieldUpdateTo" which is where
I
enter
the
value that I want the values to be updated to and "FieldWhere"
which
is
where
I enter the criteria eg CA.

Thanks again

:

It appears, then, that what you want to do is simpler than
I'd
been
thinking.

What you want to do is to create an update query that will
read
the
values
to be used in the query from your form.

Note: Remember that, once run, an update query cannot be
undone.
So
you'll
need to be sure that the user means to do the update before
you
run
it,
or
else your data will be changed.

First, let's have you create a select query (a normal query)
that
will
select the records that you want to update. We will turn this
query
into
an
update query after you post it -- it's easier to do it this
way.

Also, what is the name of the form that you'll be using to
enter
the
values
that will be used by the query? And what are the names of the
textboxes
into
which you'll type those values?

--

Ken Snell
<MS ACCESS MVP>



message
Thanks very much for your quick response.

At the moment what I have set up is a form that just shows
the
main
details
of the account - account number, name of insured etc. This
comes
from
a
query picking out certain information from an underlying
"Accounts"
table.
I don't really need to be able to see all the other
information
such
as
the
locations that are part of the account because there could
be
tens
of
thousands of them! I take it this is what you were
thinking
would
be
on
the
subform?

So what I would like are two boxes somewhere on the form -
one
that
I
would
fill out with the deductible amount to be updated eg 0.05
and
the
other
with
the criteria eg CA for California locations only. And then
a
button
that
you
press to essentially do the same as the run button in a
regular
update
query.

I hope this is what you wanted to know - thanks again for
helping
me
with
this!



:

What you seek to do can be done within a form, using
unbound
controls
on
the
form and using programming behind the form that, when you
click
a
button,
will update the data as you wish.

This is not a simple thing to set up, and talking you
through
it
will
take a
bit of iterations, so get ready for a few exchanges here!
;-)

First, let's start with what you've already set up. I
assume
that
this
is
a
form that is bound to a query or table that is returning
the
values
that
you're displaying. Are you using subforms for the children
data?
Or
are
you
using separate forms that you open from this one main
form?
It
will
be
easier for your use if you use subforms.
--

Ken Snell
<MS ACCESS MVP>





message
Hi

I am trying to build a "front end" to an application and
am
having
some
difficulty. Here's my problem:

I would basically like to be able to do via a form what
an
update
query
does:

I work in insurance and I have a table that has all the
information
about
the insured accounts in it and each account has it's own
unique
identifier.
I have a second table with all the policy information in
it.
A
third
table
has all the information about the locations insured for
each
account,
and
each location also has a unique identifier.

I would like to have a form that displays all the basic
information
about
the insured account with some additional boxes that I
can
fill
out
to
update
information in the underlying location table.

eg, say I have an account that has locations all over
the
US.
I
would
like
to be able to find that account and then fill out
additional
boxes
that
would
update the deductible values at all CA locations to 5%.
So
in
an
update
query the criteria would be CA and the update to field
would
be
0.05.

I have tried so many things and just can't get this
seemingly
basic
idea
to
work as part of a form.

Any help would be greatly appreciated.

 

[Mary Pode]

Right, this is how the form looks:

The fields I have on it are all text boxes.

They are ACCGRPID (the unique identifier for each account) (The name of this
is ComboAccGrpID) This is a field where the user can enter the number and
press tab and the following fields are filled out:
ACCGRPNAME (the name of the account) (This is called FieldAccGrpName)

The fields I want the user to be able to fill out are text boxes and are
called:
FieldUpdateEQDedTo (this is where the value for the SITEDEDAMT goes - the
value to be updated.)
FieldEQCountryWhere (which is where the user would put US)
FieldEQStateWhere (which is where the user would select the state whose
locations are to be updated eg CA)

I also have the command button which is called CommandEQUpdate.

Hope this is ok.

OK - so now the next step will be to build the form that you'll use for
entering/selecting the parameters and then running the update query,
correct?

Which parameters will you want to enter/select on the form? I assume account
number, SITEDEDAMT, and perhaps others? Which of these do you want to select
from a combo box? which do you want to allow the user to enter as a typed-in
value?

Set up the form accordingly...put textboxes and/or combo boxes on it. Give
them names that are meaningful...e.g., txtSiteDEDAmt; cboAccountNumber, etc.

Put a command button on the form that will be used to run the update query.

After you have this set up, post back and describe what you have, including
the names of the form and the controls (textboxes, comboboxes, and command
button).
--

Ken Snell
<MS ACCESS MVP>

You're almost right with the assumptions - sorry, it's probably me not
being
clear.

Everything you've said is correct except that each location is uniquely
identified by a primary key field called LOCID.

Each locations details such as address are kept in a seperate table (where
the primary key is LOCID) from where the financial details for each
location
are kept. The field SITEDEDAMT is kept in the table where the financial
details are kept (dbo_eqdet_QTE_05_01). The primary key for this table is
called EQDETID. LOCID appears here as a foreign key.

Having said all this, your final example appears to do exactly what I want
it to.

Let me recap....

Each account is uniquely identified by ACCGRPID.

Each account has many locations. Each location is uniquely identified by
EQDETID. These records are in table dbo_eqdet_QTE_05_01.

You desire to update the value for the EQSITEDEDAMT field (you also
called
this the SITEDEDAMT field?), which is in the dbo_eqdet_QTE_05_01 table
where
each record is uniquely identified by the EQDETID value.

So, assuming that I have correctly stated the information, let me give
you a
generic update query to show you how it's constructed:

UPDATE dbo_eqdet_QTE_05_01
SET EQSITEDEDAMT = "New Site DED Amt"
WHERE ACCGRPID = "The account number";

So, for example, suppose that you want to set the EQSITEDEDAMT value to
10
for all locations for account number 11223:

UPDATE dbo_eqdet_QTE_05_01
SET EQSITEDEDAMT = 10
WHERE ACCGRPID = 11223;

Now, let me take your Select query that you posted, and let's see if we
can
turn it into an appropriate update query, using the values that you
provided
for the various criteria and my example of 10 for SITEDEDAMT:

UPDATE dbo_accgrp_QTE05_01
SET dbo_eqdet_QTE05_01.SITEDEDAMT = 10
WHERE dbo_accgrp_QTE05_01.ACCGRPID=208 AND
dbo_loc_QTE05_01.COUNTRY="US" AND
dbo_loc_QTE05_01.STATECODE="CA";

--

Ken Snell
<MS ACCESS MVP>


ACCGRPID is a unique identifier for each seperate account. Each
account
can
have many locations (tens of thousands sometimes). Each location has a
value
EQSITEDEDAMT, which is the value to be updated for certain locations eg
only
the ones in CA. This value is in a table with other information about
the
locations which is not needed here. The primary key of that table is
called
EQDETID.

Hope this clarifies!

:

ACCGRPID is or is not the same value as EQDETID? In other words, does
ACCGRPID have the value that we would use to find the matching
EQDETID?
If
not, what is the relationship between ACCGRPID and EQDETID?

What we're trying to do now is to identify the children record(s) that
need
to be updated can be identified from what you select on your form.

--

Ken Snell
<MS ACCESS MVP>


Hi

The field to be updated is called SITEDEDAMT
The table it's in is called dbo_eqdet_QTE_05_01
The primary key of the table with the deductible is EQDETID
The values that corrospond to what I'm selecting on my form will be
a
primary key identifier - called ACCGRPID

Thanks


:

OK - now we need you to identify the following fields from this
query:

-- the field (and table that it is in) that holds the
deductible
value
that is to be updated;
-- the primary key field of the table that holds the deductible
field;
-- which fields correspond to the values that you're selecting
on
your
form so that you are "identifying" the appropriate person's records
that
are
to be updated.

--

Ken Snell
<MS ACCESS MVP>


Hi

Is this what you wanted? This is the SQL view of my select query
for
one
account showing all the locations in CA for that account whose
deductible
I'd
like to update.


SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY,
dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM,
dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME,
dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT
FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON
dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER
JOIN
dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID =
dbo_eqdet_QTE05_01.LOCID)
INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US
REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US
REGIONS].STATECODE)
WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND
((dbo_loc_QTE05_01.COUNTRY)="US") AND
((dbo_loc_QTE05_01.STATECODE)="CA"));

Thanks

:

Post the SQL statement of the select query that you've created.
We
need
to
modify it into an update query so that we can run it from your
form.

--

Ken Snell
<MS ACCESS MVP>

message
" What you want to do is to create an update query that will
read
the
values
to be used in the query from your form.
"

yes - this is exactly what I want to do.

I can set up a select query easily to select the locations
that I
want
to
update - I take it this query is kept in the original query
format
and
isn't
changed to a form view or anything?

The name of the form that I'll be using is called
"FormUpdateQuoteValues"

The names of the textboxes are "FieldUpdateTo" which is where
I
enter
the
value that I want the values to be updated to and "FieldWhere"
which
is
where
I enter the criteria eg CA.

Thanks again

:

It appears, then, that what you want to do is simpler than
I'd
been
thinking.

What you want to do is to create an update query that will
read
the
values
to be used in the query from your form.

Note: Remember that, once run, an update query cannot be
undone.
So
you'll
need to be sure that the user means to do the update before
you
run
it,
or
else your data will be changed.

First, let's have you create a select query (a normal query)
that
will
select the records that you want to update. We will turn this
query
into
an
update query after you post it -- it's easier to do it this
way.

Also, what is the name of the form that you'll be using to
enter
the
values
that will be used by the query? And what are the names of the
textboxes
into
which you'll type those values?

--

Ken Snell
<MS ACCESS MVP>



message
Thanks very much for your quick response.

At the moment what I have set up is a form that just shows
the
main
details
of the account - account number, name of insured etc. This
comes
from
a
query picking out certain information from an underlying
"Accounts"
table.
I don't really need to be able to see all the other
information
such
as
the
locations that are part of the account because there could
be
tens
of
thousands of them! I take it this is what you were
thinking
would
be
on
the
subform?

So what I would like are two boxes somewhere on the form -
one
that
I
would
fill out with the deductible amount to be updated eg 0.05
and
the
other
with
the criteria eg CA for California locations only. And then
a
button
that
you
press to essentially do the same as the run button in a
regular
update
query.

I hope this is what you wanted to know - thanks again for
helping
me
with
this!



:

What you seek to do can be done within a form, using
unbound
controls
on
the
form and using programming behind the form that, when you
click
a
button,
will update the data as you wish.

This is not a simple thing to set up, and talking you
through
it
will
take a
bit of iterations, so get ready for a few exchanges here!
;-)

First, let's start with what you've already set up. I
assume
that
this
is
a
form that is bound to a query or table that is returning
the
values
that
you're displaying. Are you using subforms for the children
data?
Or
are
you
using separate forms that you open from this one main
form?
It
will
be
easier for your use if you use subforms.
--

Ken Snell
<MS ACCESS MVP>





message
Hi

I am trying to build a "front end" to an application and
am
having
some
difficulty. Here's my problem:

I would basically like to be able to do via a form what
an
update
query
does:

I work in insurance and I have a table that has all the
information
about
the insured accounts in it and each account has it's own
unique
identifier.
I have a second table with all the policy information in
it.
A
third
table
has all the information about the locations insured for
each
account,
and
each location also has a unique identifier.

I would like to have a form that displays all the basic
information
about
the insured account with some additional boxes that I
can
fill
out
to
update
information in the underlying location table.

eg, say I have an account that has locations all over
the
US.
I
would
like
to be able to find that account and then fill out
additional
boxes
that
would
update the deductible values at all CA locations to 5%.
So
in
an
update
query the criteria would be CA and the update to field
would
be
0.05.

I have tried so many things and just can't get this
seemingly
basic
idea
to
work as part of a form.

Any help would be greatly appreciated.

 

[Ken Snell [MVP]]

OK - we're close now!

First, may I suggest that you use a combo box for the account number
entry/selection? That way, your users won't need to be concerned if they
should enter an incorrect account number.

Similarly, use combo boxes for the country and for the state selections.
Again, it avoids the possibility of the user entering an incorrect value,
and makes your validation much easier.

For all three of these comboboxes, be sure to set the LimitToList property
to Yes to avoid people typing an invalid value into the combobox for use.

Also, note that a textbox and a combo box, etc. are called controls on a
form or a report; fields are in tables. Controls can be bound to fields, but
they are not the same things.

In my example code below, I am assuming that you will do some validation on
the SITEDEDAMT value (I've arbitrarily selected some limits that you can
change). Also, I am assuming that no validation is needed for the account
number, state, and country information as I will assume that you will use
comboboxes, per my suggestion above.

OK - so what we want the command button to do is to run the update query.
The following code procedure needs to be put on the Click event of the
CommandEQUpdate button. If you don't know how to do this, let me know.

Private Sub CommandEQUpdate_Click()
Dim dbs As DAO.Database
Dim strSQL As String
On Error GoTo Err_Click
' Validate the value entered for the update value
If Me.FieldUpdateEQDedTo.Value < 0 Or _
Me.FieldUpdateEQDedTo.Value > 25 Then
MsgBox "The value you entered is out of range. Re-enter the value.", _
vbExclamation, "Invalid Value"
Me.FieldUpdateEQDedTo.SetFocus
Else
' Create the database object for running the update query
Set dbs = CurrentDb
' Build the update query's SQL statement
strSQL = "UPDATE dbo_accgrp_QTE05_01 "
strSQL = strSQL & "SET dbo_eqdet_QTE05_01.SITEDEDAMT="
strSQL = strSQL & Me.FieldUpdateEQDedTo.Value
strSQL = strSQL & " WHERE dbo_accgrp_QTE05_01.ACCGRPID="
strSQL = strSQL & Me.ComboAccGrpID.Value & " And "
strSQL = strSQL & "dbo_loc_QTE05_01.COUNTRY='"
strSQL = strSQL & Me.FieldEQCountryWhere.Value & "' And "
strSQL = strSQL & "dbo_loc_QTE05_01.STATECODE='"
strSQL = strSQL & Me.FieldEQStateWhere.Value & "';"
' Run the update query; if an error occurs, the query will fail and the
error
' handler will notify the user
dbs.Execute strSQL, dbFailOnError
End If

Exit_Click:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Sub

Err_Click:
MsgBox "An error has occurred while trying to update the data:" & _
vbCrLf & "Error Number " & Err.Number & ": " & _
Err.Description
Resume Exit_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

Right, this is how the form looks:

The fields I have on it are all text boxes.

They are ACCGRPID (the unique identifier for each account) (The name of
this
is ComboAccGrpID) This is a field where the user can enter the number and
press tab and the following fields are filled out:
ACCGRPNAME (the name of the account) (This is called FieldAccGrpName)

The fields I want the user to be able to fill out are text boxes and are
called:
FieldUpdateEQDedTo (this is where the value for the SITEDEDAMT goes - the
value to be updated.)
FieldEQCountryWhere (which is where the user would put US)
FieldEQStateWhere (which is where the user would select the state whose
locations are to be updated eg CA)

I also have the command button which is called CommandEQUpdate.

Hope this is ok.

< snipped >

 

[Mary Pode]

That's great!

Thank you!

OK - we're close now!

First, may I suggest that you use a combo box for the account number
entry/selection? That way, your users won't need to be concerned if they
should enter an incorrect account number.

Similarly, use combo boxes for the country and for the state selections.
Again, it avoids the possibility of the user entering an incorrect value,
and makes your validation much easier.

For all three of these comboboxes, be sure to set the LimitToList property
to Yes to avoid people typing an invalid value into the combobox for use.

Also, note that a textbox and a combo box, etc. are called controls on a
form or a report; fields are in tables. Controls can be bound to fields, but
they are not the same things.

In my example code below, I am assuming that you will do some validation on
the SITEDEDAMT value (I've arbitrarily selected some limits that you can
change). Also, I am assuming that no validation is needed for the account
number, state, and country information as I will assume that you will use
comboboxes, per my suggestion above.

OK - so what we want the command button to do is to run the update query.
The following code procedure needs to be put on the Click event of the
CommandEQUpdate button. If you don't know how to do this, let me know.

Private Sub CommandEQUpdate_Click()
Dim dbs As DAO.Database
Dim strSQL As String
On Error GoTo Err_Click
' Validate the value entered for the update value
If Me.FieldUpdateEQDedTo.Value < 0 Or _
Me.FieldUpdateEQDedTo.Value > 25 Then
MsgBox "The value you entered is out of range. Re-enter the value.", _
vbExclamation, "Invalid Value"
Me.FieldUpdateEQDedTo.SetFocus
Else
' Create the database object for running the update query
Set dbs = CurrentDb
' Build the update query's SQL statement
strSQL = "UPDATE dbo_accgrp_QTE05_01 "
strSQL = strSQL & "SET dbo_eqdet_QTE05_01.SITEDEDAMT="
strSQL = strSQL & Me.FieldUpdateEQDedTo.Value
strSQL = strSQL & " WHERE dbo_accgrp_QTE05_01.ACCGRPID="
strSQL = strSQL & Me.ComboAccGrpID.Value & " And "
strSQL = strSQL & "dbo_loc_QTE05_01.COUNTRY='"
strSQL = strSQL & Me.FieldEQCountryWhere.Value & "' And "
strSQL = strSQL & "dbo_loc_QTE05_01.STATECODE='"
strSQL = strSQL & Me.FieldEQStateWhere.Value & "';"
' Run the update query; if an error occurs, the query will fail and the
error
' handler will notify the user
dbs.Execute strSQL, dbFailOnError
End If

Exit_Click:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Sub

Err_Click:
MsgBox "An error has occurred while trying to update the data:" & _
vbCrLf & "Error Number " & Err.Number & ": " & _
Err.Description
Resume Exit_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>




< snipped >