copy contentents of subform to subform different form

[Trevor2008]

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

 

[pbaldy]

Presuming your recordset is pulling the correct items, I assume the problem is that the record on the target subform is never changed (you keep overwriting the same record). My preference would be to simply append the data into the table in the background, but you can try using DoCmd.GoToRecord to move the subform to a new record as appropriate in your loop.

Paul
MS Access MVP 2007/2008

 

[Trevor2008]

the target form is blank on launch unless the repeat TO Number button is clicked, I just tried today code:
Dim DB As Database
Dim rs As Recordset
Dim strSql As String
Dim QryRS As Integer
Dim ID As String
Dim stDocName As String
Dim stLinkCriteria As String
ID = Me.IDNumber

stDocName = "VMSU-IL"
'DoCmd.OpenForm stDocName, , , stLinkCriteria
strSql = "SELECT * FROM [VMSU-ILT-Sub] WHERE" _
& " [IDNumber]= '" & ID & "'"

Set rs = CurrentDb.OpenRecordset(strSql)
'Set Lsub = Forms![VMSU-IL]![InvoiceSubform]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Do Until rs.EOF
With rs

Forms![VMSU-IL]![InvoiceSubform]![Billing For] = Me.[Billing For]
End With
Loop
DoCmd.Close acForm, "VMSU-ILQ", acSaveNo
stDocName = "VMSU-IL"
DoCmd.OpenForm stDocName, , , stLinkCriteria
rs.Close

End Sub
but I get an error Microsoft access can't find "[" reffered to in your expession or macro. but my field refferences are the same for the code above, so I don't know what to think now

 

[Trevor2008]

After adding a few msgbox's in to see where my code is erroring, my query appears to be looking at the correct table but is only seeing 1 record that matches the criteria but there are multiple records in the table that match
The IDNumber is the linking field between the master and subform , from the VMSU-ILQ form it should use that IDNumber to pull records that contain that ID Number and copy them to the subform on VMSU-IL!invoicesubform, here is my 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" _
& " [IDNumber]= '" & Me.[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]' I know this line is wrong, even by using forms![VMSU-IL]![InvoiceSubform]![Billing For] as where I'm sening the info returns "can't find "[" in your expession or macro"




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

 

[Trevor2008]

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] & "'

 

[lespaul]

Shouldn't that be:

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

and is IDNumber really a text field?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Trevor2008]

Thanks lespaul but my thaught of reversing my where clause was worse - the rs.count message is 0, so I guess I haveoo go back to my other select statment, do you have any Idea why only 1 record is being seen/selected?

 

[Trevor2008]

The only thing I can think is happening is what is beeing counted is the # of matches to the IDnumber on the form, Ie so if there is 4 IDNumbers in the DB alll with I081111 and the Form IDNumber Is I081111 the data base see's that only one 1number that matches tha form IDNumber in the table regardless that 4 in the Db match the form ID Number