Re: VBA - Prompt from SQL Statement?

From: Cheryl Fischer (cherylfischer_at_NOSPAMmsn.com)
Date: 03/12/04 

Date: Fri, 12 Mar 2004 12:59:18 -0600

Hi Jason,

You can get your user's input using the InputBox() function. Perhaps like
this:

Dim MyVar as String

MyVar = InputBox("Enter the PO Here:")

Set rs = db.OpenRecordset _
        ("SELECT tblBase.ProdID, tblBase.MSDS " & _
        "FROM tblPickList INNER JOIN tblBase ON tblPickList.PIitem =
tblBase.ProdID " & _
        "WHERE tblPickList.PIpo) = " & Chr(34) & MyVar & Chr(34) & _
        " GROUP BY tblBase.ProdID, tblBase.MSDS;")

hth, 

--
Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX
"Jason Gyetko" <jgyetko@epower-inc.com> wrote in message
news:OUOpGKGCEHA.892@TK2MSFTNGP09.phx.gbl...
> That's what I tried to do and I get the following message:
>
> Run-time error '2465':
> Command Center can't find the field '|' referred to in your expression.
>
> I guess all I really need to know is how you can specify a variable in
your
> code then when you want to set it's value, prompt for it. Example:
>
> dim myVariable as String
> myVariable = [Prompt for Variable Data Here]
>
> When I use the brackets is when I get the above mentioned error.  Is there
> another character I have to use instead of the brackets?
>
> "PC Data***" <spam@nospam.spam> wrote in message
> news:ren4c.1786$CJ5.285@newsread2.news.atl.earthlink.net...
> > Jason,
> >
> > When you have a question like this, create the actual query and then
> switch to
> > SQL view to see the SQL for the actual query. There you will see the
> answer to
> > your question.
> >
> > How does that go .... Give a man a fish and .... but Teach a man to fish
> ...
> >
> >
> > --
> >                                          PC Data***
> > Your Resource For Help With Access, Excel And Word Applications
> >                               resource@pcdata***.com
> >                                  www.pcdata***.com
> >
> >
> > "Jason Gyetko" <jgyetko@epower-inc.com> wrote in message
> > news:ez#0JnFCEHA.580@TK2MSFTNGP11.phx.gbl...
> > > How do I prompt the user from an SQL statement in VBA?  It works if I
> put it
> > > in an actual access query.  Given the example below, I want an "Enter
> PO"
> > > prompt to pop up so a PO# can be entered, can this be done or do I
have
> to
> > > pop up a form and pull the data from a text box on it?  Thanks in
> advance.
> > >
> > > Set rs = db.OpenRecordset _
> > >         ("SELECT tblBase.ProdID, tblBase.MSDS " & _
> > >         "FROM tblPickList INNER JOIN tblBase ON tblPickList.PIitem =
> > > tblBase.ProdID " & _
> > >         "WHERE (((tblPickList.PIpo) = """ & [Enter PO] & """)) " & _
> > >         "GROUP BY tblBase.ProdID, tblBase.MSDS;")
> > >
> > >
> >
> >
>
>