[X]My Assistant

[jon1009]

Hello,
Ive creating a script that send a e-mail to Google Calendar, I have the main orders table then a order items table. What I would like to do is when I click a button on the main layout it sets a variable for the order items for example var = orderItem1, orderItem2,... etc
Ive attached a screenshot give a better Ideas. I would click end to calendar then which would store the room information in a variable (Bathroom, Lounge) so I can compose the email with it
Cheers
Jon

Attached File(s)

 Screenshot_2014_09_01_13.04.jpg ( 259.46K )Number of downloads: 2

 

[Jeff B.]

Why would you need to store-to-variable? What about building a query that grabs/assembles everything you need, then use that query as your email source?

[jon1009]

I was just going to store them in a variable as I only need to display them like orderitem1, orderitem2 etc and it would be a lot easier to add it to the google calendar
I have created a query but I still dont know who I would use the returned values
The email that iam looking for is
Subject : Order for {OrderNo}
Order {OrderNo} had been booked for fitting on {StartDate}{StartTime} until {EndDate}{EndTime}.
This order is for the following rooms {Rooms}
Location {Address}
I have all the other bits working just need to get the related rooms but Iam unsure about the best approach.

[jon1009]

I managed to achieve this using the following VBA code

CODE

Dim varOrderID As Integer
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String
varOrderID = Me.txtOrderID
Set dbs = CurrentDb
strSQL = "SELECT Room FROM tbl_OrderDetails WHERE OrderID = " & varOrderID
Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
With rsSQL
  Do While Not .EOF
    MailBody = MailBody & ![Room] & ", "
      .MoveNext
  Loop
End With

rsSQL.Close
Set rsSQL = Nothing
Me.txtRooms = MailBody