Full Version: Open and update multiple instances of one form based on continuo
UtterAccess Forums > Microsoft® Access > Access Forms
wtabor
I have an Order Request Items form (continuous subform to Order Request form) that has the following columns:
RequestID: autonumber field
Supplier: combobox based on Supplier table
ComponentType: combobox based on PurchasedComponents table, filtered by Supplier
PartNo: combobox based on PurchasedComponents table, filtered by Supplier and ComponentType
Quantity: double
JobNo: combobox based on Work table
SubmitToPurchasing: boolean
On the order request items, there will be instances where each line may have a different supplier, and multiple instances of the same supplier.
I have a cmdbutton, that when clicked, I want to create a separate purchase order for each supplier and fill in the purchase order line items with information from the order request line items. I want to carry over the Quantity, PartNo and JobNo.
The Supplier name would go into the PurchaseOrder form (Parent) and the Quantity, PartNo and JobNo would go into the PurchaseOrderItems subform - where there will be multiple line items.
I am not sure where to even begin doing this. Is there a way to count how many suppliers are listed and then use that as a loop counter for creating the purchase orders? Or is there a simpler way?
Graham R Seach
All you need to do is create a clone of the subform's recordset, cycle through its records creating order records as you go. I assume the button is on the main form.
Private Sub cmdCreateOrders_Click()
Dim rs As DAO.Recordset
Dim db As Database
Dim ws As DAO.Workspace
Dim strSQL As String
On Error Goto Proc_Err
Set ws = DbEngine(0)
Set db = CurrentDb
ws.BeginTrans
Set rs = Me.subformname.Form.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
'I don't know where you get th supplier name from.
'Maybe another query?
'But after you get it, you can write code here to put it into the main form.
strSQL = "INSERT INTO sometable (field1, field2) VALUES (" & rs!field1 & "," & rs!field2 & ")"
db.Execute strSQL, dbFailOnError
rs.MoveNext
Loop

ws.CommitTrans
Proc_Exit:
Set db = Nothing
Set rs = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Number & vbCrLf & Err.Description
ws.RollBack
Resume Proc_Exit
End Sub
Oknow this isn't great, but there's not enough information for me to write all of it. In any case, itshould get you started.
wtabor
I will try it right now - thanks so much!
siraceman
I have a similar problem and have tried many ways to solve it, unsuccessfully.
The problem.
I have a table with say 30 columns.
I need to append to an existing table the first 3 columns of the old table and repeat the data in the first 3 columns as new rows for each of the remaining columns.
cenario: Student takes a multiple choice test. Answers are scanned.
Results are in a table "ScannerResults"
"ScannerResults" has columns Date, StudentID, Name, Q1,Q2,Q3,........Q25 sometimes more.
Typical row looks like this
Date ID Name Q1 Q2 Q3 Q4 Q5
7/23/2008 4345 Able Man A B D A B
7/23/2008 5556 Rose Lee A C D A C
8/3/2008 4345 Able Man B C D B A
To track performance on a number of similar questions over different tests I will group students and their results for each question separately.
I need the new output table "Full History" to look like this
First 3 Columns in "ScannerResults" to be Date, StudentID, Name, and the 4th col to be "Response"
Typical rows to look like this
Date ID Name Response
7/23/2008 4345 Able Man A
7/23/2008 4345 Able Man B
7/23/2008 4345 Able Man D
7/23/2008 4345 Able Man A
7/23/2008 4345 Able Man B
7/23/2008 4345 Rose Lee A
7/23/2008 4345 Rose Lee C
7/23/2008 4345 Rose Lee D
7/23/2008 4345 Rose Lee A
7/23/2008 4345 Rose Lee C
8/3/2008 4345 Able Man B
8/3/2008 4345 Able Man C
8/3/2008 4345 Able Man D
8/3/2008 4345 Able Man B
8/3/2008 4345 Able Man A
In this way I can group similar questions together to determine strengths and weaknesses over different tests.
A colleague has provided me with this so far but I can't get it to work. I don't have to make a new table. I will be APPENDING to an existing table.
I have also researched the EOF statement to find the last field. Other code I have looked at suggests using EOF to determine the end of the field. Then using that in a loop.
Hope someone can help.i have tried a lot of places
Function Transposer(strSource As String, strTarget As String)
Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.Field
Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
Dim i As Integer, j As Integer
Set db = CurrentDb()
Set rstSource = db.OpenRecordset(strSource)
rstSource.MoveLast
' Create a new table to hold the transposed data.
' Create a field for each record in the original table.
Set tdfNewDef = db.CreateTableDef(strTarget)
For i = 0 To rstSource.RecordCount
Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
tdfNewDef.Fields.Append fldNewField
Next i
db.TableDefs.Append tdfNewDef
' Open the new table and fill the first field with
' field names from the original table.
Set rstTarget = db.OpenRecordset(strTarget)
For i = 0 To rstSource.Fields.Count - 1
With rstTarget
.AddNew
.Fields(0) = rstSource.Fields(i).Name
.Update
End With
Next i
rstSource.MoveFirst
rstTarget.MoveFirst
' Fill each column of the new table
' with a record from the original table.
For j = 0 To rstSource.Fields.Count - 1
' Begin with the second field, because the first field
' already contains the field names.
For i = 1 To rstTarget.Fields.Count - 1
With rstTarget
.Edit
.Fields(i) = rstSource.Fields(j)
rstSource.MoveNext
.Update
End With
Next i
rstSource.MoveFirst
rstTarget.MoveNext
Next j
db.Close
Exit Function
End Function
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.