Results 1 to 4 of 4

Thread: Arrays issue

  1. #1
    Join Date
    Feb 2009

    Unanswered: Arrays issue

    Ok, let me see if I can explain this clearly and then I'll copy and paste my current code so it'll be easier to get help.

    I have this project. I'm generating a report...and this report absolutely HAS to look a specific way. I know how I'm going to display it (via code due to the way it looks on paper), that's not the problem.

    The problem is the arrays I'm trying to create.

    I've got this loop that goes through each record in a table. It searches for a specific number in one of the fields that the user enters (to generate the report). What I then plan on doing as it goes through looking for records that match the criteria is to create two arrays (actually more than two, you'll see in a second).

    The first array will be a name array with all the contractors in the database whose records match the number requested (a check number). Then what will happen once all the names have been put into the array (no duplicates of course), an array will be made for each person and I will then have all their numbers for the matching records be put in their own array. After that I plan on creating controls based off the arrays to get the report to look the way I want it.

    Here's an example. The check number will pull up 18 records based on the ones I have in the table. There's 4 names among those 18 records (some guys have multiple records associated with their name).

    I have a default name in there already, so when I tell my array to display, it should come up with 5 names. My problem is I get the default name and then the array goes to the very last record of the 18 records, takes that person's name and has it appear 18 times.

    My message box should only pop up like 5 times. I'm having it pop up in a message box so I can see if it's reading the array right before I start creating the arrays with their money amounts.

    If my question or request for help isn't clear enough, then please let me know. Thanks in advance for any help. I'm sure this is something simple. And if my strategy for getting from point A to point B is way too time consuming and there's an easier way, then please let me know. This seems to be the easiest way to do it to me.

    Dim dbsandt As Database
    Dim recdataentry As Recordset
    Set dbsandt = CurrentDb()
    Set recdataentry = dbsandt.OpenRecordset("invoicedata")

    Dim strinput As String
    Dim name As String
    Dim namearray() As String
    ReDim Preserve namearray(0 To 1) As String
    namearray(1) = "Tim"
    Dim lngposition As Long
    Dim i As Integer

    name = ""

    strinput = InputBox("What check number would you like the report to be based off of?", "Payroll Report")

    Do Until recdataentry.EOF

    If recdataentry("Check #") = strinput Then

    name = DLookup("contractor", "invoicedata", "result.value = [Check #]")

    ReDim Preserve namearray(0 To UBound(namearray) + 1) As String
    namearray(UBound(namearray)) = name

    End If


    For lngposition = LBound(namearray) To UBound(namearray)
    MsgBox namearray(lngposition)
    Next lngposition


  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    I am not any where near convinced this is the best way of doing this, (for instance why don't you create a record set with only records where result = strinput ?).

    Having said that, the code posted maybe should have this line

    name = DLookup("contractor", "invoicedata", "result.value = [Check #]")

    modified as this

    name = DLookup("contractor", "invoicedata", "result = " & strinput)

    note: if 'result' is a text field then it should be

    name = DLookup("contractor", "invoicedata", "result = '" & strinput & "'")

    The more I look this the more I am sure there is a better way.

    Just some of the questions that spring to mind.

    Is the 'recdataentry' record set based on a different table than the table used in the DLooup (ie invoicedata).

    If so and they are related (by [Check #]?) then you should be able to produce the required Names list with a single query ? (and maybe you would not need an array!).

    You will gather from this that perhaps a little more info would be beneficial!


  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    sounds a very odd user requirement... are you certain this cannot be done as part of the base query.
    iterating through recordsets is slow and costly.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2007
    Adelaide, South Australia
    Yeah, but it can mean some really cool "please wait" screens!

    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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