Stub method is giving me issues

[mGraham]

I am using the stub method of managing additions and deletions from the database but even after adding a day's worth of diagnostic checks to the system I still can't pinpoint the issue, I don't know if Access is interfering somehow and causing my dictionary to get corrupted or what, the whole thing is written in VBA as I tried to cut Access out of the process as much as possible because it has honestly been the bane of my existence through this whole process, I would be happy to provide as much information as I can but I was hoping someone here had some experience with stubs and could give me a few pointers as to how I might be getting unexpected data into the stub creation process, the AI keeps telling me the problem is with the hydration process but diagnostics don't seem to reveal the issue, I am also woefully ignorant of workspaces and the whole DAO library in general and if I'm being honest I wouldn't have gotten this far without the copilot AI.

 

[The_Doc_Man]

What we first will need is the symptoms that you DO see because that will help us focus on the problem in the code - if that is where it is.

What we need are the error messages that let you know it isn't working. In other words, something to trace.

 

[arnelgp]

why do you think Access is a bane to you?

 

[cheekybuddha]

Out of curiosity, what is your purpose in populating a dictionary with table data here?

 

[mGraham]

why do you think Access is a bane to you?

My Background is in .Net game software development specifically the frameworks, so access tries to handle things that I expect to I ended up writing an entire metadata engine to bypass Access in the search functions of the database and also to allow ease of development for the bosses brother to be able to add any search criteria he wanted without having to know how to code.

 

[mGraham]

Out of curiosity, what is your purpose in populating a dictionary with table data here?

the whole stub system is meant to create a safe and Administrator centered approach to managing the entries and deletions in a legacy table 50+ columns that stores company info location info employee info and sales info I have added IsStub [Yes\No] and StubType [Short Text] columns and he wants two levels of delete protection and admin approvals of all additions and deletions at this point the stub is being created and I was getting mismatches that shouldn't have been mismatching in regards to the StubType which I've confirmed in the table design view multiple times so the AI suggested a hydration method which is why I'm here I'm not convinced that the AI fully understands the problem and may be running around in circles trying to diagnose it and as I know very little about this method or Access and the DAO library I am afraid that I put myself up a creek

 

[Gasman]

I have found that it can take several attempts to get something correct from an AI, and even then, you have to know what is wrong with their suggestions. If you do not know that, then you are shooting blindfolded. If you hit the target, it is just luck.

 

[mGraham]

What we first will need is the symptoms that you DO see because that will help us focus on the problem in the code - if that is where it is.

What we need are the error messages that let you know it isn't working. In other words, something to trace.

this is my base level search form on clicking the New Company button it is suppose to create a new stub and open the company Display form to enter the data which was working before I had to provide admin approval of additions and deletions which led down the stub road

iv attached the whole path and added a few notes for clarification if you need anything else i would be happy to provide
Thank you for your time and consideration

 

[mGraham]

I have found that it can take several attempts to get something correct from an AI, and even then, you have to know what is wrong with their suggestions. If you do not know that, then you are shooting blindfolded. If you hit the target, it is just luck.

yea I constantly have to remind it of the context and intended functionality of the system that is one of the reasons I'm looking for a real intelligence here that can break me out of this diagnostics cycle

 

[mGraham]

What we first will need is the symptoms that you DO see because that will help us focus on the problem in the code - if that is where it is.

What we need are the error messages that let you know it isn't working. In other words, something to trace.

also here are the most relevant debug prints
>> ValidateStubDict entered
!! ValidateStubDict: stubDict is Nothing
!! CreateCompanyStub: stubValues failed validation
!! CreateCompanyStub failed: -2147220500 - CreateStub returned invalid stub
>> Logging to ActivityLog - about to .AddNew
>> LogActivity .Update succeeded
!! LaunchEntryFlow: stubValues is Nothing
!! LaunchEntryFlow ERROR -2147220498: CreateCompanyStub returned Nothing

 

[The_Doc_Man]

