Cascading Un-linked Data From One - To - Many

[kjeckell]

I have a DB that consists of 3 tables The One Table holds box specific
information and the many table holds file specific information. I want
to be able, in a form, to change the status of a box and that to
cascade down to the files with that same box number. Layout:

BOXES:
BoxNum
Description
RequestedDate
ReqestedBy
Delivered

FILES:
BoxNum
Description
RequestedDate
RequestedBy
Delivered

I use this method because a file can be deliverd without the entire box
but the box obviously will contain all of the files. This is so that
when some one orders a box and another person orders a file from that
box I know that that particular file is not here (because the whole
box is gone). Please Help

 

[Ed Robichaud]

If you restructure your data to below and set the relationships
(Tools-Relationships), all related files will be updated without any work on
your part. You don't say what your 3rd table is, but I would also recommend
a lookup table of persons to use for [RequestedBy]
-Ed


BOXES:
BoxNum - primary key
Description
RequestedDate
ReqestedBy
Delivered

FILES:
File# - primary key
BoxNum -foreign key (linked many-1 to tblBoxes)
Description
RequestedDate
RequestedBy
Delivered

 

[Guest]

There are various ways you can update the Files table but the simplest would
be to execute an SQL statement which updates the values of the relevant
columns in those rows where the BoxNum value matches the BoxNum value of the
current record in your form. Do this either in the AftterUpdate event
procedure of the form if you want it to execute automatically whenever the
form's data is edited, or in the Click event procedure of a button on the
form if you want the execution to be on user-elective, with code like this:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE Files " & _
"SET RequestedDate = #" & _
Format(Me.RequestedDate,"mm/dd/yyyy") & "#," & _
"RequestedBy = """ & Me.RequestedBy & """," & _
"Delivered = " & Me.Delivered & _
" WHERE BoxNum = " & Me.BoxNum
cmd.CommandText = strSQL
cmd.Execute

You don't say what data types your columns are so I've assumed in the above
that RequestedDate is DateTime (consequently its formatted as mm/dd/yyy and
delimited by # date delimiter characters when the SQL statement is built),
RequestedBy is Text (consequently delimited by quotes), Delivered is Boolean
(Yes/No) and BoxNum is a number data type.