Copy subform contents

[Trevor2007]

Hi Everyone,
Im New here,
I am trying to copy the contents of a subform(Datatsh eet view)and populate the subform(datashe etview) on the next screen with the contents it just copied from the previous subform(Datashe et view).
the purpose is if the billing choices are the same for the next individuale beeing entered into the db insteead of having to re-type everything. the code I have only copies the last record in the subform (DataSheet view)


[CODE=vb]Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Set Lsub = Forms![VMSU-IL]![InvoiceSubform]
Set loRst = CurrentDb.OpenR ecordset("SELEC T * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & "';", dbOpenDynaset)
Dim I As Long
With loRst
Do Until .EOF


Lsub![Billing for] = .Fields("billin g for") & vbup
Lsub![Quantity] = .Fields("Quanti ty")
Lsub![Transaction order number] = .Fields("TO Number")
.MoveNext


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[/CODE]

[ADezii]

Hi Everyone,
Im New here,
I am trying to copy the contents of a subform(Datatsh eet view)and populate the subform(datashe etview) on the next screen with the contents it just copied from the previous subform(Datashe et view).
the purpose is if the billing choices are the same for the next individuale beeing entered into the db insteead of having to re-type everything. the code I have only copies the last record in the subform (DataSheet view)


Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Set Lsub = Forms![VMSU-IL]![InvoiceSubform]
Set loRst = CurrentDb.OpenR ecordset("SELEC T * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & "';", dbOpenDynaset)
Dim I As Long
With loRst
Do Until .EOF


Lsub![Billing for] = .Fields("billin g for") & vbup
Lsub![Quantity] = .Fields("Quanti ty")
Lsub![Transaction order number] = .Fields("TO Number")
.MoveNext


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

Depending on the specific circumstances, you may be able to bypass the majority of your code:

1. Declare a Variable to hold the Record Source of the Sub-Form on Form1
   [CODE=vb]Dim strRecordSource As String[/CODE]
   
2. Assign the Record Source of the Sub-Form to this Variable (I'll use an Absolute Reference)
   [CODE=vb]strRecordSource = Forms!Form1![<SubForm1_Contr ol>].Form.RecordSou rce[/CODE]
   
3. Assign this Variable to the OpenArgs Argument when you Open the 2nd Form
   [CODE=vb]DoCmd.OpenForm "Form2", acNormal, , , acFormEdit, acWindowNormal, strRecordSource[/CODE]
   
4. In the Open() Event of the 2nd Form, set the Record Source of the Sub-Form to the OpenArgs Property which was previously passed in the OpenForm() Method
   [CODE=vb]Private Sub Form_Open(Cance l As Integer)
   Forms!Form1![<SubForm2_Contr ol>].Form.RecordSou rce = Me.OpenArgs
   End Sub[/CODE]

[Trevor2007]

Depending on the specific circumstances, you may be able to bypass the majority of your code:

1. Declare a Variable to hold the Record Source of the Sub-Form on Form1
   [CODE=vb]Dim strRecordSource As String[/CODE]
   
2. Assign the Record Source of the Sub-Form to this Variable (I'll use an Absolute Reference)
   [CODE=vb]strRecordSource = Forms!Form1![<SubForm1_Contr ol>].Form.RecordSou rce[/CODE]
   
3. Assign this Variable to the OpenArgs Argument when you Open the 2nd Form
   [CODE=vb]DoCmd.OpenForm "Form2", acNormal, , , acFormEdit, acWindowNormal, strRecordSource[/CODE]
   
4. In the Open() Event of the 2nd Form, set the Record Source of the Sub-Form to the OpenArgs Property which was previously passed in the OpenForm() Method
   [CODE=vb]Private Sub Form_Open(Cance l As Integer)
   Forms!Form1![<SubForm2_Contr ol>].Form.RecordSou rce = Me.OpenArgs
   End Sub[/CODE]

I tired suggesstions 1 and 3 and both give me errors saying it cannot find my form, subform because it is either misspelling or doesn't exist, I used the same spellings as preiously in my code, but I haven't changed their names. option 4 I don't think I can use becase the subform being copied to is also used for new entdes and will need to be left blank if that repeat button isn't clicked.
I can get all of the billing for entries into the form , if I change the billing for line to lsub![Billing for]= lsub![billing for] & .fieldes("billi ng for") & "," or & " "but then it add all the entries to the same textbox and will seperate them by a space
or comma but I need ech entery in its respective textbox

[Trevor2007]

It looks like I need to reperse my where clause to where Me.IDNumber = IDNumber,
It looks like what is being returned is # of records in that query that have the same ID Number as the form IDNumber
but I get error to few peramitors when I Try

StrSql = "SELECT * FROM [VMSU-ILT-Sub] WHERE" _
& " Me.[IDNumber]= '" & [IDNumber] & "'"

using this code :

Dim stDocName As String
Dim stLinkCriteria As String
Dim RS As DAO.Recordset
Dim StrSql As String
Dim IDNumber As String

StrSql = "SELECT * FROM [VMSU-ILT-Sub] WHERE" _
& " Me.[IDNumber]= '" & [IDNumber] & "'"

Set RS = CurrentDb.OpenRecordset(StrSql)
MsgBox "Number of Records =" & RS.RecordCount



With RS
Do While Not RS.EOF
    
![Billing For] = [VMSU-ILT-Sub]![Billing For]




MsgBox "Billing for Items with ID Numbers from " & IDNumber & _
" to " & IDNumber & "  have been added ", vbInformation, "Addition Complete"
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
RS.Close
Set RS = Nothing
Forms![VMSU-IL]![InvoiceSubform].Requery
Loop
End With
End Sub

I know the field Im setting the value to is wrong but even setting it forms![VMSU-IL]![invoiceSubform]![Billing for] returns error "Can't find "[" Referred ta in your expression or macro"

[NeoPa]

Trevor, if you still need help with this you're going to need to put your code in the tags provided (I've done this for you this time) and specify more clearly exactly what's going wrong and where (line number etc).

[Trevor2007]

Trevor, if you still need help with this you're going to need to put your code in the tags provided (I've done this for you this time) and specify more clearly exactly what's going wrong and where (line number etc).

Dim stDocName As String
Dim stLinkCriteria As String
Dim RS As DAO.Recordset
Dim StrSql As String

stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , ,  ' open VMS-IL form
stlinkCriteria
StrSql = "SELECT * FROM  
[VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & 11. "'" = get IDNumbers from  Table 12.[VMSU-ILT-Sub] that match the IDNumber 13. On form [VMSU-IL]

Set RS = CurrentDb.OpenRecordset(StrSql)' 15. set Rs to the strSQl string
MsgBox "Number of Records =" &             17. RS.RecordCount' msg how many records in  18. table [VMSU-ILT

With RS
Do While Not RS.EOF
 
Forms![VMSU-IL]![InvoiceSubform]![Billing] 22.' Copy Contdents[Billing for] from strsql to 23.field[Billing for on [VMSU-IL]                    24. [InvoiceSubform] .[Billing for] field

stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , 27.stLinkCriteria
Loop ' till all entry's from DB Reach EOF
RS.Close ' close RS
Set RS = Nothing' set RS to nothisg
Forms![VMSU-IL]![InvoiceSubform].Requery 32.' refesh subform although I probably don't 33. need this line
End Sub

WhenI run this code witch should copy the values of of [VMSU-ILQ]invoiceSubform. [billing for] fields (continious subform) to The [Billing for] field(s) on form [VMSU-IL]![InvoiceSubform]
when I run the code only the last entry in the subform on the VMSU-ILQ screen is copied over to the VMSU-IL invoice Subform [Billing for field], each entry should apear below the previous entry
I can't use an onOpen even because the same form [VMSU-IL][InvoiceSubform] needs to be able to stay blank incase it is a new invoice
I have attached a sample of my DB the subforms may be in Datasheet view, That shouldn't Change the code any all I have changeed is the view in the on im working wish for estetic reasons.
I Hope this helps , if anyone needs more of an explenation you could just send me a PM.

[NeoPa]

Your database seems to crash my system. Is it A2007? I'm running A2003.
I was able to see some of the code, though in a limited fashion, and noticed all sorts of problems due to the fact that you don't use "Option Explicit". The way to set this on by default is to go to the Options in the Access VBA debugger window (Tools / Options / Editor) and select Require Variable Declaration. When this has been set you need to check that each existing module has the line "Option Explicit" at the top then try to compile it. This will help you immeasurably to find and fix the basic errors in your code. This is always worth doing before debugging the logic.

Perhaps you could post your fixed version when that's done and we can continue from there.

[ADezii]

Using a Backup Copy of your Database, completely replace the code in the Repeat_TO_Numbe r_Button_Click( ) Event, minus any Error Checking, with the below code segment and let me know what happens:
[CODE=vb]
Dim stDocName As String, stLinkCriteria As String, strrst As String
Dim loDb As DAO.Database, rst_2 As DAO.Recordset
Dim loRst As DAO.Recordset, Lsub As Variant, strHighNumber As String

Set loDb = CurrentDb()
Set rst_2 = loDb.OpenRecord set("VMSU-ILT-Sub", dbOpenTable)

'Sequence Numbers must be in exact alignment, so for simplicity, duplicate the code
strHighNumber = Nz(DMax("[TheSequence]", "VMSU-ILT-Main", _
"Left([TheSequence], 4) = '" & Format(Date, "yymm") & _
"'"), "")

If strHighNumber = vbNullString Then 'No numbers for this year/month
strHighNumber = Format(Date, "yymm0001")
Else
strHighNumber = Left(strHighNum ber, 4) & _
Format(Right(st rHighNumber, 4) + 1, "0000")
End If

Set loRst = CurrentDb.OpenR ecordset("SELEC T * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & "';", dbOpenDynaset)

With loRst
Do Until .EOF
rst_2.AddNew
rst_2![IDNumber] = "i" & strHighNumber
rst_2![Billing For] = .Fields("Billin g For")
rst_2![Quantity] = .Fields("Quanti ty")
rst_2![TO Number] = .Fields("TO Number")
rst_2.Update
.MoveNext
Loop
End With

loRst.Close: Set loRst = Nothing
rst_2.Close: Set rst_2 = Nothing

DoCmd.Close acForm, "VMSU-ILQ", acSaveNo

stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria[/CODE]

[Trevor2007]

Using a Backup Copy of your Database, completely replace the code in the Repeat_TO_Numbe r_Button_Click( ) Event, minus any Error Checking, with the below code segment and let me know what happens:
[CODE=vb]
Dim stDocName As String, stLinkCriteria As String, strrst As String
Dim loDb As DAO.Database, rst_2 As DAO.Recordset
Dim loRst As DAO.Recordset, Lsub As Variant, strHighNumber As String

Set loDb = CurrentDb()
Set rst_2 = loDb.OpenRecord set("VMSU-ILT-Sub", dbOpenTable)

'Sequence Numbers must be in exact alignment, so for simplicity, duplicate the code
strHighNumber = Nz(DMax("[TheSequence]", "VMSU-ILT-Main", _
"Left([TheSequence], 4) = '" & Format(Date, "yymm") & _
"'"), "")

If strHighNumber = vbNullString Then 'No numbers for this year/month
strHighNumber = Format(Date, "yymm0001")
Else
strHighNumber = Left(strHighNum ber, 4) & _
Format(Right(st rHighNumber, 4) + 1, "0000")
End If

Set loRst = CurrentDb.OpenR ecordset("SELEC T * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & "';", dbOpenDynaset)

With loRst
Do Until .EOF
rst_2.AddNew
rst_2![IDNumber] = "i" & strHighNumber
rst_2![Billing For] = .Fields("Billin g For")
rst_2![Quantity] = .Fields("Quanti ty")
rst_2![TO Number] = .Fields("TO Number")
rst_2.Update
.MoveNext
Loop
End With

loRst.Close: Set loRst = Nothing
rst_2.Close: Set rst_2 = Nothing

DoCmd.Close acForm, "VMSU-ILQ", acSaveNo

stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria[/CODE]

Thank you ADezii,
I have implemented your code but compiler is not happy with line 9 -13 (format Dmax).
I was atempting to do it with only 1rst , I will post my DB w/added code, I have also made the added change "option explicit" by neoPa.
DB is A2003

[Trevor2007]

Thank you ADezii,
I have implemented your code but compiler is not happy with line 9 -13 (format Dmax).
I was atempting to do it with only 1rst , I will post my DB w/added code, I have also made the added change "option explicit" by neoPa.
DB is A2003

Trying to attach file its not attaching, Not if it is not attached the the changes I make were both ADezii's and Neopa's and db is A2003
If I Comment aut ADezii's Format code My subform is blank, and won't this code add to the vmsu-ILT-Sub table not the VMSU-IL Inovoice subform?, I'm trying to copy contents of [VMSU-ILQ[InvoiceSubform]to the [vmsu-IL] [invoiceSubform] on [Repeat To Number]button click.
Trevor2007