change record from command button

[AHopper]

I have a table "Orders" with a field "ShipmentNumber" and
a field "Completed". On a form "Packing" I have a command
button "Save Record" (the source for the "Packing" form is
a table "Packaged"). When the "Save Record" command button
is clicked I know how to count the number of cartons for
that shipment in the "Packaged" table and compare it to
the number of cartons ordered from the "Orders" table.
When the number of cartons packed equals the number of
cartons ordered,I want the field "Completed" of the record
with the "ShipmentNumber" currently being packaged, in
the "Orders" table to be changed to "Yes" (default is No).

Orders (Table)

ShipmentNumber Ordered Complete (default No)
1 10 Yes

Since the "Packing" form does not use the "Orders" table
as it's source, I do not understand how to change the
record in the background without the user having to do
anything.

Thanks in advance for your help

Allan

 

[Lee T.]

-----Original Message-----
I have a table "Orders" with a field "ShipmentNumber" and
a field "Completed". On a form "Packing" I have a command
button "Save Record" (the source for the "Packing" form is
a table "Packaged"). When the "Save Record" command button
is clicked I know how to count the number of cartons for
that shipment in the "Packaged" table and compare it to
the number of cartons ordered from the "Orders" table.
When the number of cartons packed equals the number of
cartons ordered,I want the field "Completed" of the record
with the "ShipmentNumber" currently being packaged, in
the "Orders" table to be changed to "Yes" (default is No).

Orders (Table)

ShipmentNumber Ordered Complete (default No)
1 10 Yes

Since the "Packing" form does not use the "Orders" table
as it's source, I do not understand how to change the
record in the background without the user having to do
anything.

Thanks in advance for your help

Allan

.

Allan,

After you have done your counting to verify that the
number of cartons have shipped you should be able to
modify the following code to update the "orders" table.

The "SetWarnings" I leave to true when I am testing but
put it in to remind myself to turn if off in production.

Dim intSHIPNUM As Integer
intSHIPNUM = Forms!Packaging!ShipNo
DoCmd.SetWarnings FALSE
SQL = "UPDATE orders SET orders.Completed = ""yes"" " & _
"WHERE ((orders.ShipNo)=" & Forms!packaging!shipno & ");"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

hth

Lee T.

 

[AHopper]

Lee
It worked the first time. Thank you very much for you help.

Allan

 

[Guest]

Allan,

Glad it worked for you.

After I posted it I noticed that I forgot to take out
the DIM statement. I do something similar but I also
append data to another table and I do the DIM and
assignment to get the number so that I can run a loop to
add the required number of records. In this example they
are not needed. Sorry about that.

Lee T.