mtaylor
Oct 12 2009, 03:41 PM
Hi All!
The company I work for has an Access 2007 based program. And it just got handed off to me, only problem is that I don't know much about Access! I have signed up and will be taking classes at the end of the month, but until then, I was hoping to get a couple of things out of the way.
First we have a form that generates a report. You select your date range and then a single sales rep or you can pull the info for all sales reps. So you get one one or all.
What I would like to be able to do is select more than one rep at a time:
(obviously) I have figured out how to build the mulit select form, but I can't figure out how to allow it to pull in info for more than one rep (I get a #Error in place of the sales rep on the report now)
Please let me know if you need more information. Thanks!
mtaylor
Oct 12 2009, 03:42 PM
(Also, sorry if this is in the wrong forum, wasn't sure where to put it)
pbaldy
Oct 12 2009, 03:53 PM
mtaylor
Oct 13 2009, 09:56 AM
excellent! I'll try this out. Thanks!
pbaldy
Oct 13 2009, 09:58 AM
No problem, and welcome to UA by the way! Post back if you need help.
mtaylor
Oct 13 2009, 04:55 PM
Everything is looking good. I think I have a syntax issues now though... here's my modified code:
CODE
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click
Dim stDocName As String
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.SalesRep.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Sales Rep"
Exit Sub
End If
'add selected values to string
Set ctl = Me.SalesRep
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptEDTDetailSpecificRep", acPreview, , "qrySalesRep.strSalesRep IN(" & strWhere & ")"
Exit_Command7_Click:
Exit Sub
Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click
End Sub
and I'm getting this error now:
Syntax error (missing operator) in query expression 'qrySalesRep.strSalesRep IN(UP07 - DOUGLAS GORDON)'. Any ideas?
pbaldy
Oct 13 2009, 05:02 PM
Looks like your values are text rather than numeric, so this from the link was relevant:
'Use this line if your value is text
'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
mtaylor
Oct 13 2009, 05:28 PM
That worked, but now it wants me to enter Parameters from: qrySalesRep.strSalesRep ?
pbaldy
Oct 13 2009, 05:34 PM
Is the actual field name "strSalesRep"? If so, try:
DoCmd.OpenReport "rptEDTDetailSpecificRep", acPreview, , "strSalesRep IN(" & strWhere & ")"
If that doesn't work, can you post the db?
mtaylor
Oct 14 2009, 12:34 PM
Still no dice Paul :(
There is a front end and back end to the database. I've linked the front end here:
WIP Dev Let me know if you need any more info. Thanks!!
pbaldy
Oct 14 2009, 03:52 PM
Hard to test without data, but because of the alias, try
DoCmd.OpenReport "rptEDTDetailSpecificRep", acPreview, , "[Sales Rep] IN(" & strWhere & ")"
mtaylor
Oct 14 2009, 04:20 PM
No errors during the process, just a #Error on the report. Here's the backend data.
Back End Thank you!
pbaldy
Oct 14 2009, 04:49 PM
The error is because the query isn't pulling any data. It doesn't pull data because it's looking for an employee parameter, which it isn't finding. I took the parameter off the "Sales Rep: strSalesRep" field and it appears to work.
mtaylor
Oct 15 2009, 07:28 AM
I'm sorry, but where exactly did you take the parameter off?
(I can't wait for the Access classes to start!! a few more weeks
pbaldy
Oct 15 2009, 10:53 AM
That query has parameters on a date field and on the sales rep field. I took it off the sales rep field.
mtaylor
Oct 15 2009, 12:30 PM
AH! got it. And yes it's working great now. Thanks a Million!
pbaldy
Oct 15 2009, 12:31 PM
No problemo; glad we got it sorted out for you.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please
click here.