Re: update queries and forms

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Mary Pode (MaryPode_at_discussions.microsoft.com)
Date: 02/02/05 

Date: Wed, 2 Feb 2005 01:53:02 -0800

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.

"Ken Snell [MVP]" wrote:

> 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>
>
>
>
> "Mary Pode" <MaryPode@discussions.microsoft.com> wrote in message
> news:78B0C5BC-421C-48B0-81EE-B80B86E9F3E5@microsoft.com...
> > 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.
> >
> >
> > "Ken Snell [MVP]" wrote:
> >
> >> 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>
> >>
> >>
> >> "Mary Pode" <MaryPode@discussions.microsoft.com> wrote in message
> >> news:1A811D5E-82D3-4D8E-9FD6-1B9B5AF42F7A@microsoft.com...
> >> > 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!
> >> >
> >> > "Ken Snell [MVP]" wrote:
> >> >
> >> >> 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>
> >> >>
> >> >>
> >> >> "Mary Pode" <MaryPode@discussions.microsoft.com> wrote in message
> >> >> news:80C9529B-30F6-4FD2-9155-D252E3A513DE@microsoft.com...
> >> >> > 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
> >> >> >
> >> >> >
> >> >> > "Ken Snell [MVP]" wrote:
> >> >> >
> >> >> >> 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>
> >> >> >>
> >> >> >>
> >> >> >> "Mary Pode" <MaryPode@discussions.microsoft.com> wrote in message
> >> >> >> news:C8E32F2D-51C7-4BDB-A8C8-0CAF7067FF09@microsoft.com...
> >> >> >> > 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]" wrote:
> >> >> >> >
> >> >> >> >> 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>
> >> >> >> >>
> >> >> >> >> "Mary Pode" <MaryPode@discussions.microsoft.com> wrote in
> >> >> >> >> message
> >> >> >> >> news:2D8EA414-C0B9-4C71-9A97-69BF4ABECB60@microsoft.com...
> >> >> >> >> >" 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]" wrote:
> >> >> >> >> >
> >> >> >> >> >> 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" <MaryPode@discussions.microsoft.com> wrote in
> >> >> >> >> >> message
> >> >> >> >> >> news:A212365A-ECF9-4033-A2B1-3986AC1591DC@microsoft.com...
> >> >> >> >> >> > 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]" wrote:
> >> >> >> >> >> >
> >> >> >> >> >> >> 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>
> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >> >> "Mary Pode" <Mary Pode@discussions.microsoft.com> wrote in
> >> >> >> >> >> >> message
> >> >> >> >> >> >> news:86F15D33-A326-40C4-8FFA-9F60BA14DD0C@microsoft.com...
> >> >> >> >> >> >> > 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.
> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Relevant Pages

  • Re: update queries and forms
    ... may I suggest that you use a combo box for the account number ... OK - so what we want the command button to do is to run the update query. ... > They are ACCGRPID (the unique identifier for each account) (The name of> this ...
    (microsoft.public.access.tablesdbdesign)
  • Re: [opensuse] Post installation queries (FIRST successful installation of Linux)
    ... The easiest way to manage users on opensuse is with yast. ... You can also do the same with the command line commands of: useradd userdel and usermod. ... -c comment Set the GECOS field for the new account ... One more note -- and I hate this about the recent openSuSE installs -- you must tell the installer to set a traditional root account and password during setup or it just creates a 'Super User' out of the user account used during install. ...
    (SuSE)
  • Re: Home Wireless Networking and File Sharing
    ... Browstat and run it but it just shows the command prompt for a sec and then ... Then I put in net config workstation and got ... You do not need to be logged into the same account ... and Vista:" I have done that on all of the laptops and main PC and called ...
    (microsoft.public.windowsxp.network_web)
  • Re: Local System Account
    ... I checked on Windows Server 2003 and Vista Beta 2. ... run following command from command line: ... one can specify it to run as localsystem account. ... administrator is not same as Local System. ...
    (microsoft.public.win2000.security)
  • Re: Restricting cmd.exe access
    ... you will need to edit the System Path ... to contain this new folder path. ... > system account ... > command properly ACL'd), ...
    (Security-Basics)