473,320 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Where Condition in OpenReport

5
I am struglling with the Where condition when I use OpenReport. In a form I
have a list box in which I can choose multiple selections. Then in my report
I want to the "email" field to equal the current list box selection (I have
it loop through each selection). Will it work with a mutiple selection list
box? I know the code below is wrong but I am looking for help on how to state
the where condition:

DoCmd.OpenReport "1", acViewPreview, , "[email] = " & Nz(lst.Column(1,
varItem))

Thanks!
Jun 26 '07 #1
9 7782
ADezii
8,834 Expert 8TB
I am struglling with the Where condition when I use OpenReport. In a form I
have a list box in which I can choose multiple selections. Then in my report
I want to the "email" field to equal the current list box selection (I have
it loop through each selection). Will it work with a mutiple selection list
box? I know the code below is wrong but I am looking for help on how to state
the where condition:

DoCmd.OpenReport "1", acViewPreview, , "[email] = " & Nz(lst.Column(1,
varItem))

Thanks!
  1. What exactly are your tyring to accomplish?
  2. If the selected items in your List Box are Tom, Dick, and Harry do you want to return Tom;Dick;Harry and have [email] = this String?
  3. What exactly should your Where Condition be equal to ([email]=?).
  4. You must provide more information.
Jun 26 '07 #2
ZagWit
5
As you can see I am new to VBA! hopefully my answer below help to explain it better...

What exactly are your tyring to accomplish?
*I am trying to email a separate report to each individual based on the where condition

If the selected items in your List Box are Tom, Dick, and Harry do you want to return Tom;Dick;Harry and have [email] = this String?
*No I want to loop through Tom (send him his unique report), Dick (send him his unique report), Harry (send him his unique report), etc

What exactly should your Where Condition be equal to ([email]=?)
* I want [email] to equal "Tom's email" (from my list box) then send the report to Tom, then do the same for Dick, Harry, etc

I have it working except for being able to select only the specfic conditons from the Where condition. It is just sending a report with all the records each time.
I dont know how to write the where condition so it equals the current selection of the list box in my form
Jun 26 '07 #3
JKing
1,206 Expert 1GB
Hi, I think I understand what you're trying to accomplish as I've done something similar once before.

Give this a try:

Expand|Select|Wrap|Line Numbers
  1. Dim strLinkCriteria As String
  2. Dim varItem as Variant
  3.  
  4. strLinkCriteria =  "[email] In ("
  5.  
  6. For Each varItem In Me.lstEmail.ItemsSelected
  7.       strLinkCriteria = strLinkCriteria  & "'" & Me.lstEmail.ItemData(varItem) & "', "
  8. Next
  9.  
  10. strLinkCriteria = Left(strLinkCriteria, Len(strLinkCriteria) - 2) & ")"
  11.  
  12. DoCmd.OpenReport "1", acViewPreview, , strLinkCriteria
  13.  
  14.  
Jun 26 '07 #4
ZagWit
5
Thanks for the help! I am a real novice so here is the code tring to incoporate your help. I am getting an error 3075: Description: In Operator without () in query expresion '([email] In))'

Set lst = Me![lstSelectContacts]
'Check that at least one contact has been selected
If lst.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one contact"
lst.SetFocus
GoTo ErrorHandlerExit
End If

strLinkCriteria = "[email] In ("

For Each varItem In lst.ItemsSelected
'Check for email address
strEMailRecipient = Nz(lst.Column(1, varItem))
Debug.Print "EMail address: " & strEMailRecipient
If strEMailRecipient = "" Then
GoTo NextContact
End If

strLinkCriteria = Left(strLinkCriteria, Len(strLinkCriteria) - 2) & ")"

DoCmd.OpenReport "1", acViewPreview, , strLinkCriteria

Set rpt = Reports("1")
DoCmd.Save acReport, "1"
DoCmd.Close acReport, "1"

DoCmd.SendObject acReport, 1, _
OutputFormat:=acFormatRTF, To:=strEMailRecipient, _
Subject:=strSubject, EditMessage:=False

