Michael said:
Hi Dirk
The new structure for the receiving table would have some
fields from the master and some fields from the sub form
like in my mail although the master fields have m1, m2, m3
& m4 and the subform has sf1, sf2, sf3 & sf4, I would like
to transfer to the new table only fields m1,m3 from the
master and sf1, sf3 from the subform, but this would
comprise 1 record, like that a repition 3 times (if there
are 3 records in the subform), the data from the master
would be common to these 3 records but the subform would
have different data...
suppose the Main form has fields M1, M2, M3 & M4
and the Sub Form has fields SF1, SF2, SF3 & SF4
and the new table has M1, M3, SF1 & SF4
the M1 & M3 would be repeated from every change in the Sub
Form, if there are more than 1 records, so if the Sub Form
has 10 different records, then there will be 10
repetitions of M1 & M3 for the SF1 & SF4 would be
different...Hope I was able to explain..
Practically, a Cheque Payment Form...Cheque Details in the
Main Form & the Expense Accounts (Could be more than one)
in the detail section (Sub Form) - I need to take certain
data from the Master and some data from the detail in
another table for analysing data...Something like that.
What I am writing is a Program That stores Stock.
Different Stock arrives in One form..The Form data is the
Master Form, and the different stock is the Sub Form
(Dedtail Form), now this data I want to put in a
transaction table..so that when I have orders...that is
also a Master/Sub Form...I can then Copy Order Number and
the data that goes with that in the Main Form and The
stock that is going out namely, Sku or Article Number,
Quantity & Location
Now data from the orders table will also go to the
transaction table.... so in one table I have data
pertaining to Receipts & deliveries....
Any other idea to go about this, would appreciate that...
Michael
I'm not sure from your description whether this is a good way to go
about this or not. I'm always suspicious of designs that involve
copying data from one table to another, but your explanation sounds
reasonable as far as it goes. To come to any real conclusion about it
would require a longer and more detailed exchange of information than
probably either of us has time for at the moment. So I'll do my best to
answer your question, and let the chips fall where they may.
Consider this interpretation of your table structure, and correct it as
needed. You have a table I'll call Master, with primary key OrderNumber
and at least two other fields we'll call M3 and M4. This table is the
recordsource of your main form. You have a related table I'll call
Detail (many Detail to one Master), primary key unspecified, containing
fields OrderNumber (the link to Master), SKU, Quantity, and Location.
This table is the basis of your subform. You want to combine
information from these tables and insert it into a table I'll call
Transactions, with (at least) these fields: OrderNumber, M3, M4, SKU,
Quantity, Location. You probably also want some sort of transation type
code and maybe a transaction date/time, but I'll ignore that for the
moment.
If we are not concerned with selections from or filters on the subform,
so that records are to be created in the Transactions table for *all*
Detail records that are related to the current OrderNumber on the main
form, then the procedure to copy these records need have no more code
than this (executed from the main form):
'----- start of code (WARNING: Air Code) -----
Private Sub cmdCopyToTransactions_Click()
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim strSQL As String
If IsNull(Me!OrderNumber) _
Or IsNull(Me!M3) _
Or IsNull(Me!M4) _
Then
MsgBox "Order number, M3, and M4 must be completed first.", _
vbExclamation, "Insufficient Information"
Exit Sub
End If
strSQL =
"INSERT INTO Transactions " & _
"(OrderNumber, M3, M4, SKU, Quantity, Location) " & _
"SELECT " & _
"OrderNumber, " _
Me!M3 & " AS M3, " & Me!M4 & " AS M4, " & _
"SKU, Quantity, Location " & _
"FROM Detail " & _
"WHERE OrderNumber = " & Me!OrderNumber
Set db = CurrentDb
With db
.Execute strSQL, dbFailOnError
MsgBox _
.RecordsAffected & _
" record(s) were inserted into the Transactions table.", _
vbInformation, "Done"
End With
Exit_Point:
Set db = Nothing
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End Sub
'----- end of code -----
*NOTE* The above code assumes that the fields OrderNumber, M3, and M4
are all numeric. If these are text fields, the SQL statement that is
built must include quote characters surrounding the values that are
being picked up from the form and embedded in the SQL string.
Similarly, if any of them is a date field, it must be expressed as a
date literal, in MM/DD/YYYY format and surrounded by '#' characters.
