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

recdataentry.MoveNext
Loop

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

recdataentry.Close

Hi

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!

MTB

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.

Yeah, but it can mean some really cool "please wait" screens!

lol