managing the entries and deletions in a legacy table 50+ columns that stores company info location info employee info and sales info

If this statement means what I think it means, part of the problem is normalizing your tables because what you have described is a flat-file layout such as often occurs in Excel environments.

I further reiterate that without getting a few specific symptoms of your perceived failure, we are stuck with no starting point of attack. I can sit here in the dark about your code and say "Yep, doesn't work." But only because you SAID it doesn't work. The problem with posting code is that it will take a long time to read through it with enough detail to see what is being done. And even THAT doesn't really tell us what is wrong because we don't see the dataset you feed to that code.

The best results for consulting this forum on technical or operational issues is to give us an overview of the problem, which is going to depend, not on your programming skills but on your communications skills. Help us to help you by giving us an English-language discussion of what you were trying to do, but avoid getting into detailed technical programming discussions while explaining the purpose and context of this application.

 

[The_Doc_Man]

OK, your response crossed mine.

Error -2147220500 is, in Hexadecimal, 800403EC, which suggests you were using SAS. It says a process flow creation failed.
Error -2147220498 is, in Hexadecimal, 800403EE, which suggests that SAS attempted but failed to validate a license.

Are you using the SAS package (a statistical analysis software package)?

The "is Nothing" and "invalid stub" messages suggest that you failed to create an object and therefore, when you attempted to set an object variable, that failed. This all seems to relate to code libraries being improperly referenced - or not referenced at all. In your VBA code window, the menu bar contains the word Tools. If you click that, you get a list including References. If you click that, you get a complex box with a list of known references that each have a check-mark by them. If you check the reference, then Access will attempt to use that reference when resolving your code. I find it unlikely that your code would even compile if your references are wrong, but if your problem is ambiguous references (i.e. two libraries have name overlaps), that will compile. It just has bad odds of doing anything useful.

 

[The_Doc_Man]

On further analysis, error 0x80xx03EC and 0x80xx03EE both suggest improper configuration of something. As a generic error where xx=00, these are device-driver errors. Where xx is not 00, they suggest a corrupted, partial, or failed installation.

Since we don't know what add-in packages you are using, it is going to be hard to decide a more specific meaning.

 

[mGraham]

OK, your response crossed mine.

Error -2147220500 is, in Hexadecimal, 800403EC, which suggests you were using SAS. It says a process flow creation failed.
Error -2147220498 is, in Hexadecimal, 800403EE, which suggests that SAS attempted but failed to validate a license.

Are you using the SAS package (a statistical analysis software package)?

The "is Nothing" and "invalid stub" messages suggest that you failed to create an object and therefore, when you attempted to set an object variable, that failed. This all seems to relate to code libraries being improperly referenced - or not referenced at all. In your VBA code window, the menu bar contains the word Tools. If you click that, you get a list including References. If you click that, you get a complex box with a list of known references that each have a check-mark by them. If you check the reference, then Access will attempt to use that reference when resolving your code. I find it unlikely that your code would even compile if your references are wrong, but if your problem is ambiguous references (i.e. two libraries have name overlaps), that will compile. It just has bad odds of doing anything useful.

the reference libraries are good I'm not sure why that shows in Hex but those debugs were added via instruction from the AI, these are the ones I believe are most telling and confusing to me
>> ValidateStubDict entered
!! ValidateStubDict: stubDict is Nothing
I don't know why it is failing the validation as far as the ai can tell it is created populated and passed correctly but for some reason there is a problem somewhere in the StubDict or the StubType or both and I don't know enough about how the DAO library works or what is happening to the dictionary object that is causing it to not validate, the use of the stubDict is to store all the values that need to be preloaded into the stub prior to user input and save prompt the hydrate stub was suggested by the AI because i use two different forms to populate the dictionary before the stub is finalized

 

[Pat Hartman]

the whole thing is written in VBA as I tried to cut Access out of the process as much as possible because it has honestly been the bane of my existence through this whole process,

Welcome to AWF.

