Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    Harrow, UK
    Posts
    53

    Unanswered: Create several records in a sub-form - how?

    I have a form ("BuyingData") which has within it a subform ("SellingData"). BuyingData includes a field ("Quantity"). By default, [BuyingData].[Quantity] = 1, but can be other values.

    Each record in BuyingData has a field "BuyingItem" (autonumber).
    Each record in SellingData has a field "SellingItem" (not an autonumber).

    I want to create records in the subform according to the value of Quantity. i.e.

    if [BuyingData].[BuyingItem] = 065 and [BuyingData].[Quantity] = 1 then
    I want one record created in SellingData with a [SellingData].[SellingItem] = "065/001"

    Similarly,

    if [BuyingData].[BuyingItem] = 369 and [BuyingData].[Quantity] = 5 then
    I want five records created in SellingData with a [SellingData].[SellingItem] = "369/001", "369/002", "369/003"....."369/005".


    Any suggestions, I'm really struggling here...
    Thanks.
    Thank you,
    James

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Re: Create several records in a sub-form - how?

    Hi, the following code is ALMOST working, only need to fix the variable references in the strSQL so that the varialbles are put into the statement. Havent got time to do that, silly problem, could not tweak my mind to figure out what this tiny little error was..., hopefully someone else can quickly correct me here. Put the code behind the OnClick event of a command button on the main form, and adjust the pointers to your real table/queries and field names.

    PS. If you need leading zeros in your number series, you need to add code, or maybe just have the counters start at 100 instead of 0 ?

    Daniel

    Code:
    Private Sub cmdCreateSalesRecords_Click()
    On Error GoTo Err_AddSubRecords
    Dim strBuyingItemID As String
    Dim strQuantity As String
    Dim strSellingItemID As Variant
    Dim strSQL As String
    
    strBuyingItemID = Me.txtBuyingItemID
    strQuantity = Me.txtQuantity
    
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    Do While CInt(strQuantity) > 0
    strSellingItemID = CInt(strBuyingItemID) & "/" & CInt(strQuantity)
    strSQL = "(INSERT INTO tbl_Sales (SellingItemID, BuyingItemID, Item, [Date]) SELECT strSellingItemID as SellingItemID, strBuyingItemID as BuyingItemID, tbl_Purchases.Item, Date() as [Date] From tbl_Purchases WHERE tbl_Purchases.BID = strBuyingItemID)"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    strQuantity = CInt(strQuantity) - 1
    Loop
    
    Me.tbl_Sales_subform.Requery
    
    Exit_AddSubRecords:
        Exit Sub
    
    Err_AddSubRecords:
        MsgBox Err.Description
        Resume Exit_AddSubRecords
    
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •