email data of table

[hardik_088]

hi guys,
i googled lots of thread about automatic reminder but that all are about dates, and I am making database about inventory system for my comapany. so i have table laptops and there is field like invoice no,part number,description,Quantity etc.............
So I want that if quantity is less than three for any record i should get
an automatic email.

And i am just started to use MS access so any type of help would be appriciated.
Thank You Very Much...

 

[VilaRestal]

I think you'd have to have a form that would have a timer event that would periodically check the stock levels and send an email if any were under 3 (listing those that were). For that to work that form would have to be always open on a PC that was always on. If you have a server on a network then a special access front end could be made for it for just that purpose: A 'check stock levels and email accordingly app'

 

[hardik_088]

Thanks yar,
but i dont have any idea how to check the table data by timer .
Can you please give me a hint to do that because i know just about timer event that will count the time and do activity.

thank you very much.

 

[VilaRestal]

You would:
Make a database with a link to the table in question (External Data > Access > Link Tables)
In that database you would create a Form (it's going to be the only form so Form1 will do for a name). Make that form open at database startup (Access Options > Current Database)
In the forms design set the Timer Interval property (under the events tab) to something like 3600000 (one hour in milliseconds)
Then in the module for the form you would put code in its Timer event that opens a recordset of the table, loops through it checking each record stock level and if it's low than adding its ID to a string. At the end of the loop, if any were found with low stock then generate and send an email to whatever address with that string as the body.

It wouldn't be a simple bit of code (but not very complex either).
The database (and the form) would have to be open permanently on a computer that's always on (on a file server - the same computer that hosts the database it links to perhaps)

 

[hardik_088]

hi VilaRestal,
I tried to use timer and my table opened and its working nice but I tried to check records of table where [quantity ordered] < 3 with Dlookup function
and it is not working so can you please give me help where I am Wrong.

I have table Laptops and Form Laptops (who have all data of table Laptops) and [quantity ordered ] is a field in Table Laptops.

Here is my code in Time Event

Private Sub Form_Timer()
DoCmd.OpenForm "Laptops"
DoCmd.OpenTable "Laptops"
If (Forms!Laptops![quantity ordered].Value = DLookup("[quantity ordered]", "Laptops", "[quantity ordered] < 3 ")) Then
MsgBox "send Email"
End If
End Sub

 

[VilaRestal]

No, it's going to be more involbed than that . And no opening forms or tables needed.

The form with the Timer event can be unbound and blank.
Like I said, I think it should be the only object in a separate database that just has linked tables to the real database. A database whose sole purpose is to do these emails.

Some code to follow...

 

[hardik_088]

hi ,
i am not getting what you want to say exactly .
i link table in another database now what i should do

 

[VilaRestal]

create a blank form
go into design and set the timer interval to something like 3600000 (an hour)
save it
then put this code in its module:

Private Sub Form_Timer()
    CheckOrderLevels
End Sub
 
Private Sub CheckOrderLevels()
    On Error Resume Next
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Laptops WHERE [quantity ordered] < 3", dbOpenSnapshot)
    With rs
        If .RecordCount > 0 Then
            Dim sLaptops As String
            Dim bFirst As Boolean
            sLaptops = ""
            bFirst = True
            .MoveFirst
            Do While Not .EOF
                If Not bFirst Then sLaptops = sLaptops & vbCrLf
                sLaptops = sLaptops & .Fields("Laptop")
                bFirst = False
                .MoveNext
            Loop
            SendReport sLaptops
        End If
        .Close
    End With
End Sub
 
Private Sub SendReport(ByVal sLaptops As String)
    On Error Resume Next
    Dim olApp As Object
    Dim objMail As Object
    Set olApp = GetObject(, "Outlook.Application")
    If Err Then Set olApp = CreateObject("Outlook.Application")
    Set objMail = olApp.CreateItem(olMailItem)
    With objMail
        .BodyFormat = olFormatHTML
        .To = "[EMAIL="testaddress@nowhere.com"]testaddress@nowhere.com[/EMAIL]"
        .Subject = "Laptops That Require Ordering"
        .HTMLBody = sLaptops
        .Send
    End With
End Sub

(Thanks to shmulikharel for that third sub btw from this forum thread http://www.access-programmers.co.uk/forums/showthread.php?t=193374)

But change the "Laptop" field if necessary and the email address.

Go to Tools > References in the VBA editor and scroll down and tick Microsoft Outlook 12.0 object library.

That should do it:
When the form is open, every hour it will send a list of laptops to the email address you enter.
To refine it a bit you could put a button on the form to CheckOrderLevels now. And maybe you could put a textbox that would allow a different email address to be entered.

 

[hardik_088]

Thanks dear,
But I am confuse Module means i have to create module or in the Timer event.

 

[VilaRestal]

No the form's module.
In the form's design view click the View Code button (looks like some Gems - far right of the Design ribbon)
The Timer event is in the code: By pasting that code in the form's module the Timer event will be handled (with the first sub in the code).

 

[hardik_088]

hey
Its working nicely
Thank you very very much.

 

[hardik_088]

hi VilaRestal,
If I want to check record from many tables and when i get email there should be message like this. "You have this item is less than three in table_name."

if possible i also want all information about that data where quantity is less than three.

Thanks a lot

 

[VilaRestal]

OK here's a more generic version:

Private Sub Form_Timer()
    CheckOrderLevels "Laptops", "Laptop", "[quantity ordered]", "Laptops"
    CheckOrderLevels "Printers", "Printer", "[quantity ordered]", "Printers"
End Sub
 
Private Sub CheckOrderLevels(ByVal strTable As String, ByVal strNameField As String, ByVal strQuantityField As String, ByVal strProduct As String)
    On Error Resume Next
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT " & strNameField & " FROM " & strTable _
            & " WHERE " & strQuantityField & " < 3", dbOpenSnapshot)
    With rs
        If .RecordCount > 0 Then
            Dim sProducts As String
            Dim bFirst As Boolean
            sProducts = ""
            bFirst = True
            .MoveFirst
            Do While Not .EOF
                If Not bFirst Then sProducts = sProducts & vbCrLf
                sProducts = .Fields(strNameField)
                bFirst = False
                .MoveNext
            Loop
            SendReport strTable, strProduct, sProducts
        End If
        .Close
    End With
End Sub
 
Private Sub SendReport(ByVal strTable As String, ByVal strProduct As String, ByVal sProducts As String)
    On Error Resume Next
    Dim olApp As Object
    Dim objMail As Object
    Set olApp = GetObject(, "Outlook.Application")
    If Err Then Set olApp = CreateObject("Outlook.Application")
    Set objMail = olApp.CreateItem(olMailItem)
    With objMail
        .BodyFormat = olFormatHTML
        .To = "[EMAIL="testaddress@nowhere.com"]testaddress@nowhere.com[/EMAIL]"
        .Subject = "Automated message: " & strProduct & " Requires Ordering"
        .HTMLBody = strProduct & " is less than three in " & strTable & ":" & vbCrLf & vbCrLf _
                & sProducts & vbCrLf & vbCrLf & "This message was generated automatically."
        .Send
    End With
End Sub

Just enter a line for each table in the Timer event. Look at the sub's argument list (and code) for an explanation of what each argument represents.

To have more information, I would make queries for each table that concatenates the data into one field and then set the code to use those queries (perhaps you'd want another argument to passed through the subs showing the query's base table name and the name of the query).

 

[hardik_088]

hi ,
I tried that code but its not working .
I have printers table and form also.
my database is just doing process and not responding why?

Thanks a lot

 

[VilaRestal]

doing process and not responding?

ctrl+break to stop code executing

is it in the loop? I can't see how. The loop will end.

I was guessing the Printers table and its field names. I'd be surprised if they were right.

Comment out (put an apostrophe before) the On Error Resume Next line in CheckOrderLevels and see whether it bugs out anywhere.

If the parameters in each call to CheckOrderLevels are correct (correct name of table, correct name of name field, correct name of quantity field) then it should work the same as before.

Perhaps comment out the second call to CheckOrderLevels (the Printers one). The first one (Laptops) should work the same as before,

 

[VilaRestal]

One mistake I've spotted (but wouldn't cause it to loop forever or bug out) is the line
sProducts = .Fields(strNameField)
Should be
sProducts = sProducts & .Fields(strNameField)

 

[hardik_088]

I put the comments but it is still not working

it is giving error on here (Bold text)
Private Sub CheckOrderLevels(ByVal strTable As String, ByVal strNameField As String, ByVal strQuantityField As String, ByVal strProduct As String)
On Error Resume Next
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT " & strNameField & " FROM " & strTable _
& " WHERE " & strQuantityField & " < 3", dbOpenSnapshot)
With rs
If .RecordCount > 0 Then
Dim sProducts As String
Dim bFirst As Boolean
sProducts = ""
bFirst = True
.MoveFirst
Do While Not .EOF
If Not bFirst Then sProducts = sProducts & vbCrLf
sProducts = .Fields(strNameField)
bFirst = False
.MoveNext
Loop
SendReport strTable, strProduct, sProducts
End If
.Close

 

[VilaRestal]

I think the value of strNameField must be wrong
That's the second parameter in the CheckOrderLevels line in the Form_Timer sub
What's the name of the field in the Laptops table that identifies a laptop?
Change the second parameter to that (in quotes and in square brackets too if the name contains spaces)

 

[hardik_088]

heyy
its really working nice! unbelievable
thanks a lot

 

[VilaRestal]

You're welcome