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

[pteare]

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.

[NeoPa]

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!

[pteare]

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.

Private Sub Command3_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT bookedextras.guestbooking, bookedextras.extra " & _
            "FROM guest INNER JOIN (booking INNER JOIN bookedextras ON booking.ID = bookedextras.guestbooking) ON guest.ID = booking.guestname " & _
            "WHERE (((guest.guestname)='phil'));")

DoCmd.GoToRecord , , acNewRec
email.Value = "Dear " & Forms![guest]![guestname].Value & "," & vbCrLf & "Here is your list of booked extras:" & _
        vbCrLf & "Text from the query:" & vbCrLf

rs1.MoveFirst
Do Until rs1.EOF
    email.Value = email.Value & vbCrLf & _
                rs1!guestbooking & " -- " & rs1!extra
    rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
Set db = Nothing
        
End Sub

[pteare]

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.

[NeoPa]

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.

Private Sub Command3_Click()
  Dim db As DAO.Database
  Dim rs1 As DAO.Recordset
  Dim strSQL As String

  Set db = CurrentDb
  strSQL = "SELECT tB.BookingName, tE.ExtraName " & _
           "FROM (([BookedExtras] AS tBE INNER JOIN " & _
           "[Booking] AS tB " & _
           "ON   tBE.GuestBooking=tB.ID) INNER JOIN " & _
           "[Extra] AS tE " & _
           "ON   tBE.Extra=tE.ID) INNER JOIN " & _
           "[Guest] AS tG " & _
           "ON   tB.GuestName=tG.ID " & _
           "WHERE (tG.GuestName='phil')"
  Set rs1 = db.OpenRecordset(strSQL)

  DoCmd.GoToRecord , , acNewRec
  email.Value = "Dear " & Forms![guest]![guestname].Value & "," & vbCrLf & "Here is your list of booked extras:" & _
          vbCrLf & "Text from the query:" & vbCrLf

  rs1.MoveFirst
  Do Until rs1.EOF
      email.Value = email.Value & vbCrLf & _
                  rs1!BookingName & " -- " & rs1!ExtraName
      rs1.MoveNext
  Loop
  rs1.Close
  Set rs1 = Nothing
  Set db = Nothing
        
End Sub

[pteare]

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....

[pteare]

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.

Private Sub Command3_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim strSQL As String
Dim strGuestname As String

Set db = CurrentDb

strGuestname = Forms![guest]![guestname].Value

strSQL = "SELECT guest.guestname, extratype.extratype, extra.extraname " & _
        "FROM extratype INNER JOIN (extra INNER JOIN (guest INNER JOIN " & _
        "(booking INNER JOIN bookedextras ON booking.ID = bookedextras.guestbooking) " & _
        "ON guest.ID = booking.guestname) ON extra.ID = bookedextras.extra) " & _
        "ON extratype.ID = extra.extratype " & _
        "WHERE (((guest.guestname)='" & strGuestname & "'));"

Set rs1 = db.OpenRecordset(strSQL)

DoCmd.GoToRecord , , acNewRec
email.Value = "Dear " & strGuestname & "," & vbCrLf & "Here is your list of booked extras:" & _
        vbCrLf & "Text from the query:" & vbCrLf

rs1.MoveFirst
Do Until rs1.EOF
    email.Value = email.Value & vbCrLf & _
                rs1!guestname & " -- " & rs1!extratype & " - " & rs1!extraname
    rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
Set db = Nothing
        
End Sub

[NeoPa]

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 :)

[NeoPa]

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 :)