DAO.Recordset copy to second recordset 1

[colossalUW]

Hi there,

Access 2000 project

I've tried several iterations of this code (with the help of archived threads), and just can't quite get it to work.

I'm attempting to copy the data from one subform to another if the user chooses yes from a message box.

Basically, the first subform displays Proposed costs for a grant. If the grant status is 'Awarded', then a message box asks the user if they would like to have the Proposed costs entered into the Awarded costs subform.

My thought is to create two recordsets, one from tblCostsProposed and one from tblCostsAwarded, and enter each record from Proposed to Awarded.

When running the below code I get the error message "Item not found in this collection." Any ideas? Thanks much!

Private Sub frmPropSubUp_Exit(Cancel As Integer)
On Error GoTo frmPropSubUp_Exit_Err
'If status is Awarded, then message box asks the user if proposed costs should be entered
'into the actual costs.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim intAns As Integer
Dim rstProp As DAO.Recordset
Dim rstAct As DAO.Recordset

'Checks if status is Awarded, and displays message box if it is
If (Nz(Forms!frmGrantsEntry!GntStatus, "") = "Awarded") Then
intAns = MsgBox("The status of this proposal is Awarded." & _
vbCrLf & " " & _
vbCrLf & "Would you like to enter these Proposed costs into the " & _
"Awarded costs form automatically?" & _
vbCrLf & "If needed, you will be able to change award information " & _
"after performing this action.", vbYesNo, "Awarded Costs:")
'If user chooses No, then sub ends
If intAns = 7 Then
GoTo frmPropSubUp_Exit_Exit
ElseIf intAns = 6 Then
'Create a recordset based on the GC1# entered into the CostsProposed table
Set rstProp = CurrentDb.OpenRecordset("SELECT * FROM tblCostsProposed WHERE [GC1#] = '" & _
Me![GC1#] & "'", dbOpenDynaset)
'Create recordset to hold new entries to CostsActual table
Set rstAct = CurrentDb.OpenRecordset("tblCostsActual", dbOpenDynaset)
'Enter proposed data into actuals table

Do While rstProp.EOF = False
rstAct.AddNew
rstAct![GC1#] = rstProp![GC1#]
rstAct![YRActual] = rstProp![YRProp]
rstAct![DirActual] = rstProp![DirProp]
rstAct![IndAct] = rstProp![IndProp]
rstAct.Update
rstProp.MoveNext
Loop

'Close the recordsets
rstProp.Close
rstAct.Close


'Refresh form so changes are immediately visible
Forms!frmGrantsEntry.Refresh

End If

End If

'Error trap
frmPropSubUp_Exit_Exit:
Exit Sub
frmPropSubUp_Exit_Err:
MsgBox Error$
Resume frmPropSubUp_Exit_Exit

End Sub

 

[nickspace]

Hi,
i have encountered this situation before but dont remember what I did to solve the issue.
Can you try removing ", dbOpenDynaset" from the open recordset statement?

HTH

 

[colossalUW]

Hi,

Thanks for the response.

I removed dbOpenDynaset but received the same error. It seems like it's not finding the records in tblCostsProposed, but they are there. Hmmm...

 

[Lightning]

It could be having trouble with the fieldname [GC1#]. Generally, using special characters in a field name is not a good idea. In this case, the # could be confused for a Date delimiter, which could be causing the problem.

Try renaming the fieldname to remove the # and see if that removes the error message.

HTH
Lightning

 

[Zion7]

It's definately a control that you are trying to reference within one of your recordsets.

Does it not highlight the offensive line?

If you spelled something wrong, or used the wrong name(not from the table), you will get this error. It does not recognize the control.

Try this syntax ..

rstAct("GC1#") = rstProp("GC1#")
rstAct![YRActual] = rstProp![YRProp]

 

[PHV]

I get the error message "Item not found in this collection."
And which line of code highlighted when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tranman]

How about:

rstAct.Fields("GC1#") = rstProp.Fields("GC1#")
rstAct.Fields("YRActual") = rstProp.Fields("YRProp")
.
.

Or:
For ptr = 0 to 3
rsAct.Fields(ptr) = rstProp.Fields(ptr)
Next

Assuming the tables have the same structure?

Tranman


Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant.
Mark Twain

 

[colossalUW]

Zion7, you hit the nail on the head! Changed the syntax and now it works great. Star for you.

Thanks to all for the help.