copy subform contents from one subform to another

[Trevor]

I am trying to copy contents of one subform and place the contents on another, the contents can me anywhere from 1 line to many lines, everything I have tried so far only copies the first line over from form(VMSU-ILQ) to (VMSU-IL) I have tried booth a lookup query and textbox copy from one from to another here is a sample of my code
Dim stDocName As String
Dim stLinkCriteria As String
'Lbillfor = DLookup("[Billing For]", "VMSU-ILT-Sub", "[IDNumber] = """ & IDNumber & """")
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim L As Form
Set L = Forms![VMSU-IL]
Set Lsub = Forms![VMSU-IL]![InvoiceSubform]
L![Cluster] = Me![Cluster]
L![State] = Me![State]
'Lsub![Billing For] = Lbillfor
Lsub![Billing For] = Me![InvoiceSubform-Query].Form![Billing For]
'Lsub![Quantity] = Me![InvoiceSubfom-Query].Form![Quantity]
Lsub![Amount] = Me![InvoiceSubform-Query].Form![Amount]
Lsub![Transaction Order Number] = Me![InvoiceSubform-Query].Form![Transaction Order Number]
DoCmd.Close acForm, "VMSU-ILQ", acSaveNo
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria

its commented out now but I was uncomenting lines 3 and 11 and commenting line 12 to test each condition of lookup verse textbox
either way I was only getting the first record enterd into the subform.
the subform is in Datasheet view, the linked control is IDNumber.
If you would like to know the purpose for the subform is to list what the person on the list is beeing billed for and the reason I want to copy all contents of the subform is in case there is multiple people being billed for the same thing. the main form only ailows for entry of one individual at a time.
Any help whould be a great help at this point, thank you

[orange]

I am trying to copy contents of one subform and place the contents on another, the contents can me anywhere from 1 line to many lines, everything I have tried so far only copies the first line over from form(VMSU-ILQ) to (VMSU-IL) I have tried booth a lookup query and textbox copy from one from to another here is a sample of my code
Dim stDocName As String
Dim stLinkCriteria As String
'Lbillfor = DLookup("[Billing For]", "VMSU-ILT-Sub", "[IDNumber] = """ & IDNumber & """")
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim L As Form
Set L = Forms![VMSU-IL]
Set Lsub = Forms![VMSU-IL]![InvoiceSubform]
L![Cluster] = Me![Cluster]
L![State] = Me![State]
'Lsub![Billing For] = Lbillfor
Lsub![Billing For] = Me![InvoiceSubform-Query].Form![Billing For]
'Lsub![Quantity] = Me![InvoiceSubfom-Query].Form![Quantity]
Lsub![Amount] = Me![InvoiceSubform-Query].Form![Amount]
Lsub![Transaction Order Number] = Me![InvoiceSubform-Query].Form![Transaction Order Number]
DoCmd.Close acForm, "VMSU-ILQ", acSaveNo
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria

its commented out now but I was uncomenting lines 3 and 11 and commenting line 12 to test each condition of lookup verse textbox
either way I was only getting the first record enterd into the subform.
the subform is in Datasheet view, the linked control is IDNumber.
If you would like to know the purpose for the subform is to list what the person on the list is beeing billed for and the reason I want to copy all contents of the subform is in case there is multiple people being billed for the same thing. the main form only ailows for entry of one individual at a time.
Any help whould be a great help at this point, thank you
Can you attach a sample db?

[Trevor]

I think I may have figured it out, I just got to work, but havdn't had a chance yet to try it my plan is the same as one of my other posts about looping through an RST and order by IDNumber here is a link to my Rst Loop post, because its in datasheet view, i think its about the only way I can acomplish copying all contents of the subform
http://www.vbaexpress.com/forum/showthread.php?t=17332

[Trevor]

here is my code, but I have 2 problems, I need help with ;
Dim loDb As DAO.Database
Dim loRst As DAO.Recordset
Set loDb = CurrentDb
Set loRst = loDb.OpenRecordset("QryInvoiceCopy")
With loRst
Do Until .EOF
SendToo = SendToo & .Fields("[Billing For]") & ""
.MoveNext
Lsub![Billing For] = .Fields("billingfor") & vbNewLine
End With
Loop


loRst.Close
Set loRst = Nothing
Set loDb = Nothing
SendTo = SendToo
Loop

