UtterAccess Discussion Forums > Mail Merge - Duplicate labels required!

Full Version: Mail Merge - Duplicate labels required!

UtterAccess Discussion Forums > Microsoft® Access > Access Reports

rtalawila

Mar 9 2007, 07:55 AM

Hi I could really do with some help here as I am still learning Access.

I have a query (Qry_Brand_Serial_Numbers) that generates a list of product information which i can send to a mail merge document. e.g.

CODE

Brand               Part              Description             Free              Serial     INOUT
PANASONIC    PAN-CADC300N    PANASONIC IPOD CABLE        9
PANASONIC    PAN-CALRM10X    REMOTE CONTROL SENSOR        45
PANASONIC    PAN-CNDV2300N    PANASONIC SAT NAV        5               *1002492*    IN
PANASONIC    PAN-CNDV2300N    PANASONIC SAT NAV        5               *1002508*    IN

Some parts have serial numbers and some dont. If the part does have a serial number then each item is sent to the mail merge. If it doesnt then only one label is "rendered". so if i have 45 units of a particular part number which has no serial number i have to manually adjust mail merge document by copy and paste.

I would like to create the facility to do the following

CODE

WHILE (!EOF[Qry_Brand_Serial_Numbers]){
   IF (!Serial){                                      //All Non Serial Labels
      FOR (j=0;j<Free;j++){
         INSERT_INTO_NEW_TABLE()
     }
   ELSE{
      INSERT_INTO_NEW_TABLE()                         //Serial Labels
   }

I hope I've made sense. How do I write the VBA code to enable this? Am I going about this the right way? Can I do this from Word2003 itself?

I hope that you can help.

Kind regards
Resh

Snapper316

Mar 9 2007, 09:21 AM

If you make a query with your criteria you can just set that as a datasource for a mail-merge document in Word.

(Select * From YourTable Where Serial Is Not Null)

In Word
Using the wizard --> Tools --> Letters and Mailing --> Mail Merge Wizard

Browse for file (Db) and then select the query...

That way you dont have to copy records or w/e you were doing in the code.

rtalawila

Mar 9 2007, 09:30 AM

Thanks for your reply.

The mail merge does already work. As long as there are serial numbers for each part I can get a unique label by merge to new document. My problem is when there are no serial numbers for any given part. The mail merge label may show the free stock value as 10 but only one label is rendered. I need a way to make all 10 labels render.

It is important that all serial and non serial parts are sent to the merge document.

Hope you can help further.
Kind regards
Resh

fkegley

Mar 9 2007, 09:38 AM

Your best be will be to develop code in VBA similar to your already-posted code that roots thru the table and puts the data into a temporary table that will only be used for the mail merge. Then base the mail merge on that table.

rtalawila

Mar 9 2007, 09:48 AM

Yes I agree. I am working on it as we speak. My code so far...

CODE

Private Sub MultipleLabelGen()

Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef

Dim strResult As String
Dim strSQL As String
Dim qBrand As String
Dim IsSerial As String
Dim freestock As Integer

freestock = 0
qBrand = [Forms]![Label_Printing_Screen]![Text0]

'qdf = CurrentDb.OpenQueryDef(Label_Printing_Serials)
Set qdf = CurrentDb.QueryDefs("Label_Printing_Serials")

'Now we'll assign values to the query using the parameters option:
qdf.Parameters(0) = qBrand
'strSQL = "SELECT * from Label_Printing_Serials"

'Now we'll convert the querydef to a recordset and run it
Set rst = qdf.OpenRecordset
i = 0
'Run some code on the recordset
Do While Not rst.EOF
i = i + 1
freestock = rst.Fields("free").Value
IsSerial = rst.Fields("Serial").Value
If (freestock > 0) Then
   If (Len(IsSerial) > 0) Then
      MsgBox (freestock & "|" & IsSerial)
      'write2TempTable()
      'not written yet
   End If
End If
rst.MoveNext
Loop

'Close all objects
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing

I need to now create a temp table and write to it. Can you help further.

Regards
Resh

fkegley

Mar 9 2007, 09:56 AM

Yes what I would do is create the Temp table myself, then remove its records via code:

CurrentDb.Execute "Delete * From TempTable"

Then open the table into a recordset:

Set rs = CurrentDb.OpenRecordset "TempTable"

Then in the appropriate places in your code,

add a record to the table:

rs.AddNew

place the values into the fields

rs!FieldName = Value
etc.

then add the record to the temp table

rs.Update

rtalawila

Mar 9 2007, 12:08 PM

Thank you for all your help. Thanks to you I have completed this task with just about a day before stock take!!!

the finalised code is:

CODE

Private Sub MultipleLabelGen()

Dim db As Database
Dim rst As Recordset
Dim rstTemp As Recordset
Dim qdf As QueryDef
Dim qdfTemp As QueryDef

Dim strResult As String
Dim strSQL As String
Dim qBrand As String
Dim IsSerial As String
Dim Brand As String
Dim Desc As String
Dim FreeCounter As Integer

CurrentDb.Execute "Delete * From TEMP_LABELS"
Set rstTemp = CurrentDb.OpenRecordset("TEMP_LABELS")

Freestock = 0
qBrand = [Forms]![Label_Printing_Screen]![Text0]

'qdf = CurrentDb.OpenQueryDef(Label_Printing_Serials)
Set qdf = CurrentDb.QueryDefs("Label_Printing_Serials")

'Now we'll assign values to the query using the parameters option:
qdf.Parameters(0) = qBrand

'Now we'll convert the querydef to a recordset and run it
Set rst = qdf.OpenRecordset

'Run some code on the recordset
Do While Not rst.EOF
    Freestock = 0
    FreeCounter = rst.Fields("free").Value

    If (rst.Fields("SerialMessage").Value = "NO SERIAL") Then
          Do
            rstTemp.AddNew
                rstTemp!Brand = rst.Fields("Brand").Value
                rstTemp!Part = rst.Fields("Part").Value
                rstTemp!Description = rst.Fields("Description").Value
                rstTemp!Barcode = rst.Fields("Barcode").Value
                rstTemp!Free = rst.Fields("free").Value
                rstTemp!Actual = rst.Fields("Actual").Value
                rstTemp!SerialMessage = rst.Fields("SerialMessage").Value
                rstTemp!INOUT = rst.Fields("INOUT").Value
            rstTemp.Update
            FreeCounter = FreeCounter - 1
            'MsgBox FreeCounter
           Loop While FreeCounter > 1
    End If

    If (Len(rst.Fields("SerialMessage").Value) > 0) Then
            rstTemp.AddNew
                rstTemp!Brand = rst.Fields("Brand").Value
                rstTemp!Part = rst.Fields("Part").Value
                rstTemp!Description = rst.Fields("Description").Value
                rstTemp!Barcode = rst.Fields("Barcode").Value
                rstTemp!Free = rst.Fields("free").Value
                rstTemp!Actual = rst.Fields("Actual").Value
                rstTemp!Serial = rst.Fields("Serial").Value
                rstTemp!SerialMessage = rst.Fields("SerialMessage").Value
                rstTemp!INOUT = rst.Fields("INOUT").Value
            rstTemp.Update
            FreeCounter = FreeCounter - 1
            'MsgBox FreeCounter
    End If

    rst.MoveNext
Loop

'Close all objects
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing

rstTemp.Close
Set rstTemp = Nothing

I hope others may find it useful. I would still very much appreciate any comments you have on making it more efficient.

Kind regards
Resham Talawila

fkegley

Mar 9 2007, 01:09 PM

You're welcome. I am glad I could help. As far as I am concerned, if it works, then it is efficient.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.