How do I set a query's [ENTER] criteria with visual basic

  • Thread starter Thread starter JEFF
  • Start date Start date
J

JEFF

Greetings,

If there is a better way to approach this problem I am open to changing my
implementation. I have a report that is based on a query. This is overly
simplified but for example the query has 3 fields FName, LName and City.
For the 3rd parameter I use a Like [ENTER City] so that when a user runs the
report they can select the City to focus on.

I run this report from a macro to attach the report as an email. In order
to know who to send the email to I need to know what City I am targetting so
I query the user with an InputBox like this:

Dim pstrVendor As String
pstrVendor = InputBox("Enter name of City", "Vendor Name")
stDocName = "Report By City"
DoCmd.SendObject acReport, stDocName, , pstrEmail, , , pstrSubject,
pstrMessage, False

When I do this I get a prompt for the pstrVendor variable and I want to pass
that into the "Report by City" report to avoid getting a second prompt for
the City.

I think the correct way to do this is to generate a sql query string but for
various reasons I cannot.

Is there a way for me to pass pstrVendor to the [ENTER City] prompt?

Best regards,
Jeff
 
SendObject doesn't allow you to pass a Where clause.

The simplest way of what you're trying to do is put the name into a control
on the form, and have the query refer to that control
(Forms!NameOfForm!NameOfControl), rather than [ENTER City].
 
Back
Top