SUBJECT: Form Combo Box Causes Query Error Message

[dlhappy]

I have attached a test version (only a few test records and various forums, queries, etc. deleted) of the database causing a problem.

I'm sorry for such a long post; however, it is the only way I know to adequately explain the problem.

Using Access 2007, I created two similar combo boxes, using the control
wizard for each form, in design view, one for each of two separate databases.

For one database it presents no problem at all. But for the other, when I
create a query, initially the query displays during the creation process.
However, every time I try to re-open the query, it re-opens and displays the correct data, BUT only after I close an error message box that always pops up. The error message says:

"An invalid condition was encountered by Access due to a value that you
entered for a field or table property. Review the table for any changes
which might be out of range or are not supported by the table. Examine any changes that you may have made to the table and, if possible, revert back to a previous copy of either the database or the table to determine the differences in your settings."

I have no idea why this is happening. As for values, the field in each
database are simple text fields. I made no changes other that adding the
combo box to each database. Yet, one database never displays the error
message, and the other one always displays it.

The combo box for the database that causes no problems is for a "FROM" field on a datasheet form that I use for posting. It has, as does the other
database, one module that adds a date to an edit date field, if the record is changed. Btw, I don't know VBA programming -- the code was given to me for the module.

In the database that has no problem, I have a separate table named "from" that has all of the possible text entries that comprise the drop down list for selecting an entry for the from field on the form. The main table, has the from field designated as a text field. The "from" table also has the
"from" field designated as a text field.

