Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    2

    Unanswered: Multiple Labels for One Record

    I have an inventory database that tracks products stored in different locations:
    Inventory DB
    -------------
    ProductID (106)
    LocationA (5)
    LocationB (4)
    LocationC (3)

    What I would like to do is create a number of labels (Y) for each product/storage location combination, where Y is equal to the inventory at that location. (E.G. Product: 106: LocationA = Create 5 labels, LocationB = Create 4 labels, LocationC = create 3 labels)

    I feel like I'm doing it the "dirty" way: I run a "Make Table Query" which selects a particular product/location combination. An "append query" (essentially identical to my MakeTable Query) is run Y-1 times to create Y records for that product/location combination. Then, I create a label report from the temp table. I repeat the process for other combinations.

    Can anyone suggest a more elegant/streamlined solution to this problem? I'm 100% self-taught and still very clumsy with modules.
    Thanks!

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Good, I like 100% self taught people - I'm one as well.

    Have you been introduced to the Recordset Object? Your clumsiness with modules will fast be replaced with fancy under-the-hood code once you learn how to use these objects, which let you drive the database through code.

    Using a temp table in Access is actually a common practice - for many, many things. You can do this through code instead of using queries though.

    The first thing you can do is to use the DoCmd.RunSQL command to execute the SQL string that is equavalent to the query. To see the SQL string, open your query in SQL view.

    A method of using recordsets to put the data in your temp table would look similar to this:
    Code:
    Sub PrintLabels()
    
        Dim rsDataSource As dao.Recordset
        Dim rsDataTemp As dao.Recordset
        Dim intKey As Integer
        Dim intCount As Integer
    
        intKey = InputBox("What key value?", "Get Key Value")
        
        Set rsDataSource = CurrentDb.OpenRecordset("Select * From tblYourTable Where YourKeyValue = " & intKey) ' YourKeyValue is a field name
        Set rsDataTemp = CurrentDb.OpenRecordset("Select * From tblYourTemp")
        
        ' Clear the Temp Table
        While Not rsDataTemp.EOF
            rsDataTemp.Delete
            rsDataTemp.MoveNext
        Wend
        rsDataTemp.MoveFirst
        
        ' Create the records
        For intCount = 1 To rsDataSource("NumberOfLabels")  ' NumberOfLabels being the field name
            rsDataTemp.AddNew
            rsDataTemp("DataField") = rsDataSource("DataField") ' do this for each piece of data in the record
            rsDataTemp.Update
        Next intCount
        
        Set rsDataTemp = Nothing
        Set rsDataSource = Nothing
    
        DoCmd.OpenReport "YourReportName", acViewNormal, , "YourKeyValue = " & intKey
    End Sub
    This, of course, is very blunt and as always, there are several different ways to do the same thing with less code, but I spelled it out this way so you can see how it works.

    Actually, if you want to avoid the temp table and recordsets, try this, which simply prints the report the appropiate number of times.
    Code:
    Sub PrintLabels()
    
        Dim intKey As Integer
        Dim intCount As Integer
        Dim intMax As Integer
    
        intKey = InputBox("What key value?", "Get Key Value")
        
        intMax = DLookup("NumberOfLabels", "tblYourTable", "YourKeyValue = " & intKey) ' YourKeyValue and NumberOfLabelsis are field names
        For intCount = 1 To intMax
            DoCmd.OpenReport "YourReportName", acViewNormal, , "YourKeyValue = " & intKey
        Next intCount
    
    End Sub
    Have fun.

  3. #3
    Join Date
    May 2004
    Posts
    2

    Thanks!

    I wasn't familiar with recordsets. That makes life much, much easier.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What's your table structure like at the moment? If you have a table that tracks locations/items then you should be able to pull those into a report and make the correct number of labels no problem....
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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