Update field in related table

[Rolf]

Hello!

I have a subform with the fields itemID, unit, quantity and batch from the
table tblOrderdetails. The itemID, unit and batch are related to the
tblItemreg.
When you choose the itemID in the subform an AfterUpdate-event inserts the
unit and batch to its corresponding field in the subform (Dlookup-function).

I would like to update the batch in the tblItemreg from the batch-field in
the subform. E.g. after choosing itemID I will move to the batch-field and
update it if necessary. I'm using the following SQL in the AfterUpdate-event
for the batch-field, but I only get the warning-message that tells me that
I'm up to update 0 records.

strSQL = "UPDATE tblItemreg SET batch = '& Me.batch &'"
strSQL = strSQL + " WHERE (([itemID]) = '& Me.itemID &')"
strSQL = strSQL + ";"

DoCmd.RunSQL strSQL


Why isn't the record being updated?

 

[Marshall Barton]

I have a subform with the fields itemID, unit, quantity and batch from the
table tblOrderdetails. The itemID, unit and batch are related to the
tblItemreg.
When you choose the itemID in the subform an AfterUpdate-event inserts the
unit and batch to its corresponding field in the subform (Dlookup-function).

I would like to update the batch in the tblItemreg from the batch-field in
the subform. E.g. after choosing itemID I will move to the batch-field and
update it if necessary. I'm using the following SQL in the AfterUpdate-event
for the batch-field, but I only get the warning-message that tells me that
I'm up to update 0 records.

strSQL = "UPDATE tblItemreg SET batch = '& Me.batch &'"
strSQL = strSQL + " WHERE (([itemID]) = '& Me.itemID &')"
strSQL = strSQL + ";"

DoCmd.RunSQL strSQL

Your quotes are out of whack. Try using:

strSQL = "UPDATE tblItemreg SET batch='" & Me.batch & "' " _
" WHERE itemID = '" & Me.itemID & "' "

Note: that assumes the fields are Text fields, If they are
a number type, it would be:

strSQL = "UPDATE tblItemreg SET batch=" & Me.batch & _
" WHERE itemID = " & Me.itemID

Another important point is that RunSQL runs the query
asynchronously so you don't know when it actually updates
the table. If you want to run the query synchronously so
you can be sure the table is updated before yor code
continues, use this instead of RunSQL:

Set db = CurrentDb
db.Execute strSQL, dbFailOnError

 

[Rolf]

Thanks for your reply!

I tried changing the quotes, but the still til SQL gives me 0 rows to
update. I get the message "You are about to update 0 row(s). If you choose
YES, you can't undo the changes...." (freely translated from norwegian). The
batch-field in the tblItemreg is unchanged.

When using the "db.Execute strSQL, dbFailOnError"-metod I don't get the
updatemessage mentioned above, but no rows get updated either.

I wonder if I am missing something?


Marshall Barton skrev:

I have a subform with the fields itemID, unit, quantity and batch from the
table tblOrderdetails. The itemID, unit and batch are related to the
tblItemreg.
When you choose the itemID in the subform an AfterUpdate-event inserts the
unit and batch to its corresponding field in the subform (Dlookup-function).

I would like to update the batch in the tblItemreg from the batch-field in
the subform. E.g. after choosing itemID I will move to the batch-field and
update it if necessary. I'm using the following SQL in the AfterUpdate-event
for the batch-field, but I only get the warning-message that tells me that
I'm up to update 0 records.

strSQL = "UPDATE tblItemreg SET batch = '& Me.batch &'"
strSQL = strSQL + " WHERE (([itemID]) = '& Me.itemID &')"
strSQL = strSQL + ";"

DoCmd.RunSQL strSQL

Your quotes are out of whack. Try using:

strSQL = "UPDATE tblItemreg SET batch='" & Me.batch & "' " _
" WHERE itemID = '" & Me.itemID & "' "

Note: that assumes the fields are Text fields, If they are
a number type, it would be:

strSQL = "UPDATE tblItemreg SET batch=" & Me.batch & _
" WHERE itemID = " & Me.itemID

Another important point is that RunSQL runs the query
asynchronously so you don't know when it actually updates
the table. If you want to run the query synchronously so
you can be sure the table is updated before yor code
continues, use this instead of RunSQL:

Set db = CurrentDb
db.Execute strSQL, dbFailOnError

 

[Marshall Barton]

Using Execute is not going to fix a problem in your query.
It's a way of synchronizing your code with the table update
and avoiding the messages that RunSQL always displays.

Your quotes were wrong, so if you still get no results,
there must be some more things to straighten out in the
query. I think the first thing to do is double check the
data type of the batch and itemID fields in the table.

A useful debugging aid is to add a Debug.Print strSQL
statement just before the Execute line. Then post back with
a Copy/Paste of the SQL statement displayed in the Immediate
window.
--
Marsh
MVP [MS Access]

I tried changing the quotes, but the still til SQL gives me 0 rows to
update. I get the message "You are about to update 0 row(s). If you choose
YES, you can't undo the changes...." (freely translated from norwegian). The
batch-field in the tblItemreg is unchanged.

When using the "db.Execute strSQL, dbFailOnError"-metod I don't get the
updatemessage mentioned above, but no rows get updated either.

I wonder if I am missing something?


Marshall Barton skrev:

