Results 1 to 10 of 10
  1. #1
    Artist.Anon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    22

    Replace DLookUp() with a SELECT statement?

    I have the following code:

    Code:
    Private Sub btnLaunchUserDetail_Click()
        tmpfirstname = DLookup("Firstname", "Users", "ID = " & Me!ReportsToID)
        tmpsurname = DLookup("Surname", "Users", "ID = " & Me!ReportsToID)
        tmpemail = DLookup("Email", "Users", "ID = " & Me!ReportsToID)
        tmptelephone = DLookup("Telephone", "Users", "ID = " & Me!ReportsToID)
        MsgBox (tmpfirstname & " " & tmpsurname & vbCrLf & vbCrLf & tmpemail & vbCrLf & tmptelephone)
    End Sub
    This works, but I am not sure if it would be more efficient to use a SELECT statement?
    If using SELECT is better, how would the above code be written to accomplish what it achieves?

    I have played around but can't get the syntax etc working. All help appreciated.


    Thanks

  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
    Does this fit the situation?

    BaldyWeb - Autofill
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Artist.Anon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    22
    1. Is it 'better' to use a single SELECT statement with multiple fields or use multiple DLookUp() to get the information that I want?

    2. The example that you provide on your site doesnt tell me how to use the returned values in a msgbox?

    Thanks

  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
    DLookups would be less efficient in this instance, presuming the desired values were already in the combo. If not, opening a recordset would be more efficient than multiple DLookup's (1 call to the data rather than 4).

    MsgBox (Me.ComboName.Column(1) & " " & Me.ComboName.Column(2) & vbCrLf & vbCrLf & Me.ComboName.Column(3) & vbCrLf & Me.ComboName.Column(4))

    Adjusting the columns of course.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Artist.Anon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    22
    1. Thanks, I thought it would be more efficient to use one data access with Select

    2. I do not have a combo box. On my form I display a userid and a button that pops up a msgbox with more info based on the userid. I have tried the following code but I am new to access SQL formatting
    Code:
        SELECT Firstname, Surname, Email, Telephone from Users WHERE ID=Me!ReportsToID
        MsgBox (Users.FirstName & " " & Users.Surname & vbCrLf & vbCrLf & Users.Email & vbCrLf & Users.Telephone)
    The SELECT statement syntax is wrong was it gets highlighted in red, and not sure how to reference the fields in the next msgbox command?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can't just have a SELECT statement like that. You'd open a recordset on the SQL:

    Code:
      Dim strSQL  As String
      Dim db      As DAO.Database
      Dim rs      As DAO.Recordset
    
      Set db = CurrentDb()
      
      strSQL = "SELECT Firstname, Surname, Email, Telephone from Users WHERE ID= " & Me!ReportsToID
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
      If Not rs.EOF Then
        MsgBox (rs!FirstName & " " & rs!Surname & vbCrLf & vbCrLf & rs!Email & vbCrLf & rs!Telephone)
      End If
    
      set rs = nothing
      set db = nothing
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Artist.Anon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    22
    Holy Cow! That's a lot of code. No wonder examples of select statements I was trying wasn't working. I think I follow the logic but obvious that I need to learn how to use SQL (and how it works in Access). Any suggestions of sites that provide a decent set of tutorials etc?

    Plugged that code in and worked perfectly, thanks very much!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! I've got some techniques and tutorials on my site, but it's not comprehensive, just stuff I seemed to be addressing a lot. I'll try to think about a comprehensive tutorial.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Historypaul is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    64
    Paul,

    Just trying to learn here...

    Is the

    Code:
      If Not rs.EOF Then
      End If
    portion of this code simply to test and make sure you've actually found a record to display before popping up the msgbox?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, and you'd get an error if there were none.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 06-07-2012, 07:05 AM
  2. Select Case vs Dlookup
    By BRV in forum Programming
    Replies: 1
    Last Post: 10-28-2011, 03:18 PM
  3. using the and function in a dlookup statement
    By englisap in forum Programming
    Replies: 10
    Last Post: 01-10-2011, 09:53 PM
  4. Help Using Variable in DLookup Statement
    By bcmarshall in forum Access
    Replies: 9
    Last Post: 12-02-2010, 12:44 AM
  5. select statement
    By jellybeannn in forum Access
    Replies: 5
    Last Post: 08-13-2010, 05:21 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