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!
9 7782
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!
- What exactly are your tyring to accomplish?
- 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?
- What exactly should your Where Condition be equal to ([email]=?).
- You must provide more information.
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
Hi, I think I understand what you're trying to accomplish as I've done something similar once before.
Give this a try: -
Dim strLinkCriteria As String
-
Dim varItem as Variant
-
-
strLinkCriteria = "[email] In ("
-
-
For Each varItem In Me.lstEmail.ItemsSelected
-
strLinkCriteria = strLinkCriteria & "'" & Me.lstEmail.ItemData(varItem) & "', "
-
Next
-
-
strLinkCriteria = Left(strLinkCriteria, Len(strLinkCriteria) - 2) & ")"
-
-
DoCmd.OpenReport "1", acViewPreview, , strLinkCriteria
-
-
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
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. -
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
-
-
For Each varItem In lst.ItemsSelected
-
'Check for email address
-
strEMailRecipient = Nz(lst.Column(1, varItem))
-
strLinkCriteria = "[email] = '" & Me.lst.ItemData(varItem) & "'"
-
Debug.Print "EMail address: " & strEMailRecipient
-
If strEMailRecipient = "" Then
-
GoTo NextContact
-
End If
-
-
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
-
This should rebuild the strLinkCriteria for each email recipient and filter the report to that specific email.
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) & "'"
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!
Thanks. This works great!
You're welcome. Glad everything worked out.
JKing
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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,...
|
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...
|
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.
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
| |