System Description of Admini2017 2017 Oct. 11:
What is new in Admini2017:
It has been upgraded to Access2017.
All databases are now .accdb databases.
Use of The Cloud as backup is also new. You write the cloud address in frmAccountNumbers
What can you use the system to?
The system can be used to administrate members in an organization. You can charge the members for subscription by use of direct debit via the Danish Betalings Service (Nets).
The System uses “Betalings Service Total” Standard. Admini2017 and earlier versions of Admini cannot use the “Betalings Service Basic” standard.
BS stopped using section 0110 from April 2009,
BS section no. 0112 is used in version Admini2017.
There is not yet any standard for Direct
Debit Systems, so you cannot use the
system on other direct debit systems without first rewriting the form frmSubscription.
Information about BS: , where you can get information about the rules for the files to and from BS.
Admini2017 is running on Access2017, all files are now .accdb If you want to send e-mails direct from Admini, then you also must have MS Outlook installed. MS Word and MS EXCEL are also called from some subroutines. However, you can use Admini2017 without MS Outlook, MS Excel and MS Word
The programs are in the database AdminiPrograms2017.accdb, from this you get access to the other databases by links; therefore, first open the database AdminiPrograms2017.accdb.
Installation and Adjusting of the System:
You must install the system in the folder
You cannot use another folder, because some programs refer to files in that folder and relations between the tables also refer to that folder.
The folders C:\admini\StandardLetters, that contains standarletters, is created when you unpack Admini2017.zip. When you use the program, the backup folder C:\admini\BackUp and archive folders are created first time you backup files in Admini2017
First time you open the program, the program will inform you, if there is something wrong with your references. If there is something wrong with the references then:
Go to Code View, Click: <View> | <Design View>
Then Click: <View> | <Code View>
In Code View click: <Tools> | <References>
Find references with the word MISSING.
e.g.: MISSING: Microsoft Word 16.0 object Library
Try to find another reference. If e.g. you do not have a reference to Word 16, but you have a reference to Word 14, then uncheck Word 16 and check Word 14.
If you do not have any references to e.g. Outlook, then uncheck it, but then you will not be able to send e-mails from Admini.
If a reference is set to a program, that does not exist, then all references after is cancelled. By release of Admini the sequence of the references was: MS Access, MS Word, MS Excel, MS Outlook, that means, you cannot call MS Outlook, if you do not have MS Excel installed, you therefore have to uncheck MS Excel in the reference. References used in the program are:
VBA, MS Access 16.0 Object library, OLE Automation, VBA Extensibility 5.3, MS Active X Data object 2.1 Library, MS Scripting Runtime, MS Word 16.0, MS Excel 16.0, MS Outlook 16.0.
Excel uses Macros (it contains VBA procedures); you therefore cannot use it, if you do not enable the active conent.
AdminiStatistics.xls uses the R1C1 style, both columns and rows are labeled numerically, instead of A1 style where columns are labeled alphabetically, and rows are labeled numerically. You can go back to A1 style by: Open Excel, then click <Files><Options><Formulas><Working with Fromulas>
Adjust your account data, organisation name etc.:
Account numbers etc.:
Open the form frmAccountNumbers, here you fill in your data, they will be saved in the table tblAccountNumbers. The program that makes the invoices takes the account data from tblAccoutNumbers. (You can instead choose, to write the account numbers in the programs: <Make Invoice Normal> and <Make the Invoice Reminder>)
Open the table tblSubscriptionRates, that contains subscription rates, membertypes etc. in your organisation. Further information: see tblSubcriptionRates below.
Open frmEmailTexts and fill in the fields. Notice the most e-mails do not use all fields.
Open frmAccountNumbers, fill in the 2 drives you want to use as backup to your files in Admini.
Normally you will use one harddisk and a USB stick or a formatted CD-RW as backup.
About this Member Administration System, “Admini”:
The system is used in the rowing-club “Danske Studenters Roklub” ( ), it was originally written in the DOS system DBXL (a dBase clone) year 1989, the system was in 1996 replaced with an ACCESS 2 system, it was put on the Web spring 1997. The system was upgraded to ACCESS 95 spring 1998. The system was in 2001 upgraded to ACCESS2000, all programs were changed from DAO to ADO. Reading and writing to files is done by use of the VB File System Object Model (FSO). E-mails can be automatically created via MS Outlook.
Fall 2005: A Join Statistic was added to the excel program AdminiStatistics.xls.
E-mails text was saved in a table, which can be edited on a form. In earlier versions the text was in the program code. Most fields in the tables have a Violation Rule, so that it is not possible to have multiple lines, this means you cannot append records with multiple lines to tables in this version of Admini; exceptions are memofields and the field E-mailText1 in tblMembers.
Spring 2006. Invoices can now be written in two languages, English and Danish. A maximum charge field has been added to frmAccountNumbers. That means no invoice can be created if the amount to charge is greater than the maxCharge. All amounts are now in double variables, so there is no limits caused by use of long integer variables any more.
Version Admini2007 uses PBS section no. 0112, that has 5 address lines and uses a country code.
All older versions are now obsolete, because they used section no. 0100 or section no. 0110. PBS has now stopped using those versions.
This version AdminiPrograms2017.accdb is upgraded to Access2017, all databases are now .accdb.
Backup are now in the cloud instead of floppy drive a:
Some bugs have been removed.
The different ACCESS versions:
All databases are in this version .accdb Access2017 databases, this means, you cannot open the databases in Access20007 or earlier versions of Access.
Admini2016 uses BS “Betalings Service Total” standard 0112, 2015 June 01.
You cannot use “Betalings Service Basic”
Information about standards on: ( www.nets.dk )
The character code used in the file to BS is the normal windows default code.
The old admini95 system, used the dos code 850 on the files to BS.
Overview over the system:
The system has the following Databases and Tables:
It contains nearly all the programs and the following tables:
tblAccountNumbers contains all the account numbers, headline on the invoices and some texts that is written on the invoices. You can decide what you want to call a reminder and subscription.
You can also decide if you want information if a member has paid less than a given amount. Normally it means that one wants to remove from the club.
The cloud addresss on your computer is also in tblAccountNumbers, use frmAccountNumbers to change in tblAccountNumbers
tblSubscriptionRates contains all subscription rates, which month to charge, the different membertypes and the subscription text to write on the invoices. E.g. “kontingent aktive”.
tblLogbook contains information about the last date people was put on and off the newsletter mailing list, as well as the last date welcome letters was sent.
tblPostnr contains the Danish post numbers (post zip codes).
tblDSR_DIF_mdb is used to convert from the database tblMembers to the old systems dBase database dsr_dif.dbf, af file dsr_dif.txt is created, that can be read into dBase (DBXL).
tblEmailTexts contains texts used on e-mails, that are created on the forms.
tblRowClubs contains snail mail newsletter addresses to people, that is not member of the club, use memeberId’s that is e.g. 100 times greater than the normal members.
Linked tables: Tables are linked to the database AdminiPrograms2017 from the databases below.
It contains the tblMembers, which contains the members' names, addresses and information about subscription and newsletter related information.
It also contains the tblJoining, which contains data from members, that has sent an e-mail with enrolment data e.g. Names, addresses and other information. The frmPreJoining is used to transfer data from the e-mail to tblJoining, frmPreJoining is again used to transfer data from tblJoining to tblMembers after the new member has paid the entrance fee.
It also contains tblRowClubs, that can be used to record addresses, to which you want e.g. to sent newsletters, but they are not members of the club.
It contains tblOldmembers, which contains the same information as tblMembers, but for people removed from the club some years ago. It was made because the old floppy disk only had 1.44 MB, so you could not have the members and all removed members on one floppy disk.
It contains tblMembersSportData that contains sport information about the members that are in tblMembers. tblOldMembersSportData contains sport information about the members that is in tblOldmembers.
Sportdat.accdb also contains tblSpecialMemb that contains fields that might be used to distinct special selected members, but normally you use groups instead.
It contains tblSportDataRoprotokol, (Roprotokol =RowRecord), that contains sport information about the members who are in tblMembers. Use frmSportDataRoprotokol to read the data, you cannot change data on the form. You download the file RettighederFraRoprotokol.xlsx via the internet. You must log in to the rowprotokol if you want to change data. People in tblOldMembers are not in blSportDataRoprotokol.
It contains tblSportstatistics that is used to contain sport statistics e.g. running and rowing times for different distances, it also contains tblOldSportstatistics, that contains data deleted from tblSportstatistics, see the help on frmSportstatistics. You can open the form from frmSportdata.
It contains tblOldinvoices that contains the data that has been printed on the invoices, that is, a new record is generated for each member, each time an invoice is sent. All personal data such as subscription, surcharge and text lines e.g. “you have been removed by 1 Jan 97 ……” is stored in tblOldinvoices.
It contains tblFromPbs that contains information from the file with the payments from BS. (Nets). You can see the pay date, how it was paid etc. for each payment made by each member.
Payments made when joining the club and payments registered by use of frmFind and frmRemove is also recorded in tblFromPBS.
Frompbs.accdb also contains tblFromPbsRemoved to which payments less that the amount RemovePay in tblAccountNumbers (from frmAccountNumbers) are appended. It might be deleted in next version. It is not use anymore.
It contains tblGroupNumber that contains all the group numbers and their names etc., but not the MemberID of the people that is member of the group. The MemberIDs etc are in tblGroupMembers. You can get further information on help on the frmGroups.
It contains tblCprNo that contains cpr. numbers (Social Security Numbers). The database must be encrypted because the numbers are confidential.
It is used to special purposes, e.g. special statistics. This database is not included in the Internet version.
The forms and modules contain the most programs in this Access System.
The database AdminiPrograms2017.accdb contains some forms, some of them are written below:
It is used to find members in the database tblMembers and tblOldmembers. You can click the help button on the form to get information about how to use the form. The form has two extra forms and two help forms. One of the extra forms is used to search in the database when the name etc. is known but the MemberID is not.
It is used to join new members to tblMembers. It has an extra form and some help forms. Click on the forms help to get information about how to use the form.
It is used to transfer data from the e-mail to tblJoining. frmPreJoining is again used to transfer data from tblJoining to tblMembers after the new member has paid the entrance fee.
It is used to remove members form tblMembers. Click help on the form. It also contains some extra forms and help forms.
It is used to write and read data in tblMembersSportData and tblOldMembersSportData, it contains some extra forms and help forms. The form is normally used when one wants to find a member address etc. Records is added when the form is activated, there will be created records in tblMembersSportData for MemberID’s greater than the highest MemberID in tblMembersSportData and less than or equal to the highest MemberID in tblMembers if the MemberID exist in tblMembers. If you choose not to use some numbers less than the highest MemberID in tblMembers, then they will not be added to tblMembersSportData if you later choose to use the numbers, therefore add MemberID’s sequential, the frmJoin does it.
Is used to read data from tblSportdatRoprotokol it is a linked table to RettighederFraRoprotokol.xlsx which is downloaded by the internet from the RoProtokol
It contains data from different sport events e.g. rowing and running times
It is used to add new subscription to the members in tblMembers and to read the file from BS (Nets) and to make the file that is sent to BS. All necessary information should be on the form and its extra forms.
You can choose between two solutions: you can put people on and off the mailing list for the newsletter by use of four reports, or you can choose to send a file to the post each time you send a new newsletter. Bladkompagniet is now used instead of the Post. If Bladkompagniet cannot deliver the newsletter, then the field NewsLetterReceives is set to No.
The fields Bladk_outside, Bladk_locked, Bladk_postbox is used to decide the text in the e-mail, that is send to members that cannot receive the newsletter.
You can make a group and put some people into it, e.g. SkiTour98, see the help page on frmGroups
Used to set markers in tblMembers, see the help page on frmSetMarkersfrmEmailTexts
Is used to write e-mailtexts that is used on emails created on e.g. frmRemove
It is used to convert from tblMembers to DBXL (dBase), because some old programs are still in use. The program is special designed to those databases and cannot be used directly with other databases.
General Description of the system:
The system is a Direct Debit System.
All programs are in the database AdminiPrograms2017.accdb.
PBS = Pengeinstitutternes Betalings Service BS = Betalings Service
The following tables are used to money collection via BS (Betalings Service).
tblAccountNumbers in AdminiPrograms2017.accdb, it contains account numbers, PBS-nr, CVR-nr etc., see frmAccountNumbers for further information.
tblMembers in the database Members.accdb, it contains the member’s data: Names, addresses, debt etc.
tblSubscriptionRates in the database AdminiPrograms2017.accdb, it contains information about how much to charge the different membertypes. It also contains information about the different member types. E.g. MemberType 1 is passive, MemberType > 1 is active, but in different subscription groups. It also contains information about which month to charge the different member types. This is done by writing the word ex: ynnynnynnynn in the field Paymonths, which means that subscription is added to the field subscription in the table tblMembers for payment in January, April, July, and October but not in any other month.
tblOldInvoices in the database OldInvoi.accdb. For each invoice that is made, the data, that is not the same on every invoice, is appended to the tblOldInvoices. It is then possible to go back and see what was sent.
tblFromPBS in the database FromPBS.accdb. BS delivers files with the payments. All the most important data from those files are stored in tblFromPBS. It is then possible to see, what and when the different members have paid. In tblMembers is only recorded the balance, not all the different payments, but they are all recorded in tblFromPBS. You can open the tblFromPBS, then you can index on the MemberID and then move down to the wanted MemberID and see all the payments, the member has paid.
Who gets Invoices etc?
Addition of New Subscription:
New subscription must be added to the field Subscription in tblMembers before new invoices can be made. The subscription is added, only if the fields in tblMembers are:
AddSubscription = Yes = -1
RemoveDate = Null (that is, nothing is standing in the field)
It is also necessary that the subscription for the MemberType must be added for that pay month. Before, addition of normal subscription (not reminder) the amount in the field subscription is added to the field OldBalance and the field Subscription is set to 0.
Addition of Surcharge:
Surcharge is added, when you make reminders, to all members living in Denmark who has a debt over the debtlimit and the field ReminderTextSurcharge = Yes = -1.
Surcharge is not added to people living abroad. All people that is living abroad has postnr “0000”
Surcharge is also not added to people who has the field ReminderTextSurcharge = No = 0 in tblMembers. That means no surcharge is added and the remindertext “Rykker” is not written on the invoices.
The Invoices are sent to:
The invoice is sent to a member if
the field SendInvoice = Yes = -1 and
the field RemoveDate = Null (that is nothing is standing in the field)
the field SendInvoiceExtraordinary = Yes = -1
even if the removedate is not null, that is if he/she is removed from the
The field SendInvoiceExtraordinary is set to No after the invoice has been sent.
The invoice is not sent if the debt is smaller than the minCharge or greater than maxCharge, both are set on frmAccount.
Remarks: The removeprogram automatically sets AddSubscription = No, SendInvoices = No and fill in the RemoveDate. You therefore have to set SendInvoiceExtraordinary = Yes if you want to send a removed member an invoice.
A member living abroad, who has got an invoice by the last normal sending, will not get an invoice sent during the reminder sending. If he/she did not get an invoice during the normal sending, then he/she will get an invoice by the remindersending, but the remindertext “Rykker” will not stay on the invoice. This is regulated by the field SendAbroad in tblMembers. It will be set to No after a normal invoice sending, it is then set to Yes during the next reminder sending, nothing will be sent abroad if the flag is No = 0.
The file from BS is read into the computer. The payments are subtracted from the field Subscription. If the amount is a withdrawal “tilbagefoering” then the amount is not added see below.
Remark: for all fields a positive number means that the member is in debt to the club, a negative number means that he has paid too much.
Refuse to Pay:
A member, that pays by use of BS’s direct debit system, can refuse to pay an invoice. Normally this happen before the money is transferred. Then nothing will happen, except that it will be recorded in tblFromPBS with the text “AFVIST”, but sometimes the money has been transferred, this is recorded in tblFromPBS as a negative number, nothing is registered in the field subscription in tblMembers. The money is normally taken in and out of the bank account. You receive a letter from PBS when it happens.
Redrawn FI-betaling (payment by use of the inpaymentforms): The member is debited the amount. The amount is subtracted from the field Subscription in tblMembers. The amount is saved in the file FromPBS with a minus sign. The same is done to FromPbs.txt. Normally the amount has first been paid and later redrawn
How to do it?
The form frmSubscription with extra forms is used. Open the form by clicking it or by using the menu frmMenu. The text on the form and the help page associated to the form should be enough information.
Join New Members:
Use frmJoin with extra forms. Use the associated help page on the form. The form calculates the subscription that the new member shall have credited next quarter when he/she enrols in the middle of a quarter. It also sets the different flags e.g. SendInvoice = Yes.
Use frmRemove with extra forms. Use the associated help page on the form.
You can send all addresses to the post or Bladkompagniet each time you send a newsletter. It is used, when the post does not have a database of the members. See frmNewsletter for further information.
You can still use the old way:
Six Reports are used:
The reports are called by clicking on the buttons on the frmNewsletter.
The last date the report was used, is stored in tblLogbook.
The newsletter Reports use a SQL sentence original created by use of queries, they are stored in the subroutines Report_Open in the report. NewsletterStart: all members who has a JoinJournalDate after last time the report was used will come on the list, people who has the field NewsletterStart = Yes will also come on the list, even if they have a JoinJournaldate before last time the report was used. People with the MemberType = 6, children of members, will not be put on the mailing list.
NewsletterStop: all members who has a RemoveJournalDate after last time the report was used will come on the list, people who has the field NewsletterStop = Yes, will also come on the list even if they have a RemoveJournalDate before last time the report was used.
NewsletterChange: People will come on the list, if the field NewsletterChange = Yes = -1. This is used when a member changes name or address,
When a member is put on rptNewsletterStart then ReceiveNewsletter is set to Yes. When he/she is put on rptNewsletterStop then ReceiveNewsletter is set to No. A member will not be put on the list rptNewsletterStop if ReceiveNewsletter = No. If the post takes a member off the mailinglist, then you must change ReceiveNewsletter to No.
If Bladkompagniet cannot deliver the newsletter, then the field NewsLetterReceives is set to No.
The fields Bladk_outside, Bladk_locked, Bladk_Postbox is used to decide the text in the e-mail, that is send to members that cannot receive the newsletter.
frmSportData uses the tblMembers in the database Members.accdb and tblMembersSportdata in the database Sportdat.accdb as well as tblOldmembers and tblOldmembersSportdata. The addresses etc. are taken from tblMembers, the sport data from tblMembersSportdata.
No data in tblMembers can be changed on the frmSportData. Use the associated help page on the form for more information.
FrmSportDataRoprotokol takes sportdata from tblSportDataRoprotokol.
All new members are in tblSportDataRoprotokol, but members in tblOldMembers are not.
Uses the tblSportStatistics and the tblOldSportStatistics both in the database Sportsta.accdb.
tblSportstatistics is used to store sport data from different competitions. When the date becomes too old they can be deleted and instead stored in tblOldSportStatistics, the information from this table can be used for different statistical analyses. You can use EXCELL to analyse the data, but no program etc. has been made in Admini to analyse data.
How to use it? See the text on the frmSportStatistics and its associated help page.
On some forms, there is buttons to e-mail. First open Outlook, and let it be running on the taskbar. If you click on an e-mail button, then a new e-mail is created. You must customize the e-mail texts associated to those buttons to your address, name etc. on frmEmailTexts. You can send Some standard letters as e-mail, data are merged with tblMembers, you can call them on frmLetters, which you can open from frmFind.
The field “Control”:
You can use the field “Control” to anything, e.g. if you want to see a member record before you add new subscription, then you e.g. can set it to 5 (it is set to 1 when you join a new member), the reason can e.g. be, that he has changed address or has unknown address. You can then use the button <special> on the form frmFind to move through all members with control = the wanted value.
This is a WORD document, that can be used to send manual in payment forms, it merges data from tblMembers, you must adjust it to the actual form and you printer, it might be used, when you do not want to use BetalingsService.
The Excel WorkBook AdminiStatistics.XLSX
The workbook takes data from Members.accdb and OldMemb.accdb, it is independent of Admini2017.accdb.
AdminiStatistics.xlsx is called from frmStatistics. You can use it to make charts over the age distribution of the members.
See also the Excel paragraph in “Installation and Adjusting of the System” above.
Excel.xlsx cannot call data from an encrypted database, therefore if you use an encrypted database, you must have two admini2017.accdb installed. One non- encrypted that you use when you use the Excel programs, and one encrypted that you use when you e.g. use cpr.nr. (social security numbers), you can e.g. call the first admini2017.accdb and the other admini2017_password.accdb.
The OCR LINE:
The first number in the bottom line of the invoices.
BetalingsService creates the number in this version of Admini2017.accdb.
How to Try the System:
The Internet version include test databases, they are the databases used in DSR, but with all members erased, instead there has been put some test data, that is some non-existing people, streets etc.
The Internet version has about 30 members, but you can add as many as you want, only limited by ACCESS 2017 limits. (DSR’s system has over 9000 of which about 1000 are still members, the rest has been removed)
Included in the system is two files testdata_1.txt and testdata_2.txt they contain data as they come from BS, so you can use them to test how you read data from BS. Click: frmSubscription | <Read File from PBS> or open the menu that opens when you start the system.
The file testdata_1.txt :
All payments have transcode 297, that means that they are paid by use of an in payment form, not by use of direct debit of the members bank account. The data on the file is:
Kundenr.: 83 117 133 190 208 221 232 240
Amount 540 540 540 3 590 540 540 565
Remarks to testdata_1.txt:
MemberID 19 has paid 3 (DDK). probably he wants to remove from the club, because you can remove from the club by paying 3 DDK. If you have checked the checkboxes, on frmAccountNumbers then you will get a message and the payment will be appended to tblFromPbsRemoved.
MemberID 20 does not exist in tblMembers. MemberID 20 is in tblOldMembers, you will get an error message.
MemberID 22 should have kundenr (customer number) = 224, because 4 is a modulus 10 error check digit, but it is 221, that is you will get an error message. Nothing will be subtracted from the fields in the database tblMembers, but the payment will be registered in tblFromPbs for MemberID = 22.
The file testdata_2.txt :
The payments have been transferred by direct debit on the member’s bank account
The data on the file is:
Kundenr.: 83 117 208 221 232
Amount 565 324 590 565 0
Transcode: 236 236 236 237 238
Remarks to testdata_2.txt:
Transcode 236 means normal transfer of the money.
MemberID 20 does not exist in tblMembers, as above you will get an error message.
MemberID 22 has transcode 237, that means that the payment has been refused, but as seen the amount is not zero but 565 that means he has refused too late, the money has been transferred but will be transferred back to PBS. Nothing will be subtracted from the fields in the database tblMembers, but the payment will be registered in tblFromPbs for MemberID = 22 with a minus sign. There will be written “Afvist” in tblFromPbs for MemberID = 22.
MemberID 23 has transcode 238 that means he has cancelled automatic direct debit on his bank account, he has paid nothing. There will be written “Afmeldt” in tblFromPbs for MemberID = 23.
It is recommended to use Access in the mode: Break on unhandled Errors. In VBA choose:
tools | option | general | Break on unhandled Errors. It was so when released.
The System is copyrighted 1996-2017, but it is freeware for non-commercial use.
If you find a program error, then please mail me.
2017 October 11