After spending a day on this (pathetic) I am at my wits end.
The task in my mind is simple:
Open up a form/subform with multiple records on the subform
Open up another form/subform
Copy the records from the orginal subform to the other subform using a Do While Loop
I suspect that the easiest way to do this is to use recordsets but cannot seem to get them to work so I tried this way (excuse the simplicity of the coding idea - I am not really a programmer):
'Find out how many equipment records we need to create
Reccount = DCount([Quote Number], "[Order Details]", "[Quote Number] = " & Forms!Quotation![Quote Number])
'Move to subform and make sure that we are on the first record
Forms!Quotation![Quote Sub].SetFocus
DoCmd.GoToRecord , , acFirst
'Set up do while loop
Do While Reccount > 0
' Fill in the new record on the new form with the details of the first record of the original form
Forms![PO Request]![PO Request Details]!Quantity = Forms!Quotation![Quote Sub]![Quantity]
Forms![PO Request]![PO Request Details]![Quoted Price] = DLookup("PRICE", "Equipment", "EQUIPID = " & Forms!Quotation![Quote Sub]![Euip Id])
Forms![PO Request]![PO Request Details]![Quoted Modelnum] = DLookup("MODELNUM", "Equipment", "EQUIPID = " & Forms!Quotation![Quote Sub]![Euip Id])
Forms![PO Request]![PO Request Details]![Quoted Description] = DLookup("DESCRIPT", "Equipment", "EQUIPID = " & Forms!Quotation![Quote Sub]![Euip Id])
Forms![PO Request]![PO Request Details]![Euip Id] = Forms!Quotation![Quote Sub]![Euip Id]
Forms![PO Request]![PO Request Details]![Ext Price] = Forms!Quotation![Quote Sub]![Quantity] * Forms!Quotation![Quote Sub]![Quoted Price]
'Make sure that the new form has the focus
Forms![PO Request]![PO Request Details].SetFocus
'Save the record just created
RunCommand acCmdSaveRecord
'Start a new record on the new form
DoCmd.GoToRecord , , acNewRec
'Move to the original form and then to the subform
Forms![Quotation].SetFocus
Forms![Quotation]![Quote Sub].SetFocus
'Move to the next record on the orginal form
DoCmd.GoToRecord , , acNext
' Reduce the loop count by one.
Reccount = Reccount - 1
'Start loop again
Loop
So although the procedure seems pretty clear to me in my head for some reason Access doesn't like it. The current error is on the DoCmd.GoToRecord command at the end (where I am moving to the next original record). Access returns a "You can't go to specified record" error.
What is baffling is that the code has filled in the first AND second record of the new form/subform.
I suspect there are far better more elegant ways to do this but no amount of searching the help files has shown it to me!
Any help would be appreciated!
Thanks
RCRobman