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 ?
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)"
strQuantity = CInt(strQuantity) - 1