Transforming a Private VBA Code to Public Function

[Reese]

That is the problem. You need to call the Sub, not the module.

Aha! I had the module and the sub named the same. When you said that the name had to be unique, my thinking was that their naming was one and the same. I didn't realize that Access recognized the two things as separate. Now it seems to work fine. Thanks!

 

[Reese]

The advantages of this approach are manifold. Firstly, no code is required. Secondly the ages and the displayed text in the combo can be edited in a table instead of being hard coded.

I understand where you are coming from and perhaps that might have been a good idea from the start. Still, what's done is done and I don't want to go back and change things (there are already over 450 records in the database). So *shrug*.

Plus, I DON'T want people to manipulate the information in the table. This is a complex database and it's easy for users to accidentally mess something up if they were to change something in the table (there are over 150 fields in the main table, and yes everything is normalized and all of the fields are necessary).

Thanks for the suggestion, though.

 

[Galaxiom]

(there are over 150 fields in the main table, and yes everything is normalized and all of the fields are necessary).

Although I am prepared to consider you might be right, we have heard many similar claims on here before and I can't remember one where it turned out that the data was fully normalized.

 

[Reese]

Although I am prepared to consider you might be right, we have heard many similar claims on here before and I can't remember one where it turned out that the data was fully normalized.

Well, the table is for all of our education programs: there are 8 different ones, each requiring slightly different information. While, yes, I could have made different tables for each program, they require enough shared information that putting them in one table is FAR easier than different tables--especially when it comes to searches and summarized reports.

The main program--ZooMobiles--is an offsite program, so it requires event time, date, address information, ZooMobile type, misc. notes, payment information (including payment type, date paid, check number, etc.), who facilitated it, which animals went on it (8 fields just from that, because up to 8 animals might be on a ZooMobile), the number of participants, etc. Then there are similar things to consider with the other programs.

Then there's background fields that the user never directly interacts with, such as the AgeCode fields and a check box that is checked behind the scenes indicating that the post-event information has all been entered after the event has occured (this is important because a query automatically displays events that don't have it checked and where the date has already passed).

After a while it all adds up. So I understand that it may SOUND like too big, with the possible exception of things like the AgeCodes (which perhaps your suggestion might have been better), all of the fields are indeed necessary.

So perhaps the number of fields could have been brought down to 130 or 140, but definitely not less than that.

 

[Galaxiom]

As expected, your data is not normalized.

Here is one glaring example where you have repeating groups.

which animals went on it (8 fields just from that, because up to 8 animals might be on a ZooMobile),

These should be in a junction table. One record per animal per ZooMobile record.

one table is FAR easier than different tables--especially when it comes to searches and summarized reports

You might think so now but you will pay a premium as you get further into the project.

How would you do a report for which records a particular animal was included in? Search for them in each of the eight fields then union it?

 

[BlueIshDan]

Good catch Galaxiom. Never would have thought the module and sub would be the same name!

 

[Reese]

When I said that having everything in one table is easier than several, I wasn't specifically referring to the animal lists. I was referring to the idea of large, separate tables for ZooMobiles, Guided Tours, Wild Encounters, etc. And in that case I know it is easier to have them all in one--because that would mean for summarized total education program numbers and searches I'd have to merge them all together first before I could do anything.

These should be in a junction table. One record per animal per ZooMobile record.

How would you do a report for which records a particular animal was included in? Search for them in each of the eight fields then union it?

I admit that I hadn't even thought of a junction table for the animal lists, and yes, perhaps it would have made the animal list report easier. I don't need to search animal by animal, just a summarized list of how often each animal is utilized between dates X and Y. And, yes, I did use a union query.

But, in hindsight, that would complicate other functions. Currently I have a series of queries that--if I am booking a new event for a returning client--displays certain information from that client's previous event, including animals. If I had the animal lists on a separate table I would need to include that table in the query as well, and use a continuous subform to display the last event's animal list in the report that is used to print out an in-house invoice that has all of that event's information on one page. (This page is already crowded, the required layout of a continuous subform would make it worse.)

Admittedly I was learning while I was making this database, and I knew the least when I was making the building blocks, which has skewed everything else. In hindsight there are several behind-the-scenes functions that I would have done differently but I feel confident in my design when it comes to the fields that users directly interact with.

I know I am being defensive but several times people have made suggestions that end up leading them to say "you have too many fields" without knowing all of the features and needs that I was trying to address.

I apologize for that and thank you for your advise, because I know that it is well intended.

 

[Reese]

I can see why you believe that the animal lists should be structured in a separate table. I have already explained why I didn't (I didn't think of it) and why I feel, in hindsight, why it wouldn't have helped.

Here is one glaring example where you have repeating groups.

To try and improve I am willing to listen to suggestions. What are your other examples that you can think of? I shall try to keep an open mind and accept your ideas.