System Description of Admini2017 2020 April 27.
What is
new in Admini2017:
2020: You can see on the front page,
if your files in C:\admini are older than the files in the cloud.
That is, you can see if another has uploaded
to the cloud e.g. by use of OneDrive or Dropbox.
tblEmailTexts has been moved to a new database:
YourName_MyAdminiData.accdb.
Different people therefore can use different e-mail texts.
When you change
data on some of the forms, it will be recorded with your initial in tblJournalSignatureDate. You can open the table from frmFind.
All users must have Admini on their computer.
The Cloud is only used to exchange the files between the users.
You must arrange with the other users, who
administrate the club at a given time.
When the job is done, you send the files to
the Cloud. Another can then download the files and take over the
administration.
2017: The system 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 0112.
You cannot use this program with the “Betalings Service Basic”.
If more people administrate the club, then everybody must have the system on their computer. The files are shared through a cloud. The system is not a netbased system, you do not directly change the files in the cloud. You change the files on your computer. You send the files to the cloud, after you have finished your job.
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: www.Nets.eu where you can get information about the rules for the files to and from BS. It might sometimes be easier to search on Google.
System
requirements:
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.
It is recommended, that you use the English version of Access, there might be some problems, if you use the Danish version.
(If you use a web-based mail program, e.g. Gmail, then you might make some changes in the setup of outlook and your mail program. Google can normally answer how to do it. This is not an Admini issue, but an outlook and mail account issue)
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
C:\admini
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 content.
AdminiStatistics.xlsm uses the R1C1 style, both columns and rows are labelled numerically, instead of A1 style where columns are labelled alphabetically, and rows are labelled numerically. You can go back to A1 style by: Open Excel, then click <Files><Options><Formulas><Working with Formulas>
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>)
Subscription
Rates:
Open the table tblSubscriptionRates, that contains subscription rates, membertypes etc. in your organisation. Further information: see tblSubcriptionRates below.
Email texts:
Open frmEmailTexts and fill in the fields. Notice the most e-mails do not use all fields.
BackUp
Drives:
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” ( www.danskestudentersroklub.dk ), 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.xlsm.
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 Access2007 or earlier versions of Access.
Standards:
Admini2017 uses BS “Betalings Service Total” standard 0112, 2015 June 01.
BS stopped using
section 0110 from April 2009,
BS section no. 0112 is used in version Admini2017.
You cannot use “Betalings Service Basic”
Information about standards on: www.nets.eu , or ask Google.
The character code used in the file to BS
is the normal windows default code.
The old admini95 system, used the dos code 850 in the files to BS.
Overview
over the system:
The system has the following Databases and Tables:
AdminiPrograms2017.accdb:
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, a file dsr_dif.txt is created, that can be read into dBase (DBXL).
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.
Members.accdb
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.
tblJournalSignatureDate in Members.accdb.
When you
change in the fields on some forms, it will be recorded in tblJournalSignatureDate
with you initials and the date. It is because, if more people use the databases,
then it might be possible to correct errors.
Oldmemb.accdb
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.
Now tblOldmembers.accdb contains removed members, whose names and addresses have been anonymized because of GDPR. That is, the removed members can be used in the statistic programs, but they cannot be identified.
It contains tblEmailTexts that contains texts used on e-mails, that are created on the forms.
AdminiPrograms2017.accdb links to tblEmailTexts in the database YourName_MyAdminiData.accdb
Different people can use different text, by linking to a tblEmailTexts in their own YourName_MyAdminiData.accdb. Remark: You cannot use seek in a linked table; you must use find.
Sportdat.accdb
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.
Sportsdata is now taken from tblSportDataRoprotokol, which is taken from the excel sheet:
RettighederFraRoprotokol.xlsx.
Oldsportdata is taken from tblOldMembersSportdata together with tblOldMembers.
tblOldMembersSportdata and tblOldmembers is now in the database Oldmemb.accdb.
Sportdat.accdb also contains tblSpecialMemb that contains fields that might be used to distinct special selected members, but normally you use groups instead.
RettighederFraRoprotokol.xlsx
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 tblSportDataRoprotokol,but are in the old tblOldMemberSportdata, which is in the database Oldmemb.accdb.
Sportsta.accdb
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.
Oldinvoi.accdb
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.
Frompbs.accdb
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 recorded 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 used anymore.
Groups.accdb:
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.
Forms:
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:
frmFind
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.
frmJoin
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.
frmPreJoining
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.
frmRemove
It is used to remove members form tblMembers. Click help on the form. It also contains some extra forms and help forms.
frmSportDataRoprotokol
Is used to read data from tblSportdatRoprotokol, it is a linked table to RettighederFraRoprotokol.xlsx, which is downloaded by the internet from the RoProtokol
frmSportData
It is the old form to read sportsdata. You cannot open it from the frontpage in this version of Admini. The frmSportDataRoprotokol replaces frmSportData.
frmSportData can be 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.
tblSportsDataRoprotokol from RettighederFraRoprotokol.xlsx is now used
instead of tblMembersSportData, but the old table is
not deleted.
frmSportStatistics
It contains data from different sport events e.g. rowing and running times
frmSubscription
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.
frmNewsletter
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.
frmGroups:
You can make a group and put some people into it, e.g. SkiTour98, see the help page on frmGroups
frmSetMarkers:
Used to set markers in tblMembers, see the help page on frmSetMarkers
frmEmailTexts
Is used to write e-mailtexts that is used on emails created on e.g. frmRemove
The e-mail texts are in the table tblEmailTexts, that is in the database MyName_MyAdminiData.accdb. Different people can have different tblEmailTexts.
You must yourself link tblEmailText from your MyName_MyAdminiData.accdb to AdminiPrograms2017.accdb
frmConvert_DBXL_ACCESS
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, except the statistic programs, that is in
AdminiStatistics.xlsm and Admini_Sql_Statistics.xlcm.
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)
Or
the field SendInvoiceExtraordinary = Yes = -1
even if the removedate is not null, that is if he/she is removed from the
club.
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.
Registering
Payments:
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 or frmPrejoning with extra forms. Use the associated help page on the form. The forms 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.
Remove Members:
Use frmRemove with extra forms. Use the associated help page on the form.
Newsletter:
Use frmNewsletter.
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:
rptNewsletterStart
rptNewsletterStartDoc
rptNewsletterStop
rptNewsletterStopDoc
rptNewsletterChange
rptNewsletterChangeDoc
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.
Sport
Statistics:
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.
Manual InpaymentForm.DOCX:
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.XLSM
The workbook takes data from Members.accdb and OldMemb.accdb, it is independent of Admini2017.accdb.
AdminiStatistics.xlsm 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.xlsm 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.
You can have 99.999.999 MemberID in the
database. The max MemberID is 99.999.999
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.
Error
trapping:
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-2020, but it is freeware for non-commercial use.
If you find a program error, then please mail me.
Ole Hansen
E-mail: Admini@Hansen25.dk
Web: http://www.hansen25.dk/Admini
2020 April 27.