Loop through Subform Records

[karl009]

Hi,

Am working on a database that will hold some data for two departments.

The data from dept1 is entered, when entered I would like it to pass to dept2, so I have four tables say;

dept1header
dept1lines
dept2header
dept2lines

I am able to get the header information from the input form aka dept1 to the dept2 table using the following code;

Private Sub PasstoSales_Click()

Dim FLineRefs As String
Dim MORefs As String

FLineRefs = Forms![frmMReport]![FCOLineRef]
MORefs = Forms![frmMReport]![MORef]

SQLCom = "INSERT INTO tblSalMHeader (FCOLineRef, MORef) VALUES ('" & FLineRefs & "', '" & MORefs & "')"

DoCmd.RunSQL SQLCom

End Sub

How would I loop through sub form records doing the same as above.

Many Thanks
Karl

 

[DavidAtWork]

what is the SQL that drives the sub form, I would think you could use that as a base for your INSERT statement, something like:
INSERT INTO tblMyTable(field1, field2, field3) SELECT ? FROM ? where this part would be something similar to the SQL that drives the sub form records
David

 

[jdraw]

Could you tell us in plain English what you are trying to do? I'm not following the header and lines it sounds like a report.
Someone may suggest other options/approaches if they understood the requirement.

 

[karl009]

Hi,

When I enter data into a form I would like the data to be inserted into two tables.

So I have;

TableA1Header (MainForm)
TableA2Lines (Subform)

TableB1Header
TableB2Lines

The form that is created is inputting the data into tables A1 and A2, I would like the same data to go into B1 and B2.

Any ideas?

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=99777
About half way down - you can download this sample to look over.
Sample database with a form and subform and their associated one to many tables

If you download and look at the database examples here, maybe you can get some ideas. This demonstrates the association of one-to-many between two tables.

If you have a second table to update with the same values, there are several ways to do that. Using Execute with an append query (in vba code) would be easy.
Or, use a record set in a Function.
For example: When one form is updated, I copy selected fields over to a transaction log table - completely independent of the form's table.
The concept is to build a function that takes selected values from a form or record and just shove them over to a different table.

Function LogUsage_GIS(ByVal strFormName As String, _
    strCallingProc As String, Optional ControlName) As Boolean
10       On Error GoTo Err_LogUsage_GIS
          Dim rst As DAO.Recordset  ' The tLogUsage_GIS table
20            Set rst = CurrentDb.OpenRecordset("tLogUsage_GIS", 2, dbAppendOnly + dbSeeChanges)
30            rst.AddNew
40                rst![UseDate] = Now()
50                rst![strFormName] = strFormName
60                rst![CallingProc] = strCallingProc
70                rst![UserName] = Environ("username")
80                If Not IsMissing(ControlName) Then
90                    rst![ControlName] = Left(ControlName, 75)
100               End If
110           rst.Update
120           rst.Close
130           LogUsage_GIS = True

Exit_LogUsage_GIS:
140       Set rst = Nothing
150       Exit Function

Err_LogUsage_GIS:
          ' just resume next and the usage is not logged
  'MsgBox Err.Number & Err.Description
160       Err.Clear
170       Resume Exit_LogUsage_GIS
End Function

 

[Pat Hartman]

From your original description, it sounds like you are thinking of Access as you would think of a spreadsheet. In a relational database, it would be wrong to have a table for each department. You would use queries to segregate data if you wanted to see only data for a single department. What happens to the data in table 2 if you change the values in table 1? Are you expecting the two to sync? That isn't going to happen. Use a single table.

If you are trying to implement some kind of work-flow then you need a separate table that logs the hand off of a record from one department to another.