Troubleshooting VBA code - (1 Viewer)

[dgreen]

This is likely to be a multi-part posting. What the heck is causing this weird behavior?

My code to open a form is supposed to close the navigation form, log that the form is closed (all of this works right) and then open the Contacts form (where the code is breaking down).

What happens when it's supposed to open the Contacts form, the next step jumps to a function fosUserName() as String (screen capture below). I only hit F8 once from the above view to the below view. This doesn't make sense to me.

Then it keeps getting weirder. It cycles through this code 5 times (each time hitting the Exit Function).

It then moves on to a subform (f_Contacts_Experience_Relationships) of the Contacts form. I have Filter As You Types on the main form and other subforms so why is it just picking on this form?

After the FAYT function runs, it moves to a Public Function IsBlank(arg as Variant) as Boolean. It cycles thru this code 50+ times before....

Eventually opening not just the form requested (f_Contacts) but also another form or query or table (whichever one just happens to be highlighted at the time.

 

[arnelgp]

Form_OPen event is Not the the best thing to initialize Any variables since those variables "does not exist" on this event.
move your code to the Load/Activate/Timer event, variables are created on these events.

 

[dgreen]

Moved the FAYT code to Form_Load. Thanks for the observation.... however, the code still goes to fosUserName(). Same errors as above, to include it going to the subform FAYT Form_Load code.

move your code to the Load/Activate/Timer event, variables are created on these events.

 

[arnelgp]

add a Breakpoint at the cmdContacts Click event. use F8 to start tracing which form is making those function call.

 

[The_Doc_Man]

Repetitious calling of that fOSUserName() function makes me think there is a comparison going on in a query that references the function. So maybe are you looking at trying to find a particular user by comparing the fOSUserName to something in a table? And... if it repeats 5 times, do you happen to have 5 users in the table at the moment? Or perhaps is the person you wanted the 5th person in the table? Either one would make sense.

 

[Micron]

We might have to see a db copy to troubleshoot. Is there any code in a subform that can cause this? A subform loads first, then the main form. If you don't know that, you can end up with weird behaviors.

 

[dgreen]

add a Breakpoint at the cmdContacts Click event. use F8 to start tracing which form is making those function call.

What you see above in the initial posting is that tracing using F8. In sequence, nothing skipped.

 

[dgreen]

The form's query has over 700 people in it. I am using fosusername in the query though. There aren't any filters or criteria though opening it up.

Repetitious calling of that fOSUserName() function makes me think there is a comparison going on in a query that references the function. So maybe are you looking at trying to find a particular user by comparing the fOSUserName to something in a table? And... if it repeats 5 times, do you happen to have 5 users in the table at the moment? Or perhaps is the person you wanted the 5th person in the table? Either one would make sense.

 

[dgreen]

We might have to see a db copy to troubleshoot. Is there any code in a subform that can cause this? A subform loads first, then the main form. If you don't know that, you can end up with weird behaviors.

There are multiple subform on the main form. Not sure why the first one only gets opened, in the flow.

If there was code causing it, wouldn't the F8 step have caught it?

I tried earlier today to reduce the Db to its essentials to try and narrow down the issues. I'll work on it tmrw.

 

[Micron]

I am using fosusername in the query though.

I would think that will call the function once for each record. When you use a function to return a value to a query record, it will fire for every record.

If there was code causing it, wouldn't the F8 step have caught it?

Not necessarily I'd say - because you showed us the code behind the main form, not the subform, no? Any subform code will run first. However, as I mentioned here I'd say you've revealed the reason for the fosUserName function running repeatedly. I've used that one several times; it was never meant to be used in a query.

 

[dgreen]

I would think that will call the function once for each record. When you use a function to return a value to a query record, it will fire for every record.

Not necessarily I'd say - because you showed us the code behind the main form, not the subform, no? Any subform code will run first. However, as I mentioned here I'd say you've revealed the reason for the fosUserName function running repeatedly. I've used that one several times; it was never meant to be used in a query.

I use the fosusername to find the user's pictures folder, where I dump a pictures library for their local computer.

Only one of the subforms appears to fire, apparently on the FAYT code. All of the other subforms, which also have different FAYT code, dont appear to fire.

Will continue discussion tomorrow.

 

[dgreen]

The form's query has over 700 people in it. I am using fosusername in the query though. There aren't any filters or criteria though opening it up.

But the subform that gets opened up first does have less.

 

[Micron]

I use the fosusername to find the user's pictures folder, where I dump a pictures library for their local computer.

A DLookup would possibly suffice, using the value of a user variable that the function has passed to it. Some use TempVars rather than that function. As I mentioned, I wouldn't use that function in a query that can return more than one record.

 

[dgreen]

Still troubleshooting. Some of my notes. 1st part is about the IsBlank. 2nd issue is why an extra object is opening (haven't started to narrow down this one)

1st: Issue (Why is isblank occurring?)
Subforms open before the main form.
Also looks like they open in the sequence of the tabs on the main form (left to right)
IsBlank appears to only happen on the f_Contacts_Experience_Relationship subform.
67 records in the Contacts_Experience_Relationship table.
Varying degrees of population. None are completely empty.
Only 4 with a blank organization.
The form has a Load event that initializes a FilterAsYouType event on a combo box.
It’s only after this event occurs that the IsBlank function is triggered. (Screen capture from 1st posting shows the code)
Other subforms with that also have FAYT initialize events open fine without the IsBlank triggering.

2nd Issue (Why any highlighted Access object also opens when I open f_Contacts)
Visual of issue in 1st posting. Haven't figured outanything on this one.

 

[Micron]

I don't see how we can possibly answer those questions without sitting in your chair, or at least examining a db copy. If it is all your code (or at least you understand what it does) then the route to take is step through everything from the beginning, and maybe use the locals and watch windows as well. However, this also requires that you know about the order of events. Now that you know a subform opens first, you'd start by stepping through that code, not the main form, ensuring that you step through all of the events. You might also eliminate portions (e.g. don't load a bunch of subforms on a tab control) and see what happens. Any issue that goes away points to one of the objects being involved, be that a form or a query or expression involved in that form, so you put them back one by one (if that will allow other things to work) and see which one is causing an issue. It is a process of elimination at times.

 

[dgreen]

Acknowledge.

It is a process of elimination at times.

Going to save a new copy of the database and start the elimination process.

 

[dgreen]

File Uploaded

I realize I'm asking for a lot and some potentially deep researching....

Because of how inter-related my forms are with other elements of the database, I've left in all queries and modules. I've reduced the tables and forms to Navigation and the Contacts form that for some reason it triggering a spellchecker error.

It happens for this database when you have opened f_Contacts and then click from one record to another.

I would love for someone to show me how simple this was to fix. I've been struggling with this for months.

 

[Micron]

so I open the db, click on the button to open the contacts form, which opens....now what's supposed to happen/be done?
EDIT - never mind, I see that you said "when navigating records"

P.S. looks to me like the function is being called more than once, first time it seems to have an empty string (or perhaps rtf/html character or a CrLf) and the second time, it's probably null. Still looking...

 

[Micron]

That's awfully convoluted and hard to follow. In the property sheet I see lots of events defined, yet when I click to view them, there's nothing there except for the Sub and End sub statements. Not sure if you deleted them or that is about corruption but it implies that there were a lot of events that could call the same function(s) yet after about 30 minutes I still haven't figured out why the spell check function gets called more than once. I have to put this aside for a while. Maybe this info will help someone else if they chime in before I can look some more. I also see you have a timer event for 1000 but no event defined, so I guess that's not going to be an issue. If I pick it up again, I will do a c/r before attempting anything.

 

[dgreen]

Went thru the vba code and put things in A-Z order for the Contact form and the subforms.
Removed the 'ghost' Events - where there was something shows as an [Event Procedure] but nothing was supposed to be there.

Maybe it helps with readability. Still having the same issue where the SpellChecker error message shows up when you click between records on the Contact form.

So, try this.... Remove the subform (f_Contacts_Experience_Relationship) from the form (F_Contacts). The error goes away.