In the database that causes the problem, I have a separate table named "type" It has one field, named type (a text field), and three records (single, family, lifetime) to indicate membership categories. (Actually I don't really need a combo box to allow me to type only the first letter of each category on the 2010-type field (a text field) on the form. The form is a datasheet form created from the main table. (The form -- and main table [Membership] -- have many fields, such as name, address line 1, state, status, email address, telephone, and for each year, three fields, such as 2010-date, 2010-type, 2010-paid.
Incidentally, I'm keeping this membership database as a volunteer for a non-profit membership organization. I set up the combo box and the "type" table because I wanted to see how a combo box works -- so, I set up a combo box for each database (the other database is an inventory of my digital disks (DVDs, CD, etc. -- the from Combo box lists all of the original sources that my burned CDs & DVDs were made from, such as MP3, AVI, MPG, FLAC, FLAC+Cue Sheet, etc.)

For both databases, the combo boxes work fine...it is only when I run a test by creating a query from the main table (Membership) and only selecting the field (2010-Type) bound by the combo box that I have a problem with the one database -- that is, the error message pops up, as described above. (The property box for cbo2010-Type says Column Bound: 1 -- so I'm assuming that it's bound.....I say assuming, because I lost track, but I used the wizard to create the combo box, deleted it, ran the wizard again, etc. Most times I think it said bound on the design view and I think once or twice said unbound).. Incidentally, if this problem can be resolved, I want to create more combo boxes for 2011-Type, 2012-Type, etc. with all of the combo boxes on the forum getting its listing from the Type table (and the type table should have a single field, called type). Also, a null value (data not required for 2010-Type field on the Membership table because there are some years that members don't renew, then come back in a following year, etc. -- or the field is blank on a members records, because he/she has not renewed yet).

I've made changes to property sheets, etc. but nothing seems to work to get rid of the error message pop-up box. (I renamed the "Type" Table to "2010-Type" to agree same field name in main table and even tried using that field as a primary key in each -- that would not be good -- but I tried it just to see if helped with the problem -- it didn't).

Any suggestions how to remedy this situation would be appreciated, or even advice as to why this should always happen with the one database and never with the other..

Thanks.

 

[vbaInet]

Welcome to AWF!

Very detailed explanation. Where to start

Could you please cut-down and mention in a few lines what the problem is?

 

[dlhappy]

Welcome to AWF!

Very detailed explanation. Where to start

Could you please cut-down and mention in a few lines what the problem is?

Hi vbaInet:

The problem is that since I added the combo box, when I create a query, I get the above error message for the field that the combo box is related to.

Please open the attached sample datbase (in the zip file) and click on this query: TEST-Membership Query-2010-TYPE. When the error message comes up, click on it's help button and you'll see the stated error (detailed in my first post). Close the error box and the query opens and shows the correct data. Can you check the properties, etc. and see what has to be done so that the error message box does not pop-up.

Thanks.

 

[vbaInet]

Your database isn't at all normalized and the Membership table has no primary key. It seems you don't understand the concepts of relational databases but you do know about Access controls. Access goes beyond just knowing how to create controls and setting their control sources. My advise would be to normalize your db and set the appropriate Primary Keys/Foreign Keys.

Here are tutorials:
Access 2003
Access 2007
Access 2007

 

[dlhappy]

Hi vbaInet:

I revised both tables, in the main table, Membership, I used autonumber (ID) because that is the only field that can't have duplicates.

In the Type (that is the type of membership), I already had the single field in that table, namely "Type" already designated as the primary key for that table. I then established the relationship as follows: Type field from the Type table, related to 2010-Type in the Membership table. Eventually I would add more combo boxes to the form and have a one to many relationship, that is Type (from the Type table) related to 2010-Type, 2011-Type, 2012-Type, etc. from the membership table.

Anyway, despite establishing the relationship, I still get the error message creating a query from tblMembership, selecting only 2010-Type. Once created it opens without an error, but always has the pop-up error box just as before, every time it is re-opened for viewing.

I'm attaching the newly revised Test Database.

Do you see anything in the properties that is wrong? I can't imagine what invalid filed or condition the error box is talking about.

Please click on this query: TEST-Membership Query-2010-Type
to see the pop-up message and click "show help" for the explanation.

The DB is much smaller now since I also performed a compact/repair on it.

Again, many thanks for your help, and patience.

 

[vbaInet]

What you've done is add an ID to your Membership table and linked it up to your Type table. If initially I felt that was the solution to your problem I would have actually done that myself and posted the "solution" back to you.

You have created 3 fields (i.e. Date, Type, Paid) for each year starting from 1998 up to 2010, that on its own is a serious breach of normalization and without sorting out that part of your db the problems you're experiencing now cannot be circumvented. You need to ask yourself this, "what would my table look like in 10 years". Would it be another 30 fields?

Do some google search on normalization and relationships. You would even find information about this on the links I provided.

For starters, your Membership table's ID would be unique to each member. Rename that ID and call it MembershipID so its intuitive. Move it to the top of your table too (i.e. make it the first field in your table). You correctly made Type in your Types table the Primary Key (PK). However, do not use names like Type or Date because that's a reserved name in Access and with such names you will most certainly run into problems. Also DO NOT use spaces. If you're going to remove the spaces you would need to amend the queries, reports and forms that depend on that field.

You need a third table which will be a junction table between Type and Membership. You may call it MembersTrans and the fields could be:

MembersTrans
--------------------------
TransID (Autonumber - PK)
MemberID (Number, Foreign Key linked to Membership table's ID, MembershipID)
TransDate (Date - Default value Date())
TransType (Text, Foreign Key linked to Types table)
TransPaid (Currency - NOT Number)

For every transaction a record will be saved in that table. The year can be picked from the TransDate field when you run the query using a function called Year, so you see why you don't need a field per year in your Membership table?

 

[dlhappy]

Hi vbaInet:

First, before I go on, I want to thank you very much for taking the time not only to review my sample DB but also for the detailed instructions that you prepared.

Besides the fact that I really have to read much more to learn about the proper use of databases (up to now I have only used single table databases and prepared queries that sort on various fields, etc. --- for example my audio cassettes inventory -- by composer, by title, by conductor, by cast, by bank tape remaining, etc. and of course, each cassette has an inventory number -- which is also physically marked on each cassette so as to be able to find it -- I have approx 2,600 audiocassettes). No though I have digital (CDs, DVDs, and more recently just saved in digital formats on external drives).

There are several problem, but first let me address your last suggestion, about not having three fields for each year (date, type, paid)

While I think I understand you last suggestion, I don't think it would work in my case. Here's why: Merely posting all transactions and then using a query to extract the information by year would be fine if the payments were paid in the year to which they pertain. Unfortunately, in some cases it does not work that way. Btw, I don't receive the payments, so Date () -- the current system date is not the correct transaction date. I often receive a summary of payments received a month after their receipt but post the information retroactively. Anyway, we had one member send in a payment in 2010 and state that he forgot to renew his membership for 2009 -- so would we please apply 1/2 of his payment to each year. So, there is a 2010 payment date in the 2009-Date field. Another member who was already paid up for 2009 sent another payment in 2009 and told us to apply it to his membership for the next three years. What I do in those cases is post the payment in 2010-Paid, 2011-Paid and 2012-Paid BUT SHOW THE ACTUAL 2009 date that he paid in each of the three date fields, namely 2010-Date, 2011-Date and 2012-Date. Also, payments made in mid-November are considered to be for membership for the following calendar year. So, merely filtering by year of payment will not necessarily show the year for which it was made.

Also, manually, I was able to merely add to payments for one member to come up with a total to put in his record's 2010-Paid field. I would not otherwise know how to handle the situation if I just merely posted payments by sequentially, as in a check register. Here is what happened: member one paid his membership dues. Then, maybe a month or so later, I received information from the treasurer that member two (an overseas) member, sent in a 55 payment. Of the 55.00, 35.00 was for his own (member two's) membership. He said that he owed member one 20.00 and member one said to send it to us and to credit him, member one with an additional membership contribution for the 20.00. Member one had already paid 40.00 for his single membership (35.00 + 5.00 contribution) so all I did was change the posting form from 40.00 to 60.00 in the 2010-paid field and added an explanation in the notes (memo) field.



I took over recording the membership data a few months ago. It was always done on an Excel worksheet and I merely created a db with similar fields and then imported all of the data from Excel to Access. The people who need to see this information do not have Access and so I export from various queries (or even the membership posting form (with a count of members and total dollar amounts on the bottom row). They want to see all of the years (as in my membership table or datasheet view form) so that they can analyze members by year by merely scrolling through the exported worksheet.

I decided to use Access because I can more easily archive members (a check box-true/false yes/no field) who have not paid for two years (UNLESS the status field indicates that they are in a special category such as "Lifetime" or advisors, or complimentary) or those who are listed deliberately twice (two records)-- with one address for winter mailings and another for summer mailings, and also archive those with an invalid address, and thereby create a mailing list query, excluding such archived members. I also recently made a query and exported to Excel and emailed it to the concerned parties -- they wanted a listing of 2010 paid renewals to date -- so all I had to do was prepare a query where the 2010-Paid field was >0.

If I did not use Access and merely just used Excel, it would be much harder...I would always be resorting records, filtering, copying and pasting, and saving multiple worksheets (as opposed to having saved queries in Access -- always up to date, and ready to be exported and email at a moments notice. Also, the Access memo field is better than typing and vertically expanding a cell in Excel.

So, I'll have to, as I said, really read up about the proper way to design a db and somehow think of how I can possibly generate queries with the 3 fields for each year without having such a large table. Your solution would be great if all of the payments for a given year, were actually for that year.

Again, I want to thank you for all the time that you have taken with this.

 

[vbaInet]

Wow! You must enjoy typing or you have amazing typing skills

Hi vbaInet:

First, before I go on, I want to thank you very much for taking the time not only to review my sample DB but also for the detailed instructions that you prepared.

You're welcome

Besides the fact that I really have to read much more to learn about the proper use of databases (up to now I have only used single table databases and prepared queries that sort on various fields, etc. --- for example my audio cassettes inventory -- by composer, by title, by conductor, by cast, by bank tape remaining, etc. and of course, each cassette has an inventory number -- which is also physically marked on each cassette so as to be able to find it -- I have approx 2,600 audiocassettes). No though I have digital (CDs, DVDs, and more recently just saved in digital formats on external drives).

Easily manageable in Access. Create a table which will hold values of all types of formats - CDs, DVDs etc...

There are several problem, but first let me address your last suggestion, about not having three fields for each year (date, type, paid)

While I think I understand you last suggestion, I don't think it would work in my case. Here's why: Merely posting all transactions and then using a query to extract the information by year would be fine if the payments were paid in the year to which they pertain. Unfortunately, in some cases it does not work that way. Btw, I don't receive the payments, so Date () -- the current system date is not the correct transaction date. I often receive a summary of payments received a month after their receipt but post the information retroactively. Anyway, we had one member send in a payment in 2010 and state that he forgot to renew his membership for 2009 -- so would we please apply 1/2 of his payment to each year. So, there is a 2010 payment date in the 2009-Date field. Another member who was already paid up for 2009 sent another payment in 2009 and told us to apply it to his membership for the next three years. What I do in those cases is post the payment in 2010-Paid, 2011-Paid and 2012-Paid BUT SHOW THE ACTUAL 2009 date that he paid in each of the three date fields, namely 2010-Date, 2011-Date and 2012-Date. Also, payments made in mid-November are considered to be for membership for the following calendar year. So, merely filtering by year of payment will not necessarily show the year for which it was made.

Also, manually, I was able to merely add to payments for one member to come up with a total to put in his record's 2010-Paid field. I would not otherwise know how to handle the situation if I just merely posted payments by sequentially, as in a check register. Here is what happened: member one paid his membership dues. Then, maybe a month or so later, I received information from the treasurer that member two (an overseas) member, sent in a 55 payment. Of the 55.00, 35.00 was for his own (member two's) membership. He said that he owed member one 20.00 and member one said to send it to us and to credit him, member one with an additional membership contribution for the 20.00. Member one had already paid 40.00 for his single membership (35.00 + 5.00 contribution) so all I did was change the posting form from 40.00 to 60.00 in the 2010-paid field and added an explanation in the notes (memo) field.

Two date fields, one to hold the current date just as Default (automatically set as Date()) for when a new payment is entered and the other to hold the Date the last payment was made (manual entry). Having these two dates you can even check the difference in dates to see how long it took a customer to pay the full amount. You would also have a YES/NO field to indicate whether or not the full payment has been received. In your Memberships table you would have a date which indicates for which year the payment made is for. Validation can be done between this date and the Payment date to warn you if the payment date is after the subscription date.

These type of systems are already in place and it could be compared to the sort that Gymnasiums use.

It can be setup once you get your head round the basics so don't think your case is impossible