Need a report based on the valve of list boxes

[Uncle Gizmo]

First of all, I probably goofed. There are 3 choices. Air force, Army, and Navy. They should have been in the example but one may have gotten away.

I am trying to add the MARS list box to the tblRoster as you suggested.
When I add a list box to the frmRoster with the following, I am getting two options in the box on the form, Air Force and Navy.

Control Source RosterMARS
Row Source " ";"Air Force";"Army";"Navy"
Row Type Value List
Bound Column 1
Allow Value List Edits Yes

This is the same as I did for the list boxes on my old form. I can't figure this one out!

I'm not 100% sure from your reply if you are aware of the essence of my question. It's something you need to address immediately before you proceed further.

How are you going to put the existing records into the new database?

This is not a trivial question, it needs a lot of thought and must be considered early on in your database construction not at the end.

 

[ka3pmw]

First I made a copy of the old database. Then I deleted the columns that I did not want. I then renamed the columns I wanted to the same as the new form.
Then I did a copy and paste from the old form to the new form. I got no errors.

 

[Uncle Gizmo]

I have provided you with a highly modified database structure. You are not going to be able to copy your old database into the new database.

 

[Uncle Gizmo]

I suggest you do some experiments and see if you can copy the data into the new database. Not much point in continuing until this issue is resolved.

 

[ka3pmw]

I suggest you do some experiments and see if you can copy the data into the new database. Not much point in continuing until this issue is resolved.

The only thing I copied was the data from the old Roster table. I did not copy the structures or anything else. I renamed the old fields so the copy and paste would accept them. I pasted that into the tblRoster table.

I'll do that again to be sure.

 

[ka3pmw]

The only thing I copied was the data from the old Roster table. I did not copy the structures or anything else. I renamed the old fields so the copy and paste would accept them. I pasted that into the tblRoster table.

I'll do that again to be sure.

I did it and it worked. Do you want me to send a sample to you?

 

[Uncle Gizmo]

I did it and it worked. Do you want me to send a sample to you?

Well, if it's essentially the working copy I posted earlier, then I'd like to see why its not working now.

 

[ka3pmw]

Well, if it's essentially the working copy I posted earlier, then I'd like to see why its not working now.

I also fixed the Affiliate members report, the Affiliate Summery, and added totals to report by Call sign and Last Name.

Check out the Switch Board too.

 

[Uncle Gizmo]

The form Paid "frmPaid" does not work properly because the "Years" are missing from the look up table "tlkpInfo" you will need to put them back in to get it to work properly.

 

[Uncle Gizmo]

I note that the roster table now contains around 140 records; however none of the members listed in the roster table have any associate records in the affiliate table, dues table and Mars table. This is what I mentioned earlier, the new design may well lose you data, which is an anathema to any database programmer. I hope you are aware of this issue.

 

[Uncle Gizmo]

You will have to be careful with that look up table "tlkpInfo" as it appears you are making changes to it without understanding the significance of those changes. I draw your attention to the field "tlkpInfoID" row 27 "Cash" the value listed under the field "tlkpInfoGrp" is 21 it should be 22.

 

[Uncle Gizmo]

The idea is that the report field for Class will show the number of Novice, Tech, HF Tech, General, Advanced, and Extra members there are, also the number of blanks in that list box.

To address your question here:- I had one entry in the lookup table which said "Missing" This missing was automatically added to a new record to give you this functionality. I note you have replaced the word missing with an empty entry. I would advise you not to have an "empty" entry as it makes it difficult to understand what's going on when you are initially building your database. You can change it to an "empty" entry later, once you have everything working properly. For now I strongly advise keeping some word or token in there so that you can easily see what's going on. As I said; removing the word "missing" makes it difficult to see what's going on. When I attempt to add a new record no "Missing" or 'empty blank cell' appears to be added. It looks like you have changed one of the default settings for one of the fields. If you want to maintain this feature then you will need to reinstate it.

 

[Uncle Gizmo]

How do I prevent the * line from showing in the affiliate and MARS sub forms?

In answer to this question... I think I suggested changing the data entry mode. The property that needs changing is called "Allow Additions" however if you set this to false you will no longer be able to add records. If you place a command button above the subform, have the command button cycle the "Allow Additions" from true/false. That way you would get the Look you require, and still be able to add records.

 

[Uncle Gizmo]

The form Dues "sfrmDues" does not work properly because the reference number 22 had been changed to 21 and some of the payment methods have been removed from the look up table "tlkpInfo" you will need to put them back in and make sure the reference number is correct to get it to work properly.

 

[Uncle Gizmo]

I noticed that in the query "qryAffiliateMembers" you have excluded records if they are Null. Again I already had this setup to use the default entry "Missing". I can tell you from experience that you are much better off with a proper entry like "Missing", and not relying on a null value or an empty string to give you information. If you don't like the word missing you can use n/a or at the push I suppose an empty string would do but I don't like it. It's undefined/indefinite. In other words easy to confuse and make a mistake, something you don't want to be doing at this stage in your db development.

 

[Uncle Gizmo]

I noticed that in the query "qryCountOfAffiliateRoster" you have excluded records if they are Null. Again I already had this setup to use the default entry "Missing". I refer to my previous answer where I state this is problematic. I don't understand why you have remove functionality, Functionality that you asked for? Now you do not have a count of members who do not have any affiliates? That was one of the questions you asked Here:-

 

[Uncle Gizmo]

The reason that the Mars Members report was not working was because you have changed the join in the query "qryMARSMembers". You have moved the join/link from the lookup table ID "tlkpInfoID" to your new field in that table "MarsIndex"... You need to re-attach it to the field MarsMars.

 

[Uncle Gizmo]

Once you have reversed your tinkering and got the database working again, I suggest you approach making further changes with caution.

Always make a backup before making even minor changes. I number my databases DatabaseName_1a.accdb, DatabaseName_1b.accdb, DatabaseName_1c.accdb... for minor changes and DatabaseName_2a.accdb, DatabaseName_2b.accdb for more substantial changes.

I noticed whilst using your latest database that it appeared a bit flakey. I had to close it and open it on more than one occasion. That is a hint that the VBA within the database is becoming confused! (for want of a better description). If you ever notice this yourself; indeed, if you are doing heavy concentrated development where you are deleting - creating loads of objects it's always a good idea..... to create a completely blank "empty database" and copy all the objects from your old database into this new blank database.

 

[ka3pmw]

In the Affiliate Query we only want the names of the people that are affiliated with that category as well as the summary. This is to track who is trained for certain positions.

Will do on the "tlkpInfo" We don't do PayPal and credit cards, that's why I took those out.

What I sent you was a copy of the table with 142 records removed for the privacy of the members. I neglected to run the query again after I removed them.

I will put the years back.

I think I have answered everything now. I have a public service net in about 20 min. It takes about an hour, then I can make the changes. I will be busy tomorrow helping my daughter do the baking for Christmas so I'll have to finish tonight.

Thanks and Merry Christmas.

 

[Uncle Gizmo]

I note that the affiliate table, and one or possibly two of the other tables have some blank cells in them. You need to add indexes to prevent null values being added to the table.