Find Record Combobox - Strange Behavior 1

[xweyer]

I have a tabbed form "Master_Customer". There is form to add customers on one tab and a form to add customer's accounts on another tab. After adding a customer on the first form I want to able to add their accounts on the other tab/form.

The problem I am having is this. After a new customer is added I requery the "Accounts" form. There is a combobox on the "Accounts" form provided to select any given customer. The combobox was created using the Access combobox wizard (and so draws its data from a cloned version of the Accounts form's recordset). The combobox is displaying the newly created Customer(s) properly but does not locate the new customer's record when their name is selected from the dropdown (the record displayed on the form does not change). The combobox works normally for all other (non-new) customers but won't locate any new customers until the form has been opened and closed.

To add to the mystery when I view the form's record source
(while still in form view) the entries for the new customers are there but are not retrievable (until the form has been closed and reopened).

Does anyone have any ideas as to what might be going on here and/or how to correct it?

 

[xweyer]

Here's the story. If you want this to work you should have the requery performed in the On Change event of the Master_Customer form's Tab Control. e.g.

If Me!TabCtl0 = [The number of the tab index of the tab containing the Accounts form] Then Me.Accounts.Requery

Just be careful that you don't mistake the tab's Page Number for its Tab Index as they are two separate properties.

If you want to go for overkill and requery the combobox itself directly for good measure you could also do something like below (but it should be unnecessary).

If Me!TabCtl0 = [The number of the tab index of the tab containing the Accounts form] Then Me.Accounts.Form!MyCombo.Requery

 

[TheAceMan1]

How are ya xweyer . . .

Some assemblence of your table structure is in order. Do you have a [blue]Customers[/blue] table and an [blue]Accounts[/blue] table in a relationship ... or is this one table ... or what?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[xweyer]

Hi Aceman1,

Well, this is strangely fortuitous turn of events. To answer your question Customers and Accounts are two separate tables with the "Customers_Id" as the linking field.

But it's pretty much irrelevant since the drop down is now working perfectly.

Let me explain. Between my two posts above I finally figured out what is was that I was doing wrong. I had the "If Then" statement referencing the Page Name (which was Page1" of the "Accounts" tab instead of it's Page Index (and not the tab index as I mistakenly referred to it in the last post - ARRGH!).

On my way in to report that I'd got the drop down working correctly I noticed that my post was pretty far down in the queue with no responses so I figured either I'd done a bad job explaining the issue or it was a fairly unusual one.

So I thought I might as well have a little fun and answer my own question. I planned on following it up with a third post that would have made it more apparent that that was what I was doing. I know it's a bit lame - but I thought that I was probably the only one who'd ever see it and at least there would be an answer there if anyone else was searching on a similar problem. (Hey, after staring at VBA code for hours who wouldn't be punchy at the end of the day). Anyway I got interrupted before I could enter the third post and your response arrived in the meantime.

But that's a good thing because your handle rang a bell. I'd visited well over 40 sites while Googling in search an answer but I seemed to associated your name with a post that led me to using the on change event of tab control as the proper location of the "requery" code. So I checked my history and sure enough I'd visited this Tek-tips thread early on (thread702-1442715).

So I'm glad you showed up in time to get credit for a valuable post made in the past. Kind of a post, post, past credit so to speak.

Got to love serendipity when it rears its unlikely head.

A couple of final thing or two for anyone patient enough to have waded through my ramblings to this point. The line that worked for me was in the form..

"If Me!TabCtl{The Number of the Tab Control] = [The number of the PAGE INDEX of the tab containing the Accounts form] Then Me.Accounts.Requery"

Be careful because the page name can be a page number and there is a page caption and a tab index as well.

Another thing that I didn't notice that might have tipped me to the problem sooner was when I was stepping through the code after selecting the "Accounts" tab and triggering the event was that the "If Then" line was being highlighted and so I assumed executed. However what I didn't notice was that only the "IF" half of the line that was highlighted (in other words the THEN was never happening because of the "Page Number" mistake).

I don't usually do one line if/thens so I wasn't aware of this pretty neat debugging behavior.

Finally, thanks again to Aceman1 for the code and event location.