Results 1 to 10 of 10
  1. #1
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45

    VBA code to display value from SQL query


    Hello,

    I am not sure this is possible but I am trying to do something on one of my input forms. I want to display a Customer Name from a different table when a value is input into the form. Conceptually this is what I am trying to do:

    Acct # : Blank input form that the customer inputs
    Customer Name: Automatically found based on the above Acct #... The SQL would be Select Customer_Name from NonCoreCustomers where Noncorecustomers.Acct = me.acct

    Here is the VBA I have been trying to do:

    Dim sSql As String
    sSql = "SELECT noncorecustomers.[customername] " & _
    "FROM noncorecustomers " & _
    "WHERE noncorecustomers.acct='" & Me.acct.Value & "';"

    I am not sure how to show this value. If there is another way I am open to options. The main goal here is being able to display a value from another table that is linked based on PKs in the form.

    Thanks guys!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Simplest would probably be to use a combo box to select customers and use this technique:

    http://www.baldyweb.com/Autofill.htm

    Second simplest would probably be a DLookup(). Continuing with what you have, you'd open a recordset on that SQL and get the name from the record returned. Let me know if you want more info on that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Quote Originally Posted by pbaldy View Post
    Simplest would probably be to use a combo box to select customers and use this technique:

    http://www.baldyweb.com/Autofill.htm

    Second simplest would probably be a DLookup(). Continuing with what you have, you'd open a recordset on that SQL and get the name from the record returned. Let me know if you want more info on that.
    Thank you for the response. The first one seems like an option if I want to have a drop down list of the account numbers to retrieve customer name and possibly a different field.

    Can you give me more information with DLookup? I will do some research on it also. I have used it before but I will most likely have the expression wrong.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Here's a good reference on the syntax:

    DLookup Usage Samples

    But using a combo has the added benefit of preventing the user from entering a non-existent account (or letting them and controlling the new account being added to the account table).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Here is what I am trying right now.

    Private Sub iVueAccount_AfterUpdate()

    Me.CustomerName = DLookup("CustomerName", "NonCorecustomers", "iVueAccount = Me.iVueAccount)

    End Sub

    Me.CustomerName is the name of the text box
    "CustomerName" is the field name to output from table "NonCoreCustomers"
    "IvueAccount" is the field name in Non Core Customers
    Me.IvueAccount is the text box that the user inputs data for the form.

    It is causing debug error

  6. #6
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Nevermind on the DLookup. I was able to get that to work. I am going to also test out the simpler way with the combo box to see if I can get that to work.

    Thank you very much!

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problemo! Post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Will do.

    I have a separate question that you may be able to assist with.

    Do you know the script/cmd to enter in a word document to open a shortcut to a macro from an access database? I am setting up an automatic scheduled task to login, run the macro, logoff. I can double click the shortcut and it works fine, but my scripting is causing an error.

    This is my current code in a .CMD file

    START "%SYSTEMDRIVE%\Documents and Settings\rkolofer\My Documents\Shortcut to SwitchMacro in Switch_Date.accdb"

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, not sure what's going wrong. I'd probably just run it from Windows Scheduled Tasks.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    agent- is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    45
    Yeah thats what I did also. I just needed to add /x "Macro name" after my database to run the macro. Works well!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Code to display latest record number field created?
    By rowardHoark in forum Programming
    Replies: 1
    Last Post: 01-31-2011, 08:03 AM
  2. Query to display ONLY entries from today
    By Juan4412 in forum Queries
    Replies: 9
    Last Post: 01-15-2011, 05:34 PM
  3. Display Query Name
    By jgelpi16 in forum Programming
    Replies: 7
    Last Post: 07-20-2010, 08:46 AM
  4. HELP! Display a query result into form
    By leanne in forum Forms
    Replies: 15
    Last Post: 06-23-2010, 09:18 PM
  5. Query to display in single row
    By access in forum Queries
    Replies: 10
    Last Post: 01-14-2010, 11:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums