Textbox value = all subform records

[SpiderPig]

First off, good day to all!

I've been searching for a solution to this for weeks, can anybody here help me?... I'm a bit too novice to get my head round it

I have a form (CustomerForm) that contains general customer information with a subform (PartsForm), in datasheet view, it shows items bought by the selected customer. Now, I need to transfer the list of parts used by the particular customer into a textbox on the main form so I can merge to a word document.

So far the main form has a button that has 2 events to set the total value of parts purchased into a SubTotal textbox and this works perfectly. I don't know how to place the records in the PartsUsed texbox (please refer to the commented line in the code below for what I'm trying to achieve) and for it to work as I intend it needs to be done similar to using the CTRL+A/C/V method so i can keep the formatting (i.e. the tab stops between columns help me place the info correctly in the merged word document).

Code:
Private Sub SendInfoToMainForm_Click()
On Error GoTo Err_SendInfoToMainForm_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Forms![CustomerForm].SubTotal.Value = [CustomerForm].Form![PartsForm].Form!OrderDetailsTotal
'Forms![CustomerForm].PartsUsed.Value = all records in the PartsForm including the subforms headers... exactly as if using CTRL+A/C/V to paste the records into the PartsUsed textbox on the CustomerForm

Exit_SendInfoToMainForm_Click:
Exit Sub
Err_SendInfoToMainForm_Click:
MsgBox Err.Description
Resume Exit_SendInfoToMainForm_Click

End Sub
________________________________________________________________

Private Sub SendInfoToMainForm_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub


The subform has OrderID, PartID, PartDescription, UnitPrice, TotalPrice (& OrderDetailsTotal in the footer).
To link the parts used for a particular customer I have the subform child link = OrderID (PartsForm) & Master
link = CustomerID (CustomerForm).



I hope I haven't confused you with my explanation to what I need help with.

 

[CyberLynx]

In my opinion to do what you want you will need to pull up a Recordset of the Parts Ordered based on the Customer ID and the Order ID then format the Fields returned from that Recordset into a String variable which you would then ultimately apply to the TextBox.

By formating I mean alignment. Since you also want a header for the data you will want to align the returned Recordset data under the proper Heading. Doggy at best. Aligning things just right to display into a WORD document is difficult to do.

I think it would be best to apply the data into a WORD Table control based from your SELECT query for the Recordset which you would of used to get the Parts Ordered for that particular Customer. Check this out: Importing Access Data into a Word Table.

.

 

[SpiderPig]

Thanks for your input CyberLinx, you were spot on about pulling up a recordset based on the relative ID's. It was so obvious I overlooked it.

Formatting wasn't a concern for me so I didn't need to worry about the headers aligning properly because I knew that CTRL+C/V held all the info I needed to get it to MS Word. Basically it contained the formatting i needed (i.e. the tab spacing) so I just set up my word document with the tab stops set where I needed them. It looks squashed up in the PartsUsed textbox but that don't matter because I can set the textbox hidden as the info is already there in the subform.

I'm happy with the outcome because it had bugged me for too long although it'd be nice if I could open the subform (see the code below) silently. What i mean is you see the subform flash on the screen momentarily as it goes through the code. Also on error it stays until you acknowledge the error message. Other than that it works a treat so thanks for pointing me in the right direction.

Here is the Event code I used:

Private Sub SendInfoToMainForm_Click()
On Error GoTo Err_SendInfoToMainForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PartsForm"
stLinkCriteria = "[OrderID]=" & Me![CustomerID] 'links all parts used by the selected customer

DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria 'acFormsDS allows me to select all records and headers
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.Close 'closes PartsForm
Me.PartsUsed.SetFocus 'highlights PartsUsed textbox on main form
DoCmd.RunCommand acCmdPaste
Me.SendInfoToMainForm.SetFocus 'used to remove the highlighting from PartsUsed textbox

DoCmd.RunCommand acCmdRefresh

Forms![CustomerForm].SubTotal.Value = [CustomerForm].Form![PartsForm].Form!OrderDetailsTotal

Exit_SendInfoToMainForm_Click:
Exit Sub

Err_SendInfoToMainForm_Click:
MsgBox "There are no parts to update this time. Click OK to continue. "
DoCmd.Close
Me.PartsUsed.SetFocus 'used to delete invalid entries in PartsUsed textbox
DoCmd.RunCommand acCmdDelete
Me.SubTotal = "0" 'used to reset SubTotal to zero

Resume Exit_SendInfoToMainForm_Click

End Sub