check database before acepting new enrolment in training

[megatronixs]

Hi all,

I have some code that is used to scan Outlook emails and then entering them in a database for trainings. The idea is to automate the emails received in the training team.
a person would reply on the email, and when the automation runs, it pulls it to the enrolment table in the database. I use a event id to make all trainings unique.
The other factor that plays, is the amount of places available for the training. If ther would be a training with only 12 places, the code should first check if the event id has still places available, if yes, then enter the data in the table and respond with email to the user, or, if no places available, then respond with email saying sorry, no places.

The below link is the one containing the test database and more about the automation:
http://www.access-programmers.co.uk/forums/showthread.php?t=290816

Any clue to count the amount of event id's in the tabel that is equal as the event id in the email and enter it in the table if still not reached the limit of the places.
The amounts of places available are in the table "tbl_event" in the column "available_places" minus the column "reserved_places" (there could be 12 places, but 2 could be reserved and then only would be 10 available in reality).

Thanking you all in advance and wishing you all a happy new year

 

[sneuberg]

Let's say the event_ids are in a table name tbl_enrollment and the event_id from the email is in a variable named strEventID and event_id is a textfield in the table, then

DCount("*", "[tbl_enrollment]", "[event_id] = '" & strEventID & "'")

would get the count of the enrollments for that event_id.


You could get the values for "available_places" and "reserved_places" with DLookup.

 

[megatronixs]

Hi,

It works great
Will play with the DLookup to see where I get

Happy new year

 

[Gasman]

Sneuberg,

Could the o/p use a query to get all three fields in one pass and then do the math in a form?.

Let's say the event_ids are in a table name tbl_enrollment and the event_id from the email is in a variable named strEventID and event_id is a textfield in the table, then

DCount("*", "[tbl_enrollment]", "[event_id] = '" & strEventID & "'")

would get the count of the enrollments for that event_id.


You could get the values for "available_places" and "reserved_places" with DLookup.

 

[sneuberg]

Sneuberg,

Could the o/p use a query to get all three fields in one pass and then do the math in a form?.

Probably could do the math in the query too but without knowing what tables are involved and their relationships I just made a rather generic suggestion.

 

[Gasman]

Probably could do the math in the query too but without knowing what tables are involved and their relationships I just made a rather generic suggestion.

OK, thank you.
It was just that I was thinking of the query route rather than multiple aggregate functions.

 

[megatronixs]

Hi,

I got so far with the below code.
The only problem so far I see is that when there is a multiple enrolment and 1 place available, it will stop the whole thing and not enrol at least one for the training.
How to solve this?

Next part is the email response the user will receive if the enrolment was succesfull or if there are no more places, email with a sorry message.

Sub ExtractParticipants(InboxItem As Outlook.MailItem)
On Error GoTo ExtractParticipants_Err

Dim EmailHTML As MSHTML.HTMLDocument
Dim i As Long
Dim NumberOfParticipants
Dim EventID As String
Dim strEventID As String
Dim strEventID_ As String
Dim Participant As String
Set EmailHTML = New MSHTML.HTMLDocument
EmailHTML.Body.innerHTML = InboxItem.HTMLBody
EventID = EmailHTML.getElementById("eventid").innerText


    strEventID_ = DLookup("[available_places]", "tbl_event", "[event_id] = " & EventID & "")
    strEventID = DCount("*", "[tbl_enrolment]", "[event_id] = '" & EventID & "'")
    


NumberOfParticipants = 6 'Set this equal to the number of participants
For i = 1 To NumberOfParticipants
    Participant = EmailHTML.getElementById("p" & i).innerText 'concatenate i to "p" to get p1, p2, etc
    If Participant <> " " And Participant <> vbNullString And Participant <> "enter text" Then
        
         If strEventID >= strEventID_ Then
    MsgBox "Ok"
    Else
    MsgBox "not OK"
    Exit Sub
    End If
        
    CurrentDb.Execute "INSERT INTO tbl_enrolment (event_id,participant_name) Values( '" & EventID & "','" & Participant & "')"

    Debug.Print strEventID
    Debug.Print strEventID_
    
    End If
Next i


EmailHTML.Close
Set EmailHTML = Nothing

ExtractParticipants_Exit:
    Exit Sub

ExtractParticipants_Err:
    If Err.Number = 91 Then
        MsgBox "The email table is missing something"
         Resume ExtractParticipants_Exit
    Else
        MsgBox Error$
        Resume ExtractParticipants_Exit
    End If
End Sub

greetings in the new year

 

[sneuberg]

It looks like strEventID_ needs to be kept current within the loop. Rather than put the DCount in the loop maybe you could just decrement it by 1 for each one added.

 

[megatronixs]

Hi,

I changed it to this, and now it works.
It was looping fine, taking the first (I had left only 4 users enrolled in the tabel to test it with having the available places to 5), when it looped the second user, the "strEventID" would be 5 and as it was less or equal, it would still take the second user. when I only used less than (<), it would let the first user enrol, but not the second one as this in fact would be the 6th.

I just wonder how to compose the email when only one user from the five was enrolled.

Sub ExtractParticipants(InboxItem As Outlook.MailItem)
On Error GoTo ExtractParticipants_Err

Dim EmailHTML As MSHTML.HTMLDocument
Dim i As Long
Dim NumberOfParticipants
Dim EventID As String
Dim strEventID As String
Dim strEventID_ As String
Dim Participant As String
Set EmailHTML = New MSHTML.HTMLDocument
EmailHTML.Body.innerHTML = InboxItem.HTMLBody
EventID = EmailHTML.getElementById("eventid").innerText

NumberOfParticipants = 6 'Set this equal to the number of participants
For i = 1 To NumberOfParticipants
    strEventID_ = DLookup("[available_places]", "tbl_event", "[event_id] = " & EventID & "")
    strEventID = DCount("*", "[tbl_enrolment]", "[event_id] = '" & EventID & "'")
    Participant = EmailHTML.getElementById("p" & i).innerText 'concatenate i to "p" to get p1, p2, etc
    If Participant <> " " And Participant <> vbNullString And Participant <> "enter text" Then
        
         If strEventID < strEventID_ Then
    MsgBox "OK"
    Else
    MsgBox "not OK"
    Exit Sub
    End If
        
    CurrentDb.Execute "INSERT INTO tbl_enrolment (event_id,participant_name) Values( '" & EventID & "','" & Participant & "')"
    
    End If
Next i

EmailHTML.Close
Set EmailHTML = Nothing

ExtractParticipants_Exit:
    Exit Sub

ExtractParticipants_Err:
    If Err.Number = 91 Then
        MsgBox "The email table is missing something"
         Resume ExtractParticipants_Exit
    Else
        MsgBox Error$
        Resume ExtractParticipants_Exit
    End If
End Sub

Greetings.