I’m hoping someone can help, I have another silly Access Question,
using just VBA, I have a vairiable, it’s a unique key for a table, I want to get another piece of info from the table, that’s on the same line as the key. I can’t add a query, or form, just modify the VBA
the background, we used to generate email’s to users via their unique ID’s, we have a new email system that dosen’t use their unique ID’s so we need now to generate them now by surname and first inital, all three items are held in a single table. The mail to, is a function that is given the users Unique Id, I just want that to retreive the required info rather than have to mess with dozens of forms.
I’m sure someone can help…
Set dbs = Workspace.OpenDatabase(“c:\database Name”, , False)
sSQL1 = “Select * from table1”
Set Rst = dbs.OpenRecordset(sSQL1, dbOpenSnapshot)
sSQL2 = "Select * from table2 where uniquekey = " & Rst(0) ’ Rst(0) Uni key
Set Rst2 = dbs.OpenRecordset(sSQL2, dbOpenSnapshot)
'I would use a DIM statement to hold the records next
Dim A1, A2, A3, A4 as String
While Rst.eof = false
A1 = Rst2(0) ’ Uni #
A2 = Rst2(1) ’ Email etc…
'Place your email handler in here…
Wend
Rst2(#) of course is the value Id from Table
You can always PM me …!
Got it working,
the code has gone from
Public Function Mailme(Mail_To As String, Subject As String)
'Create an email
On Error Resume Next
DoCmd.SendObject , , , Mail_To, , , Subject, "Message", True
End Function
to
Public Function Mailme(Mail_To As String, Subject As String)
'Create an email
On Error Resume Next
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL1 As String
Dim FirstName As String
Dim Surname As String
Set dbs = CurrentDb
sSQL1 = "Select * from [user]"
Set rs = dbs.OpenRecordset(sSQL1)
While rs.EOF = False
If rs(0) = Mail_To Then
FirstName = rs(2)
Surname = rs(3)
Else
End If
rs.MoveNext
Wend
Mail_To = Surname & " " & Left(FirstName, 1)
DoCmd.SendObject , , , Mail_To, , , Subject, "Message", True
End Function
I’m sure it’s not as neat as it could be but it works for me 
Cheers Greg
Adam if you’re looping thru a large group you’ll need to move the Docmd before the Wend…
I would also add a small timer event maybe to allow the code to open the MAPI time to process the request…maybe something like:
Dim I as Long
Mail_To = Surname & " " & Left(FirstName, 1)
DoCmd.SendObject , , , Mail_To, , , Subject, “Message”, True
I = 1
Do While I < 10000
I = I + 1
Loop
Wend
Good Luck Adam…you can always PM me for Access stuff…