I have a subform with the fields itemID, unit, quantity and batch from the
table tblOrderdetails. The itemID, unit and batch are related to the
tblItemreg.
When you choose the itemID in the subform an AfterUpdate-event inserts the
unit and batch to its corresponding field in the subform (Dlookup-function).

I would like to update the batch in the tblItemreg from the batch-field in
the subform. E.g. after choosing itemID I will move to the batch-field and
update it if necessary. I'm using the following SQL in the AfterUpdate-event
for the batch-field, but I only get the warning-message that tells me that
I'm up to update 0 records.

strSQL = "UPDATE tblItemreg SET batch = '& Me.batch &'"
strSQL = strSQL + " WHERE (([itemID]) = '& Me.itemID &')"
strSQL = strSQL + ";"

DoCmd.RunSQL strSQL

Your quotes are out of whack. Try using:

strSQL = "UPDATE tblItemreg SET batch='" & Me.batch & "' " _
" WHERE itemID = '" & Me.itemID & "' "

Note: that assumes the fields are Text fields, If they are
a number type, it would be:

strSQL = "UPDATE tblItemreg SET batch=" & Me.batch & _
" WHERE itemID = " & Me.itemID

Another important point is that RunSQL runs the query
asynchronously so you don't know when it actually updates
the table. If you want to run the query synchronously so
you can be sure the table is updated before yor code
continues, use this instead of RunSQL:

Set db = CurrentDb
db.Execute strSQL, dbFailOnError

 

[Rolf]

You where right about the datatype. The itemID-field where number type and I
had written the SQL as textfield. Didn't see that before I used your
debug-tip.
Thanks for useful help!

Marshall Barton skrev:

Using Execute is not going to fix a problem in your query.
It's a way of synchronizing your code with the table update
and avoiding the messages that RunSQL always displays.

Your quotes were wrong, so if you still get no results,
there must be some more things to straighten out in the
query. I think the first thing to do is double check the
data type of the batch and itemID fields in the table.

A useful debugging aid is to add a Debug.Print strSQL
statement just before the Execute line. Then post back with
a Copy/Paste of the SQL statement displayed in the Immediate
window.
--
Marsh
MVP [MS Access]

I tried changing the quotes, but the still til SQL gives me 0 rows to
update. I get the message "You are about to update 0 row(s). If you choose
YES, you can't undo the changes...." (freely translated from norwegian). The
batch-field in the tblItemreg is unchanged.

When using the "db.Execute strSQL, dbFailOnError"-metod I don't get the
updatemessage mentioned above, but no rows get updated either.

I wonder if I am missing something?


Marshall Barton skrev:

Rolf wrote:
I have a subform with the fields itemID, unit, quantity and batch from the
table tblOrderdetails. The itemID, unit and batch are related to the
tblItemreg.
When you choose the itemID in the subform an AfterUpdate-event inserts the
unit and batch to its corresponding field in the subform (Dlookup-function).

I would like to update the batch in the tblItemreg from the batch-field in
the subform. E.g. after choosing itemID I will move to the batch-field and
update it if necessary. I'm using the following SQL in the AfterUpdate-event
for the batch-field, but I only get the warning-message that tells me that
I'm up to update 0 records.

strSQL = "UPDATE tblItemreg SET batch = '& Me.batch &'"
strSQL = strSQL + " WHERE (([itemID]) = '& Me.itemID &')"
strSQL = strSQL + ";"

DoCmd.RunSQL strSQL

Your quotes are out of whack. Try using:

strSQL = "UPDATE tblItemreg SET batch='" & Me.batch & "' " _
" WHERE itemID = '" & Me.itemID & "' "

Note: that assumes the fields are Text fields, If they are
a number type, it would be:

strSQL = "UPDATE tblItemreg SET batch=" & Me.batch & _
" WHERE itemID = " & Me.itemID

Another important point is that RunSQL runs the query
asynchronously so you don't know when it actually updates
the table. If you want to run the query synchronously so
you can be sure the table is updated before yor code
continues, use this instead of RunSQL:

Set db = CurrentDb
db.Execute strSQL, dbFailOnError

.

 

[Mike Painter]

Hello!

I have a subform with the fields itemID, unit, quantity and batch
from the table tblOrderdetails. The itemID, unit and batch are
related to the tblItemreg.
When you choose the itemID in the subform an AfterUpdate-event
inserts the unit and batch to its corresponding field in the subform
(Dlookup-function).

I would like to update the batch in the tblItemreg from the
batch-field in the subform. E.g. after choosing itemID I will move to
the batch-field and update it if necessary. I'm using the following
SQL in the AfterUpdate-event for the batch-field, but I only get the
warning-message that tells me that I'm up to update 0 records.

strSQL = "UPDATE tblItemreg SET batch = '& Me.batch &'"
strSQL = strSQL + " WHERE (([itemID]) = '& Me.itemID &')"
strSQL = strSQL + ";"

DoCmd.RunSQL strSQL

What is tableItemReg?
The only place a dlookup would be needed is if Batch refers to something
that changes over time. A change in the batch number would trigger an update
to a Batch Table (and I'd use dLast to get teh latest value.)
Related tables rarely need to use dlookup as a proper query does all that
work for you.

Even then forms based on a query could be updated with out the need of
running an update query.
I do it all the time with price since that may change over time.
Me.Price = me.hidden price from current table