Trevor2008
Technical User
Hi I am trying to copy the contents of a datasheet subform on a button click to a blank subform on a different screen
the purpose is for billing an indiviuale if the billing is the same but the individual is different this will eliminate repetitive typeing for the end user, my code will only move the last record to the new form, I can't use an onopen event on my form the subform is being copied to because the same form needs to remain blank if it is a new bill. here is my code:
Dim stDocName As String
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim strrst As String
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Set Lsub = Forms![VMSU-IL]![InvoiceSubform]
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & "';", dbOpenDynaset)
With loRst
Do Until .EOF
Lsub![Billing for] = .Fields("Billing For")
Lsub![Quantity] = .Fields("Quantity")
Lsub![Transaction order number] = .Fields("TO Number")
.MoveNext
'Forms![VMSU-IL]![InvoiceSubform].Requery
Loop
End With
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
DoCmd.Close acForm, "VMSU-ILQ", acSaveNo
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ErrorHandler:
If Err.Number = 3021 Then
Resume Next
End If
End Sub
the purpose is for billing an indiviuale if the billing is the same but the individual is different this will eliminate repetitive typeing for the end user, my code will only move the last record to the new form, I can't use an onopen event on my form the subform is being copied to because the same form needs to remain blank if it is a new bill. here is my code:
Dim stDocName As String
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim strrst As String
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Set Lsub = Forms![VMSU-IL]![InvoiceSubform]
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & "';", dbOpenDynaset)
With loRst
Do Until .EOF
Lsub![Billing for] = .Fields("Billing For")
Lsub![Quantity] = .Fields("Quantity")
Lsub![Transaction order number] = .Fields("TO Number")
.MoveNext
'Forms![VMSU-IL]![InvoiceSubform].Requery
Loop
End With
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
DoCmd.Close acForm, "VMSU-ILQ", acSaveNo
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ErrorHandler:
If Err.Number = 3021 Then
Resume Next
End If
End Sub