That's great!
Thank you!
"Ken Snell [MVP]" wrote:
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
"Mary Pode" wrote in message
...
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" wrote in message
...
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
snipped
|