NextContact:
Next varItem
Jun 26 '07 #5
JKing
1,206 Expert 1GB
Sorry, I missed your second post while I was searching for my example. The code I previously posted would give you a report with all the select emails in one report. I see now you want a report generated for each email. A few simple changes should do it.

Expand|Select|Wrap|Line Numbers
  1. Set lst = Me![lstSelectContacts]
  2. 'Check that at least one contact has been selected
  3. If lst.ItemsSelected.Count = 0 Then
  4. MsgBox "Please select at least one contact"
  5. lst.SetFocus
  6. GoTo ErrorHandlerExit
  7. End If
  8.  
  9. For Each varItem In lst.ItemsSelected
  10. 'Check for email address
  11. strEMailRecipient = Nz(lst.Column(1, varItem))
  12. strLinkCriteria = "[email] = '" & Me.lst.ItemData(varItem) & "'"
  13. Debug.Print "EMail address: " & strEMailRecipient
  14. If strEMailRecipient = "" Then
  15. GoTo NextContact
  16. End If
  17.  
  18. DoCmd.OpenReport "1", acViewPreview, , strLinkCriteria
  19.  
  20. Set rpt = Reports("1")
  21. DoCmd.Save acReport, "1"
  22. DoCmd.Close acReport, "1"
  23.  
  24. DoCmd.SendObject acReport, 1, _
  25. OutputFormat:=acFormatRTF, To:=strEMailRecipient, _
  26. Subject:=strSubject, EditMessage:=False
  27.  
  28. NextContact:
  29. Next varItem
  30.  
This should rebuild the strLinkCriteria for each email recipient and filter the report to that specific email.
Jun 26 '07 #6
ZagWit
5
Thanks for the help...

I am getting a compile error

Method or data member not found for .1st in this statement

strLinkCriteria = "[email] = '" & Me.lst.ItemData(varItem) & "'"
Jun 26 '07 #7
JKing
1,206 Expert 1GB
Sorry I missed that you had set lst to your control. I thought lst was the name of your listbox. Just remove the Me.

strLinkCriteria = "[email] = '" & lst.ItemData(varItem) & "'"

Hope everything works out!
Jun 26 '07 #8
ZagWit
5
Thanks. This works great!
Jun 27 '07 #9
JKing
1,206 Expert 1GB
You're welcome. Glad everything worked out.

JKing
Jun 27 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Peter yeshew | last post by:
How can i make my Else..If statement make look no so cumbersome? Besides the condition for previewing the report doesnt work,may be my If condition does no apply properly. Perhaps this is due to...
3
by: Tony Jenkins | last post by:
I am trying to implement the code to "Print only the Current Record in a report as posted by Dav Ashish. But I am having a problem. I get a parameter Entry box asking me to enter the OrderID,...
3
by: eddiec | last post by:
hi everyone, I have a report in an adp that uses aggregate functions in its record source and I am trying to figure out how to filter the records displayed in the report: DoCmd.OpenReport...
1
by: Tom Louchbaum | last post by:
Can someonw pls tell me how to reference the Where clause made by the VBA statement DoCmd.OPENREPORT "Report",acviewpreview,,where as a control within the Report itself? Thanks.
8
by: Mike Charney | last post by:
I am trying to write an Open Form command in VBA with a where clause in it. I need to be able to do two different where clauses: 1) With a between in it: i.e. Between Date1 and Date2 or...
3
by: russellhq | last post by:
Hi, i am trying to open a report based on values picked in a form but get stuck with one of the values. I have a feeling it has to do with the yes/no datatype but am not sure since I am quite new...
2
by: Jim Devenish | last post by:
I have a report which can be displayed on the screen with: DoCmd.OpenReport "RemittanceAdvice",acViewNormal,,"PaymentID = " & paymentRecID I now wish to create a snapshot of this, using the...
7
by: lostdawg | last post by:
Hi, I have a question regarding placing the Where condition in this section of code Private Sub Command219_Click() DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70...
2
by: Ceebaby via AccessMonster.com | last post by:
Hi Folks I wondered if someone could point me in the right direction before I completely tear my hair out. I have a user selection form where options can be selected for a report. Users now...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.