You might want to spend some time actually trying to understand how Access works. Access is a RAD tool. It is supposed to do things for you. That is the point. It also looks like your tables are not designed properly. That is a common problem when people who are accustomed to designing web apps try to use a relational database platform.

One thing you don't seem to know is that tables are arrays. You don't need to create records first in a dictionary. This may be good practice for a web page but it is a complete waste of time when using Access.

Why are you even using Access at all? Did someone tell you that this is their preferred platform? If so, they are going to be terribly disappointed by what you are doing.

Compilers are very good at analyzing code. People are not. You also have not posted your schema.

Having come to Access from the mainframe world myself, I can appreciate how difficult it can be to get your head around how to control Access so your first inclination will always be to write code because you already know how to write code and VBA is simple enough that you can work out how to use code to control some things but you can't use code to stop Access from doing what Access wants to do and that is why you really should not continue on this path. Stop, take a breath. Either stop using Access or take the time to learn how it works so you can stop fighting it. I've been developing for 50+ years. I've written my million lines of code. I don't need the practice. Once you come to grips with that, Access will be your friend. Code is your last stop on the train to control Code will mostly be used to validate data or handle batch processes like creating invoices for the month and printing them all out or emailing them to clients. Your first stop on the control train is queries, then property settings, then VBA functions, then and only then you get to write code.

I'm going to attach a link to some videos and the database I created to show how form and report events work. The database comes with a sample database that you can modify as you find additional events you want to explore. I made the videos and database because you are not alone. Many people have no clue how form events work or what you might use them for. If you know what an "exit" is, that is how you should look at them. Microsoft has written thousands of lines of code to control how the form works generically. The events are "exits" that it gives you so that you can add your own code at relevant places as Access is processing the form. The most important and little understood event of all is the form's BeforeUpdate event. That event is the last one that runs BEFORE a record is saved. THIS is the event where you put your validation code so that you can ensure the data is correct and present so you can decide to PREVENT Access from saving the record if there is an error or you can ALLOW Access to save the edited record. Most people who come to Access with some programming experience always want to stop Access from saving records. You can't UNLESS you put your code into the correct event which is the form's BeforeUpdate event.

PLEASE don't just jump into the database. Watch at least one of the videos so you understand how the logging app works.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

PS, you will ultimately get better help if you are willing to post the entire application. Sometimes we can debug air code or come up with suggestions because between us all, we've made pretty much every dumb mistake it is possible to make but having a "working" platform makes the task ever so much easier.

 

[The_Doc_Man]

I'm not sure why that shows in Hex

It didn't. I translated to hex because that is how you decompose a status return to its components. In hex, the leading 8 says "non-fatal error." The fatal ones begin with "C". The next 3 digits identify the failing "facility". The digits after that are the actual error code.

As to the rest of what you said, I am no closer to understanding the problem. It's a case of being unable to see the forest for the trees because you have (to carry on the metaphor) too much underbrush in your discussion.

Try explaining your project as seen from an altitude of 10,000 feet. Don't tell us how you approach the problem. Tell us, from that high-level view, what IS the problem you are attacking. Talking about hydration, you could be dealing with athletes who need more Gatorade, or you could be talking about farm irrigation, or you could be dealing with first aid burn treatment, or you could be talking about a chemical synthesis that involves mixing in water and some hygroscopic chemical.

 

[CJ_London]

my understanding is hydration is simply populating a recordset - once a record is ‘full’ it is hydrated. E.g importing data from one or more sources to populate one or more tables

 

[mGraham]

It didn't. I translated to hex because that is how you decompose a status return to its components. In hex, the leading 8 says "non-fatal error." The fatal ones begin with "C". The next 3 digits identify the failing "facility". The digits after that are the actual error code.

As to the rest of what you said, I am no closer to understanding the problem. It's a case of being unable to see the forest for the trees because you have (to carry on the metaphor) too much underbrush in your discussion.

