Multiple Records in a Subform

[ccg_0004]

If I have a Order Form with a subform for Order Details, can I have a append query that creates a log for each record in the subform? So far it will only create a record for the first entry in the subform.

OrderForm=
Name
Address
Date
Notes

Subform=
Tool1
Tool2
Tool3


I want to click "Update" button and create a log entry for each tool into a table called event history.

When I click update I then want 3 separate records in the tbl_EventHistory.

Tool Date Address Event
Tool1 5/5 1600 Pennsylvania Ave. On Rent
Tool2 5/5 1600 Pennsylvania Ave. On Rent
Tool3 5/5 1600 Pennsylvania Ave. On Rent

And when the the tools are returned I want to click a "Return" button and add that information to the log.

Tool Date Address Event
Tool1 5/15 1600 Pennsylvania Ave. Off Rent
Tool2 5/15 1600 Pennsylvania Ave. Off Rent
Tool3 5/15 1600 Pennsylvania Ave. Off Rent

Thanks much
CG!!!

 

[pbaldy]

If you're comfortable with recordsets, you could open a recordset using the recordset clone of the subform, looping through the records using the value(s) from each to append a record to the other table (probably using a second recordset based on that table).

 

[ccg_0004]

Thanks for the help Paul. I have done some preliminary rsch on Recordsets through this site, Google, and Access but I would not even know where to begin. Thank you for trying, let me know if you come across anything else...

 

[pbaldy]

Quick and dirty, but maybe gets you started:

  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset

  Set db = CurrentDb()

  strSQL = "SELECT * FROM SecondTable WHERE 1=0"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  With Me.SubformControlName.Form.RecordsetClone
    If (.RecordCount) Then
      .MoveFirst
      Do Until .EOF
        rs.AddNew
        rs!TargetField = .Fields!SourceField
        rs!SecondField = .Fields!SecondField
        rs.Update
        .MoveNext
      Loop
    End If
  End With

  Set rs = Nothing
  Set db = Nothing

 

[ccg_0004]

I am truly amazed at the support I get on this site for my membership price. *FREE* I cannot believe that users like you are so willing to take time to help beginners like myself and others. One love PBALDY, one love

CG

 

[pbaldy]

No problem, mon.

Though it should be noted that sometimes the help you get is worth what you paid for it.

 

[ccg_0004]

clever

No problem, mon.

Though it should be noted that sometimes the help you get is worth what you paid for it.

Clever on both counts my friend, very nice... Very insightful the second statement, never considered the site possbily being detrimental to a DB developer (too much of a good thing, maybe?)

Quickie on the code you sent: Works great! Yay!! Need more- BOO!!!

The issue is: I need one of the target fields to be filled with a value from the *MAIN* form. I tried this to work...

Private Sub Command86_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()

strSQL = "SELECT * FROM LocationStatus WHERE 1=0"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With Me.[zzzInvoice Details].Form.RecordsetClone
If (.RecordCount) Then
.MoveFirst
Do Until .EOF
rs.addnew
rs!ToolID = .Fields!ToolID
rs!DateofEvent = .Fields![Start Date]
rs!Event = "Off Rent"
rs!CurrentLocation = Forms![Rental Contract 2]!Technician
rs.Update
.MoveNext
Loop
End If
End With

but it did not work. Error msg=

RUNTIME 2450 - can't find the specified form (gives name) referred to.

(and how do you get the code to be in a separate box when you post your comments? not doing so can be sloppy at times...)

 

[pbaldy]

If that textbox is on the same form the code is running from:

rs!CurrentLocation = Me.Technician

Otherwise it looks fine, which would indicate that either the form or control name is misspelled (you want the name of the control containing the value).

If you surround your code with code tags you'll get the box. In regular reply mode, the # icon.

My comment on value from the site was meant as a joke, but it is somewhat true in that you don't necessarily know the skills of the person responding. You could ask a question and get a totally bogus answer from somebody. I learned much of what I know from sites like this, so I certainly don't consider it detrimental.