End Sub
first problem is Set loRst = loDb.OpenRecordset("QryInvoiceCopy") this line of my code causes an error when run Error too few peramitors, expected 1
and my second problem, witch I suspect will fix the first is on my criteria field for my query I have set to order by IDnumber on the form and I try to put an = infont of the expression, it seems to save but when i go to run the query the = is not there
and that citera line is : [forms]![VMSU-ILQ]![IDNumber]
I'm trying for
=[forms]![VMSU-ILQ]![IDNumber]
but that doesn't seem to be working for me
to clear up any questions the field in the table is [Billing for] , the uniqe field is the IDNumber on the VMSU-ILQ form , so what should happen is that when the command button is clicked it should open that record set order by IDNumber then place the [billing for] entries in the propor field in the subform. I have it set right now to .fields ("[billing for]") & vbnew line, but I realy don't need the vbnew line I need it to go to the next entry below the previous ( arrow down), i can't use sendkeys, i get expected end of statment error.
i just changed my query critera to [forms]![VMSU-ILQ]![IDNumber] but I still get the error: too few peramitors expected 1 on the same line.

[Trevor]

now when i run the query with the critera change it show what it should show but when i run my code I still get the 3061 error

[orange]

now when i run the query with the critera change it show what it should show but when i run my code I still get the 3061 error

Trevor,

Here is a link that might shed some light on the error.

http://www.pcreview.co.uk/forums/thread-1632430.php

I don't undestand
SendTo = SendToo

[Trevor]

The method of using the query was causing the error 3061 expected 1
so I have changed it up the code a little, it seems to want to work but still isn't working correctly , I need each entry to go to the next line below the previous one for it respective field , the code I have now, cause access to not respond, I have a "," as a seperator , when what i realy need is for it to enter the next entry in the box below it(subform in databsheet view)

Dim qryDB As DAO.Database
Dim loRst As DAO.Recordset
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & "';")
With loRst
Do Until .EOF
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim L As Form
Set L = Forms![VMSU-IL]
Set Lsub = Forms![VMSU-IL]![InvoiceSubform]
Lsub![Billing for] = .Fields("billing for")
DoCmd.Close acForm, "VMSU-ILQ", acSaveNo
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Loop
End With
Set loRst = Nothing
Set qryDB = Nothing
loRst.Close
End sub
ps. the sendtoo = sendToo , was from my loop rst problem I was having before, and has nothing to do with this post, other that it was part of that previous post I had the link to, to show what method I was going to attemp to use to copy subform in datasheet view to another subform(also in datasheet view) Oddly enogh the same link you sent me, is the same link I saw earlier and decied to use a select statment instead of the query I was trying to use, thanx.

[Trevor]

