Results 1 to 8 of 8
  1. #1
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    Code behind a report is giving an error that has nothing to do with the statement

    I have a report that uses the detail section to format the data for display in a footer section.

    The data looks like this

    ProviderId, Name, Month, year, mbrship


    1111 John Smith 4 2014 200
    1111 John Smith 5 2014 219
    1111 John Smith 6 2014 217


    The Report should display it like this

    1111 John Smith
    Apr 2014 May 2014 Jun 2014
    200 219 217


    It's actually more involved but this is the basis of the report. The complexity prevents me from using a simple crosstab.

    The code behind the report has a couple of arrays defined and in the detail section there are the fields I need to build the arrays.

    The code behind the form is as follows
    Code:
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
        Select Case Me.CAPMONTH
            Case Is = 4
                If Left(Me.LOB, 1) = "C" Then
                    commmbrship(1) = commbrship(1) + txtMember
                    
                Else
                    srmbrship(1) = srmbrship(1) + Me.Member
                End If
            Case Is = 5
                If Left(Me.LOB, 1) = "C" Then
                    commmbrship(2) = commbrship(2) + Me.Member
                Else
                    srmbrship(2) = srmbrship(2) + Me.Member
                End If
            
            Case Is = 6
                If Left(Me.LOB, 1) = "C" Then
                    commmbrship(3) = commbrship(3) + Me.Member
                Else
                    srmbrship(3) = srmbrship(3) + Me.Member
                End If
            
            Case Is = 7
            
                If Left(Me.LOB, 1) = "C" Then
                    commmbrship(4) = commbrship(4) + Me.Member
                Else
                    srmbrship(4) = srmbrship(4) + Me.Member
                End If
            
            Case Is = 8
                If Left(Me.LOB, 1) = "C" Then
                    commmbrship(5) = commbrship(5) + Me.Member
                Else
                    srmbrship(5) = srmbrship(5) + Me.Member
                End If
            
            Case Is = 9
        
                If Left(Me.LOB, 1) = "C" Then
                    commmbrship(6) = commbrship(6) + Me.Member
                Else
                    srmbrship(6) = srmbrship(6) + Me.Member
                End If
        End Select
    End Sub
    The me.member is the field I'm trying to put into the arrays also named the textbox associated with that field as txtmember and tried it both ways. Everytime I try to compile I get the error
    Sub or function not defined. I know that it's not a sub or function. There is a textbox with the field name assigned and the textbox.name defined. What am I missing here?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Doubt it will make difference but & character is the preferred concatenation operator.

    I don't see array objects declared. http://office.microsoft.com/en-us/ac...080206268.aspx

    Never tried anything like this to generate a report. Not really understanding what you are doing with the arrays and how this puts data on report. I use VBA and temp tables for some unusual manipulation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    The array objects are declared above that section

    Private commmbrship (1 to 6) as Long
    Private srmbrship (1 to 6) as Long

    I don't use the ampersand because the contents of the field are longs and I don't want to concatenate I want to sum. The reason for the array is as I stated the Data in the report comes in multiple rows for each provider. I want 1 row per provider with the membership in columns by month. The crosstab doesn't work because there are 2 fields to identify the provider. the ID Number and the name. some providers have multiple ID's because they have multiple offices. If I do a cross tab there's a row for the provider Id and then another row for the name I don't want that. I've done this a number of times on other reports and never had an issue like this one.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The sample data shows ProviderID and Name on same row and only one ProviderID. How would crosstab result in ProviderID and Name on separate rows? Do you mean there would be multiple rows for each Name? Regardless, building a report to run perpetually based on a crosstab is not easy anyway.

    Is it your desire to combine the multiple ProviderID's to single record for each Name? Or do you want each ProviderID listed?

    I am still not clear how this data is displayed on report. However, I think the code could be simpler and not use Case. Consider:

    Code:
    If Left(Me.LOB, 1) = "C" Then
        commmbrship(Me.CAPMONTH - 3) = commbrship(Me.CAPMONTH - 3) + txtMember
        
    Else
        srmbrship(Me.CAPMONTH - 3) = srmbrship(Me.CAPMONTH - 3) + Me.Member
    End If
    I know that doesn't help with the original error. Sorry, I have no idea why Member errors. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    The Provider ID and Provider Name are 2 different fields. In a crosstab each field would be designated a row header resulting in 2 rows 1 for the provider ID and 1 for the Provider name. The data for this DB is all in SQL Server and no-one outside of the company has access to our SQL Server. Access is used to query and report on the data. There are no local tables so sending the DB wouldn't work. Also for those Providers that have multiple ID's we need 1 row per ID.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, the RowHeader fields would be on one row. CROSSTAB is an aggregation query. RowHeaders are 'grouping' criteria. So 111111 John Smith would be one row and 111112 John Smith would be another row.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Everytime I've ever done a crosstab report each Item in the row header section has been on a different row. I will try that though. Thanks

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Odd - not been my experience.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-08-2014, 02:45 AM
  2. Form with VBA code giving Error
    By tgwacker in forum Access
    Replies: 1
    Last Post: 12-03-2013, 10:14 PM
  3. Linked Tables Giving Error Message
    By 18ck in forum Access
    Replies: 2
    Last Post: 11-23-2012, 06:30 AM
  4. DCount on a Query giving #Name? error
    By Huddle in forum Access
    Replies: 9
    Last Post: 06-20-2012, 11:40 AM
  5. NoData() still giving me an error.
    By cowboy in forum Programming
    Replies: 3
    Last Post: 04-08-2010, 12:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums