Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Referencing query results in code?

    The code I'm using does work fine at the moment, but I am trying to reduce the amount of data on the form (maybe I don't need to any opinions appreciated.)



    Code:
    oMail.Body = Me!Site_Name & " " & Me!Asset_Type & vbCrLf & Me!Address_1 & vbCrLf & Me!Address_2 & vbCrLf & Me!Address_3 & vbCrLf & Me!Postcode & vbCrLf & vbCrLf & "Hospital Details:" & vbCrLf & Me!Hospital_Name & vbCrLf & Me!Hospital_Address & vbCrLf & Me!Hospital_Postcode & vbCrLf & "Tel: " & Me!Hospital_Telephone
    oMail.Subject = Me!Site_Name & " Details"
    This piece of code takes the details off the open form and puts them into outlook. Great!

    But the way I've done this means I have to display a lot of information that's really no use on the form. When there are 7000 records on that form I'm concerned it may slow it down if I have all the fields for every record.

    I wans hoping I can reference a query rather than the "me!". Is this possible? I'm assuming it is I just cant find what I'm looking for.

    (this is part of me clearing up 2 years of bad practice. haha)

    Thanks.

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    referencing individual query fields I mean just to clear up any confusion.

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Update: I've realised this information doesn't need to be on the form at all. As long as its in the forms query. Would anyone have input if its better to query a selected record to find this information to keep down how much data has to load. OR keep it as is.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Here is a subroutine to get the data from a query:

    Code:
    Private Function SetUpBody()
        Dim rst As Recordset, strSQL As String, strBody As String
        On Error GoTo SUP_Err
        
        strSQL = "SELECT ... FROM ... WHERE ... = " & Me!..ID & ";"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        rst.MoveFirst
        If Err <> 0 Then GoTo SUP_Err
        
        Do Until rst.EOF
            strBody = strBody & rst!... & vbCrLf
            rst.MoveNext
        Loop
        
        Set rst = Nothing
        SetUpBody = strBody
        Exit Function
        
    SUP_Err:
        MsgBox Err & " " & Err.Description
    End Function

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Okay, I shall have a go. Appreciate that.

  6. #6
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Making little progress, but still.. Little is better than none! Ill continue tomorrow and probably update If I cant get it to work.

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    7000 records isn't a lot in today's world - I doubt you would notice a difference between including all the fields in the query and not having them. I would say leave it as it is - that way you don't have to worry about using code to retrieve data; the form does it for you.

  8. #8
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Okay, Thanks for your input. I may do this anyway just so I know how. Seems useful. The database has saved a lot of time from peoples every day work so I've ended up with some time to tidy it up a bit haha.

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

Similar Threads

  1. Compacting or referencing code
    By Thompyt in forum Programming
    Replies: 4
    Last Post: 12-16-2016, 06:10 PM
  2. referencing results in the same query,
    By Homegrownandy in forum Queries
    Replies: 5
    Last Post: 10-28-2015, 08:56 AM
  3. Referencing search results
    By Homegrownandy in forum Access
    Replies: 5
    Last Post: 07-21-2015, 10:10 AM
  4. Color Code Query Results
    By jo15765 in forum Queries
    Replies: 6
    Last Post: 01-10-2013, 09:13 PM
  5. Replies: 3
    Last Post: 10-15-2010, 11:17 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