Creating Multiple Records

[lzook88]

I am using an unbound form to move products and keep track of inventory.



To manage the database easier each item that we physically have gets its own record. 1 record = 1 item etc..

so on the form I have Product and Quantity.. I have my code that will create one record per product put in the form. I can't figure out how to create say 20 records for item1 and 10 records for item2

Private Sub Command61_Click()
Dim DBSS As Database
Dim rs As DAO.Recordset
Dim i As Integer

Set DBSS = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT ProductName, OldLocation, NewLocation FROM tblTransfers")




For i = 1 To 20
    If Me.Controls("item" & i) & "" = "" Then
        Exit For
    Else
        With rs
            .AddNew
            !ProductName = Me.Controls("item" & i).Value
            !OldLocation = Me.txtFrom.Value
            !NewLocation = Me.txtTo.Value
            .Update
        End With
    End If
Next i

rs.Close
Set rs = Nothing
DoCmd.Close
End Sub

The Quantity TextField in the form is Quan1 to Quan20

Hopefully i explained well enough..

Thanks
Zook

[orange]

I think you are heading in the wrong direction, but I may be misunderstanding your post.

Here is a generic data model for a typical Inventory Control system.

It may not reflect your business, but it is generic (has most common parts ) of such a system.
You can add, drop, ignore the model. It is meant to help you get a picture of a typical inventory database.

You should review Normalization.
http://databases.about.com/od/specif...malization.htm
http://rogersaccessblog.blogspot.ca/...on-part-i.html


Here is a youtube video you should review for concepts, ideas and approaches.
Good luck.

[ssanfu]

So you have 20 unbound text boxes for the product name and 20 text boxes for the number of items to add?

You need two loops - you are missing an inner loop.
Maybe something like this:

**untested**

Private Sub Command61_Click()
    Dim DBSS As Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim j As Integer

    Set DBSS = CurrentDb
    Set rs = CurrentDb.OpenRecordset("SELECT ProductName, OldLocation, NewLocation FROM tblTransfers")

    'outer loop - products
    For i = 1 To 20
        If Me.Controls("item" & i) & "" = "" Then
            Exit For
        Else
            With rs
                'inner loop - quantity
                For j = 1 To Me.Controls("quan" & j)
                    .AddNew
                    !ProductName = Me.Controls("item" & i).Value
                    !OldLocation = Me.txtFrom.Value
                    !NewLocation = Me.txtTo.Value
                    .Update
                Next j
            End With
        End If
    Next i

    rs.Close
    Set rs = Nothing
    DoCmd.Close
End Sub

[lzook88]

When I use

For j = 1 to Me.Controls("quan" & j)

Next j

I get an error saying quan0 cant be found

[lzook88]

Okay I figured out what was going on with the code:

For i = 1 To 20
    If Me.Controls("item" & i) & "" = "" Then
        Exit For
    Else
        With rs
            For j = 1 To Me.Controls("quan" & i).Value
                .AddNew
                !ProductName = Me.Controls("item" & i).Value
                !OldLocation = Me.txtFrom.Value
                !NewLocation = Me.txtTo.Value
                .Update
            Next j
        End With
    End If
Next i

[orange]

lzook88,

Do you have a description of your business opportunity to help readers understand what you are trying to solve/automate? You are asking about details of interacting loop constructs under a general question re Inventory Tracking. Perhaps others can read between the lines, but I prefer to hear the issue/opportunity from the poster.

[lzook88]

Sorry about.

This is used for EMT Supply Inventory. We don't really care about the orders we make as much as the amount of product we currently have in each location and station. The orders are monitored through the administrative staff at our supply location. So we just want to track it coming into the main Station and being sent to the units/bags from there.

The goal of this automation was to create a recrodset for each individual item. So say we have 5 gauzes in the Station it would create five new records that say gauze as the product and station as the location.