Try explaining your project as seen from an altitude of 10,000 feet. Don't tell us how you approach the problem. Tell us, from that high-level view, what IS the problem you are attacking. Talking about hydration, you could be dealing with athletes who need more Gatorade, or you could be talking about farm irrigation, or you could be dealing with first aid burn treatment, or you could be talking about a chemical synthesis that involves mixing in water and some hygroscopic chemical.

I guess I don't know what you want from me, I uploaded the functions and subroutines from the button click to the function I think might be the point of failure or at least lead to the point of failure, the error occurs in a higher level function and I've been tracing it to the dictionary object I mentioned but don't know why it is not validating, as for hydration I am using the term the AI did hoping someone will know about it because I certainly haven't figured it out yet.

as for an explanation of the project it is a database with a single table that uses reference ID# to point at data deeper in the table so my rows are like 50 or something columns long this is an old database I'm trying to repair as I go along so their are a number of helper functions that I have already debugged for that purpose and can provide them if anyone thinks it will help, so to continue it is a database that I have already worked out the search and display functionality and it only requires stress testing now I am working on the add edit and delete functionality and as the boss was telling me he wants an Admin interface that allows him to approve of all changes to the database I wasn't sure how exactly to go about this as I'm unfamiliar with access which is what the database was originally programmed in so I decided to consult with the AI about developing a system that would provide that functionality and have been on that road since, so now when I click the New Company button it calls a function called LaunchEntryFlow (uploaded earlier) that returns a true or false value and the false return triggers my error because it is failing to validate the dictionary object, the validation only checks if the primary key was transferred into the dictionary the check is saying that the dictionary = Nothing but I can't seem to track down why, I am happy to provide any other code that anyone might need I've been tracking this thing down for the better part of 48 hours now and am more than happy to take any suggestions.

 

[mGraham]

PS, you will ultimately get better help if you are willing to post the entire application. Sometimes we can debug air code or come up with suggestions because between us all, we've made pretty much every dumb mistake it is possible to make but having a "working" platform makes the task ever so much easier.

I would upload the whole project but the table is technically not mine to share and all you would do is click the new company button and a message box after telling you that the LaunchEntryFlow function (uploaded earlier) returned the false value, some games rely on a nested database to handle maps and inventory and animations and so on, and I did consult with the AI about some things like using the .Tag line to pass metadata and that the PK isn't assigned to a stub until the .Update event and that I needed to use DAO to make access play nice, but really, I've been learning as I go. Thanks for the videos I'm sure they will come in handy if you want anything else from me feel free to ask

 

[Pat Hartman]

I'm not convinced that the AI fully understands the problem and may be running around in circles trying to diagnose it

The AI knows very little about VBA and less about how Access works. All it can go on is what it finds on the web so it is learning from questions with more bad answers than good if you base that assessment on the number of posts in any given thread. In the past few years there have been changes to the web software that allow askers to mark a post as "the answer" but the AI's will still have trouble knowing if the answer is completely in the marked thread since humans are sloppy with this stuff.

Having the boss approve all changes to the database is a ridiculous waste of his time. The request is almost certainly driven by the fact that the existing application (as well as your new version) does NO ERROR CHECKING and therefore, the data is crap and unreliable. I posted a link that should help you to understand how validation should be done using the BeforeUpdate event.

If you had asked humans a question of how to satisfy the boss's request, the answer would have been to use a shadow table. At least that would have been the answer once we knew that the tables were not properly normalized. Shadow tables are still possible but more difficult with multiple related tables as you would find in a real relational database. Then your edit forms could be bound to the shadow table(s). To add a new record it goes into the shadow table as pending approval. When the boss approves, the app copies the row to the production table and deletes the row in the shadow table. Only a few lines of code and two queries to make that happen. If someone wants to edit an existing record, you have an edit button on the non editable view form which copies the row to the shadow table and marks it pending so you edit it there. Again a trivial piece of code although if you intend to use the shadow table to manage updates, you should add a flag so that when the boss approves the record, you know whether to run an append query or an update query. Maybe it's not too late. Maybe you should get rid of all your code and switch to that method now.