code help with do.. loop and dlookup in a report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kaosyeti<[at]>comcast<[dot]>net - 31 Jan 2006 21:48 GMT

hey... i'm not very good at vba yet so i don't know where to start on this
issue. i have a report that needs to pull data from a couple of different
places, at different times. here's the breakdown:

i have a table that has sale data for all the sales consultants at one
location.
i have a report that needs to pull the number of sales for each sales
consultant
i need this report to be 'generic' enough not to have to hard-wire anyone's
name into the code.

what i'm think is sort of like this....

a function that pulls the first sales consultant's name from tblfinancelog
for the given time period...
then it stores that name in an array and pulls the next name from the table...

if the 2nd name is different, it stores it in the array as well...
and so on until only the sales consultants that actually have sales during
the time period are pulled...

then...

a textbox on the report that pulls the 1st name in the array...
another textbox on the report that pulls the 2nd name in the array...
etc until all names are pulled.

the absolute most sales consultants at any given location would be about 30..
many times it will be closer to 10.

this is what i TRIED to start with:

Private Function getslcns()
Dim slcn As String
Dim counter As Integer
Dim allslcns As String

counter = 0
slcn = DLookup("*", "tblfinancelog", "[date] between #" & Forms!
formquarterly!txtboxYTDstartdate & "# and #" & Forms!formquarterly!
txtboxYTDenddate & "#")

Do
Do While counter < 30
counter = counter + 1
allslcns = slcn & slcn

End Function

i'm absolutely willing to consider ANY alternative to my strategy that will
work. the end result is that i need a list of the names, one in each text
box and i'll be able to do other calculations off of each name to pull the
sales data myself.

thanks for looking.

Reply to this Message

George Nicholson - 01 Feb 2006 01:23 GMT

Use SQL and a recordset to get your list. Then you can open the query as a
recordset and loop through the names...

Something like (this is untested aircode):

Dim db as dao.Database
Dim rst as dao.Recordset
Dim strSQL as String
Dim c as Integer
Dim intMax as integer

' Simply get a list of consultants with sales during January 2006
strSQL = "SELECT DISTINCT Consultant FROM tblSalesData WHERE
Month([SalesDate]) = 1 and Year([SalesDate])=2006 ORDER BY Consultant;"

'(or, get a list of Consultants and their total Sales??)
'strSQL = "SELECT DISTINCT Consultant, Sum(Sales) As MSales FROM
tblSalesData WHERE Month([SalesDate]) = 1 and Year([SalesDate])=2006 GROUP
BY Consultant ORDER BY Consultant;"

Set db = CurrentDB
Set rst = db.OpenRecordset(strSQL)

If rst.EOF then Exit Sub
rst.MoveLast
rst.MoveFirst

If rst.Recordcount <=30 then
intMax = rst.Recordcount
Else
inMax = 30
End If

For c = 1 to intMax
'Assume textbox controls are unbound and named
"txtConsultant01..."txtConsultant30"
' Put name in TextBox
Me.Controls("txtConsultant" & Format(c,"00") = rst!Consultant

' Move to next consultant
rst.MoveNext
Next c
rst.Close

Set rst = Nothing
Set db = Nothing

HTH,

Signature

George Nicholson

Remove 'Junk' from return address.

> hey... i'm not very good at vba yet so i don't know where to start on this
> issue. i have a report that needs to pull data from a couple of different
[quoted text clipped - 55 lines]
>
> thanks for looking.

Reply to this Message

kaosyeti<[at]>comcast<[dot]>net - 03 Feb 2006 15:44 GMT

hey.. from what i gather in your code, it looks like it is what i need. i
had one error (so far) that i can't get around. on this line:

Me.Controls ("txtboxsalesperson" & Format(c, "00") = rst!salesperson)

i get an invalid use of property error. my field name in the table is
salesperson1, my controls on this report are txtboxsalesperson01 through
txtboxsalesperson30. the debug window opens with rst!salesperson1 as if i
had selected it (ie, highlighted in blue), if that matters. the whole code i
have is:

Dim db
Dim rst As Recordset
Dim strSQL As String
Dim c As Integer
Dim intMax As Integer

' Simply get a list of consultants with sales during January 2006
strSQL = "SELECT DISTINCT [salesperson1] FROM tblfinancelog WHERE [date]
between # & forms!formquarterly!txtboxYTDstartdate & # and # &
txtboxYTDenddate & # ORDER BY [salesperson1]"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

If rst.EOF Then Exit Sub
rst.MoveLast
rst.MoveFirst

If rst.RecordCount <= 30 Then
intMax = rst.RecordCount
Else
inMax = 30
End If

For c = 1 To intMax
'Assume textbox controls are unbound and named
'"txtConsultant01..."txtConsultant30"
' Put name in TextBox
Me.Controls ("txtboxsalesperson" & Format(c, "00") = rst!salesperson1)

' Move to next consultant
rst.MoveNext
Next c
rst.Close

Set rst = Nothing
Set db = Nothing

End Sub

Notes:
--The Dim statement for db was an unknown type so i left it blank (which lets
access figure it out for itself i hope -- not a good practice but good when
you don't know what to put in there.

--The Dim statement for rst was an unknown type but i changed it to recordset,
which seemed logical.

--The version of access i'm using is access 2003 on windows XP.

--The form mentioned in my sql statment IS opened during this whole process
so the date range is available.

--I did try to make this work using various syntax changes with the bad line
of code (exclamation marks, commas, etc..) but nothing worked.

thanks again.

Greg

>Use SQL and a recordset to get your list. Then you can open the query as a
>recordset and loop through the names...
[quoted text clipped - 49 lines]
>>
>> thanks for looking.

Signature

Greg

Reply to this Message

George Nicholson - 07 Feb 2006 02:08 GMT

Try moving the closing parenthesis (that was missing entirely from my post):

> Me.Controls ("txtboxsalesperson" & Format(c, "00") = rst!salesperson)

should be:
Me.Controls ("txtboxsalesperson" & Format(c, "00")) = rst!salesperson1

HTH,

Signature

George Nicholson

Remove 'Junk' from return address.

> hey.. from what i gather in your code, it looks like it is what i need. i
> had one error (so far) that i can't get around. on this line:
[quoted text clipped - 130 lines]
>>>
>>> thanks for looking.

Reply to this Message

Duane Hookom - 01 Feb 2006 02:29 GMT

Is there a good reason why you are using recordsets and arrays when a report
bound to a table or query might do the same thing?

Signature

Duane Hookom
MS Access MVP
--

Reply to this Message