Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a form from a query make in SQL View (2002 SP2)

    My office is getting ready to upgrade our document management system from Hummingbird's Docs Open to DM5. On occasion, I need access to the document where it lives on the server (and not just through the DMS's interface). Our new DMS has completely locked our ability to see a document's real path. I have a query created in SQL view that will give me the path to the document, but I would like a way to automate it a bit more.
    <hr>SELECT P.DOCNUMBER,V.VERSION,V.VERSION_LABEL,P.DOCSERVER_ LOC + P.PATH+C.PATH

    FROM DOCSADM.PROFILE P,DOCSADM.COMPONENTS C,DOCSADM.VERSIONS V Where P.DocNumber = C.DocNumber AND P.DocNumber = V.DocNumber AND C.Version_ID = V.Version_ID AND P.DocNumber = 600000<hr>
    Currently, I have to go into the query, switch to SQL view, manually change the document number (the 600000 at the end), then save to see my path. I was wanting to set up a form that I could type in the document number and it would automatically do all the background stuff and display my path for me. Is there a way to do this?

    Thanks,
    Becky

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating a form from a query make in SQL View (2002 SP2)

    A very simple solution is to replace 600000 by [Enter Document Number]. The user will be prompted with the text between the square brackets.
    Your idea of using a form is more user-friendly. Say that you have a form frmDocPath bound to the query with an unbound text box txtDocNumber. You can refer to this text box by replacing the 600000 by

    [Forms]![frmDocPath]![txtDocNumber]

    You would have to requery the form when the user has entered something in the text box.

    An alternative is to leave the criteria out, and to use the Combo Box Wizard to put a combo box on the form that searches for the document number - make sure that the Wizard button on the Control Toolbox is "on" before you place the combo box.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a form from a query make in SQL View (2002 SP2)

    I tried doing exactly what you said, but I don't get a prompt. I only get the error message below. I tried just removing the 60000 (everything after the =), but I got another error similar to the attached.

    Did I do something wrong?

    Thanks,
    Becky
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a form from a query make in SQL View (2002 SP2)

    Where are you trying to create this query? If you create it in an Access mdb with linked SQL Server tables, it should work. If you are using an ADP or trying to create the query in SQL Server, it definitely won't, since SQL Server knows nothing whatsoever about Access forms and T-SQL doesn't handle parameters like that.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating a form from a query make in SQL View (2002 SP2)

    I hadn't realized that this was an ODBC connection to a SQL Server database. Unknown territory for me, so I hope somebody else takes over. Sorry about the confusion.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Creating a form from a query make in SQL View (2002 SP2)

    The error message you got suggests there was something wrong with the syntax of the query, but you should also be able to simply use a parameter query - we do it all the time with ODBC attached SQL Server tables. Are you familiar with parameter queries?
    Wendell

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a form from a query make in SQL View (2002 SP2)

    Unfortunately, I'm not at all familiar with parameter queries. My boss (the system admin) and I are trying to figure this out with absolutely no knowledge of what we're actually doing. <img src=/S/woops.gif border=0 alt=woops width=58 height=36> We've worked with queries and forms and all kinds of Access stuff, but this is our first time trying to talk to anything outside of Access.

    Thanks,
    Becky

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Creating a form from a query make in SQL View (2002 SP2)

    To do a parameter query, you put a text string in the query grid for the Criteria row that is surrounded by square brackets, for example <font color=blue>[Enter the Date you wish to use in your query]</font color=blue>, and when you run the query you will get a message box with that text string and a text box to supply the date value. I would suggest you find an introductory book on Access if you are starting pretty much from scratch - one of the Step-byStep books or Dummies books or something like that. In the meantime, you might find some of the info you need at one of the websites that offer online tutorials - http://databases.about.com/cs/tutorials/ is one such site, and they have a queries tutorial.

    <font color=6495ed>Updated to add a link to a Parameter Query tutorial:</font color=6495ed>
    Here's a link to a tutorial on Parameter Queries.
    Wendell

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FIX-Creating a form from a query made in SQL View

    Silly me, I forgot that my husband is an Access & SQL database programmer! <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22> I sent the stuff over to him, and he fixed everything right up. In my original query, he removed the "AND P.DocNumber = 600000" section and created a second query (a regular access one, this time) that linked to a field in a new empty table he created. The table has a field named "docnumber" just like the the SQL database. The form he created lets you add an entry to the new table, then immediately run the query. It takes a few seconds to run, but it gives us exactly what we needed.

    Thanks to all for the help!
    Becky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •