Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    1,487

    Angry Unanswered: How to finish a Report with Null records?

    Of coarse, normal protocol dictates that, "If you don't know something then search the forums for what you need and if you can't find it, then ask". Well... I would love to say that I've been haggeling with the delema below for days.... BUT I CAN'T! It's been months. Do you have any idea how many forums and news groups there are out there?. I do, I checked em all. To me, this then means that what I seek MUST contain a simple solution and I'm just to darn blind to see it.

    Here is what I am trying to do. I have a report that displays a employee time sheet for 6 employees. The report can contain 19 records to complete one printable report page. When I generate the report, the 6 employee records are displayed (of coarse), but that's all. I would like the remaining report page to fill with null fields (empty boxes so to speak).

    Can someone please help this simple minded fool....Thank you.

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    The only way to do what you are asking, that i can think of, would to to populate a temp table with the real records (the 6). As part of this process determine the # of records added to the table and then use vb to add the additional blank records (use an auto number) to this temp table till you have 19 rows of data. the print from the temp table

    The bigger question is why? If all the data you have is printing, why should it matter if part of you report is full of blank space?

    S-

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Well if you're populating the report table thru code it's easy ... Like stated before count the # of records added and then add blank ones to make up the differenct to 19 ...

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Originally posted by sbaxter
    The only way to do what you are asking, that i can think of, would to to populate a temp table with the real records (the 6). As part of this process determine the # of records added to the table and then use vb to add the additional blank records (use an auto number) to this temp table till you have 19 rows of data. the print from the temp table

    The bigger question is why? If all the data you have is printing, why should it matter if part of you report is full of blank space?

    S-
    Thank you very much sbaxter for your suggestion. I will give it a try. Thank you.

    The task is not that the report would not be filled with blank space, but filled with blank boxes (that surround the fields in the detail section). This is so that once the report has been printed and issued, people can add to the report via ink pen and return it.

    Thanks again

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Originally posted by M Owen
    Well if you're populating the report table thru code it's easy ... Like stated before count the # of records added and then add blank ones to make up the differenct to 19 ...
    M Owen - Thank you for your suggestion. I don't suppose I could bother you a little more for a small code sample? (if you have the time). Thank you very much!!

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    CyberLynx,

    Sure. Here is the VBA code for my monthly billing report ... This will give you an idea of how to do this ... Mind all you need to do is have 2 LOOPS. One to iterate thru your actual data and the other to add the makeup records ...

    Code:
    Option Compare Database
    Option Explicit
    
    Dim MyUserID As String
    Dim SQLString As String
    
    Dim RptTbl As String
    Dim BillingMonth As Integer
    Dim BillingYear As Long
    
    Dim DBConnectionActive As Boolean
    
    Dim MyConnect As ADODB.Connection
    Dim MyRecSet As ADODB.Recordset
    
    '
    ' *** Beginning of Functions and Methods
    '
    
    Function RetrieveBillingDetail() As Integer
        On Error GoTo Err_RBD
        
        Dim STDate As Date, EDate As Date
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        STDate = CDate(BillingMonth & "/1/" & BillingYear)
        MonOfLbl.Caption = Format(STDate, "mmm yyyy")
        EDate = DateAdd("m", 1, STDate)
        EDate = DateAdd("d", -1, EDate)
        
        ' Billing Detail
        SQLString = "SELECT [WO Billing Detail].WorkOrderNumber, CustomerContacts.[First name] & ' ' & IIf(CustomerContacts.MI &''='','',CustomerContacts.MI & '. ') & CustomerContacts.[Last name] AS Expr1, [GM Division].Name, Accounts.[Account Number], [WO Billing Detail].LaborCharged, [WO Billing Detail].PurchasesCharged, [WO Billing Detail].LaborCredit, [WO Billing Detail].PurchasesCredit"
        SQLString = SQLString & " FROM ((([WO Billing Detail] INNER JOIN [Work Orders II] ON [WO Billing Detail].WorkOrderNumber = [Work Orders II].WorkOrderNumber) LEFT JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) LEFT JOIN Accounts ON [Work Orders II].AccountID = Accounts.AccountID) LEFT JOIN [GM Division] ON CustomerContacts.DivisionID = [GM Division].DivisionID"
        SQLString = SQLString & " WHERE ([WO Billing Detail].[Billing Date]BETWEEN #" & STDate & "# AND #" & EDate & "#) ORDER BY Accounts.[Account Number] ASC;"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM [" & RptTbl & "] WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' W.O. #
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Customer's Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
                ' Division
                TrgRecSet.Fields(2).Value = MyRecSet.Fields(2).Value
                ' Account #
                TrgRecSet.Fields(3).Value = MyRecSet.Fields(3).Value
                ' Labor Charged
                TrgRecSet.Fields(4).Value = MyRecSet.Fields(4).Value
                ' Purchases Charged
                TrgRecSet.Fields(5).Value = MyRecSet.Fields(5).Value
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        ' Payments
        SQLString = "SELECT Payments.WorkOrderNumber, CustomerContacts.[First name] & ' ' & IIf(CustomerContacts.MI &''='','',CustomerContacts.MI & '. ') & CustomerContacts.[Last name] AS Expr1, [GM Division].Name, Accounts.[Account Number], Payments.LaborPortion, Payments.PurchasesPortion"
        SQLString = SQLString & " FROM (((Payments INNER JOIN [Work Orders II] ON Payments.WorkOrderNumber = [Work Orders II].WorkOrderNumber) LEFT JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) LEFT JOIN Accounts ON [Work Orders II].AccountID = Accounts.AccountID) LEFT JOIN [GM Division] ON CustomerContacts.DivisionID = [GM Division].DivisionID"
        SQLString = SQLString & " WHERE ((Payments.ReceivedDate BETWEEN #" & STDate & "# AND #" & EDate & "#) AND (Payments.Refund=False)) ORDER BY Accounts.[Account Number] ASC;"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM [" & RptTbl & "] WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' W.O. #
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Customer's Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
                ' Division
                TrgRecSet.Fields(2).Value = MyRecSet.Fields(2).Value
                ' Account #
                TrgRecSet.Fields(3).Value = MyRecSet.Fields(3).Value
                ' Labor Portion
                TrgRecSet.Fields(4).Value = MyRecSet.Fields(4).Value
                ' Purchases Portion
                TrgRecSet.Fields(5).Value = MyRecSet.Fields(5).Value
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        LoadingDelay
        
        RetrieveBillingDetail = 1
    Exit_RBD:
        Exit Function
        
    Err_RBD:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveBillingDetail = 0
        Resume Exit_RBD
    End Function
    
    Function EstablishDBConnection() As Integer
        On Error GoTo Err_EDBC
        
        Set MyConnect = New ADODB.Connection
        Set MyRecSet = New ADODB.Recordset
        
        MyConnect.CursorLocation = adUseClient
        MyConnect.Open "DSN=Billing System;"
        
        MyRecSet.CursorType = adOpenForwardOnly
        MyRecSet.LockType = adLockOptimistic
        MyRecSet.CursorLocation = adUseClient
        
    EstablishDBConnection = 1
    Exit_EDBC:
        Exit Function
        
    Err_EDBC:
        
        MsgBox Err.Number & ": " & Err.Description
        EstablishDBConnection = 0
        Resume Exit_EDBC
    End Function
    
    Private Sub Report_Open(Cancel As Integer)
        Dim IsOK As Integer
    
        MyUserID = CurrentUser()
    
        DBConnectionActive = False
        
        IsOK = EstablishDBConnection
        If IsOK = 0 Then
            Cancel = 1
            Exit Sub
        End If
        DBConnectionActive = True
    
        Dim ParmListString() As String
        ' Format:
        '
        ' Monthly Billing Summary table, Month & Year
        '
        ParmListString = Split(Forms![Billing Reports].ParameterTxt, ",", -1, vbTextCompare)
    '    ParmListString = Split(Me.OpenArgs, ",", -1, vbTextCompare)
        RptTbl = ParmListString(0) & ""
        BillingMonth = CInt(ParmListString(1))
        BillingYear = CLng(ParmListString(2))
        
        RetrieveBillingDetail
        
        Me.RecordSource = RptTbl
    End Sub
    
    Private Sub Report_Close()
        If DBConnectionActive Then
            MyConnect.Close
            Set MyRecSet = Nothing
            Set MyConnect = Nothing
        End If
    End Sub

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Thanx a million M Owen. I really appreciate it. TTYL.

  8. #8
    Join Date
    Nov 2003
    Posts
    1,487

    Smile

    GOT IT!!!

    Thanks everyone...

    I created a temp table and added what was needed. Works GREAT!
    (heh...I figured it was something simple)

    Thanks again.

Posting Permissions

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