I am using this code, but doest populate beyond the first line
and also produces an error "text enterd is not vaid for the field" but the text is vaid because I can enter text into it manualy just fine;
fixed the error w/ resum next , stil can't populate beyond first row

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.OpenRecordset("SELECT * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & "';")
With loRst
Do Until .EOF
.MoveNext

Lsub![Billing for] = .Fields("Billing For") & "," ' I am getting error: "no current record here"
Loop
End With
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
DoCmd.Close acForm, "VMSU-ILQ", acSaveNo
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

[orange]

I am using this code, but doest populate beyond the first line
and also produces an error "text enterd is not vaid for the field" but the text is vaid because I can enter text into it manualy just fine;
fixed the error w/ resum next , stil can't populate beyond first row

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.OpenRecordset("SELECT * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & "';")
With loRst
Do Until .EOF
.MoveNext

Lsub![Billing for] = .Fields("Billing For") & "," ' I am getting error: "no current record here"
Loop
End With
loRst.Close
Set loRst = Nothing
Set loDb = Nothing
DoCmd.Close acForm, "VMSU-ILQ", acSaveNo
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub


A couple of things.
stLinkCriteria is never valued so why not just comment it out.
move the .MoveNext command to just before the Loop statement

How many records are in [VMSU-ILT-Sub]?
Can you provide some data values ?

[Trevor]

records in vmsu-ILT-sub can vary from 1- any # you want to pick
vmsu-ilt-sub can store any # of records no max value.

what I have been using for testing in the billing for I have used an LU with values of Abc,def , hij , ect.... , yes Ive been using letters ow the alphabet to test
I commentted Dim stlinkcritera and move the .movenext as you suggested but still it is only comying 1 line , in the case it happents to be the last entry on the subform

[Trevor]

in the example I'm testing there happens to be 4 [billing for] enteries in vmsu-ILT sub, if your are asking that or for the columes , there are [Billing for], [TO Number]- uses an input mask, [quantity], [Amount], [Total], I Don't think You can feed values to Total since its controle source is a formula.
your prevous suggestion about moving the .movenext took care of any error message I was previously getting.

[orange]

in the example I'm testing there happens to be 4 [billing for] enteries in vmsu-ILT sub, if your are asking that or for the columes , there are [Billing for], [TO Number]- uses an input mask, [quantity], [Amount], [Total], I Don't think You can feed values to Total since its controle source is a formula.
your prevous suggestion about moving the .movenext took care of any error message I was previously getting.

Is it working? Or is it failing somewhere else?

[Trevor]

It is only coping one line of the subform not all of the subform contentss like it should ( not subform is datasheet view)
IE [billng for] = ghi
mno
pqur
xyz
The only rest that seems to copy over is [billing for] xyz

I have no clue what is happenin to all of the other records I am trying to copy code is now :

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.OpenRecordset("SELECT * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & Me.[IDNumber] & "';")
With loRst
Do Until .EOF

Lsub![Billing for] = .Fields("Billing For") & ","
Lsub![Quantity] = .Fields("Quantity") & ","
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

If I change the coe line for Lsub![Billing for] to

Lsub![Billing for] = Lsub![Billing for] & .Fields("Billing For") & ","
it add 4 or how ever many billing for entries to the same textbox seperated by a "," but i need each seperate entry to go to the box below the previous entry

[Trevor]

I read a post here http://groups.google.com/group/microsoft.public.access.forms/browse_thread/thread/7e5faf247c4cb9fd/470a432177b16b7%23470a432177b16b7 about how to copy a subform contents to a new subform, an I need alot of help I would Like to hard code it into my form given the above critera in previous posts, in the post they are using the query builder in access but when you copy and past the query into a form it doesn't work it errors out.
thanks for helping

[Trevor]

This is the query I came up with and I'm getting error expected end of statement after insert and at the begining and end of every line.
(Im tryiny tohard code it into my form, I haven't tried it any other way)
after I run the query I need the results to go to my subform [VMSU-IL]!invoiceSubform]

INSERT INTO Forms![VMSU-IL]![InvoiceSubform] ([Billing For], [Quantity]
[Amount] ,[Field4 Name])
FROM [VMSU-ILT-sub] WHERE [IDNumber] = " & Me.IDNumber)

[orange]

This is the query I came up with and I'm getting error expected end of statement after insert and at the begining and end of every line.
(Im tryiny tohard code it into my form, I haven't tried it any other way)
after I run the query I need the results to go to my subform [VMSU-IL]!invoiceSubform]

INSERT INTO Forms![VMSU-IL]![InvoiceSubform] ([Billing For], [Quantity]
[Amount] ,[Field4 Name])
FROM [VMSU-ILT-sub] WHERE [IDNumber] = " & Me.IDNumber)
Trevor,

I think we need to look at exactly what you are trying to do. Then look at ways to do it.
Copying data from a subform to another subform is not the issue in my view.
Forms and subforms are interfaces to underlying tables. You may need to create a second subform with a different recordsource (or query), but it isn't an issue of copying data between subforms - at least not on the surface.

As far as the INSERT statement goes, INSERT will insert data into a Table. So your INSERT into a Form just will not work.

Can you describe what you are trying to do in business terms -- not in forms and subforms, but in terms of what you need done?

[Trevor]

ok well you know I have a form w/ subform, the purpose is to log what is beeing billing for each individual, and the subform lists what is beeing billed,
In the even that the billing is the same for another individual, I have a repeat bill button that the user can click and it will populate the subform on the log screen with the contents of the invoicesubform on the previous query screen , to eminate redundent typing by the end user, because when they enter these billing it is usualy numerus people with the same bill, just different people who the bill is for. the log screen must also be able to be blank in the even that it is not a reapeat bill.
I hope this is what you wanted to know and doesn't confuse you.
I check this periodicly though out they day if you need more help with my explination.

[Trevor]

here is a copy of my form

[Trevor]

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"

[orange]

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"

I do not understand what it is you are trying to do in business terms.

I have looked at your database.
You have 4 tables:
BillLu
BillPayedLU
VMSU-ILT-Main
VMSU-ILT-Sub

And 4 Forms:
InvoiceSubform-Log
InvoiceSubform-Query
VMSU-IL
VMSU-ILQ

No descriptions.

[Trevor]

I am trying to copy all contents of the subform on the [VMSU-ILQ] form to the subform on the [VMSU-IL] form