Results 1 to 7 of 7
  1. #1
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    51

    Add Record from One Subform to another Subform

    Hi,

    I’m looking for help on something I’m trying to do. I have a Sales Order Entry Screen. It’s a Main Form with Two Separate Subforms. One Subform is the Items Order Entry Part where the products sold get selected/entered with quantity and cost. The other Subform shows a list of the items the customer has purchased in the past. I’d like to be able to click an item in the Templates Subform (products purchased in the past) and have it auto entered or copied into the other subform (the actual order.) I’m not a trained/skilled programmer. I’ve tried everything I know to try but I’ve not been successful. This is what I have so far. Probably not even close…

    If you’re able to help, I’d greatly appreciate it!!!

    Private Sub cmdAddItem_Click()
    On Error GoTo btnAddProduct_Click_error


    Dim S As String

    If Nz(Me![ITEMNUM].Column(0), "") = "" Then
    GoTo btnAddProduct_Click_Exit
    End If

    S = Me![ITEMNUM].Column(0)
    ' DoCmd.RunCommand acCmdSaveRecord

    Forms![frmSalesOrders]![ItemsPanel].SetFocus
    If Me.NewRecord Then
    Me![InvoiceItemDescription] = S
    Me![InvoiceHeaderCustomer].Requery
    Else
    DoCmd.RunCommand acCmdRecordsGoToNew
    Me![InvoiceItemDescription] = S
    Me![InvoiceHeaderCustomer].Requery
    End If

    ' DoCmd.RunCommand acCmdSaveRecord

    btnAddProduct_Click_Exit:
    On Error Resume Next
    Exit Sub

    btnAddProduct_Click_error:
    Select Case Err
    Case Else
    MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, "Error in module btnAddProduct_Click"
    Resume btnAddProduct_Click_Exit
    End Select
    End Sub

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Does each record in the "products purchased in the past" table have a Primary Key value?

  3. #3
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    51
    Yes, TemplateID. I could include in the Template Subform Query.

    I run a make table query daily that looks at sales history and creates a customer template table.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You can create an append query that selects the records based on the TemplateID value that should make it much easier to add the record. I assume the two tables have very similar structures.

  5. #5
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    51
    This is beyond me.... Way too complicated for me...

    I have a simple append query. I've tried to do this in the past (been working on this issue off and on for months.)

    Main Sales Order Form
    Subform - Items Ordered
    Subform - Items Ordered in the Past (Template)

    I'd love to append the item number from the Templates subform into the Items Ordered subform and then requery the line in the Items Ordered subform. If I can copy just the one item number from below and insert it into the actual order and then requery the row in the items ordered subform - all of the other fields will be populated (item info, cost, sell price, etc.)

    When I run the following append query I get error "too few parameters."

    When I view the append subform when the sales order form is open, I get a list of all the items in the customer's template, not the specific item I'm selecting. I have a command button next to each item in the customer template subform. In the on-click event of the template subform next to each item I'm simply running: CurrentDb.Execute "qryAppendInvoiceItems", dbFailOnError

    I think I need to capture the specific item from the Templates Subform so only that Item is Appended. I also think there needs to be some code to Requery the Items ORdered Subform becuase there is information required in the Items Ordered Subform that's populated using AfterUpdate Event when an items is entered or selected.

    I would have given up on this long ago if it wasn't such a big deal. It would be a huge help to my operation. It would save a lot of time during the order entry process. I'm surprised I can't find any help online about inserting a record from one subform into another. Seems like something that would be used a lot.

    Anyway - Thx for helping!


    APPEND QUERY
    INSERT INTO tblSalesInvoiceItems ( InvoiceItemDescription, CompanyNum )
    SELECT qryCustomerTemplates.InvoiceItemDescription, qryCustomerTemplates.CompanyNum
    FROM qryCustomerTemplates;

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Another way would be to have a listbox on the main form showing a list of all products purchased. The user can double-click one of the products and in the DoubleClick event run the append query and requery both subforms.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Create an append query appends records from the "Items Ordered in the Past" table to the "Items Ordered" table then look at the SQL for that query and remove any fields that you do not want to copy. Now add a WHERE Clause that selects the record with the TemplateID that you want. If I were doing it I would put in a fixed number so I could get the syntax. Not you can copy the SQL to your form's code module and enclose it in quotes to make a string. You can then replace the fixed number with a reference to the TemplateID you have saves and then you can execute a db.Execute YourSQL...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-06-2015, 12:38 AM
  2. Replies: 7
    Last Post: 04-15-2014, 01:15 PM
  3. Replies: 8
    Last Post: 08-09-2013, 09:52 AM
  4. Replies: 6
    Last Post: 08-22-2012, 03:24 AM
  5. Replies: 6
    Last Post: 05-05-2012, 08:43 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums