By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

Put Query results into a memo field - VBA, access 07

P: 8
Hello,

I was wondering if someone might be able to help me. Iím a little stuck as to how to put the results of a query into a memo field.

I run a ski chalet company and I have a bookings database. In this I store all the emails that go back and forth with my guests, these are stored in a memo field.

Iím trying to make a system to template my emails for me. Ideally Iíd like it to template and email that has a list of all the extras theyíve booked (ski lessons, ski hire etc), maybe something like:

Dear <GUEST NAME>

Here is a list of all your ski lessons, hire and lift passes:
<guestextra>
<guestextra>
<guestextra>

Etc.

Iíve put a button on the form that stores this emails and when I click it the code to do the above will hopefully run!

I can get the first bit working to put the guestname in at the start (as for that it only has to look up one value), then put it all into the memo field along with the ďhere is a list of your ski lessons....Ē etc. I have the query running fine that selects all the guest extras for that particular guest. I donít know how to get the results of that query to go as text into the memo field as well.

Iím using access 2007 on a vista computer. I am doing this in VBA, though Iím only a beginner at such things.

If anyone has any ideas Iíd be very grateful to hear about them. Even just directions to research as Iíve been searching for ages but Iím not sure really what Iím looking for.

Many thanks for your help,

Phil Teare.
Sep 23 '08 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,494
If I understand you correctly, then you want to access the SQL in a Recordset object (DAO.Recordset I would suggest).

From here, you can manipulate and use the returned values (See Basic DAO recordset loop using two recordsets). Add them to a string variable that will end up as the Memo field I would suggest.

Welcome to Bytes!
Sep 23 '08 #2

P: 8
Thanks for that. Your directions to reasearch were spot on. I've got it all just about working now. I'm having one mental block though.

I have a table of guests which stores the guest names, email addressed etc, and a table of extra which stores the options for ski lessons, ski hire etc. There is then a table called booked extras which just has the foreign keys from these two, and this table is the list of who has booked what. My query gets it's results from this bookedextras table.

As the query is made of foreign keys, the results are just numbers. How can I get the numbers as the text from the original tables? Sorry I know this should be really simple but for the life of me I can't figure it out!

My current code is below. It works but just produces a list of numbers
("2 -- 4" rather than "Phil -- Beginner lessons" as I would like it to.)

Many thanks again,

Phil.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2. Dim db As DAO.Database
  3. Dim rs1 As DAO.Recordset
  4. Set db = CurrentDb
  5. Set rs1 = db.OpenRecordset("SELECT bookedextras.guestbooking, bookedextras.extra " & _
  6.             "FROM guest INNER JOIN (booking INNER JOIN bookedextras ON booking.ID = bookedextras.guestbooking) ON guest.ID = booking.guestname " & _
  7.             "WHERE (((guest.guestname)='phil'));")
  8.  
  9. DoCmd.GoToRecord , , acNewRec
  10. email.Value = "Dear " & Forms![guest]![guestname].Value & "," & vbCrLf & "Here is your list of booked extras:" & _
  11.         vbCrLf & "Text from the query:" & vbCrLf
  12.  
  13. rs1.MoveFirst
  14. Do Until rs1.EOF
  15.     email.Value = email.Value & vbCrLf & _
  16.                 rs1!guestbooking & " -- " & rs1!extra
  17.     rs1.MoveNext
  18. Loop
  19. rs1.Close
  20. Set rs1 = Nothing
  21. Set db = Nothing
  22.  
  23. End Sub
Sep 23 '08 #3

P: 8
Hello again,

Sorry I forgot to say I could use a few dlookups I guess but i was wondering if there were any other (more efficient?!) methods, as this is just my trial database for figuring stuff out, the really bookings one is much larger and there woudl be many tables to look things up in with all the foreign keys....

Thanks!

Phil.
Sep 23 '08 #4

NeoPa
Expert Mod 15k+
P: 31,494
You're quite right not to use DLookup()s in this situation. This really should be a job for a properly defined query.

With the info I have to hand, I can't be too sure of things, but I would guess there is a table for extras somewhere. In fact you say so.

I'll tell you what I'll do. I'll put together some code where I guess what MetaData you have for the relevant tables, and you will have to go through the code, understand it, and make any changes required to convert it to work in your database.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2.   Dim db As DAO.Database
  3.   Dim rs1 As DAO.Recordset
  4.   Dim strSQL As String
  5.  
  6.   Set db = CurrentDb
  7.   strSQL = "SELECT tB.BookingName, tE.ExtraName " & _
  8.            "FROM (([BookedExtras] AS tBE INNER JOIN " & _
  9.            "[Booking] AS tB " & _
  10.            "ON   tBE.GuestBooking=tB.ID) INNER JOIN " & _
  11.            "[Extra] AS tE " & _
  12.            "ON   tBE.Extra=tE.ID) INNER JOIN " & _
  13.            "[Guest] AS tG " & _
  14.            "ON   tB.GuestName=tG.ID " & _
  15.            "WHERE (tG.GuestName='phil')"
  16.   Set rs1 = db.OpenRecordset(strSQL)
  17.  
  18.   DoCmd.GoToRecord , , acNewRec
  19.   email.Value = "Dear " & Forms![guest]![guestname].Value & "," & vbCrLf & "Here is your list of booked extras:" & _
  20.           vbCrLf & "Text from the query:" & vbCrLf
  21.  
  22.   rs1.MoveFirst
  23.   Do Until rs1.EOF
  24.       email.Value = email.Value & vbCrLf & _
  25.                   rs1!BookingName & " -- " & rs1!ExtraName
  26.       rs1.MoveNext
  27.   Loop
  28.   rs1.Close
  29.   Set rs1 = Nothing
  30.   Set db = Nothing
  31.  
  32. End Sub
Sep 23 '08 #5

P: 8
thanks so much for that. If I'm understanding you and your code right then what you are saying is I should change the initial query in the first place to produce the text I need (rather than just a foreign key ID), as opposed to trying later to translate those foreign keys back to text.

That makes so much more sense than my way! Many thanks for your time and thoughts, I'm off to attack that....
Sep 24 '08 #6

P: 8
wonderful! It's all grand and working perfectly now. Many many thanks for your help, I'm very grateful. I don't know how long it would have taken me otherwise! I've put the final working code below just for others reading through this solution who might want to see how it ended up.

Thanks again,

Phil.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2. Dim db As DAO.Database
  3. Dim rs1 As DAO.Recordset
  4. Dim strSQL As String
  5. Dim strGuestname As String
  6.  
  7. Set db = CurrentDb
  8.  
  9. strGuestname = Forms![guest]![guestname].Value
  10.  
  11. strSQL = "SELECT guest.guestname, extratype.extratype, extra.extraname " & _
  12.         "FROM extratype INNER JOIN (extra INNER JOIN (guest INNER JOIN " & _
  13.         "(booking INNER JOIN bookedextras ON booking.ID = bookedextras.guestbooking) " & _
  14.         "ON guest.ID = booking.guestname) ON extra.ID = bookedextras.extra) " & _
  15.         "ON extratype.ID = extra.extratype " & _
  16.         "WHERE (((guest.guestname)='" & strGuestname & "'));"
  17.  
  18. Set rs1 = db.OpenRecordset(strSQL)
  19.  
  20. DoCmd.GoToRecord , , acNewRec
  21. email.Value = "Dear " & strGuestname & "," & vbCrLf & "Here is your list of booked extras:" & _
  22.         vbCrLf & "Text from the query:" & vbCrLf
  23.  
  24. rs1.MoveFirst
  25. Do Until rs1.EOF
  26.     email.Value = email.Value & vbCrLf & _
  27.                 rs1!guestname & " -- " & rs1!extratype & " - " & rs1!extraname
  28.     rs1.MoveNext
  29. Loop
  30. rs1.Close
  31. Set rs1 = Nothing
  32. Set db = Nothing
  33.  
  34. End Sub
Sep 24 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
thanks so much for that. If I'm understanding you and your code right then what you are saying is I should change the initial query in the first place to produce the text I need (rather than just a foreign key ID), as opposed to trying later to translate those foreign keys back to text.

That makes so much more sense than my way! Many thanks for your time and thoughts, I'm off to attack that....
Absolutely. You express the idea perfectly :)
Sep 24 '08 #8

NeoPa
Expert Mod 15k+
P: 31,494
Good stuff, and thanks for posting your eventual code.

It's very gratifying when a member picks up and goes with what you're trying to say :)
Sep 24 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.