Change Value in Different Table

[James X. Bond]

Good afternoon!

Hello to everyone. Can somebody please tell me how to
change the value of the data in a field in a differenct
table? I tried to use DLookUp in my form but the data in
the field is "READ ONLY".

I want it to be this way -> once I made an update in the
value of my field in XYZ Form I want the value to
deducted in the value of the field on the XYZ Table
(which is NOT my record source).

I would appreciate if someone could answer my question.
More power.

 

[Douglas J. Steele]

By definition, DLookup is read-only: it can only retrieve a value, not
change it.

To update, you either need to use an UPDATE query (which you can, of course,
run programmatically) or open a recordset and update the data that way.

 

[Guest]

Now I understand why it's so called DLookup!
Can you give me a sample code Mr. Steele?

Thanks!

 

[Douglas J. Steele]

Not without knowing more about your tables and stuff.

The syntax for an Update query is

UPDATE table
SET newvalue
WHERE criteria;

An example would be something like:

Dim strSQL As String

strSQL = "UPDATE XYZ" & _
" SET Field1 = Field1 - " & Me.txtDeductions & _
" WHERE ID = " & Me.txtProductID
CurrentDb.Execute strSQL, dbFailOnError

This assumes that txtDeductions and txtProductID are text boxes on the
current form that have the relvant values in them. (It's using DAO, so if
you're using Access 2000 or 2002, you may need to add a reference to DAO)